Postgres optimize for insert/read only

Question

I'm working on a database with the following characteristics:

• Many inserts (in the range of 1k/second)
• Lots of indices on the data, complex joins
• NO Deletes or updates, only inserts, read and table drops
• I don't care if the reads to the database reflect accurate state
• Data isn't critical, I'm already running fsync=off

I already know a fair bit about postgres optimization, but I was hoping there might be some additional tricks that are more suited to my particular use case.

Answer 1

You can disable the WAL, perhaps by pointing it to /dev/null or RAMDISK. Note there is some speculation that you may not be able to restart the DB after even a clean stop, so I advise caution and testing.

Make sure you cluster your tables. Partitioning might help as well.

Certainly disable synchronous_commits.

http://wiki.postgresql.org/wiki/FAQ#How_do_I_tune_the_database_engine_for_better_performance.3F

Answer 2

You might want to use unlogged tables (available as of 9.1) for that. It's basically a table for which the WAL is disabled.