From: Craig Ringer <ringerc@ringerc.id.au>
To: roberthanco@o2.pl
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgresql - performance of using array in big database
Date: Wed, 08 Aug 2012 14:33:02 +0800
Message-ID: <5022081E.5080900@ringerc.id.au> (raw)
In-Reply-To: <50220337.25b3650a.64b9.4287SMTPIN_ADDED@mx.google.com>
References: <50220337.25b3650a.64b9.4287SMTPIN_ADDED@mx.google.com>
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
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-performance@postgresql.org
Cc: ringerc@ringerc.id.au, roberthanco@o2.pl
Subject: Re: Postgresql - performance of using array in big database
In-Reply-To: <5022081E.5080900@ringerc.id.au>
* 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