Split View: DB 성능 튜닝 완전 가이드: 파라미터를 만지기 전에 측정하는 순서

The Complete Guide to Database Performance Tuning: Measure Before You Touch a Parameter

• Introduction
• 1. The Order of Tuning
• 2. Profiling the Workload — pg_stat_statements
• 3. Wait Events — Splitting the Bottleneck
• 4. Cache and I/O Metrics
• 5. The Memory Budget
• 6. The Write Path — Checkpoints and WAL
• 7. The Maintenance Path — autovacuum
• 8. Comparing Before and After
• Quiz: Check Your Understanding
• Closing Thoughts
• References
• Further Reading

Introduction

This blog already has several PostgreSQL performance tuning articles. PostgreSQL Performance Tuning in Practice, Query Optimization and Performance Tuning, and PostgreSQL 17 Performance Lab each approach it from a different angle.

This guide covers order, not parameters. The trouble with tuning articles is that they are usually a list of "what you can change." Lists are useful, but what you actually need in the field is which thing to look at first. Reaching for shared_buffers when a slowness report arrives produces a completely different outcome than first checking which query is spending the time.

So this guide follows a diagnostic order: workload profile, then wait events, then cache and I/O, and only then parameters. At each step it lays out what to look at and what to conclude, together with the defaults documented for the version.

The reference engine is PostgreSQL 18, and every default was confirmed in the PostgreSQL 18 documentation. Several defaults changed in PostgreSQL 18, so check your version.

1. The Order of Tuning

The order in which you ask questions is half of tuning.

1. What is slow — everything, or one query? Always, or in a particular window?
2. Where is the time going — executing or waiting? If waiting, on what?
3. Why — a bad plan, too much data, insufficient resources, or contention?
4. What will you change — the query, the schema, an index, or a parameter?

Note that parameters are number four. Only a minority of problems are solved by parameter tuning; most are a specific query, index, or schema problem. And a parameter affects the whole server, so a wrong change breaks other workloads.

Conversely, some problems really are parameter-only, because the defaults assume a small server. shared_buffers defaults to 128MB, work_mem to 4MB, and max_wal_size to 1GB. Leaving those untouched on a server with 256GB of RAM means letting the hardware idle.

2. Profiling the Workload — pg_stat_statements

The first step is "which queries spend the time." The answer lives in pg_stat_statements.

Installation is two steps: register it in shared_preload_libraries (a server restart is required) and create the extension.

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
compute_query_id = on
pg_stat_statements.max = 10000
pg_stat_statements.track = all

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

The documented defaults are 5000 for pg_stat_statements.max, top for track, on for track_utility, off for track_planning, and on for save. track_planning records planning time separately, but the documentation notes a performance penalty, so leave it off by default and turn it on only when needed.

How you read it matters. Sort by total time, not by average time.

SELECT calls,
       round(total_exec_time::numeric, 1)          AS total_ms,
       round(mean_exec_time::numeric, 2)           AS mean_ms,
       rows,
       shared_blks_hit, shared_blks_read,
       round(100.0 * shared_blks_hit
             / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
       left(query, 70) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

A 200ms query running 100 times a day is a far smaller load than a 3ms query running five million times. The latter never appears in a "slow query list." That is why a slow query log alone is not enough.

Columns to look at alongside it:

• rows divided by calls — how many rows come back per execution. A large value may mean the application is fetching more than it needs.
• temp_blks_written — temporary file writes. Anything above zero means a sort or hash spilled to disk because work_mem was insufficient.
• wal_bytes — how much WAL this query generated. Use it to find the source of write load.

To make the measurement window explicit, reset at the start of observation. The function signature is pg_stat_statements_reset(userid, dbid, queryid, minmax_only), and calling it with no arguments resets everything.

3. Wait Events — Splitting the Bottleneck

The second step is "is it burning CPU or waiting?" Fail to distinguish those and you will tune the wrong thing.

wait_event_type in pg_stat_activity gives the answer. Here are the values defined by the documentation.

The trick is to sample and look at the distribution. A single snapshot can be a coincidence.

-- Run repeatedly at one-second intervals to build a distribution
SELECT coalesce(wait_event_type, 'CPU') AS wait_type,
       wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active' AND backend_type = 'client backend'
GROUP BY 1, 2
ORDER BY 3 DESC;

The baseline interpretation: an active backend with a NULL wait_event is genuinely consuming CPU. A high share there means the query itself is heavy or the plan is bad, and you should look at queries and indexes rather than parameters. A dominant IO share means insufficient cache or a storage limit. A dominant Lock share is not a resource problem at all — it is a design and transaction-boundary problem.

If Client dominates, the database is idle. Tuning the database in that state is a waste of time.

4. Cache and I/O Metrics

The third step is "where do the reads come from?"

-- Per-table buffer hit ratio, ordered by most reads
SELECT relname,
       heap_blks_read, heap_blks_hit,
       round(100.0 * heap_blks_hit
             / nullif(heap_blks_hit + heap_blks_read, 0), 2) AS heap_hit_pct,
       idx_blks_read, idx_blks_hit
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC
LIMIT 15;

There is a trap to be careful of here. heap_blks_read counts blocks that were not in the PostgreSQL buffer pool and were therefore requested from the operating system. If they were in the OS page cache, no physical disk I/O occurred. Judging disk load from this number alone overstates it.

pg_stat_io, available since PostgreSQL 16, gives a more accurate picture. It breaks out reads, hits, evictions, and fsyncs by backend_type, object, and context.

SELECT backend_type, object, context,
       reads, hits, evictions, fsyncs
FROM pg_stat_io
WHERE reads > 0 OR evictions > 0
ORDER BY reads DESC
LIMIT 20;

Large values where context is bulkread or vacuum are normal — bulk scans and VACUUM deliberately use a limited ring buffer so they do not pollute the buffer pool. The problem case is a large evictions count where context is normal. That means the working set does not fit in shared_buffers and buffers are being pushed out continuously.

Look at table access patterns as well.

SELECT relname, seq_scan, seq_tup_read, idx_scan,
       n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 15;

A large seq_scan is not automatically bad — for a small table a sequential scan is the right answer. What to look at is seq_tup_read divided by seq_scan, that is, how many rows one sequential scan reads. When that value is large and idx_scan is small, you have an index candidate.

5. The Memory Budget

Now the parameters. Three memory parameters serve three different purposes.

shared_buffers — PostgreSQL's own buffer pool. Default 128MB. The documentation's guidance: "If you have a dedicated database server with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the memory in your system," and "because PostgreSQL also relies on the operating system cache, it is unlikely that an allocation of more than 40% of RAM to shared_buffers will work better than a smaller amount." Changing it requires a server restart.

work_mem — the working area for sorts and hashes. Default 4MB. This is the calculation people get wrong most often. The documentation's own words: "Note that a complex query might perform several sort and hash operations at the same time, with each operation generally being allowed to use as much memory as this value specifies before it starts to write data into temporary files. Also, several running sessions could be doing such operations concurrently. Therefore, the total memory used could be many times the value of work_mem; it is necessary to keep this fact in mind when choosing the value."

In other words, connection count times work_mem understates the worst case. Hash-family operations multiply further by hash_mem_multiplier (default 2.0). The safe approach is a conservative global value, raised per session only for heavy analytical queries.

-- In this session only, for this query
SET LOCAL work_mem = '256MB';

maintenance_work_mem — memory for maintenance work. Default 64MB. The documentation defines it as "the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY." Few maintenance operations run at once, so it is safe to set it much higher than work_mem.

effective_cache_size — allocates no memory and only changes the planner's assumption. Default 4GB. It is an estimate of available cache including the OS cache, and if it is smaller than reality the planner underrates index scans.

Some I/O defaults changed in PostgreSQL 18. effective_io_concurrency now defaults to 16, the newly introduced io_method defaults to worker, and io_combine_limit defaults to 128kB. If you upgraded from an earlier version, check these items in the documentation.

6. The Write Path — Checkpoints and WAL

If writes are slow or latency spikes periodically, suspect checkpoints.

The documented defaults: checkpoint_timeout is 5 minutes, checkpoint_completion_target is 0.9, max_wal_size is 1GB, and min_wal_size is 80MB.

Symptom-to-cause mapping: periodic latency spikes at intervals of tens of seconds most likely mean max_wal_size is small and checkpoints are being triggered by WAL volume rather than by time. To confirm, turn on log_checkpoints and look at the checkpoint reason in the log. Seeing xlog instead of time as the reason confirms it.

ALTER SYSTEM SET log_checkpoints = on;
SELECT pg_reload_conf();

Raising max_wal_size reduces checkpoint frequency and smooths the latency spikes. The costs are longer crash recovery and more disk usage. Both of those must be decided together with the business requirement (RTO).

synchronous_commit defaults to on, meaning every commit waits until the WAL is safely on disk. Turning it off increases write throughput substantially, but the last few commits can be lost in a crash. Data consistency itself is not broken, but transactions you told the client were committed can disappear. Whether that trade is acceptable is a domain decision. It can be toggled per session or per transaction, so relaxing it for writes to a subset of tables such as audit logs is also possible.

wal_compression defaults to off. Turning it on compresses full page images and reduces WAL volume at the cost of CPU. Worth considering when replication bandwidth is the bottleneck.

commit_delay defaults to 0 and commit_siblings defaults to 5. These are knobs for chasing group-commit effects under very high commit concurrency, and used carelessly they only add latency. Touch them after everything else has been checked.

7. The Maintenance Path — autovacuum

Bloat is the cause of slowness more often than people expect. When autovacuum cannot keep up, dead rows accumulate, sequential scans read more pages, and indexes grow.

The documented defaults: autovacuum on, autovacuum_max_workers 3, autovacuum_naptime 1 minute, autovacuum_vacuum_threshold 50 tuples, autovacuum_vacuum_scale_factor 0.2 (20% of the table), autovacuum_analyze_threshold 50 tuples, autovacuum_analyze_scale_factor 0.1 (10%), autovacuum_vacuum_cost_delay 2 milliseconds, and autovacuum_freeze_max_age 200 million transactions.

PostgreSQL 18 adds autovacuum_vacuum_max_threshold with a default of 100 million tuples. That ceiling keeps vacuum from being deferred indefinitely even on very large tables.

The default scale factor becomes a problem on large tables. On a 100-million-row table, 20% is 20 million rows. That many must die before vacuum runs. Lowering it per table is the standard response.

-- Manage large tables by absolute volume rather than by ratio
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor  = 0.01,
  autovacuum_analyze_scale_factor = 0.005
);

This setting changes a table storage parameter, so it takes only a SHARE UPDATE EXCLUSIVE lock. It blocks neither reads nor writes.

If autovacuum runs but cannot keep up, look at the throttling. The 2-millisecond default of autovacuum_vacuum_cost_delay exists to limit vacuum's impact on service traffic, but on a write-heavy system it can prevent vacuum from ever catching up. Raising autovacuum_max_workers is another option, but the total cost budget is shared among the workers, so it must be considered together with the delay setting.

Check progress like this.

SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Warning: Do not run VACUUM FULL or CLUSTER in production just because bloat is already severe. Both take an ACCESS EXCLUSIVE lock that blocks even reads, and both rewrite the whole table, requiring free disk space equal to the original size. The PostgreSQL documentation itself advises that "administrators should strive to use standard VACUUM and avoid VACUUM FULL." If only the indexes are the problem, REINDEX INDEX CONCURRENTLY is the safe alternative.

8. Comparing Before and After

The last step, and the one most often skipped. A change without a comparison is not tuning; it is guessing.

The procedure:

Step 1 — record a baseline. Just before the change, reset pg_stat_statements and save the result of observing for a fixed period (at least one business cycle). Save the wait-event distribution and a pg_stat_io snapshot too.

Step 2 — change one thing at a time. Change three at once and you cannot tell which one had the effect, nor which to revert when things get worse.

Step 3 — confirm how to revert first. A value set with ALTER SYSTEM is reverted with ALTER SYSTEM RESET. Check in advance whether the parameter needs a restart: shared_buffers does, work_mem does not.

-- Change
ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();

-- Confirm: where did the value come from
SELECT name, setting, unit, source, pending_restart
FROM pg_settings
WHERE name IN ('work_mem', 'shared_buffers', 'max_wal_size',
               'effective_cache_size', 'random_page_cost');

-- Revert
ALTER SYSTEM RESET work_mem;
SELECT pg_reload_conf();

The source column of pg_settings tells you where a value came from. Most "I edited the config file but nothing changed" problems are solved right here. If pending_restart is true, a restart is required for the value to apply.

Step 4 — measure again with the same metrics. The same query and the same observation window as step 1. And decide on one representative metric for whether it improved. The sum of total_exec_time for the top 20 queries, the p95 response time, or throughput per second — any one is enough.

Step 5 — record the decision. Why you changed it, what the evidence was, and how much it improved. Six months later, someone will definitely ask why this value is set the way it is.

Quiz: Check Your Understanding

Closing Thoughts

The biggest waste in performance tuning is not a wrong parameter but starting without measuring. Double shared_buffers without knowing which query spends the time and you cannot even tell whether things got better or worse.

To restate the order: find the source of the load with pg_stat_statements, split the bottleneck by wait event, confirm the resource situation with cache and I/O metrics, and only then change parameters one at a time, measuring each time. Follow that order and tuning becomes a procedure rather than a craft, and a procedure can be handed to a team.

And finally, check your version. In PostgreSQL 18 the default for effective_io_concurrency became 16, io_method is new, and EXPLAIN ANALYZE now includes buffer information automatically. Half the tuning values floating around the internet are based on versions from years ago.

The diagnostic queries in this guide can be run directly in the Postgres Playground.

References

• PostgreSQL 18, Resource Consumption: https://www.postgresql.org/docs/18/runtime-config-resource.html (accessed 2026-08-15)
• PostgreSQL 18, Write Ahead Log: https://www.postgresql.org/docs/18/runtime-config-wal.html (accessed 2026-08-15)
• PostgreSQL 18, Automatic Vacuuming: https://www.postgresql.org/docs/18/runtime-config-autovacuum.html (accessed 2026-08-15)
• PostgreSQL 18, Query Planning: https://www.postgresql.org/docs/18/runtime-config-query.html (accessed 2026-08-15)
• PostgreSQL 18, Monitoring Database Activity: https://www.postgresql.org/docs/18/monitoring-stats.html (accessed 2026-08-15)
• PostgreSQL 18, pg_stat_statements: https://www.postgresql.org/docs/18/pgstatstatements.html (accessed 2026-08-15)
• PostgreSQL 18, Routine Vacuuming: https://www.postgresql.org/docs/18/routine-vacuuming.html (accessed 2026-08-15)
• PostgreSQL 18, EXPLAIN: https://www.postgresql.org/docs/18/sql-explain.html (accessed 2026-08-15)

Further Reading

• Previous in series: The Complete Guide to Data Modeling — from logical model to physical schema
• Back to the first: The Complete Guide to PostgreSQL Indexes — the index lifecycle
• The Complete Guide to SQL Execution Plans — how the optimizer picks a plan
• PostgreSQL Performance Tuning in Practice — the parameter catalog
• PostgreSQL VACUUM and MVCC Internals — how bloat happens
• Postgres Playground — run the diagnostic queries