Message-ID: <54CFCFA7.1050301@pinpointresearch.com>
Date: Mon, 02 Feb 2015 11:27:35 -0800
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.4.0
MIME-Version: 1.0
To: Naresh Soni <jmnaresh@gmail.com>, pgsql-admin@postgresql.org
Subject: Re: Hi Community
References: 
 <CADg8u6kEOrqzmVE8BY34RAE_XzxQE9ZvjFz--v1q7jZNxj58wg@mail.gmail.com>
In-Reply-To: 
 <CADg8u6kEOrqzmVE8BY34RAE_XzxQE9ZvjFz--v1q7jZNxj58wg@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------050100090506010907020303"
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

This is a multi-part message in MIME format.
--------------050100090506010907020303
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

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


--------------050100090506010907020303
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html>
  <head>
    <meta content=3D"text/html; charset=3Dutf-8" http-equiv=3D"Content-Ty=
pe">
  </head>
  <body bgcolor=3D"#FFFFFF" text=3D"#000000">
    <div class=3D"moz-cite-prefix">On 02/02/2015 03:18 AM, Naresh Soni
      wrote:<br>
    </div>
    <blockquote
cite=3D"mid:CADg8u6kEOrqzmVE8BY34RAE_XzxQE9ZvjFz--v1q7jZNxj58wg@mail.gmai=
l.com"
      type=3D"cite">
      <div dir=3D"ltr">Good Evening Community
        <div><br>
        </div>
        <div>This is my first question on the list, I wanted to ask if
          postgres can handle multi millions records? =C2=A0for example t=
here
          will be =C2=A01 million records per table per day, so 365 milli=
ons
          per year.</div>
        <div><br>
        </div>
        <div>Is yes, then please elaborate.=C2=A0</div>
        <div><br>
        </div>
        <br>
      </div>
    </blockquote>
    Yes. Maybe. But you will need to elaborate. (A plain text file can
    store millions of records, too. The use pattern is what matters.)<br>
    <br>
    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.<br>
    <br>
    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:<br>
    <br>
    <ul>
      <li>You say 1-million/day/table. How many tables (i.e. what is the
        total record count)?<br>
        <br>
      </li>
      <li>How long do you want to keep the data (total anticipated
        database size)?<br>
        <br>
      </li>
      <li>Can the data be easily partitioned? For example, can you set
        up child tables by month for query and archive efficiency?<br>
        <br>
      </li>
      <li>What sort of query mix do you anticipate? What portion of
        inserts, updates, selects,...? Lots of data-warehouse type
        queries?<br>
        <br>
      </li>
      <li>Is the schema fairly simple or are there lots of complex
        foreign-key constraints?<br>
        <br>
      </li>
      <li>What sort of performance do you need - fraction-of-a-second
        transactional response or overnight analytical runs?<br>
        <br>
      </li>
      <li>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.</li>
    </ul>
    <br>
    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.<br>
    <br>
    Welcome to the list.<br>
    <br>
    Cheers,<br>
    Steve<br>
    <br>
  </body>
</html>

--------------050100090506010907020303--