Tuning PostgreSQL for a Matrix Synapse Homeserver

Statistics Modules

Some of the following sections will rely on statistics generated by either pg_buffercache or pg_stat_statements, which are optional modules included with PostgreSQL that offer statistics on various tasks that PostgreSQL has seen.

These extensions cause PostgreSQL to use slightly more shared memory, and a few percent higher CPU tim - there's no direct harm in leaving them running, but if your objective is for maximum performance, after enabling them and completing your investigation, you can disable them again in the Disabling Statistics section below

Enabling Statistics

  1. Open your postgresql.conf file, search for the shared_preload_libraries setting, then add pg_buffercache,pg_stat_statements to its value (making sure to comma-separate each entry).

    If it's not present, simply add the following line:

    shared_preload_libraries = 'pg_buffercache,pg_stat_statements'

  2. Restart the PostgreSQL server for the changes to take effect, then run these queries:

    CREATE EXTENSION IF NOT EXISTS pg_buffercache;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Resetting Statistics

To reset the statistics collected by pg_stat_statements, you can execute the following command:

SELECT pg_stat_reset();

If your server has been running a long time, it's definitely worth running this to ensure you're looking at fresh numbers.

You can check when the stats were last reset for each database using a query like this:

SELECT datname AS database,
       stats_reset AS stats_last_reset
FROM pg_stat_database
WHERE datname
NOT LIKE 'template%';

 database  |       stats_last_reset
-----------+-------------------------------
 synapse   | 2023-12-22 12:13:28.708593+00
(1 row)

(Note: An empty value here would mean the stats have never been reset, according to PostgreSQL's records)

Disabling Statistics

Once you're done investigating, there's no need to remove the line from postgresql.conf - simply run the queries below to disable the extensions, so they'll stop running, but be available next time you need them:

DROP EXTENSION IF EXISTS pg_buffercache;
DROP EXTENSION IF EXISTS pg_stat_statements;