--- title: "How to Tune PostgreSQL Performance on Ubuntu 24.04" description: "Tune PostgreSQL 16 on Ubuntu 24.04 by sizing memory, WAL, planner and autovacuum settings in postgresql.conf, then measure the results with pg_stat_statements." category: "Database Management" language: en source: https://cubepath.com/docs/database-management/postgresql-optimization-postgresql-conf updated: 2026-09-25 --- # How to Tune PostgreSQL Performance on Ubuntu 24.04 PostgreSQL ships with deliberately conservative defaults so it can start on almost any machine. On a dedicated database server those defaults leave most of the RAM and disk bandwidth unused. In this tutorial you will size the main memory, write-ahead log (WAL), query planner, connection and autovacuum settings for PostgreSQL 16 on Ubuntu 24.04, apply them in a separate drop-in file, and use `pg_stat_statements` to check whether they actually helped. The example values target a dedicated server with 8 GB of RAM, 4 vCPUs and SSD/NVMe storage. The guide explains the formula behind each one so you can scale it to your own hardware. ## Prerequisites To follow this guide you need: - A server running Ubuntu 24.04 LTS, for example a CubePath VPS, used mainly as a database server. - PostgreSQL 16 installed from the Ubuntu repositories (`sudo apt install postgresql`). - A non-root user with `sudo` privileges. - A recent backup of your databases, and ideally a staging server where you can test changes first. ## Step 1 - Gathering information about the server Every setting in this guide depends on how much memory the server has, how many CPU cores it has and whether the disks are SSDs. Check the CPU count and memory first: ```bash nproc free -h ``` ```output 4 total used free shared buff/cache available Mem: 7.8Gi 1.1Gi 4.9Gi 64Mi 2.0Gi 6.7Gi Swap: 0B 0B 0B ``` Check whether the disks are rotational. A value of `0` in the `ROTA` column means SSD or NVMe: ```bash lsblk -d -o NAME,ROTA,SIZE ``` ```output NAME ROTA SIZE vda 0 160G ``` Finally, confirm the PostgreSQL version and cluster name. On Ubuntu, clusters are managed with the `pg_lsclusters` helper: ```bash pg_lsclusters ``` ```output Ver Cluster Port Status Owner Data directory Log file 16 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log ``` The rest of the guide assumes cluster `16/main`. Adjust the paths if yours differs. ## Step 2 - Creating a drop-in configuration file The main configuration file is `/etc/postgresql/16/main/postgresql.conf`. Instead of editing it line by line, keep your tuning in a separate file. Ubuntu's `postgresql.conf` already contains `include_dir = 'conf.d'`, so any `.conf` file in `/etc/postgresql/16/main/conf.d/` is loaded after the main file and overrides it. Confirm the include directive is present: ```bash grep "^include_dir" /etc/postgresql/16/main/postgresql.conf ``` ```output include_dir = 'conf.d' # include files ending in '.conf' from ``` Save the current values so you can compare them later: ```bash sudo -u postgres psql -Atc "SELECT name || ' = ' || setting || coalesce(unit, '') FROM pg_settings ORDER BY name" > ~/pg_settings_before.txt ``` Now create the tuning file. The following sections explain each group of settings; add them all to this one file: ```bash sudo nano /etc/postgresql/16/main/conf.d/90-tuning.conf ``` Keeping everything in one file makes it easy to review, version and revert: delete the file and restart, and you are back to the defaults. ## Step 3 - Sizing memory settings PostgreSQL uses its own buffer cache (`shared_buffers`) on top of the Linux page cache, plus per-operation memory for sorts and hashes. Add the following block to `90-tuning.conf`: ```ini # Memory (8 GB RAM, dedicated server) shared_buffers = 2GB # about 25% of RAM effective_cache_size = 6GB # about 75% of RAM, planner hint only work_mem = 16MB # per sort/hash operation, per query node maintenance_work_mem = 512MB # VACUUM, CREATE INDEX, ALTER TABLE huge_pages = try ``` What each setting does: - `shared_buffers`: the PostgreSQL buffer cache. Start at 25% of RAM. Going above 40% rarely helps because the operating system cache also holds your data. Changing it requires a restart. - `effective_cache_size`: does not allocate anything. It tells the planner how much data is likely cached in total (shared buffers plus page cache), which makes index scans more attractive. Use 50 to 75% of RAM. - `work_mem`: memory for each sort or hash step before it spills to disk. A single complex query can use several multiples of it, and every connection can run such a query, so keep it modest. A rough ceiling is `(RAM - shared_buffers) / (max_connections * 3)`. With 6 GB free and 100 connections that is about 20 MB. - `maintenance_work_mem`: used by `VACUUM`, `CREATE INDEX` and `ALTER TABLE ADD FOREIGN KEY`. Only a few of these run at once, so it can be much larger than `work_mem`. 5 to 10% of RAM, up to about 2 GB, is a good range. - `huge_pages = try`: the default. PostgreSQL uses huge pages if the kernel has them reserved and falls back silently if not. It is listed here so you know it exists; reserving huge pages with `vm.nr_hugepages` is worth doing only when `shared_buffers` is several gigabytes or more. > Tip: If a specific report needs more sort memory, raise `work_mem` for that session only with `SET work_mem = '256MB';` instead of raising it globally. ## Step 4 - Tuning WAL and checkpoints Every change is written to the WAL before it reaches the data files. A checkpoint flushes dirty pages to disk; if checkpoints happen too often, the server writes the same pages over and over and write latency spikes. Add this block: ```ini # WAL and checkpoints wal_buffers = 16MB min_wal_size = 1GB max_wal_size = 4GB checkpoint_timeout = 15min checkpoint_completion_target = 0.9 wal_compression = on ``` - `max_wal_size` is the amount of WAL after which a checkpoint is forced. The default of 1 GB triggers frequent checkpoints on any write-heavy workload. 4 GB is a reasonable starting point; make sure the disk holding `/var/lib/postgresql` has room for it. - `checkpoint_timeout` sets the maximum time between checkpoints. Longer intervals mean fewer writes but longer crash recovery. 15 minutes is a common compromise. - `checkpoint_completion_target = 0.9` (the default since PostgreSQL 14) spreads checkpoint writes over 90% of the interval. - `wal_buffers = 16MB` is what the default automatic sizing (`-1`) picks once `shared_buffers` is large enough; setting it explicitly documents the value. - `wal_compression = on` compresses full-page images in the WAL, trading some CPU for less WAL volume. In Step 9 you will check whether checkpoints are triggered by time (good) or by WAL volume (increase `max_wal_size`). ## Step 5 - Adjusting the query planner for SSDs The planner's cost model still assumes spinning disks, where a random read costs four times a sequential read. On SSDs and NVMe the difference is small, so lower `random_page_cost` and allow more concurrent I/O. Add: ```ini # Planner (SSD/NVMe storage) random_page_cost = 1.1 effective_io_concurrency = 200 default_statistics_target = 100 ``` - `random_page_cost = 1.1` makes the planner choose index scans when they are actually cheaper. Leave it at `4.0` only if your data really lives on HDDs. - `effective_io_concurrency = 200` lets bitmap heap scans prefetch many blocks in parallel, which SSDs handle well. - `default_statistics_target = 100` is the default. If `EXPLAIN ANALYZE` shows row estimates that are badly off for a specific column, raise the target for that column only, for example `ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;`, and run `ANALYZE orders;`. ## Step 6 - Configuring parallel workers and connections PostgreSQL can split large scans, joins and aggregates across several worker processes. Match the limits to the number of vCPUs: ```ini # Parallelism (4 vCPUs) max_worker_processes = 8 max_parallel_workers = 4 max_parallel_workers_per_gather = 2 max_parallel_maintenance_workers = 2 # Connections max_connections = 100 ``` - `max_parallel_workers` caps the total number of parallel workers; set it to the number of cores. - `max_parallel_workers_per_gather` is how many workers one query node can use. Half the cores leaves room for concurrent queries. - `max_connections`: each connection is a separate process with its own memory. Raising it to 500 or 1000 usually makes performance worse. If your application needs many connections, keep this value around 100 and put a connection pooler such as PgBouncer (`sudo apt install pgbouncer`) in front of PostgreSQL. ## Step 7 - Tuning autovacuum and logging Autovacuum removes dead row versions and refreshes planner statistics. The default thresholds wait until 20% of a table has changed, which on a table with 50 million rows means 10 million dead rows before any cleanup. Lower the scale factors and let autovacuum work faster: ```ini # Autovacuum autovacuum_max_workers = 3 autovacuum_naptime = 30s autovacuum_vacuum_scale_factor = 0.05 autovacuum_analyze_scale_factor = 0.02 autovacuum_vacuum_cost_limit = 1000 ``` - The scale factors trigger vacuum after 5% and analyze after 2% of a table changes. - `autovacuum_vacuum_cost_limit` raises how much work autovacuum does before it pauses. The default (200 via `vacuum_cost_limit`) is sized for old HDDs. Then add logging that helps you find slow queries and memory spills: ```ini # Logging log_min_duration_statement = 500ms log_temp_files = 0 log_lock_waits = on log_checkpoints = on log_autovacuum_min_duration = 1s # Query statistics shared_preload_libraries = 'pg_stat_statements' ``` - `log_min_duration_statement = 500ms` logs every statement that runs longer than half a second. - `log_temp_files = 0` logs every temporary file, which tells you when `work_mem` is too small for a query. - `shared_preload_libraries` loads `pg_stat_statements`, which records execution statistics for every normalized query. It is included in the Ubuntu `postgresql-16` package. Save and close the file. ## Step 8 - Validating and applying the configuration Before restarting, check that PostgreSQL can parse the file. The `pg_file_settings` view reads the configuration files from disk and reports errors without applying anything: ```bash sudo -u postgres psql -c "SELECT sourcefile, sourceline, name, error FROM pg_file_settings WHERE error IS NOT NULL;" ``` An empty result means every line is valid: ```output sourcefile | sourceline | name | error ------------+------------+------+------- (0 rows) ``` `shared_buffers`, `max_connections`, `max_worker_processes`, `wal_buffers`, `huge_pages`, `autovacuum_max_workers` and `shared_preload_libraries` only take effect after a restart, so restart the service during a maintenance window: ```bash sudo systemctl restart postgresql sudo systemctl status postgresql@16-main --no-pager ``` ```output ● postgresql@16-main.service - PostgreSQL Cluster 16-main Loaded: loaded (/usr/lib/systemd/system/postgresql@.service; enabled-runtime; preset: enabled) Active: active (running) since Thu 2026-09-24 10:12:41 UTC; 3s ago ``` If the service fails to start, the reason is in the cluster log: ```bash sudo tail -n 30 /var/log/postgresql/postgresql-16-main.log ``` Confirm the new values are live and nothing is waiting for a restart: ```bash sudo -u postgres psql -c "SELECT name, setting, unit, pending_restart FROM pg_settings WHERE name IN ('shared_buffers','work_mem','max_wal_size','random_page_cost','shared_preload_libraries');" ``` ```output name | setting | unit | pending_restart --------------------------+--------------------+------+----------------- max_wal_size | 4096 | MB | f random_page_cost | 1.1 | | f shared_buffers | 262144 | 8kB | f shared_preload_libraries | pg_stat_statements | | f work_mem | 16384 | kB | f ``` `shared_buffers` is reported in 8 kB pages: 262144 x 8 kB = 2 GB. For later changes to settings that do not need a restart (for example `work_mem`, `random_page_cost` or the autovacuum and logging settings), edit the file and reload instead: ```bash sudo systemctl reload postgresql ``` ## Step 9 - Measuring the effect Tuning without measuring is guesswork. Enable `pg_stat_statements` in the database your application uses (replace `your_database`): ```bash sudo -u postgres psql -d your_database -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;" ``` After the application has run for a while, list the queries that consume the most total time: ```bash sudo -u postgres psql -d your_database -c "SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time::numeric, 2) AS mean_ms, left(query, 60) AS query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;" ``` These are the queries to optimize first, usually with an index or a rewrite rather than more configuration. Check the buffer cache hit ratio. On an OLTP workload it should be above 99%; if it is much lower and the server has free RAM, `shared_buffers` can grow: ```bash sudo -u postgres psql -c "SELECT datname, round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct FROM pg_stat_database WHERE datname NOT LIKE 'template%';" ``` Check what triggers checkpoints. In PostgreSQL 16 the counters live in `pg_stat_bgwriter`: ```bash sudo -u postgres psql -c "SELECT checkpoints_timed, checkpoints_req FROM pg_stat_bgwriter;" ``` ```output checkpoints_timed | checkpoints_req -------------------+----------------- 142 | 3 ``` Most checkpoints should be `timed`. If `checkpoints_req` grows quickly, WAL fills up before the timeout and `max_wal_size` should be increased. Finally, look for queries that spill to disk. With `log_temp_files = 0`, they appear in the log: ```bash sudo grep "temporary file" /var/log/postgresql/postgresql-16-main.log | tail -n 5 ``` Frequent large temporary files for the same query mean it needs more `work_mem` (per session or per role with `ALTER ROLE report_user SET work_mem = '128MB';`) or a better index. ## Scaling the values to other servers Use these starting points and adjust after measuring: | Setting | 2 GB RAM | 8 GB RAM | 32 GB RAM | |---|---|---|---| | `shared_buffers` | 512MB | 2GB | 8GB | | `effective_cache_size` | 1536MB | 6GB | 24GB | | `work_mem` | 4MB | 16MB | 32MB | | `maintenance_work_mem` | 128MB | 512MB | 2GB | | `max_wal_size` | 1GB | 4GB | 16GB | For analytics or reporting workloads with few concurrent users and large queries, lower `max_connections`, raise `work_mem` substantially (64 MB to 256 MB) and allow more parallel workers per query. ## Troubleshooting - **PostgreSQL does not start after the change**: check `/var/log/postgresql/postgresql-16-main.log`. A message like `could not map anonymous shared memory` means `shared_buffers` is too large for the available RAM. Lower it or remove `90-tuning.conf` to return to the defaults. - **The server runs out of memory under load**: `work_mem` multiplied by active connections is too high. Lower `work_mem` or `max_connections`, and use a pooler. - **A setting does not change**: another file may override it. Run `sudo -u postgres psql -c "SELECT name, setting, sourcefile FROM pg_settings WHERE name = 'work_mem';"` to see which file the active value comes from. Values set with `ALTER SYSTEM` live in `postgresql.auto.conf`, which is read last and wins. ## Conclusion You moved PostgreSQL 16 from its conservative defaults to settings sized for the server's RAM, CPUs and SSD storage, applied them in a single drop-in file and confirmed with `pg_stat_statements`, the cache hit ratio and checkpoint counters that they work. From here, add indexes for the slowest queries in `pg_stat_statements`, put PgBouncer in front of PostgreSQL if you need hundreds of connections, and set up regular backups with `pg_dump` before tuning further.