Database performance guides grounded in workload decisions.
Use these guides to frame the evidence and trade-offs behind a production change. Each one ends with the checks to run on your own system.
Guides
PostgreSQL performance and locking
Diagnose blocking, indexing and physical-design problems from production evidence before changing the schema. Related: PostgreSQL performance investigation.
Method · New
How to profile slow PostgreSQL queries
pg_stat_statements to rank, auto_explain to capture the real plan, EXPLAIN ANALYZE to measure, then validate.
Lab-tested · NewPostgreSQL 18 async I/O: does io_method help?
worker cut a cold sequential scan by a third; io_uring won on a bitmap scan. How to measure it on your storage.
Lab-testedFind slow queries with pg_stat_statements
Rank statements by total time, diff snapshots and read buffer and temp signals before changing anything.
Lab-tested · NewAutovacuum not removing dead tuples
Autovacuum ran four times and removed nothing while one session sat idle in transaction. Find the xmin blocker.
Lab-tested · Newwork_mem and temp file spills
Raising work_mem 64 times removed every spill and saved at most 8%. Measure the spill cost before changing memory.
Lab-tested · NewTransaction ID wraparound: warning, shutdown, recovery
The real warning and stop error, why VACUUM FREEZE did not help, and the recovery steps that did.
LockingPostgreSQL concurrency and locking
Lock modes, advisory locks and deadlocks, plus how to find the session at the head of a blocking chain.
IndexingWhen BRIN beats B-tree
Compare physical correlation, lossy rechecks and ordered access on the real query.
PartitioningScaling with partitioning
Range, list and hash partitioning; pruning, retention and key constraints before migrating a table.
Physical designCLUSTER and query performance
Measure heap locality against rewrite time, disk space and the ACCESS EXCLUSIVE lock.
ArchitectureSharding PostgreSQL
FDW and Citus approaches, shard keys, rebalancing and cross-shard work.
DiagnosisWhat AI misses in production diagnosis
Why a plausible query recommendation can still be unsafe without workload context.
PostgreSQL upgrades and support dates
Version deadlines on RDS, Aurora and community PostgreSQL, and how to plan an upgrade that does not regress performance. Related: PostgreSQL upgrade readiness.
Checklist · New
PostgreSQL 14 upgrade checklist
Inventory, blockers, baseline, rehearsal, cutover and validation for RDS, Aurora and self-managed PG14.
RDS · Aurora · NewPostgreSQL 14 Extended Support: what it will cost
Billing from 1 March 2027 per vCPU-hour on every primary, standby and replica. How to list and price yours.
Lab-tested · Newpg_upgrade --check failures and fixes
Real PG14 to PG18 output: checksum mismatch, prepared transactions and reg* columns, and what the check misses.
Lab-tested · Newpg_upgrade from 14 drops logical replication slots
The check passed with a CDC slot present; after the upgrade it was gone. RDS and Aurora block instead.
RDS · AuroraRDS and Aurora 14 to 18: downtime and Blue/Green
In-place versus Blue/Green, and the logical replication limits that invalidate a green environment.
Lab-testedSlow after a major upgrade? Check statistics first
A PG14 to PG17 pg_upgrade lost optimizer statistics and a join went from 222 ms to 904 ms.
RDS · Oct 2026RDS PostgreSQL 14.18/14.19 lose standard support on 31 October
Minor-version deadlines that arrive before the PG14 major-version dates, and how to check your instances.
End of lifePostgreSQL 14 EOL planning
Current dates and the upgrade-readiness sequence.
pgvector and vector search
Recall, latency, filtering and memory trade-offs when vector search runs inside PostgreSQL. Related: pgvector performance review.
Security · Lab-tested · New
pgvector 0.8.7 security fix: check and upgrade
IVFFlat build overflow in 0.8.6 and earlier. Version checks, who could trigger it, and what the upgrade did to indexes.
Lab-tested · NewFiltered vector search returns too few rows
Why HNSW plus a WHERE filter returned 0 of 10 rows, and measured fixes from iterative scans to partial indexes.
IndexingHNSW vs IVFFlat is a workload decision
Recall, latency, memory, build cost and filtering trade-offs.
ArchitectureSemantic search API with FastAPI and pgvector
From a FastAPI demo to measured recall, tenant filtering and latency.
Other databases
Concurrency and performance guides beyond PostgreSQL. SQL Server, Snowflake and analytical workloads are covered on their capability pages. Related: All capabilities.
SQL Server · Lab-tested
SQL Server parameter sniffing
One cached plan read 9 pages for one customer and 900,524 for another. Query Store diagnosis and measured fixes.
Snowflake · NewSnowflake Gen2 vs Adaptive warehouses
Idle credits, spills, pruning and queuing from ACCOUNT_USAGE decide which one costs less for your workload.
SQL Server · MSSQLServer.dev ↗SQL Server tutorials
Window functions, CTEs, isolation levels, row versioning, temporal tables and change tracking, on Kiran's SQL Server blog.
MongoDBMongoDB locking and concurrency
WiredTiger lock modes, versioning, expiring lock fields and transaction retries.
SQL ServerSQL Server performance review
Query Store, plan regressions, blocking chains and ORM implicit conversions.
SnowflakeSnowflake performance and cost
Query history, warehouse sizing, pruning, spills and workload isolation.