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
isimported via a java application. The import is organized via files, that are parsed by the application; each file
hodsthe 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
(13orbits) 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
timestampcolumn and one child table per quarter. Unfortunately, the import of 13 orbits now takes 1 hour instead of 10
minutesas 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
youreally want to be using. It's quite a lot faster than multiple inserts - especially when there's any network latency
betweenyou 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
autovacuumduring 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