Message-ID: <5022081E.5080900@ringerc.id.au>
Date: Wed, 08 Aug 2012 14:33:02 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120717 Thunderbird/14.0
MIME-Version: 1.0
To: roberthanco@o2.pl
CC: pgsql-performance@postgresql.org
Subject: Re: Postgresql - performance of using array in big database
References: <50220337.25b3650a.64b9.4287SMTPIN_ADDED@mx.google.com>
In-Reply-To: <50220337.25b3650a.64b9.4287SMTPIN_ADDED@mx.google.com>
Content-Type: multipart/alternative;
	boundary="------------010107040404060502010703"

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

On 08/03/2012 05:14 PM, roberthanco@o2.pl wrote:

 > It is read-only table so every integer column have an index.

First tip: Define the table without the indexes. INSERT your data, and 
only after it is inserted create your indexes.

Similarly, if you're making huge changes to the table you should 
consider dropping the indexes, making the changes, and re-creating the 
indexes. You might not have to drop the indexes if you aren't changing 
indexed fields, since HOT might save you, but it depends a lot on the 
specifics of the table's on-disk layout etc.

> The server is: 12 GB RAM, 1,5 TB SATA, 4 CORES. All server for postgres.
> There are many more tables in this database so RAM do not cover all 
> database.

OK, in that case more info on the disk subsystem is generally helpful. 
Disk spin speed, type? RAID configuration if any? eg:

   4 x 750GB 7200RPM Western Digital  Black SATA 3 HDDs in RAID 10 using 
the Linux 'md' raid driver

or

   2 x 1.5TB 7200RPM "Enterprise/near-line" SATA3 HDDs in RAID 1 using a 
Dell PARC xxxx controller with BBU in write-back cache mode.

... though if you're only bulk-inserting the BBU doesn't matter much.
> |
> | I wonder what option would be better in performance point of view.

I would advise you to test on a subset of your data. Try loading the 
same 50,000 records into different databases, one with each structure. 
Measure how long the load takes for each design, and how long the 
queries you need to run take to execute. Repeat the process with 500,000 
records and see if one design slows down more than the other design 
does. Etc.

> I need to make a good decision because import of this data will take 
> me a 20 days.

For the sheer size of data you have you might want to think about using 
pg_bulkload. If you can't or don't want to do that, then at least use 
COPY to load big batches of your data.

--
Craig Ringer

--------------010107040404060502010703
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">On 08/03/2012 05:14 PM,
      <a class="moz-txt-link-abbreviated" href="mailto:roberthanco@o2.pl">roberthanco@o2.pl</a> wrote:<br>
      <br>
      &gt; It is read-only table so every integer column have an index.<br>
      <br>
      First tip: Define the table without the indexes. INSERT your data,
      and only after it is inserted create your indexes.<br>
      <br>
      Similarly, if you're making huge changes to the table you should
      consider dropping the indexes, making the changes, and re-creating
      the indexes. You might not have to drop the indexes if you aren't
      changing indexed fields, since HOT might save you, but it depends
      a lot on the specifics of the table's on-disk layout etc.<br>
      <br>
    </div>
    <blockquote
      cite="mid:50220337.25b3650a.64b9.4287SMTPIN_ADDED@mx.google.com"
      type="cite">
      <meta http-equiv="Context-Type" content="text/html; charset=UTF-8">
      The server is: 12 GB RAM, 1,5 TB SATA, 4 CORES. All server for
      postgres.<br>
      There are many more tables in this database so RAM do not cover
      all database.<br>
    </blockquote>
    <br>
    OK, in that case more info on the disk subsystem is generally
    helpful. Disk spin speed, type? RAID configuration if any? eg:<br>
    <br>
      4 x 750GB 7200RPM Western Digital  Black SATA 3 HDDs in RAID 10
    using the Linux 'md' raid driver<br>
    <br>
    or<br>
    <br>
      2 x 1.5TB 7200RPM "Enterprise/near-line" SATA3 HDDs in RAID 1
    using a Dell PARC xxxx controller with BBU in write-back cache mode.<br>
    <br>
    ... though if you're only bulk-inserting the BBU doesn't matter
    much.<br>
    <blockquote
      cite="mid:50220337.25b3650a.64b9.4287SMTPIN_ADDED@mx.google.com"
      type="cite">
      <code><span class="pln"><br>
        </span></code>
      I wonder what option would be better in performance point of view.
      <br>
    </blockquote>
    <br>
    I would advise you to test on a subset of your data. Try loading the
    same 50,000 records into different databases, one with each
    structure. Measure how long the load takes for each design, and how
    long the queries you need to run take to execute. Repeat the process
    with 500,000 records and see if one design slows down more than the
    other design does. Etc.<br>
    <br>
    <blockquote
      cite="mid:50220337.25b3650a.64b9.4287SMTPIN_ADDED@mx.google.com"
      type="cite">
      I need to make a good decision because import of this data will
      take me a 20 days.<br>
    </blockquote>
    <br>
    For the sheer size of data you have you might want to think about
    using pg_bulkload. If you can't or don't want to do that, then at
    least use COPY to load big batches of your data. <br>
    <br>
    --<br>
    Craig Ringer<br>
  </body>
</html>

--------------010107040404060502010703--