PostgreSQL Tuning

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

Hardware: RAM, I/O, CPU, network

Operating System

PostgreSQL configuration parameters

Connection handling

Threads vs Processes

Shared Caches (shared_buffer)

…

https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY

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: https://www.percona.com/blog/mysql-server-memory-usage/

PostgreSQL

Process caches: work_mem Connections: pg_stat_activity

SQL> 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

SQL> 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:

SQL> SET LOCAL work_mem = ‘16MB’;

SQL> ALTER ROLE reporting_user SET work_mem = ‘16MB’;

How to see current usage: ???

What happens if you do it wrong? –> OoM

Sources:

Most important tuning parameters

Transaction handling

Temporary tables

log_temp_files = 0
temp_file_limit

Stored Procedures

Schema tuning

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.