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.

Give this a shot: depesz.com/index.php/2011/01/03/waiting-for-9-1-unlogged-tables — sayap
– sayap, Commented May 26, 2011 at 5:26
Sounds like you are using the wrong tool for the job. Why use an ACID compliant database when ACID compliance is not what you want? Why not use something designed for speed like fallabs.com/kyotocabinet or hypertable.org — Eelke
– Eelke, Commented May 26, 2011 at 5:37
Which side of the equation would you like to tune first? the INSERTs or SELECTs? You've hit the biggies. Unlogged tables are going to be hot, but until then... have you increased your checkpoint_segments yet? — Sean
– Sean, Commented May 26, 2011 at 18:49
Inserting needs to be optimized. sayap, that is a great post. I'm testing this now in the 9.1beta. — drewrobb
– drewrobb, Commented May 27, 2011 at 1:49

Seth Robertson · Accepted Answer · 2011-05-26 05:31:26Z

2

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

edited May 26, 2011 at 5:31

answered May 26, 2011 at 5:24

Seth Robertson

31.7k8 gold badges67 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Guilherme Salgado · Accepted Answer · 2013-02-14 20:11:14Z

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.

answered Feb 14, 2013 at 20:11

Guilherme Salgado

1471 silver badge8 bronze badges

Collectives™ on Stack Overflow

Postgres optimize for insert/read only

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related