From: Steve Crawford <scrawford@pinpointresearch.com>
To: Naresh Soni <jmnaresh@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: Hi Community
Date: Mon, 02 Feb 2015 11:27:35 -0800
Message-ID: <54CFCFA7.1050301@pinpointresearch.com> (raw)
In-Reply-To: <CADg8u6kEOrqzmVE8BY34RAE_XzxQE9ZvjFz--v1q7jZNxj58wg@mail.gmail.com>
References: <CADg8u6kEOrqzmVE8BY34RAE_XzxQE9ZvjFz--v1q7jZNxj58wg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
On 02/02/2015 03:18 AM, Naresh Soni wrote:
> Good Evening Community
>
> This is my first question on the list, I wanted to ask if postgres can
> handle multi millions records? for example there will be 1 million
> records per table per day, so 365 millions per year.
>
> Is yes, then please elaborate.
>
>
Yes. Maybe. But you will need to elaborate. (A plain text file can store
millions of records, too. The use pattern is what matters.)
For reference, I have a modest database of about 350-million records
running on modest hardware of a several-year-old 4-core/24GB RAM/4-drive
RAID10 with battery-backup server and get mostly acceptable performance
with query peaks well over 1,000 TPS at about 2/3 writes.
The people here can give you lots of good advice with purchasing the
PostgreSQL Performance book being a good one. PostgreSQL is feature-rich
but you will need to design and choose appropriately. To get started a
few questions jump out:
* You say 1-million/day/table. How many tables (i.e. what is the total
record count)?
* How long do you want to keep the data (total anticipated database size)?
* Can the data be easily partitioned? For example, can you set up
child tables by month for query and archive efficiency?
* What sort of query mix do you anticipate? What portion of inserts,
updates, selects,...? Lots of data-warehouse type queries?
* Is the schema fairly simple or are there lots of complex foreign-key
constraints?
* What sort of performance do you need - fraction-of-a-second
transactional response or overnight analytical runs?
* What hardware can you throw at it? At one end I've run PostgreSQL on
a Raspberry Pi. At the other end, I'm about to migrate a database to
a 20-core/128GB machine with Intel SSD storage that can purportedly
match the TPS of 2,000 15k drives. (This is certainly not the upper
limit of hardware for running PostgreSQL). Each is appropriate for
its use-case.
In any case, don't let the number of options and features overwhelm you
- you don't need to optimize prematurely. Describe your use-case here
for some starting suggestions, get familiar with it, and adjust your
design and settings as necessary.
Welcome to the list.
Cheers,
Steve
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: scrawford@pinpointresearch.com, jmnaresh@gmail.com
Subject: Re: Hi Community
In-Reply-To: <54CFCFA7.1050301@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox