Configuration of PostgreSQL

/ home / computer / postgresql / postgresql-for-mysql-admins / .

Configuration Variables

MySQL Server System Variables vs. PostgreSQL Server Configuration Settings

SQL> SHOW GLOBAL VARIABLES;
SQL> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_s%';
SQL> SELECT * FROM performance_schema.global_variables;

postgres=# SHOW ALL;
postgres=# SHOW shared_buffers;
postgres=# SELECT name, (setting::INT * 8192)/1024/1024 FROM pg_settings WHERE name LIKE 'shared_buf%';

• PostgreSQL Server Configuration
• MySQL Server System Variable Reference
• MariaDB Server System Variables

Most important server configuration settings

Connection related / listener

MySQL: bind_address

$ ss -tlpn
State      Recv-Q     Send-Q         Local Address:Port           Peer Address:Port     Process
LISTEN     0          200                127.0.0.1:5432                0.0.0.0:*         users:(("postgres",pid=6002,fd=7))
LISTEN     0          200                    [::1]:5432                   [::]:*         users:(("postgres",pid=6002,fd=6))

Change listen_addresses = '*' in postgresql.conf and then systemctl restart postgresql. Additionally in pg_hba.conf:

host    all             all              0.0.0.0/0                       md5
host    all             all              ::/0                            md5

followed by: systemctl reload postgresql.

Sources: Connection settings and The pg_hba.conf File

Changing server configuration settings

File: /etc/postgresql/16/main/postgresql.conf

pg_ctl reload vs. systemctl restart

ALTER SYSTEM SET <configuration_parameter> = <value>;
ALTER SYSTEM RESET <configuration_parameter>;
SELECT pg_reload_conf();

For the whole databsase cluster. Alterative to postgresql.conf postgresql.auto.conf

$ pg_ctl reload
$ kill -SIGHUP $(pidof postgres)
$ systemctl restart postgres

postgres=# select name, setting, context, source, boot_val, reset_val, sourcefile, pending_restart from pg_settings limit 20;
                 name                  |  setting   |  context   | source  | boot_val  | reset_val | sourcefile | pending_restart
---------------------------------------+------------+------------+---------+-----------+-----------+------------+-----------------
 allow_in_place_tablespaces            | off        | superuser  | default | off       | off       |            | f
 allow_system_table_mods               | off        | superuser  | default | off       | off       |            | f
 application_name                      | psql       | user       | client  |           | psql      |            | f
 archive_cleanup_command               |            | sighup     | default |           |           |            | f
 archive_command                       | (disabled) | sighup     | default |           |           |            | f
 archive_library                       |            | sighup     | default |           |           |            | f
 archive_mode                          | off        | postmaster | default | off       | off       |            | f
 archive_timeout                       | 0          | sighup     | default | 0         | 0         |            | f
 array_nulls                           | on         | user       | default | on        | on        |            | f
 authentication_timeout                | 60         | sighup     | default | 60        | 60        |            | f
 autovacuum                            | on         | sighup     | default | on        | on        |            | f
 autovacuum_analyze_scale_factor       | 0.1        | sighup     | default | 0.1       | 0.1       |            | f
 autovacuum_analyze_threshold          | 50         | sighup     | default | 50        | 50        |            | f
 autovacuum_freeze_max_age             | 200000000  | postmaster | default | 200000000 | 200000000 |            | f
 autovacuum_max_workers                | 3          | postmaster | default | 3         | 3         |            | f
 autovacuum_multixact_freeze_max_age   | 400000000  | postmaster | default | 400000000 | 400000000 |            | f
 autovacuum_naptime                    | 60         | sighup     | default | 60        | 60        |            | f
 autovacuum_vacuum_cost_delay          | 2          | sighup     | default | 2         | 2         |            | f
 autovacuum_vacuum_cost_limit          | -1         | sighup     | default | -1        | -1        |            | f
 autovacuum_vacuum_insert_scale_factor | 0.2        | sighup     | default | 0.2       | 0.2       |            | f
(20 rows)

ALTER DATABASE <your_database_name> SET log_statement = 'all';

• ALTER SYSTEM command

$ psql --user=postgres --command='SHOW config_file'
               config_file
-----------------------------------------
 /etc/postgresql/16/main/postgresql.conf


postgres=# SHOW data_directory;
       data_directory
-----------------------------
 /var/lib/postgresql/16/main

SHOW ALL;

postgres=# select name, setting from pg_settings where name like 'log\_%';

Per process settings (similar to Oracle PGA)

work_mem ~ tmp_table_size

Sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files.

Source: Resource Consumption

RAM usage

shared_buffers analogous to MyISAM: key_buffer_size (or less close to innodb_buffer_pool_size). See also here

WAL settings

In PostgreSQL, the “WAL” stands for Write-Ahead Log. It is the core component that ensures your data is safe and durable (the ‘D’ in ACID). It is the analogous to InnoDB Redo Log / InnoDB Transaction Log.

max_wal_size ~ innodb_log_file_size / innodb_redo_log_capacity?

The WAL’s are located under $datadir/pg_wal

Tuning Checkpoints and WAL

To avoid I/O bursts:

checkpoint_timeout 300s / 5min max_wal_size 1GB checkpoint_completion_target 0.9 / 90%

You can check the PostgreSQL logs:

$ ll pg_wal/
-rw------- 1 mysql mysql 16777216 Feb 10 21:50 000000010000001500000055
-rw------- 1 mysql mysql 16777216 Feb 10 21:52 000000010000001500000056
-rw------- 1 mysql mysql 16777216 Feb 10 21:54 000000010000001500000057
-rw------- 1 mysql mysql 16777216 Feb 10 21:55 000000010000001500000058
-rw------- 1 mysql mysql 16777216 Feb 10 21:58 000000010000001500000059

Or at the pg_stat_bgwriter view:

-- Query does NOT work on my vanilla PostgreSQL 19:
-- Check the ratio of timed vs. requested checkpoints
SELECT
    checkpoints_timed, checkpoints_req,
    -- Calculate the ratio of size-driven checkpoints
    round((checkpoints_req::numeric / (checkpoints_timed + checkpoints_req) * 100), 2) AS req_ratio_percent
FROM
    pg_stat_bgwriter;

SELECT name, size FROM pg_ls_waldir() where name not like '%history' and name not like '%backup';
           name           |   size
--------------------------+----------
 00000001000000150000003B | 16777216
 000000010000001500000046 | 16777216
 000000010000001500000048 | 16777216
 00000001000000150000004D | 16777216
 000000010000001500000044 | 16777216

synchronous_commit ~ innodb_flush_log_at_trx_commit - See also here

See slides from Swiss PG day 2025 (Luigi Nardi)

Sources:

• https://www.postgresql.org/message-id/be9e8d91-86c5-d8d7-2785-6f7b621f6d77%402ndquadrant.com
• WAL Configuration
• https://www.enterprisedb.com/blog/impact-full-page-writes
• https://medium.com/@wasiualhasib/what-is-buffers-checkpoint-in-postgresql-pg-stat-bgwriter-76d7deb4105d
• Troubleshooting PostgreSQL WAL Growth: max_wal_size and Replication Slots
• https://dba.stackexchange.com/questions/237303/how-to-increase-postgres-wal-size
• https://www.simplified.guide/postgresql/wal-tune-setting

mysql> SHOW GLOBAL VARIABLES LIKE '...';

postgres=# SELECT name, setting, unit FROM pg_settings WHERE name LIKE '%checkpoint%' OR name LIKE '%wal%' OR name LIKE 'full_page%';
             name              |  setting  | unit
-------------------------------+-----------+------
 auto_explain.log_wal          | off       |
 checkpoint_completion_target  | 0.9       |
 checkpoint_flush_after        | 32        | 8kB
 checkpoint_timeout            | 300       | s
 checkpoint_warning            | 30        | s
 full_page_writes              | on        |
 log_checkpoints               | on        |
 max_slot_wal_keep_size        | -1        | MB
 max_wal_senders               | 10        |
 max_wal_size                  | 1024      | MB
 min_wal_size                  | 80        | MB
 track_wal_io_timing           | off       |
 wal_block_size                | 8192      |
 wal_buffers                   | 512       | 8kB
 wal_compression               | off       |
 wal_consistency_checking      |           |
 wal_decode_buffer_size        | 524288    | B
 wal_init_zero                 | on        |
 wal_keep_size                 | 0         | MB
 wal_level                     | replica   |
 wal_log_hints                 | off       |
 wal_receiver_create_temp_slot | off       |
 wal_receiver_status_interval  | 10        | s
 wal_receiver_timeout          | 60000     | ms
 wal_recycle                   | on        |
 wal_retrieve_retry_interval   | 5000      | ms
 wal_segment_size              | 16777216  | B
 wal_sender_timeout            | 60000     | ms
 wal_skip_threshold            | 2048      | kB
 wal_sync_method               | fdatasync |
 wal_writer_delay              | 200       | ms
 wal_writer_flush_after        | 128       | 8kB
(31 rows)

Settings affecting PostgreSQL optimizer

Affects Optimizer choice:

random_page_cost See also here (https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-RANDOM-PAGE-COST)

See also for MySQL: The Optimizer Cost Model and MariaDB: The Optimizer Cost Model from MariaDB 11.0 Default: 4.0 -> SSD 2.0, all in RAM/Cache -> 1.0?