Skip to content
Tutorials Center
My learning

Topics

Lesson 90 of 125 ยท Performance

Tuning the Database Server

Tune a database server: buffer pool and shared_buffers, work_mem, connections and pooling, autovacuum and statistics, checkpoints, WAL and monitoring.

The earlier lessons in this chapter made individual queries faster: reading query plans, rewriting queries and adding the right indexes. Those fixes matter most, and they always come first. But every query also runs inside a server with a fixed amount of memory, a limited number of connections, background jobs that clean up and collect statistics, and a disk that has to make every commit durable. If those settings are wrong for your workload, even perfect queries are slow.

This lesson covers the server settings that matter most, in the order you'd usually check them: memory (the buffer cache and per-query work memory), connections, statistics and cleanup (autovacuum and ANALYZE), durability (WAL, checkpoints and commit settings) and monitoring. PostgreSQL, MySQL and SQL Server settings are read-only; SQLite exposes its own versions of the same ideas as PRAGMA statements, which you can run and change below.

How to tune: measure first

Server tuning has a reputation for magic numbers copied from blog posts. Resist that. A sound process looks like this:

  1. Find the real bottleneck. Is the server short of CPU, memory, disk I/O or connections? Or is one bad query responsible for most of the load? Monitoring (last section) answers this.
  2. Fix queries and indexes first. A missing index can cost 1,000 times more than a mistuned cache. No setting rescues a query that scans 50 million rows per request.
  3. Change one setting at a time, for a reason you can state, and measure before and after under realistic load.
  4. Write it down. Keep configuration in version control or infrastructure code, with a comment explaining each non-default value.

Defaults are deliberately conservative so the database starts on a small laptop. On a dedicated server, a few memory settings are almost always worth raising; most of the hundreds of other settings are best left alone.

Seeing the current settings

Every database lets you inspect its configuration with SQL (read-only):

SQL
-- PostgreSQL
SHOW shared_buffers;
SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'max_connections');

-- MySQL / MariaDB
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SELECT @@max_connections;

-- SQL Server
SELECT name, value_in_use FROM sys.configurations
WHERE name IN ('max server memory (MB)', 'cost threshold for parallelism');

SQLite's settings are per connection, and each PRAGMA doubles as a table-valued function (pragma_page_size() and so on), so one query can collect them all:

SQL A settings overview with PRAGMA functions ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The shop database is 12 pages of 4,096 bytes (49,152 bytes, with no free pages). The cache size is -2000, the journal mode delete, synchronous is 2 (FULL) and temp_store is 0 (the compiled-in default). The rest of this lesson explains what each of these means and what its server-database counterpart is.

Memory: the buffer cache

Reading from memory is thousands of times faster than reading from disk, so every database keeps recently used pages (fixed-size blocks of tables and indexes) in a memory cache. When a query needs a page that's already cached, it's a cache hit; otherwise the page must be read from disk. A busy database should serve the vast majority of reads (often over 99%) from memory.

Database Setting Typical starting point on a dedicated server
PostgreSQL shared_buffers About 25% of RAM. PostgreSQL also relies on the operating system's file cache, so going much higher rarely helps. Set effective_cache_size (a planner hint, not an allocation) to about 50โ€“75% of RAM.
MySQL (InnoDB) innodb_buffer_pool_size 50โ€“75% of RAM. InnoDB does most of its own caching, so this is the single most important MySQL setting.
SQL Server max server memory (MB) Leave a few GB plus room for other software for the OS; SQL Server otherwise takes nearly all available memory.
SQLite PRAGMA cache_size Per connection; the default is about 2 MB.
SQL
-- PostgreSQL: written to postgresql.auto.conf; shared_buffers needs a restart
ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET effective_cache_size = '24GB';

-- MySQL 8.0: the buffer pool can be resized online and persisted
SET PERSIST innodb_buffer_pool_size = 24 * 1024 * 1024 * 1024;

-- SQL Server
EXEC sp_configure 'max server memory (MB)', 28672;
RECONFIGURE;

A rule of thumb: you want the working set (the data and indexes that queries touch regularly, not the whole database) to fit in the cache. A 500 GB database whose queries mostly touch the last month of orders may run happily with a 16 GB cache.

SQLite's cache_size has a quirk worth knowing: a negative number means kibibytes, a positive number means pages. And the page size itself can only change when the file is rebuilt:

SQL Cache size and page size in SQLite ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first PRAGMA page_size after setting 8192 still reports 4096, because the existing file keeps its page size. After VACUUM rewrites the file, it reports 8192, and the same data now fits in 8 pages instead of 12. Larger pages can help read-heavy workloads that scan a lot of data; the 4,096-byte default matches most file systems and is a good choice for typical applications. Because cache_size is a per-connection setting, an application sets it right after connecting, the same way it sets foreign_keys (see Connecting to Databases from Code).

Memory for sorts, hashes and joins

The buffer cache is shared. Separately, each query gets working memory for operations that build temporary structures: sorting for ORDER BY, hash tables for GROUP BY and hash joins, and DISTINCT. When an operation needs more than its allowance, it spills to disk (temporary files) and becomes much slower.

In PostgreSQL the allowance is work_mem (default 4 MB). The catch: it applies per operation, per query. A complex query can contain several sorts and hashes, and 200 connections might each run one, so the worst case is roughly work_mem ร— operations ร— connections. Setting work_mem = '1GB' globally can run a server out of memory. The usual approach is a modest global value and a larger one for specific sessions that need it:

SQL
-- PostgreSQL
ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();             -- no restart needed for this one

-- Just for this session (e.g. a nightly report)
SET work_mem = '512MB';

-- Index builds and VACUUM have their own allowance
ALTER SYSTEM SET maintenance_work_mem = '1GB';

-- Did a sort spill? EXPLAIN ANALYZE shows it
EXPLAIN (ANALYZE) SELECT * FROM orders ORDER BY ordered_at;
--   Sort Method: external merge  Disk: 10240kB   <- spilled
--   Sort Method: quicksort  Memory: 2048kB       <- fit in memory

MySQL's per-session equivalents are sort_buffer_size, join_buffer_size and tmp_table_size; SQL Server grants query memory automatically and reports spills as warnings in the execution plan.

In SQLite, the same operations show up in plans as USE TEMP B-TREE, and PRAGMA temp_store decides whether those temporary structures live in memory or in temporary files:

SQL Where sorts and groups do their work ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first plan needs two temporary B-trees, one for GROUP BY and one for ORDER BY. After PRAGMA temp_store = MEMORY (which reports 2), both would be built in RAM. The index then removes the GROUP BY step entirely: SQLite reads the index in customer order and only sorts the final, much smaller result. That's the general lesson: giving sorts more memory helps, but avoiding the sort with an index helps more.

Connections

Each connection costs server memory (in PostgreSQL, a whole process, often 5โ€“10 MB or more once it has run some queries). And only about as many queries as there are CPU cores can truly run at the same moment; the rest wait, holding memory and locks. That's why "just raise max_connections" usually makes things worse.

Database Setting Default
PostgreSQL max_connections 100 (restart required to change)
MySQL max_connections 151
SQL Server user connections 0 = up to 32,767 (limited by memory in practice)

The healthy pattern is a small number of busy connections instead of a large number of idle ones:

  • Use a connection pool in every application instance, sized modestly (often 10โ€“20).
  • When many processes connect (serverless functions, many microservices, PHP workers), put an external pooler such as PgBouncer (transaction pooling mode) or ProxySQL in front of the server, so thousands of client connections share a few dozen server connections.
  • Set timeouts so that stuck work doesn't hold connections forever:
SQL
-- PostgreSQL
ALTER SYSTEM SET statement_timeout = '30s';                     -- cancel runaway queries
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';   -- kill forgotten open transactions

-- MySQL: limit SELECT run time (milliseconds)
SET GLOBAL max_execution_time = 30000;

An idle in transaction session is especially harmful: it holds locks and, in PostgreSQL, prevents cleanup of old row versions across the whole database.

SQLite has no server and no connection limit, but writers take turns, so the equivalent setting is how long a connection waits for a lock before giving up:

SQL
-- SQLite: wait up to 5 seconds for a lock instead of failing with "database is locked"
PRAGMA busy_timeout = 5000;

Statistics and cleanup: ANALYZE and autovacuum

The query planner chooses plans based on statistics: row counts, the number of distinct values, and value distributions. Stale statistics produce bad plans, such as a scan where an index search would be a thousand times faster. Every major database refreshes statistics automatically, but bulk loads and big deletes can outrun the automatic process, so run ANALYZE (or UPDATE STATISTICS in SQL Server) after them.

PostgreSQL has an extra job. Because of its MVCC design, an UPDATE or DELETE leaves the old row version behind as a dead tuple. Autovacuum is the background process that removes dead tuples and refreshes statistics. If it falls behind, tables bloat (grow with dead space), queries slow down, and in the extreme case the server must stop to avoid transaction ID wraparound. Never turn autovacuum off; tune it instead:

SQL
-- PostgreSQL: by default a table is vacuumed after about 20% of its rows change.
-- For a huge, busy table, make it start much earlier:
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

-- Which tables have the most dead tuples, and when were they last cleaned?
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

MySQL's InnoDB purges old versions with background threads and updates statistics automatically (innodb_stats_auto_recalc); SQL Server has AUTO_UPDATE_STATISTICS. SQLite has no automatic statistics, but PRAGMA optimize is designed to be run regularly (for example, when a connection closes). It runs ANALYZE only where it judges it worthwhile:

SQL Statistics with PRAGMA optimize ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Before PRAGMA optimize, the shop has no sqlite_stat* tables. Afterwards, sqlite_stat1 holds a row for each index, for example 120 30 for orders_status_idx: 120 rows, about 30 per status value. The planner now knows that a status lookup isn't very selective. The Backups, Migrations and Maintenance lesson shows how to read these numbers and reclaim space with VACUUM.

Durability: WAL, checkpoints and commits

When you COMMIT, the database promises the change survives a crash. To keep that promise without rewriting data files on every commit, databases first append each change to a sequential log: the WAL (write-ahead log) in PostgreSQL and SQLite, the redo log in InnoDB, the transaction log in SQL Server. Data pages are written to their real place later. A checkpoint is the moment when all changed pages up to some log position are flushed to the data files, so older log can be recycled.

Checkpoint settings are a trade-off between steady performance and recovery time:

  • Frequent checkpoints keep crash recovery short but cause bursts of writes.
  • Infrequent checkpoints smooth out writes but mean more log to replay after a crash, and more disk for log files.
SQL
-- PostgreSQL: checkpoint at most every 15 minutes or when 4 GB of WAL accumulates,
-- and spread the writes over 90% of the interval
ALTER SYSTEM SET checkpoint_timeout = '15min';
ALTER SYSTEM SET max_wal_size = '4GB';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;

-- MySQL 8.0.30+: total redo log size
SET PERSIST innodb_redo_log_capacity = 4 * 1024 * 1024 * 1024;

The other durability lever is how strictly a commit waits for the disk:

Database Setting Fully durable (default) Faster, can lose recent commits on a crash
PostgreSQL synchronous_commit on off: may lose the last few hundred milliseconds of commits, but never corrupts data
MySQL innodb_flush_log_at_trx_commit 1 2 (flush about once per second) or 0
SQLite (WAL mode) PRAGMA synchronous FULL NORMAL: safe from corruption; a power loss may roll back the last commits

Never use fsync = off (PostgreSQL) or synchronous = OFF (SQLite) on data you care about. Those settings skip waiting for the disk entirely, and a crash or power loss can then corrupt the database, not just lose a few commits. They're only for throwaway data you can rebuild, such as a one-off bulk load into a fresh file.

In SQLite, the write-ahead log is optional. The default delete journal mode makes readers and the writer block each other; WAL mode lets readers continue while one connection writes, and is the right choice for almost every application. Combined with synchronous = NORMAL, it's also much faster for many small transactions:

SQL WAL mode and commit durability in SQLite ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

PRAGMA journal_mode = WAL answers wal, synchronous now reports 1 (NORMAL), and SQLite will checkpoint automatically whenever the log reaches 1,000 pages. Unlike most PRAGMAs, WAL mode is stored in the database file, so it stays on for every future connection. The insert works as usual: journal modes change how commits reach the disk, never what queries return.

Monitoring

You can only tune what you can see. Every serious deployment watches a few things continuously:

  • Slow and expensive queries. PostgreSQL's pg_stat_statements extension, MySQL's slow query log and performance_schema, and SQL Server's Query Store record which queries use the most total time. Sort by total time, not by the slowest single run: a 5 ms query run 2 million times an hour matters more than a 10-second report run once a day.
  • Active sessions and locks. What is running right now, for how long, and who is waiting for whom.
  • Cache hit ratio, temp file usage, checkpoint frequency and replication lag.
  • Server resources: CPU, memory, disk I/O latency, disk space (running out of disk is one of the most common outages).
SQL
-- PostgreSQL: the 5 queries with the most total execution time
SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms,
       left(query, 60) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

-- PostgreSQL: what's running now, longest first
SELECT pid, state, now() - query_start AS running_for, left(query, 60) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY running_for DESC;

-- PostgreSQL: buffer cache hit ratio per database
SELECT datname, round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct
FROM pg_stat_database;

-- MySQL: log every statement slower than 1 second
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

Managed services show most of these numbers in their dashboards (for example, Amazon RDS Performance Insights or Azure's Query Performance Insight), and tools such as Prometheus exporters and Grafana dashboards collect them for self-hosted servers. Set alerts on disk space, replication lag, connection count and long-running transactions before you need them.

A tuning checklist

Area First thing to check
Queries Top queries by total time; their plans and indexes
Memory Buffer cache size (shared_buffers, innodb_buffer_pool_size); hit ratio
Work memory Sorts or hashes spilling to disk; raise per session where needed
Connections Pool sizes, an external pooler for many clients, statement and idle timeouts
Statistics Autovacuum / auto-statistics keeping up; ANALYZE after bulk changes
Durability WAL/redo size and checkpoint spacing; never disable fsync
Hardware SSD storage; for PostgreSQL on SSDs, random_page_cost around 1.1 instead of 4

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A PostgreSQL server has 200 connections. A teammate proposes setting work_mem = '1GB' globally to speed up sorts. What is the risk?

    Keep work_mem modest globally and raise it with SET work_mem for the sessions (like nightly reports) that really need it.
  2. 2. In SQLite, what does PRAGMA cache_size = -16000; mean?

    Negative values are sizes in KiB, positive values are page counts. With 4,096-byte pages, 16,000 pages would be about 64 MB.
  3. 3. Your application has slow peaks with 2,000 open connections to PostgreSQL, most of them idle. What usually helps most?

    Each connection costs memory, and only about as many queries as CPU cores can truly run at once. Pooling reduces the number of server connections without reducing the work done.
  4. 4. Why should autovacuum never be switched off in PostgreSQL?

    MVCC leaves dead tuples behind on UPDATE and DELETE. Autovacuum removes them and refreshes statistics; tune it per table instead of disabling it.
  5. 5. Which setting may lose the last few commits after a power loss but does NOT risk corrupting the database?

    synchronous_commit = off only delays the commit acknowledgment; the WAL stays consistent. fsync = off and synchronous = OFF skip disk syncs entirely and can corrupt data on a crash.

Summary

  • Tune by measuring: find the bottleneck, fix queries and indexes first, then change one setting at a time.
  • The buffer cache (shared_buffers ~25% of RAM, innodb_buffer_pool_size 50โ€“75%, SQL Server max server memory, SQLite cache_size) should hold the working set.
  • Work memory (work_mem) is per operation per query; raise it per session, and watch for sorts spilling to disk. In SQLite, USE TEMP B-TREE steps and temp_store are the equivalent.
  • Keep connections few and busy: application pools, an external pooler (PgBouncer, ProxySQL) and statement/idle timeouts.
  • Fresh statistics keep plans good; PostgreSQL's autovacuum also removes dead tuples and must never be disabled. SQLite offers PRAGMA optimize.
  • WAL and checkpoints trade steady writes against recovery time. Relax commit durability only knowingly (synchronous_commit = off, SQLite synchronous = NORMAL in WAL mode); never disable fsync.
  • Monitor top queries by total time, active sessions, cache hit ratio, disk space and replication lag.

Next: PostgreSQL Essentials, where you'll start the Database Systems chapter with the most popular open-source server database.