1/21
Database optimization is the process of making a database answer queries faster, use less CPU, memory and disk, and handle more users without slowing down. A slow database is usually not a hardware problem first. It is a query problem, an index problem, or a design problem. Optimization means finding the slow queries, fixing how they run, and checking the result with measurements — not guessing. Full guide: https://denebrixai.com/blog/database-optimization/
Slow query log
Rank queries by total time and fix the top few first. In PostgreSQL use pg_stat_statements; in MySQL use the slow query log; in SQL Server use wait statistics and DMVs.
EXPLAIN ANALYZE
Run EXPLAIN, and EXPLAIN ANALYZE for read queries, to see the real plan and catch full table scans on large tables. EXPLAIN ANALYZE actually runs the query, so do not run it on a write query in production without care.
Indexing
Add indexes on columns used in WHERE, JOIN, GROUP BY and ORDER BY. Avoid functions on indexed columns in WHERE, because that can stop the index being used. Do not over-index — every extra index slows INSERT and UPDATE.
SELECT only what you need
Avoid SELECT *. Select only the columns the query needs. This cuts I/O, memory and network time, especially on wide tables.
Query rewrite
Rewrite OR conditions, correlated subqueries and unnecessary JOINs where a simpler form does the same work. Test each rewrite with EXPLAIN.
Caching
If the same query runs again and again, cache the result. Caching cuts read load far more than tuning alone when reads repeat.
Partitioning
Partition large tables by date or range when reads are mostly for one period. MySQL and PostgreSQL both support partitioning, and it lets the database scan one partition instead of the whole table.
Buffer pool / memory
Size the buffer pool and cache so hot indexes and data fit in RAM. In MySQL that is innodb_buffer_pool_size; in PostgreSQL it is shared_buffers and effective_cache_size.
Connection pooling
Opening a new connection for every query adds overhead. Use a pooler such as PgBouncer for PostgreSQL, or your driver's pool.
ANALYZE and VACUUM
Run ANALYZE regularly so the planner has fresh statistics. Remove unused indexes, and vacuum / maintain tables in PostgreSQL so bloat does not grow.
Measure after every change
Measure before and after a change: query time, rows scanned, buffer hits, CPU and I/O. If a change does not improve the measured number, revert it.
N+1 query problem
Running one query to get a list, then one extra query per item in a loop — 101 queries instead of 1. Fix it with a single smart JOIN.
Full table scan
The database reads every row of a table to answer a query. On a large table this is the usual cause of a slow query — check it with EXPLAIN.
B-Tree index
The default index type in most databases. Like an index at the back of a textbook, it lets the database jump straight to the right rows instead of scanning all of them.
Covering index
An index that contains all the columns a query needs, so the database never has to read the table itself.
Keyset pagination
Pagination with WHERE id > last_id instead of OFFSET. Page 5,000 loads as fast as page 1, because the database does not skip rows one by one.
OFFSET pagination
Pagination with LIMIT + OFFSET. It gets slower as the page number grows, because the database still scans and discards all the earlier rows.
PgBouncer
A connection pooler for PostgreSQL. It reuses a small pool of connections instead of opening a new one for every query.
Materialized view
A pre-computed result stored like a table. Expensive queries run once, and later reads use the stored result instead of re-computing it.
Buffer hit
A read served from memory (cache) instead of disk. More buffer hits and fewer disk reads mean faster queries — check the buffer hit ratio after a change.
MySQL — what to check
InnoDB buffer pool (innodb_buffer_pool_size), slow query log, partitioning by range.
PostgreSQL vs SQL Server — what to check
PostgreSQL: EXPLAIN ANALYZE, pg_stat_statements, ANALYZE, VACUUM, PgBouncer. SQL Server: wait statistics, DMVs, clustered vs non-clustered indexes, columnstore for analytics.