There are several aspects of optimizing database performance, such as hardware configuration, network settings, database configuration, rewriting of SQL queries, maintenance of indexes, and so on. In this chapter, we will focus only on basic configuration and query rewriting.

Generally speaking, tuning database performance requires knowledge about the system's nature; for example, we need to know whether the database system can be used for online analytical processing (OLAP) or online transactional processing (OLTP). The database system may be IO or CPU bound; these define the whole database cluster setup, such as the number of CPUs, CPU power, RAID configuration, amount of RAM, and the database's cluster configuration. After the database server is configured, one could use a benchmark framework, such as pgbench, to calculate the number of transactions per second (TPS) for the database server setup.

The second step in optimizing database performance is carried out after the system is up and running, and often periodically. In this state, one could set up a monitoring system, such as a pgbadger monitoring tool, PostgreSQL Workload Analyzer (PoWA), and pg_stat_statements, to find bottlenecks and slow queries.

To optimize a slow query, it should be analyzed first. If the query is poorly written, rewriting it might be enough. Otherwise, missing indexes can be created, server configuration settings can be amended, the physical structure can be refactored, and so on.

The topics we will be covering in this chapter are :

• PostgreSQL configuration tuning
• Tuning performance for write
• Tuning performance for read
• Detecting problems in query plans
• Common mistakes in writing queries
• Table partitioning
• Query rewriting