PostgreSQL on AlmaLinux series, part 5. Previous: part 4, PostgreSQL Streaming Replication on AlmaLinux 10: Primary/Standby Setup, Switchover and Failover. Next up: major-version upgrades with pg_upgrade.
A fresh PostgreSQL install is configured to start on almost any machine, which means its defaults are tiny: 128 MB of shared buffers, 4 MB of sort memory and a planner that assumes spinning disks. This guide tunes a PostgreSQL server on AlmaLinux 10 in the order that pays off most: size the memory and WAL settings for the hardware, turn on the statistics you need to find slow queries, read plans with EXPLAIN ANALYZE, fix them with the right indexes, and keep tables healthy with autovacuum. Every change is made with ALTER SYSTEM and verified from pg_settings, so you always know what is active.
Applies to: PostgreSQL 18 from the PGDG repository on AlmaLinux 10, as installed in part 1 (service postgresql-18, data directory /var/lib/pgsql/18/data, binaries in /usr/pgsql-18/bin). With the AppStream PostgreSQL 16 package the service is postgresql and the data directory is /var/lib/pgsql/data; the settings are the same except the few marked as PostgreSQL 17 or 18 features. Also applies to RHEL, Rocky Linux and Oracle Linux 10.
Prerequisites
- The lab server
db1(192.168.1.40) running PostgreSQL 18, set up as in part 1. The examples assume 16 GB of RAM, 4 vCPUs and SSD storage; scale the numbers to your machine. - Root or sudo access, and the
postgresOS account. - The contrib package, which provides
pg_stat_statementsand thepgbenchbenchmark tool:sudo dnf install -y postgresql18-contrib(AppStream:postgresql-contrib). - A recent backup (see part 3). Tuning is low risk, but a typo in a memory setting can stop the server from starting.
- If you built the standby from part 4, plan to apply the same settings on
db2too:ALTER SYSTEMwritespostgresql.auto.confon the server where you run it, and that file is not replicated.
Step 1: Take a baseline of the hardware and current settings
Know what you are tuning for before changing anything. Check memory, CPU count and whether the data disk is rotational (ROTA 0 means SSD/NVMe):
free -h
nproc
lsblk -d -o NAME,SIZE,ROTA,MODEL
df -h /var/lib/pgsqlThen look at the settings this guide touches, with their units and where each value came from:
sudo -iu postgres psqlSELECT name, setting, unit, source, context
FROM pg_settings
WHERE name IN ('shared_buffers','effective_cache_size','work_mem','maintenance_work_mem',
'max_wal_size','min_wal_size','checkpoint_timeout','random_page_cost',
'effective_io_concurrency','max_connections','io_method','huge_pages')
ORDER BY name;The context column tells you how a change takes effect: postmaster needs a restart, sighup needs a reload, and user can even be set per session or per role. Record a quick throughput baseline with pgbench so you can measure the effect of your changes later:
sudo -iu postgres createdb pgbench
sudo -iu postgres /usr/pgsql-18/bin/pgbench -i -s 50 pgbench
sudo -iu postgres /usr/pgsql-18/bin/pgbench -c 10 -j 4 -T 60 pgbenchScale factor 50 creates about 5 million rows in pgbench_accounts (roughly 750 MB). Note the tps line at the end. Run the same command after tuning; a synthetic benchmark is not your application, but a big drop tells you something went wrong.
Step 2: Size the memory settings
Four settings matter most. A good starting point for a dedicated database server:
| Setting | Default | Rule of thumb | 16 GB example |
|---|---|---|---|
shared_buffers | 128MB | About 25% of RAM (rarely above 8 to 16 GB) | 4GB |
effective_cache_size | 4GB | 50 to 75% of RAM; a planner hint, allocates nothing | 12GB |
work_mem | 4MB | Per sort/hash operation, per connection; start at 16 to 64MB | 32MB |
maintenance_work_mem | 64MB | VACUUM, CREATE INDEX, ALTER TABLE; 5 to 10% of RAM, up to 1 to 2 GB | 1GB |
work_mem is the one to be careful with. It is a limit per sort or hash node, not per connection: one complex query can use several multiples of it, and hash operations may use work_mem × hash_mem_multiplier (2.0 by default). With 100 connections a value of 32MB is safe on 16 GB; 256MB globally is not. Give big memory only to the roles that need it instead, such as the reporter role from part 2.
Apply the settings with ALTER SYSTEM, which writes them to /var/lib/pgsql/18/data/postgresql.auto.conf and overrides postgresql.conf:
ALTER SYSTEM SET shared_buffers = '4GB';
ALTER SYSTEM SET effective_cache_size = '12GB';
ALTER SYSTEM SET work_mem = '32MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER ROLE reporter SET work_mem = '256MB';The role setting applies to new sessions of reporter. Leave autovacuum_work_mem at its default of -1, which makes autovacuum workers use maintenance_work_mem; each worker can allocate that much, so lower it only if you raise autovacuum_max_workers a lot.
Step 3: Tune WAL and checkpoints
Every checkpoint flushes all dirty buffers to disk, and the first change to each page after a checkpoint writes a full page image to the WAL. Checkpoints that come too often waste I/O. The defaults (max_wal_size 1GB, checkpoint_timeout 5min) force frequent checkpoints on any busy server.
ALTER SYSTEM SET max_wal_size = '4GB';
ALTER SYSTEM SET min_wal_size = '1GB';
ALTER SYSTEM SET checkpoint_timeout = '15min';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM SET wal_compression = 'lz4';checkpoint_completion_target is already 0.9 by default since PostgreSQL 14; setting it explicitly documents the intent. wal_compression compresses full page images and usually shrinks WAL volume noticeably for a little CPU. The PGDG packages are built with LZ4 and Zstandard support; confirm with /usr/pgsql-18/bin/pg_config --configure | tr ' ' '\n' | grep -E 'lz4|zstd'. A longer checkpoint interval also means a longer crash recovery, so do not go far beyond 15 to 30 minutes.
After the server has run under load for a while, check whether checkpoints are triggered by the timer (good) or by WAL volume (raise max_wal_size). PostgreSQL 17 and later report this in pg_stat_checkpointer:
SELECT num_timed, num_requested, write_time, sync_time, buffers_written
FROM pg_stat_checkpointer;If num_requested grows faster than num_timed, the server is filling max_wal_size before the timeout. The log also warns with checkpoints are occurring too frequently. On PostgreSQL 16 the same counters are in pg_stat_bgwriter (checkpoints_timed and checkpoints_req).
Step 4: Tell the planner about SSD storage
The planner’s default random_page_cost of 4.0 says a random read costs four times a sequential one, which is true for spinning disks and makes it shy away from index scans. On SSD, NVMe or a SAN with a large cache, lower it:
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
ALTER SYSTEM SET maintenance_io_concurrency = 200;effective_io_concurrency controls how many read-ahead requests PostgreSQL issues for bitmap heap scans and, in PostgreSQL 18, sequential scans and vacuum too. PostgreSQL 18 introduced asynchronous I/O, controlled by io_method. The default, worker, runs the I/O through a pool of background processes (io_workers, default 3) and works everywhere, so leave it unless testing shows otherwise. io_uring is only available if the server was built with liburing; check with pg_config --configure | grep -o liburing before trying it, because the server will not start with an unsupported value. Changing io_method needs a restart.
Step 5: Use huge pages for shared memory
With 4 GB of shared buffers, mapping the shared memory with 2 MB huge pages instead of 4 KB pages saves page-table memory and TLB misses. The default huge_pages = try uses them only if the kernel has some reserved. Ask PostgreSQL how many it needs for the new settings. This must run with the server stopped, so do it in the same maintenance window as the restart in step 6:
sudo systemctl stop postgresql-18
sudo -iu postgres /usr/pgsql-18/bin/postgres -D /var/lib/pgsql/18/data -C shared_memory_size_in_huge_pagesIt prints a number, for example around 2,100 for 4 GB of shared buffers. Reserve that many pages (round up a little) and apply it:
echo 'vm.nr_hugepages = 2200' | sudo tee /etc/sysctl.d/90-postgresql-hugepages.conf
sudo sysctl --system
grep -E 'HugePages_(Total|Free)' /proc/meminfoIf HugePages_Total comes back lower than requested, memory is too fragmented; reboot and the reservation is made early at boot. Keep huge_pages = try while you test, and switch to on only when you want the server to refuse to start without them.
Step 6: Restart and confirm what is active
shared_buffers, io_method and the huge pages reservation only take effect at server start; the other settings need just a reload, but a single restart covers everything. Start the server again (or restart it if you skipped step 5) and look for anything still waiting:
sudo systemctl restart postgresql-18
sudo -iu postgres psql -c "SELECT name, setting, unit FROM pg_settings WHERE source = 'configuration file' AND sourcefile LIKE '%auto.conf' ORDER BY name;"
sudo -iu postgres psql -c "SELECT name FROM pg_settings WHERE pending_restart;"
sudo -iu postgres psql -c "SHOW huge_pages_status;"The first query lists everything set by ALTER SYSTEM, the second should return zero rows, and huge_pages_status (PostgreSQL 17 and later) should say on. Settings with context sighup only need SELECT pg_reload_conf();.
Step 7: Find slow queries with pg_stat_statements and the slow query log
Tuning the server is the coarse adjustment; most real gains come from fixing specific queries. pg_stat_statements records execution counts and timings for every normalized query. It must be preloaded, and ALTER SYSTEM SET shared_preload_libraries replaces the whole list, so check what is already there first:
SHOW shared_preload_libraries;
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
ALTER SYSTEM SET track_io_timing = on;
ALTER SYSTEM SET log_min_duration_statement = '500ms';
ALTER SYSTEM SET log_temp_files = 0;
ALTER SYSTEM SET log_autovacuum_min_duration = '30s';If the first command showed other libraries, include them in a comma-separated list. track_io_timing adds read and write times to statistics and plans; run /usr/pgsql-18/bin/pg_test_timing first and keep it on if the reported per-loop overhead is under about 100 ns, which is normal on current hardware. log_min_duration_statement logs any statement slower than 500 ms, and log_temp_files = 0 logs every sort or hash that spilled to disk, a sign that work_mem is too small for that query. Restart, then create the extension in each database you want to inspect:
sudo systemctl restart postgresql-18
sudo -iu postgres psql -d appdb -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"After the application has run for a while, list the statements that use the most total time:
SELECT round(total_exec_time::numeric, 1) AS total_ms,
calls,
round(mean_exec_time::numeric, 2) AS mean_ms,
rows,
shared_blks_hit, shared_blks_read,
left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;A query with a modest mean time but millions of calls can matter more than one slow report. Reset the counters after a fix with SELECT pg_stat_statements_reset(); so the next measurement is clean. The slow query log is in /var/lib/pgsql/18/data/log/ with the PGDG defaults.
Step 8: Build a lab table to practise on
To see indexes and plans change, create a separate database with a two-million-row table. Nothing here touches appdb or invdb from earlier parts:
sudo -iu postgres createdb perflab
sudo -iu postgres psql -d perflabCREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL,
status text NOT NULL,
email text NOT NULL,
amount numeric(10,2) NOT NULL,
created_at timestamptz NOT NULL
);
INSERT INTO orders (customer_id, status, email, amount, created_at)
SELECT (random() * 99999)::int + 1,
CASE WHEN random() < 0.02 THEN 'pending'
WHEN random() < 0.10 THEN 'cancelled'
ELSE 'shipped' END,
'User' || g || '@Example.com',
round((random() * 500)::numeric, 2),
now() - interval '2000000 seconds' + g * interval '1 second'
FROM generate_series(1, 2000000) AS g;
ANALYZE orders;
SELECT pg_size_pretty(pg_total_relation_size('orders'));Running ANALYZE after a bulk load matters: the planner’s row estimates come from these statistics, and autovacuum may not have gathered them yet.
Step 9: Read a plan with EXPLAIN ANALYZE
EXPLAIN shows the plan the optimizer chose; EXPLAIN ANALYZE actually runs the query and adds real row counts and timings. Since PostgreSQL 18, ANALYZE output includes buffer statistics automatically (on 16 and 17, add BUFFERS yourself). Ask for one customer’s ten newest orders:
EXPLAIN ANALYZE
SELECT id, amount, created_at
FROM orders
WHERE customer_id = 4242
ORDER BY created_at DESC
LIMIT 10;Without a suitable index the plan has this shape (example output; your timings and buffer counts will differ):
Limit (cost=... rows=10 width=22) (actual time=... rows=10 loops=1)
-> Gather Merge (... rows=20 ...) (actual ... rows=10 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (...)
Sort Key: created_at DESC
Sort Method: quicksort Memory: 25kB
-> Parallel Seq Scan on orders (... rows=8 ...) (actual ... rows=7 loops=3)
Filter: (customer_id = 4242)
Rows Removed by Filter: 666660
Buffers: shared hit=... read=...
Planning Time: ... ms
Execution Time: ... msHow to read it:
- Read from the innermost node outward. Each node shows the planner’s estimate (
cost,rows) and what really happened (actual time,rows,loops). Multiply actual rows byloopsfor the total. Rows Removed by Filterin the hundreds of thousands to return a handful of rows is the classic sign of a missing index.- A big gap between estimated and actual rows (say 10 vs 100,000) means stale or insufficient statistics. Run
ANALYZE, or raise the statistics target for that column withALTER TABLE ... ALTER COLUMN ... SET STATISTICS 1000. Sort Method: external merge Disk: ...means the sort did not fit inwork_mem.Buffers: shared readcounts 8 KB pages read from the OS (or disk);hitmeans they were already in shared buffers.
EXPLAIN ANALYZE really executes the statement. For an UPDATE or DELETE, wrap it in BEGIN; … ROLLBACK;.
Step 10: Add the right indexes
A B-tree index on the filter column, with the sort column second, lets PostgreSQL jump straight to that customer’s newest rows and stop after ten. On a production table always build indexes with CONCURRENTLY, which does not block writes (it cannot run inside a transaction block, and takes longer):
CREATE INDEX CONCURRENTLY orders_customer_created_idx
ON orders (customer_id, created_at DESC);Run the same EXPLAIN ANALYZE again. The plan becomes a Limit over an Index Scan using orders_customer_created_idx with no sort and no filtered rows, and the execution time drops from tens of milliseconds to well under one. Column order matters: this index also serves WHERE customer_id = ? on its own. PostgreSQL 18 adds B-tree skip scan, so it can sometimes use it for a condition on created_at alone when customer_id has few distinct values, but do not rely on that for a high-cardinality leading column like this one.
Other index types worth knowing:
-- Partial index: only the ~2% of rows a work queue cares about
CREATE INDEX CONCURRENTLY orders_pending_idx
ON orders (created_at) WHERE status = 'pending';
-- Expression index: case-insensitive lookups must use the same expression
CREATE INDEX CONCURRENTLY orders_email_lower_idx
ON orders (lower(email));
-- Covering index: INCLUDE lets an index-only scan answer the query
CREATE INDEX CONCURRENTLY orders_customer_amount_idx
ON orders (customer_id) INCLUDE (amount);
-- BRIN: tiny index for range queries on naturally ordered, append-only data
CREATE INDEX orders_created_brin
ON orders USING brin (created_at);Test each with the query that should use it, for example EXPLAIN ANALYZE SELECT * FROM orders WHERE lower(email) = 'user123@example.com';. A WHERE email = ... query will not use the expression index. Index-only scans need the visibility map to be current, which is one more reason autovacuum must keep up. Compare the sizes:
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS size, idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;Every index slows down writes and takes space, so remove the ones nothing uses. After a representative period (and checking the standby as well, since idx_scan is counted per server), list candidates:
SELECT schemaname, relname, indexrelname, idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i USING (indexrelid)
WHERE idx_scan = 0 AND NOT i.indisunique AND NOT i.indisprimary
ORDER BY pg_relation_size(indexrelid) DESC;Drop an index you no longer need with DROP INDEX CONCURRENTLY index_name;. Never drop indexes that back primary keys, unique constraints or foreign-key lookups just because the counter is low.
Step 11: Keep autovacuum ahead of the workload
An UPDATE or DELETE leaves the old row version behind as a dead tuple. Autovacuum reclaims that space, updates the visibility map, refreshes planner statistics and freezes old rows to prevent transaction ID wraparound. Never turn it off; tune it instead. A table is vacuumed when its dead tuples exceed:
autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × live rows
(defaults: 50 + 0.2 × rows, capped at autovacuum_vacuum_max_threshold in PostgreSQL 18)For a 100-million-row table the default waits for 20 million dead rows, which is far too late. Create some churn on the lab table and watch the counters:
UPDATE orders SET status = 'shipped' WHERE status = 'pending';
SELECT relname, n_live_tup, n_dead_tup,
round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
last_autovacuum, last_autoanalyze, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;Make autovacuum react sooner and work faster on large or busy tables. Per-table settings are the precise tool; the global cost limit lets workers do more I/O before they pause, which SSDs can absorb:
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02,
autovacuum_analyze_scale_factor = 0.01);
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 1000;
SELECT pg_reload_conf();Leave autovacuum_max_workers at 3 unless many large tables need vacuuming at once; the cost limit is shared among the active workers, so more workers alone do not make vacuuming faster. In PostgreSQL 18 it can be changed with a reload, up to autovacuum_worker_slots. Watch a running vacuum with:
SELECT pid, relid::regclass, phase, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_vacuum;The most common reason autovacuum cannot clean up is something holding back the oldest visible transaction: a long-running or idle-in-transaction session, an abandoned replication slot, or hot_standby_feedback on a standby running long queries (see part 4). Find old transactions, and keep an eye on wraparound age:
SELECT pid, usename, state, now() - xact_start AS xact_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;
SELECT slot_name, active, xmin, restart_lsn FROM pg_replication_slots;
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;
ALTER SYSTEM SET idle_in_transaction_session_timeout = '15min';
SELECT pg_reload_conf();A database age approaching autovacuum_freeze_max_age (200 million by default) triggers aggressive anti-wraparound vacuums; ages above one billion need attention now. VACUUM FULL returns space to the OS but takes an exclusive lock and rewrites the table, so use it only in a maintenance window.
Step 12: Do not fix slowness with max_connections
Each connection is a separate backend process with its own memory, and hundreds of mostly idle connections hurt throughput and multiply your work_mem risk. Keep max_connections near the default of 100 (a few hundred at most) and put a connection pooler in front of applications that open many connections; part 7 covers pgBouncer. On a standby, max_connections, max_worker_processes, max_wal_senders, max_prepared_transactions and max_locks_per_transaction must be at least as large as on the primary, so raise them on db2 first.
Verify the tuning
SELECT name FROM pg_settings WHERE pending_restart;returns no rows, andSHOW shared_buffers;shows the new value.- The buffer cache hit ratio for busy OLTP databases is typically above 99% once the cache has warmed up:
SELECT datname,
round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct
FROM pg_stat_database
WHERE datname IS NOT NULL;- The
pgbenchrun from step 1 shows equal or bettertps. - Your top
pg_stat_statementsentries use index scans inEXPLAIN ANALYZE, and the slow query log is quiet. n_dead_tupstays a small fraction ofn_live_tupon busy tables andlast_autovacuumis recent.- On the OS side,
iostat -x 5(packagesysstat) shows whether the disks are saturated; see Check Disk I/O on Oracle Linux for reading its output.
Troubleshooting
The server will not start after a change. Check sudo journalctl -u postgresql-18 -e and the newest file in /var/lib/pgsql/18/data/log/. A typo or an impossible value (for example shared_buffers larger than RAM, huge_pages = on without reserved pages, or an unsupported io_method) is named in the error. Edit /var/lib/pgsql/18/data/postgresql.auto.conf as the postgres user to remove the bad line, then start the server; once it is running, ALTER SYSTEM RESET name; is the clean way to undo a setting.
could not map anonymous shared memory or out-of-memory kills. Total memory is overcommitted. Lower shared_buffers or work_mem, check dmesg for the OOM killer, and remember that every connection can use several work_mem allocations.
pg_stat_statements must be loaded via "shared_preload_libraries". The extension was created but the library is not loaded. Add it to shared_preload_libraries (keeping any existing entries) and restart; a reload is not enough.
The planner ignores your index. Run ANALYZE table; first. Then check that the query matches the index: the same expression (lower(email)), a leading column in the WHERE clause, and matching data types, since comparing an integer column to a text parameter or wrapping the column in a function prevents index use. If the condition matches a large share of the table, a sequential scan really is cheaper. To test, SET enable_seqscan = off; in your session only and compare the actual times, then RESET enable_seqscan;.
CREATE INDEX CONCURRENTLY failed and left an invalid index. A failed concurrent build (deadlock, unique violation, cancelled session) leaves an index marked invalid that still slows writes. Find it with SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;, drop it with DROP INDEX CONCURRENTLY, and build it again.
Tables keep growing even though autovacuum runs. Dead rows cannot be removed while an old transaction or replication slot still needs them; use the queries in step 11 to find the blocker. If last_autovacuum is old on a busy table, lower its scale factor and raise autovacuum_vacuum_cost_limit. Space freed by vacuum is reused by new rows but not returned to the OS, so a table that has stopped growing is healthy.
Checkpoint warnings in the log. checkpoints are occurring too frequently means max_wal_size is too small for the write volume; raise it and confirm the WAL filesystem has room for it.
PostgreSQL tuning cheat sheet
| Task | Command |
|---|---|
| Show a setting and where it came from | SELECT name, setting, unit, source FROM pg_settings WHERE name = 'work_mem'; |
| Change a setting persistently | ALTER SYSTEM SET work_mem = '32MB'; |
| Undo a setting | ALTER SYSTEM RESET work_mem; |
| Reload configuration | SELECT pg_reload_conf(); |
| Settings waiting for restart | SELECT name FROM pg_settings WHERE pending_restart; |
| Per-role setting | ALTER ROLE reporter SET work_mem = '256MB'; |
| Huge pages needed | postgres -D datadir -C shared_memory_size_in_huge_pages (server stopped) |
| Run and time a query | EXPLAIN ANALYZE SELECT ...; (add BUFFERS before PG 18) |
| Build an index without blocking writes | CREATE INDEX CONCURRENTLY name ON table (cols); |
| Find invalid indexes | SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid; |
| Top queries by total time | SELECT calls, total_exec_time, query FROM pg_stat_statements ORDER BY 2 DESC LIMIT 10; |
| Dead tuples per table | SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY 2 DESC; |
| Per-table autovacuum | ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0.02); |
| Running vacuums | SELECT * FROM pg_stat_progress_vacuum; |
| Wraparound age | SELECT datname, age(datfrozenxid) FROM pg_database; |
| Benchmark | pgbench -c 10 -j 4 -T 60 dbname |
What’s next in the PostgreSQL series
Part 6 covers major-version upgrades with pg_upgrade; PostgreSQL 18’s pg_upgrade keeps optimizer statistics, so a freshly upgraded server no longer starts with an empty planner. Earlier parts: part 1, Install PostgreSQL on AlmaLinux 10: PGDG vs AppStream, pg_hba.conf and Remote Access; part 2, PostgreSQL Users, Roles and Privileges on AlmaLinux 10; part 3, PostgreSQL Backup and Restore on AlmaLinux 10; and part 4, PostgreSQL Streaming Replication on AlmaLinux 10.
Official references: PostgreSQL documentation: Resource Consumption, WAL Configuration, Using EXPLAIN, Indexes, Routine Vacuuming, pg_stat_statements and Linux huge pages.