6 Performance bad practices
This chapter covers
- Going to production with the default PostgreSQL configuration
- Managing connection limits and life cycle
- Letting transactions go on for too long
- Turning off autovacuum may help (for a short while)
- Explicit locking and its associated risks
- Having too few or too many indexes and the effect this has on performance
We know by this point in the book that PostgreSQL is a database powerhouse—a flexible database suited for many uses that can perform extremely well when you ask it nicely. And by asking it nicely, I mean being aware of how it does things so that we can avoid entering code, configuration, and usage patterns that lead to suboptimal outcomes. It’s now time to start discussing some really common ways of hurting performance in Postgres. These may seem obvious to some but surprising to others, so they’re well worth exploring.
6.1 Default configuration in production
Let’s start at the top: you’ve just freshly installed PostgreSQL on your shiny new cloud compute instance, memory-optimized with 16 GB of RAM, all set for database awesomeness. You decide to restore your database into this instance from the latest dump of the data you have.
Let’s generate a decent-sized database using our old friend pgbench—the utility that comes with Postgres for running benchmark tests. First, we create it from the command line, as user postgres:
createdb pgbench
We now have an empty database to populate: