Re: optimizing advice

On Dec 1, 2009, at 1:34 AM, Rüdiger Sörensen wrote:

> dear all,
>
> I am building a database that will be really huge and grow rapidly. It holds data from satellite observations. Data is imported via a java application. The import is organized via files, that are parsed by the application; each file hods the data of one orbit of the satellite.
> One of the tables will grow by about 40,000 rows per orbit, there are roughly 13 orbits a day. The import of one day (13 orbits) into the database takes 10 minutes at the moment. I will have to import data back to the year 2000 or even older.
> I think that there will be a performance issue when the table under question grows, so I partitioned it using a timestamp column and one child table per quarter. Unfortunately, the import of 13 orbits now takes 1 hour instead of 10 minutes as before. I can live with that, if the import time will not grow sigificantly as the table grows further.
>
>
>
> anybody with comments/advice?

Make sure you're running the latest release of postgresql.

Import directly into the appropriate child table, rather than relying on redirecting inserts into the parent table.

Do the import using copy protocol, not insert. I don't know whether, or how well, java supports that but it's something you really want to be using. It's quite a lot faster than multiple inserts - especially when there's any network latency between you and the database - and somewhat faster than inserts with multiple sets of values.

For the bulk imports of old data, import the data first, then build the indexes and add constraints later. Disable autovacuum during the initial bulk import. Also, consider turning fsync off for those imports (less of a win on modern versions, but every little helps).

Take a look at http://www.postgresql.org/docs/current/interactive/populate.html

Do all the usual tuning stuff - http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server , http://wiki.postgresql.org/wiki/Performance_Optimization

Cheers,
Steve