PostgreSQL Performance Tuning

/computer/postgresql/postgresql-indexes

/ home / computer / postgresql / PostgreSQL for MySQL admins / .

Table of Contents

Details

• Performance definition

  • latency
  • throughput

• PostgreSQL Tuning

  • Hardware Tuning
  • Operating System Tuning
  • Server Configuration Parameters
    • Connection handling
    • Caches (shared_buffer, private cache)
    • Most important tuning parameters
  • Transaction handling
  • Temporary tables
  • Stored Procedures
  • Schema tuning
    • Data models / normalization
    • Data types
    • Indexing
  • Data loading / Logical Restore
  • SQL Query Tuning
  • The PostgreSQL Query Planner
  • Profiling
  • Performance views

• Monitoring

  • O/S (CPU, RAM, I/O, Network)
  • PostgreSQL (Processlist, Performance metrics)

Hardware Tuning

• Support-Checklist: Hardware
• RAM
• I/O
• CPU
• Network

Operating System Tuning

• Support-Checklist: Operating System
• Managing Kernel Resources
• PostgreSQL and Shared Memory
• Shared Memory
• Huge Pages

Server Configuration Parameters

• Source: Server Configuration
• Christophe Pettus, PGX, 2026-06-23: All Your GUCs in a Row: fsync

postgres=# show shared_buffers;
 shared_buffers 
----------------
 128MB

postgres=# SELECT name, setting, unit, category FROM pg_settings WHERE name IN ('work_mem', 'hash_mem_multiplier', 'temp_buffers', 'shared_buffers');
        name         | setting | unit |        category         
---------------------+---------+------+-------------------------
 hash_mem_multiplier | 2       |      | Resource Usage / Memory
 shared_buffers      | 16384   | 8kB  | Resource Usage / Memory
 temp_buffers        | 1024    | 8kB  | Resource Usage / Memory
 work_mem            | 4096    | kB   | Resource Usage / Memory

Connection handling

Connections and Authentication

Threads vs. Processes → More than 100 connections → Connection Pool

• pgBouncer
• Pgpool-II
• JDBC: Connection Pools and Data Sources

Shared Caches (shared_buffers)

• Memory
• PostgreSQL shared buffer cache or shared memory buffers
• Rule of thumb: shared_buffers: 25% of RAM

Private Caches

Private caches are per thread (MySQL) or per process (PostgreSQL). So the number of connections multiplies with the size of private caches (roughly).

MySQL

Thread caches: sort_buffer_size, read_buffer_size, join_buffer_size, read_rnd_buffer_size and thread_stack (SHOW GLOBAL VARIABLES LIKE .... Connections: max_connections, max_used_connections or threads_running (SHOW GLOBAL STATUS LIKE ...)

The main point is – there are a lot of memory consumers out where and trying to find peak possible usage for each is impractical – so my advice would be measure what you get in practice and how memory consumption reacts to changing various variables. For example you may find out increasing sort_buffer_size from 1MB to 4MB and 1000 max_connections increases peak memory consumption just 30MB not 3000MB as you might have counted.

• Source: Peter Zaitsev, Percona, 2006-05-18: MySQL Server Memory Usage

PostgreSQL

• Process caches: work_mem
• Connections: pg_stat_activity

postgres=# SELECT name, setting, unit, category FROM pg_settings WHERE name IN ('work_mem', 'hash_mem_multiplier', 'temp_buffers');
              name               | setting | unit |             category
---------------------------------+---------+------+-----------------------------------
 hash_mem_multiplier             | 2       |      | Resource Usage / Memory
 temp_buffers                    | 1024    | 8kB  | Resource Usage / Memory
 work_mem                        | 4096    | kB   | Resource Usage / Memory

postgres=# SELECT application_name, wait_event_type, wait_event, state, backend_type FROM pg_stat_activity WHERE backend_type = 'client backend';
 application_name | wait_event_type | wait_event | state  |  backend_type
------------------+-----------------+------------+--------+----------------
 psql             |                 |            | active | client backend

Process caches (PGA) = (n x work_mem (for sorts) + (n x work_mem x hash_mem_multiplier (for hash operations)) x active sessions

Example: Process memory: (1 x 16M + (1 x 16M x 2) x 150 = 7200 M (7 G)

Additionally:

• backend overhead, memory contexts, connection-local memory
• shared memory, OS cache dynamics, other processes
• parallel query workers (more processes, more operations)

Better to increase hash_mem_multiplier than work_mem. Better change per process than globally:

postgres=# SET LOCAL work_mem = '16MB';

or

postgres=# ALTER ROLE reporting_user SET work_mem = '16MB';

What happens if you do it wrong? → OoM

Sources:

• The OOM-Killer Summoning Ritual: “Just Increase work_mem”
• Memory
• Time to increase hash_mem_multiplier default?
• The surprising logic of the Postgres work_mem setting, and how to tune it

Most important tuning parameters

• Server Configuartion
• PGTune
• PostgreSQL Configurator
• Connections and Authentication
• Resource Consumption
• https://postgresqlco.nf/doc/en/param/autovacuum_work_mem/
• https://www.postgresql.org/docs/current/runtime-config-vacuum.html

Parameter	Topic	Default	Recommendation
max_connections	Connections, Memory	100	Depends on application needs and RAM, consider connection pool if bigger, requires a restart
shared_buffers	Memory	128 MB	25 - 40% of RAM, requires restart
maintenance_work_mem	Memory, Maintenance	64 MB	low concurrency!, 5 - 10% of RAM???, set autovacuum_work_mem!
work_mem	Memory	4 MB	1 - 2% of available Memory, several times per query!
huge_pages	Memory	try	Only for huge amount of RAM? requires restart
effective_io_concurrency	I/O	16	Depends on version (<=v17/>=v18), 200??? higher for faster I/O system, details
io_method	I/O	worker	io_uring
io_workers	I/O	3	only effect if io_method = worker
bgwriter_lru_maxpages	Background Writer	100	higher on high traffic?
bgwriter_delay	Background Writer	200 ms	lower on high traffic?
bgwriter_lru_multiplier	Background Writer	2.0	1.0 “just in time”, larger values provide some cushion against spikes
bgwriter_flush_after	Background Writer	0
wal_buffers	WAL	-1	Auto is mostly OK, -1 = 1/32 x shared_buffers, increase on busy server
min_wal_size	Checkpointing, WAL	80 MB	4 GB??? For batches
max_wal_size	Checkpointing, Memory, WAL	1 GB	Small cause frequent checkpoints, high causes longer recovery times
wal_compression	WAL	off	lz4, reduces WAL size
wal_writer_flush_after	WAL	1 MB	0???
wal_writer_delay	WAL	200 ms
synchronous_commit	WAL	on	off, for performance but risk of data loss (not inconsistencies!)
checkpoint_timeout	Checkpointing	300 s	Higher values can lead to longer crash recovery
checkpoint_completion_target	Checkpointing	0.9	checkpoint_completion_target = (checkpoint_timeout - 120s) / checkpoint_timeout, do not increase
default_statistics_target	Planner	100	Higher values results in better estimates but longer ANALYZE, Increase on skewd data: All Your GUCs in a Row: default_statistics_target
effective_cache_size	Planner	4 GB	Higher index scans, lower sequential scans, 75% of RAM???, details
join_collapse_limit	Planner	8	Size of joins? Increase results in better query plans
random_page_cost	Planner	4.0	Lower on SSD or all in RAM, [details]](https://vondra.me/posts/some-more-thoughts-on-random-page-cost/ “Some more thoughts on random_page_cost”)
seq_page_cost	Planner	1.0
jit	Planner	on	off/on???
max_parallel_workers	Parallelism	8	75% of available cores???
max_worker_processes	Parallelism	8	100% of available cores???, ~vCPU, requires restart
max_parallel_workers_per_gather	Parallelism	2	16% of cores???
max_parallel_maintenance_workers	Parallelism, Maintenance	2	12% of cores???
autovacuum	Maintenance	on	on
track_counts	Maintenance	on	on (autovacuum)
autovacuum_max_workers	Maintenance	3
autovacuum_work_mem	Maintenance	-1	maintenance_work_mem

A sample from MarkC: https://github.com/mdcallag/mytools/blob/master/bench/conf/arc/oct24/c32r128/pg18beta3_o2nofp/conf.diff.cx10b_c32r128

postgres=# show huge_pages_status;
 huge_pages_status 
-------------------
 off

Sources

• https://www.pgmustard.com/blog/enable-parameters-work-differently-in-postgres-18
• https://www.percona.com/blog/importance-of-tuning-checkpoint-in-postgresql/
• https://stormatics.tech/semabs-planet-postgresql/important-postgresql-parameters

---

Transaction handling

Isolation Levels

PostgresSQL has 3 isolation levels (MySQL 4): read committed, repeatable read and serializable. read committed is the default. read uncommittedis NOT allowed and falls back to read committed (without a warning). It is not recommended to change the isolation level globally but on a session level (the application knows what it does?):

postgres=# show default_transaction_isolation;
 default_transaction_isolation 
-------------------------------
 read committed

postgres=# SET SESSION default_transaction_isolation = 'repeatable read';
SET

postgres=# show default_transaction_isolation;
 default_transaction_isolation 
-------------------------------
 repeatable read

Read only transactions

Transactions can be set to read only.

Deferrable transactions

A deferrable transaction is one that waits for a safe snapshot rather than starting immediately.

Autocommit

postgres=# \echo :AUTOCOMMIT
on
postgres=# \set AUTOCOMMIT off
postgres=# \echo :AUTOCOMMIT
off

Sources:

• https://thebuild.com/blog/all-your-gucs-in-a-row-defaulttransactionisolation-and-defaulttransactionreadonly/
• https://thebuild.com/blog/all-your-gucs-in-a-row-defaulttransactiondeferrable/

---

Locking

Temporary tables

log_temp_files = 0
temp_file_limit

Stored Procedures

Schema tuning

PostgreSQL Indexes

Data models / normalization

Table Tuning

Data types

---

Numeric type

MySQL	PostgreSQL	size	range signed / unsigned *
TINYINT	❌	1 byte	-128 to +127 / 0 to 255
SMALLINT	smallint	2 bytes	-32768 to +32767 / 0 to 65535
MEDIUMINT	❌	3 bytes	-8388608 to 8388607 / 0 to 16777216
INT	integer	4 bytes	-2147483648 to +2147483647 / 0 to 4294967296
BIGINT	bigint	8 bytes	-9223372036854775808 to +9223372036854775807 / 0 to 18446744073709551615

^* Attention: PostgreSQL does NOT know UNSIGNED *INT datatypes!

Sources:

• Numeric Types
• 2024-12-06: Support for unsigned integer type

Monetary types

Character types

Binary data types

Date/time types

Boolean type

Enumerated types

Geometric types

etc.

Sources:

• Data Types

---

Indexing

Different index types - no Index Clustered Table (reorganize) - find unused indexes

• B-Tree
• Hash
• GiST
• SP-GiST
• GIN
• BRIN

https://www.postgresql.org/docs/current/indexes-types.html

Index creation time

• Hans-Jürgen Schönig, Cybetec, 2025-05-06: CREATE INDEX: Data types matter

SQL> SELECT * FROM t_demo LIMIT 10;
SQL> \timing
SQL> SHOW maintenance_work_mem;

SQL> SELECT tablename, indexname, indexdef
  FROM pg_indexes
 WHERE schemaname = 'public'
ORDER BY tablename, indexname;

SQL> VACUUM ANALYZE;
SQL> CHECKPOINT;

SQL> CREATE INDEX ON t_demo (v1);
SQL> DROP INDEX t_demo_v1_idx;

SQL> SELECT version();

Find duplicate or redundant indices in PostgreSQL:

• https://postgres.ai/docs/postgres-howtos/performance-optimization/indexing/how-to-find-redundent-indexes
• https://www.rockdata.net/tutorial/check-duplicate-indexes/
• https://dba.stackexchange.com/questions/27481/is-a-composite-index-also-good-for-queries-on-the-first-field/27493#27493
• https://www.percona.com/blog/useful-queries-for-postgresql-index-maintenance/
• https://wiki.postgresql.org/wiki/Index_Maintenance
• https://www.postgresql.org/docs/current/btree.html#BTREE-IMPLEMENTATION
• https://medium.com/@jramcloud1/25-postgresql-17-performance-tuning-detecting-and-removing-unused-indexes-a6f01d1d9055
• https://tech-talk.the-experts.nl/find-unused-postgres-indexes-pg-stat-statements-hypopg-e1314fd1b6ae
• https://gitlab.com/postgres-ai/postgres-checkup/-/blob/a6c8b6ae0772d5f7afb0a7004a740f6643a31aa2/resources/checks/H004_redundant_indexes.sh
• Joan Frey, DBI services, 2024-10-10: Enhancing PostgreSQL Performance with Index Advisor and HypoPG

Prefixed indices and function based indices (FBI).

See also Migration!

---

Data loading / Logical Restore

• Christophe Pettus, PGX, 2026-06-23: All Your GUCs in a Row: fsync
• Christophe Pettus, PGX, 2026-07-24: All Your GUCs in a Row: full_page_writes

---

SQL Query tuning

Currently running queries

MySQL / MariaDB have a command SHOW [FULL] PROCESSLIST that shows all running queries. A similar behavior in PostgreSQL can be achieved as follows:

postgres=# SELECT pid AS "Id", usename AS "User"
, CASE WHEN client_addr IS NULL THEN 'localhost' ELSE CAST(client_addr AS VARCHAR) END AS "Host"
, datname AS "db", state AS "Command"
, CURRENT_TIMESTAMP - state_change as "Time"
, wait_event AS "State", SUBSTR(LTRIM(query, ' '), 0, 64) AS "Info"
FROM pg_stat_activity WHERE backend_type = 'client backend'
;
   Id   | User |   Host    |  db  | Command |       Time       | State |                 Info                  
--------+------+-----------+------+---------+------------------+-------+---------------------------------------
 130347 | dba  | localhost | test | active  | 00:00:05.928094  |       | select * from test;
  92182 | dba  | localhost | test | active  | -00:00:00.000002 |       | SELECT pid AS "Id", usename AS "User"+
        |      |           |      |         |                  |       | , CASE WHEN client_addr I

Make sure that track_activities is on:

postgres=# SHOW track_activities;
 track_activities 
------------------
 on

KILL a query or a connection:

postgres=# SELECT pg_cancel_backend(pid);

postgres=# SELECT pg_terminate_backend(pid);

Source: Show PostgreSQL current (running) process list

---

Slow Query Log

There is only a single PostgreSQL log file per database cluster, so you cannot have that out of the box. [ Source: Laurenz Albe, 2016-05-29 ]

postgres=# SELECT name, setting FROM pg_settings
 WHERE name in ('data_directory', 'logging_collector', 'log_directory', 'log_destination');
       name        |               setting               
-------------------+-------------------------------------
 data_directory    | /home/dba/database/postgres-18/data
 log_destination   | stderr
 log_directory     | log
 logging_collector | off

The logfile is called in this example: ${PGDATA}/log/error.log.

• Source: Error Reporting and Logging

Enable the slow Query Log

PostgreSQL does NOT have a dedicated slow query log. Slow query are written to the general PostgreSQL log.

Slow queries can be enabled as follows;

postgres=# SHOW log_min_duration_statement;
 log_min_duration_statement 
----------------------------
 -1

postgres=# \l
postgres=# ALTER DATABASE test SET log_min_duration_statement = '100ms';   -- 0 is also possible for all
postgres=# ALTER SYSTEM SET log_min_duration_statement = '250ms';
postgres=# SELECT pg_reload_conf();
test=# SELECT pg_sleep(1);
 pg_sleep 
----------

postgres=# show log_min_duration_statement;
 log_min_duration_statement 
----------------------------
 250ms


postgres=#  \c test
You are now connected to database "test" as user "dba".
test=# SELECT pg_sleep(1);
 pg_sleep 
----------

test=# show log_min_duration_statement;
 log_min_duration_statement 
----------------------------
 100ms

--> No entry here for database postgres!!!
2026-06-10 12:05:43.640 CEST [51402] LOG:  duration: 1012.677 ms  statement: SELECT pg_sleep(1);

Sources:

• Error Reporting and Logging
• Postgres Logging for Performance Optimization

Execution Plans in log file

postgres=# LOAD 'auto_explain';
LOAD

postgres=# SET auto_explain.log_min_duration = '100ms';
SET

2026-06-12 17:26:17.660 CEST [92182] LOG:  duration: 151.373 ms  plan:
        Query Text: select * from test limit 1000000;
        Limit  (cost=0.00..18342.03 rows=1000000 width=35)
          ->  Seq Scan on test  (cost=0.00..1391907.24 rows=75886224 width=35)
2026-06-12 17:26:17.660 CEST [92182] LOG:  duration: 151.691 ms  statement: select * from test limit 1000000;

• Source: auto_explain — log execution plans of slow queries (shared object!)

Aggregating the slow queries

Similar to mysqldumpslow or mariadb-dumpslow

pgBadger

The tool to aggregate slow queries in PostgreSQL is called pgBadger. See also on GitHub: pgBadger Releases and pgBadger Installation.

You must first enable SQL query logging to have something to parse:

log_min_duration_statement = 0

You need to enable other parameters in postgresql.conf to get more information from your log files:

log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0
log_error_verbosity = default

Build and run pgBadger as follows:

$ tar xzf pgbadger-13.2.tar.gz
$ cd pgbadger-13.2/
$ perl Makefile.PL
$ make
$ # sudo make install
$ ./pgbadger --outfile=pgbadger_report.html /home/dba/database/postgres-18/log/error.log
[========================>] Parsed 160596 bytes of 160596 (100.00%), queries: 17, events: 503
LOG: Ok, generating html report...

Demo output see here.

Views similar to MySQL Performance Schema (P_S)

A similar view to track statistics of SQL planning and execution provides the pg_stat_statements extension/module. It comes with the postgresql-contrib package.

Configuration:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.track = all
pg_stat_statements.max = 10000
track_io_timing = on

postgres=# CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

• Enabling pg_stat_statements.
• See on GitHub.

postgres=# SELECT query, calls, ROUND(total_exec_time::numeric/1000, 3) AS tot_time, ROUND(min_exec_time::numeric/1000, 3) AS min_time, ROUND(max_exec_time::numeric/1000, 3) AS max_time, ROUND(mean_exec_time::numeric/1000, 3) AS mean_time, ROUND(stddev_exec_time::numeric/1000, 3) AS stddev_time, rows
  FROM public.pg_stat_statements
 ORDER BY tot_time DESC
 LIMIT 10;
                                        query                                        | calls | tot_time | min_time | max_time | mean_time | stddev_time |   rows    
-------------------------------------------------------------------------------------+-------+----------+----------+----------+-----------+-------------+-----------
 select * from test                                                                  |     2 |   20.426 |   10.182 |   10.244 |    10.213 |       0.031 | 151861718
 select *                                                                           +|     1 |   10.168 |   10.168 |   10.168 |    10.168 |       0.000 |  75930859
 from test as t1                                                                    +|       |          |          |          |           |             | 
 where $1                                                                            |       |          |          |          |           |             | 
 SELECT pg_sleep($1)                                                                 |     2 |    2.002 |    1.001 |    1.001 |     1.001 |       0.000 |         2
 SELECT pg_sleep($1)                                                                 |     1 |    1.001 |    1.001 |    1.001 |     1.001 |       0.000 |         1
 select * from test limit $1                                                         |     6 |    0.522 |    0.001 |    0.168 |     0.087 |       0.075 |   3102000
 CREATE EXTENSION pg_stat_statements                                                 |     1 |    0.036 |    0.036 |    0.036 |     0.036 |       0.000 |         0
 CREATE EXTENSION pg_stat_kcache                                                     |     1 |    0.025 |    0.025 |    0.025 |     0.025 |       0.000 |         0
 ALTER SYSTEM SET log_min_duration_statement = $1                                    |     1 |    0.014 |    0.014 |    0.014 |     0.014 |       0.000 |         0
 CREATE TABLE products (id int, category_id int)                                     |     1 |    0.007 |    0.007 |    0.007 |     0.007 |       0.000 |         0
 SELECT name, setting, unit, category FROM pg_settings WHERE name in ($1 /*, ... */) |     3 |    0.004 |    0.001 |    0.001 |     0.001 |       0.000 |        13

---

The PostgreSQL Query Planner

• Christophe Pettus, PGX, 2026-06-22: All Your GUCs in a Row: enable_async_append
• [Query Planning](https://www.postgresql.org/docs/current/runtime-config-query.html
• Planner/Optimizer
• Amit Dhiman, 2025-07-08: How PostgreSQL Query Planner Really Works
• Christophe Pettus, PGX, 2026-06-23: All Your GUCs in a Row: enable_indexscan and enable_bitmapscan

EXPLAIN and EXPLAIN ANALYZE queries

test=# \timing
Timing is on.

test=# SELECT * FROM test WHERE id = 5290358;
   id    |             data              |             ts             
---------+-------------------------------+----------------------------
 5290358 | Some data to blow table up... | 2026-03-18 17:31:39.104354
(1 row)

Time: 0.557 ms

test=# EXPLAIN SELECT * FROM test WHERE id = 5290358;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Index Scan using test_pkey on test  (cost=0.57..8.59 rows=1 width=35)
   Index Cond: (id = 5290358)
(2 rows)

Time: 0.422 ms

test=# EXPLAIN ANALYZE SELECT * FROM test WHERE id = 5290358;
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Index Scan using test_pkey on test  (cost=0.57..8.59 rows=1 width=35) (actual time=0.032..0.034 rows=1.00 loops=1)
   Index Cond: (id = 5290358)
   Index Searches: 1
   Buffers: shared hit=5
 Planning Time: 0.108 ms
 Execution Time: 0.072 ms
(6 rows)

Time: 0.660 ms

test=# \timing
Timing is off.

Hints

• No hints (…)
  • Aus Prinzip!
• Work arounds
  • join_collaps_limit = 1
• Hints Tabelle (v17) pg_hint_plan
• pg_plan_advice, pg_stash_advice (v19)

Benchmarking

pg_bench hammerdb https://laurenz.github.io/pgreplay/

Monitoring

• pgwatch: https://pgwat.ch/v5.x/
• pg_stat_statements, pg_wait_sampling, pg_profile, (diff) html report
• Software Catalogue - Reporting tools
• PostgreSQL Workload Analyzer
• PoWA Quickstart
• PoWA on GitHub
• pg_stat_kcache