Performance Tuning PostgreSQL

Question

Keep in mind that I am a rookie in the world of sql/databases.

I am inserting/updating thousands of objects every second. Those objects are actively being queried for at multiple second intervals.

What are some basic things I should do to performance tune my (postgres) database?

Answer 1

It's a broad topic, so here's lots of stuff for you to read up on.

• EXPLAIN and EXPLAIN ANALYZE is extremely useful for understanding what's going on in your db-engine
• Make sure relevant columns are indexed
• Make sure irrelevant columns are not indexed (insert/update-performance can go down the drain if too many indexes must be updated)
• Make sure your postgres.conf is tuned properly
• Know what work_mem is, and how it affects your queries (mostly useful for larger queries)
• Make sure your database is properly normalized
• VACUUM for clearing out old data
• ANALYZE for updating statistics (statistics target for amount of statistics)
• Persistent connections (you could use a connection manager like pgpool or pgbouncer)
• Understand how queries are constructed (joins, sub-selects, cursors)
• Caching of data (i.e. memcached) is an option

And when you've exhausted those options: add more memory, faster disk-subsystem etc. Hardware matters, especially on larger datasets.

And of course, read all the other threads on postgres/databases. :)