How to increase database performance

"How do I increase database performance?" is the single most common question on every operations channel, and the most common answer — "buy a bigger box" — is almost always wrong. Hardware buys you a constant factor; a missing index buys you an order of magnitude. Buffer-pool tuning buys you breathing room; an N+1 fixed in the application makes the problem evaporate. The order in which you investigate matters more than the absolute techniques.

This guide is engine-agnostic where it can be — the same principles apply to MySQL, MariaDB, PostgreSQL, and to a lesser extent SQL Server and Oracle — and it calls out engine-specific tools when relevant. The throughline: measure, find the actual bottleneck, fix the single biggest thing, re-measure. Do not skip ahead to "tune the config".

The order of operations

Work through these layers in order. Most problems are solved in the first two; skipping ahead is how teams end up with a fast cache layer in front of a slow query that should never have run.

Measure. Without numbers you are guessing. Slow query log, EXPLAIN, pg_stat_statements / performance_schema, real query latency from the app.
Fix queries. Indexes, query shape, N+1 patterns, unnecessary SELECT *, joins on unindexed columns.
Fix the schema. Data types, normalization (or controlled denormalization), constraints, partitioning, archival.
Tune configuration. Buffer pools, work memory, connection limits, checkpoint behaviour.
Fix the OS and disk. Filesystem, scheduler, fsync semantics, NUMA, swap.
Scale out. Read replicas, connection pooling, sharding, caching, separation of OLTP and analytics.

Almost every team finds the win in steps 2 or 3. Steps 4 onward are real, but they are amplifiers — they make a healthy database faster, they rarely save a sick one.

1. Measure first

You cannot tune what you do not measure. The minimum viable observability for a database:

Enable the slow query log.

MySQL / MariaDB:
```
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
```
Analyse with pt-query-digest /var/log/mysql/slow.log — it groups queries by fingerprint and shows you which patterns burn the most cumulative time.

PostgreSQL: enable pg_stat_statements and set log_min_duration_statement = 1000 (log anything over 1s).

CREATE EXTENSION pg_stat_statements;
SELECT query, calls, total_exec_time, mean_exec_time
  FROM pg_stat_statements
  ORDER BY total_exec_time DESC LIMIT 20;

Always read EXPLAIN (or EXPLAIN ANALYZE). Before you "optimise" anything, read what the planner actually does. In Postgres, EXPLAIN (ANALYZE, BUFFERS) shows real time and cache hits. In MySQL 8, EXPLAIN ANALYZE plus EXPLAIN FORMAT=TREE is the modern equivalent.

Watch the OS. A "slow database" with an iowait of 80% is a slow disk problem. With high %us it is a CPU/query problem. With high context-switch counts it's lock or connection contention. Tools: vmstat 1, iostat -x 1, pidstat -d 1, top -H.

If you cannot answer "which query is slowest, by total time spent, in the last hour?", that is the first problem to fix — every other optimisation is shooting in the dark.

2. Fix the queries and indexes

The single largest source of "the database is slow" problems is queries doing more work than they need to.

The non-negotiables

Index columns used in WHERE, JOIN, and ORDER BY. A 100M-row table with no index on the lookup column will scan 100M rows on every query.
Use composite indexes correctly. An index on (a, b) helps WHERE a = ? and WHERE a = ? AND b = ?, but not WHERE b = ?. Put the most selective / equality column first.
Index foreign keys. MySQL auto-indexes FKs; Postgres does not. A missing FK index makes DELETE on the parent table degenerate.
Stop using SELECT *. Return only the columns you need. Wide BLOB/TEXT columns in the projection force temp tables to disk.
Avoid functions on indexed columns in WHERE. WHERE DATE(created_at) = '2026-05-14' cannot use an index on created_at. Rewrite as a range: WHERE created_at >= '2026-05-14' AND created_at < '2026-05-15'. (Or build a functional index — Postgres CREATE INDEX … ON table (DATE(created_at)).)
Kill the N+1. Loading 1 parent and then 1 child row per parent in a loop is the most common application-side cause of "the DB is slow". Replace with a single join or a batched WHERE id IN (...).
LIMIT deeply paginated queries are O(offset). OFFSET 100000 LIMIT 20 reads 100,020 rows. Use keyset pagination: WHERE id > {last_seen_id} ORDER BY id LIMIT 20.

Diagnosing the query

Take the top query from the slow log / pg_stat_statements and run it under EXPLAIN ANALYZE. Look for:

Sequential / full table scans on large tables. Add an index, or rewrite the predicate so an existing index is usable.
Sort operations spilling to disk. Either reduce the result set, add an index that already provides the order, or raise sort_buffer_size (MySQL) / work_mem (Postgres) for the session.
Nested loop joins on millions of rows. Usually a planner picking the wrong plan because statistics are stale. ANALYZE the table (Postgres) or run ANALYZE TABLE (MySQL).
Using temporary; Using filesort in MySQL EXPLAIN. The query is building a temp table — often a sign of grouping/sorting on a non-indexed expression.

Don't over-index

Every index speeds reads and slows writes (the index must be updated on every INSERT/UPDATE/DELETE). On a write-heavy table, ten indexes is a problem. Audit redundant indexes:

MySQL: pt-duplicate-key-checker
Postgres: SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0; — indexes that have never been used.

3. Fix the schema

The schema decisions you made on day one outlast every other tuning effort.

Use the smallest data type that fits. INT is 4 bytes; BIGINT is 8. A column holding values up to 1,000 doesn't need either — SMALLINT (2 bytes) is fine. Multiply by row count.
Use DATETIME/TIMESTAMP types, not strings. Storing dates as VARCHAR defeats every range optimization the planner has.
Normalize, then denormalize where you have proven it helps. Premature denormalization makes writes correct in two places, which they rarely stay.
Keep BLOB/TEXT columns out of hot rows. A users table with a profile_json TEXT column makes every SELECT * heavy. Either project explicitly, or split the column into a side table.
Partition very large tables. Range partitioning by date is the common pattern — events_2026_05, events_2026_06. Old partitions can be detached and archived without touching the live table. Both MySQL 8 and Postgres 12+ support declarative partitioning.
Archive cold data. A 500 GB table where 95% of queries hit the last 30 days is a candidate for hot/cold separation: move rows older than N days to an archive table or external storage.

4. Configuration tuning

Only after the queries and schema are sane. The configuration knobs that genuinely matter:

MySQL / MariaDB (InnoDB)

[mysqld]
# Memory
innodb_buffer_pool_size       = 24G       # 70-75% of RAM on a dedicated host
innodb_buffer_pool_instances  = 8         # 1 per ~1 GB pool, up to ~8
innodb_log_file_size          = 2G        # MySQL 5.7
innodb_redo_log_capacity      = 8G        # MySQL 8, dynamic

# Concurrency
innodb_io_capacity            = 2000      # for SSDs; 200 is for spinning disk
innodb_io_capacity_max        = 4000
innodb_flush_method           = O_DIRECT  # bypass the OS page cache
innodb_flush_neighbors        = 0         # 0 on SSD, 1 on HDD

# Logging
slow_query_log                = 1
long_query_time               = 1
log_queries_not_using_indexes = 1

# Connections
max_connections               = 300       # only as high as your pooler needs

The single largest win is right-sizing the InnoDB buffer pool. If your hot data fits in RAM, you are CPU-bound and the rest is fine-tuning. If it doesn't, you're disk-bound and no other knob saves you.

PostgreSQL

# Memory
shared_buffers                = 8GB       # ~25% of RAM
effective_cache_size          = 24GB      # ~75% of RAM (planner hint, not allocation)
work_mem                      = 32MB      # per-operation; tune up cautiously
maintenance_work_mem          = 1GB       # for VACUUM / CREATE INDEX

# Write performance
wal_buffers                   = 16MB
checkpoint_completion_target  = 0.9
max_wal_size                  = 8GB
min_wal_size                  = 2GB

# Planner
random_page_cost              = 1.1       # SSD; 4.0 was for spinning rust
effective_io_concurrency      = 200       # SSD

# Logging
log_min_duration_statement    = 1000
log_checkpoints               = on
log_lock_waits                = on

The two biggest decisions: shared_buffers (≈25% of RAM is the canonical starting point) and work_mem (per-operation, multiplied by the number of concurrent operations — be conservative).

Connection limits

max_connections is not a performance lever. Postgres allocates per-connection memory; 1,000 connections is a problem regardless of shared_buffers. Use a connection pooler — PgBouncer in front of Postgres, ProxySQL in front of MySQL — and keep the database's max_connections modest (200–400).

5. OS, disk, and hardware

Once the database is configured correctly, the floor is set by the kernel and the disk underneath.

Use SSDs / NVMe. This is no longer a tuning recommendation; it is a baseline. Random read IOPS of 50k+ versus 200 on a spinning disk is two orders of magnitude.
Filesystem. ext4 and xfs are both fine for production databases. Avoid btrfs and zfs unless you have a specific reason (snapshots, compression) and have tested.
Mount with noatime. Disables atime updates on every read.
Disable transparent huge pages for MySQL on Linux (echo never > /sys/kernel/mm/transparent_hugepage/enabled) — they cause latency spikes on the buffer pool.
Swappiness. Set vm.swappiness=1 (not 0 — 0 can OOM-kill the database under pressure). Sized correctly, a database should never page out, but a small amount of swap is a safety valve.
NUMA. On multi-socket boxes, MySQL benefits from numa_interleave=1 (or numactl --interleave=all on the wrapper script) to keep the buffer pool from being lopsided.
Separate WAL/redo log from data. On dedicated, expensive hardware. On cloud volumes it usually doesn't help — both live on the same backing storage anyway.
Watch the disk. A degrading SSD (rising iostat await, growing media-error counters in smartctl) makes a healthy DB look unhealthy. Monitor it.

6. Scale out

When a single node has been honestly optimised and is still saturated, then scale.

Read replicas. Send read traffic (analytics, search, dashboards) to replicas, keep writes on the primary. Be aware of replication lag — if your application reads its own writes immediately, replicas will return stale data.
Connection pooling. PgBouncer / ProxySQL between the app and the database. Lets you have thousands of short-lived application connections backed by a small, persistent pool of DB connections.
Caching. Redis or Memcached in front of expensive read queries. The hard part is invalidation; the easy part is the speed.
Sharding / partitioning across nodes. When one machine cannot hold the working set in RAM regardless of size, partition the data across N nodes by tenant, by range, or by hash. This is a one-way door — the application has to know the sharding key — so it's the last resort, not the first.
OLTP vs analytics separation. Long-running analytical queries on the OLTP database steal buffer pool and lock rows other clients need. Move them to a replica, a column store (ClickHouse, DuckDB), or a warehouse.

Common anti-patterns

Raising max_connections to fix a slow database. It does the opposite — more connections compete for the same locks and the same buffer pool.
Adding a Redis cache to hide a missing index. The cache works until invalidation lags, then production lights up.
Mass-applying mysqltuner output without measuring. Per-thread buffer increases (sort_buffer_size, read_buffer_size) multiply by max_connections; you can push the server into swap with one paste.
OPTIMIZE TABLE / VACUUM FULL on a live table. They take exclusive locks. Use pt-online-schema-change (MySQL) or pg_repack (Postgres) for online compaction.
Disabling fsync / innodb_flush_log_at_trx_commit=0. Yes, it's faster. It also loses transactions on a crash. Only acceptable for explicitly ephemeral data.
Reaching for sharding before fixing the schema. Sharding is permanent; an index migration is reversible.

A short triage runbook

When somebody says "the database is slow", in this order:

Is it slow now, or slow yesterday? If now: live EXPLAIN ANALYZE on the slowest query, watch iostat, check disk and CPU. If yesterday: slow log / pg_stat_statements.
What was the slowest query by total time spent in the window? Run EXPLAIN. Is it doing a table scan? Missing index? Stale stats? Fix that one thing first.
Did something change recently? A deploy, a schema migration, a data growth spike, a new background job? "Slow since Monday" usually has a Monday cause.
Is the disk fine? iostat -x 1 — %util near 100% or rising await is your answer.
Is the buffer pool / shared_buffers big enough for the working set? If the page-read rate from disk is high under steady traffic, no.
Are connections saturated? Active vs idle, time in Waiting for lock — both visible in SHOW PROCESSLIST / pg_stat_activity.

If you've gone through that list and the answer is still "the box itself is the bottleneck", then you have a scaling decision to make.

Summary

The honest short version of "how do I make my database faster":

Measure. Slow log + EXPLAIN + OS counters. Without this, every change is guessing.
Fix the top three slowest queries. Usually means adding an index, removing a SELECT *, or killing an N+1 in the app.
Sanity-check the schema. Data types, missing indexes on foreign keys, oversized rows.
Right-size the buffer pool / shared_buffers. Most other config knobs are minor next to this one.
Put a connection pooler in front. PgBouncer or ProxySQL.
Scale out — replicas, caches, sharding — only when a properly tuned single node can't keep up.

Tuning is iterative. Change one thing, measure, decide if it helped. A team that follows that loop will out-perform a team that pastes "the ten settings every DBA should have" into my.cnf every time.