- 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.
No comments:
Post a Comment