postgres performance tuning

2023-10-23 ยท 2 min read

use separate disks for DB data and WAL files #

An excerpt from the official postgres manual:

It is advantageous if the WAL is located on a different disk from the main database files. This can be achieved by moving the pg_wal directory to another location (while the server is shut down, of course) and creating a symbolic link from the original location in the main data directory to the new location.

PostgreSQL WAL Internals

Related info from an azure cloud disk performance tuning guide:

As an example, you can apply these guidelines to SQL Server running on Premium Storage by doing the following,

  1. Configure "ReadOnly" cache on premium storage disks hosting data files. a. The fast reads from cache lower the SQL Server query time since data pages are retrieved much faster from the cache compared to directly from the data disks. b. Serving reads from cache, means there is additional Throughput available from premium data disks. SQL Server can use this additional Throughput towards retrieving more data pages and other operations like backup/restore, batch loads, and index rebuilds.
  2. Configure "None" cache on premium storage disks hosting the log files. a. Log files have primarily write-heavy operations. Therefore, they do not benefit from the ReadOnly cache.

Azure disk caching performance guide

See also: https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-storage?view=azuresql

pgMustard - EXPLAIN Glossary #

A glossary for what all the terms in an EXPLAIN ANALYZE query mean.

pgMustard ($100/yr) - EXPLAIN ANALYZE query analyzer-analyzer #

Helps you review Postgres query plans quickly

A simple yet powerful tool to help you speed up queries

Reading EXPLAIN ANALYZE output can be tough.

Even if you know how, Postgres includes so much useful information, it is easy to miss something important. And for queries that do a lot, the plans are even more useful-yet-time-consuming.

pgMustard speeds up your journey from knowing which query is a problem to working out what can be done about it.