pg_stat_statements: the first thing to install on any PostgreSQL
PostgreSQL pg_stat_statements: the first query diagnostics extension to install. Find the three queries consuming 80% of database resources.
Performance
8 tagged articles
PostgreSQL
Data Warehouse
DWH Partitioning: When 3 Years of Data Weigh Too Much
Range partitioning on an 800-million-row fact table: from 12-minute quarterly queries to 40 seconds. Monthly implementation, exchange and local indexes.
PostgreSQL
VACUUM and autovacuum: why PostgreSQL needs someone to clean up
PostgreSQL VACUUM and autovacuum: bloat diagnosis on a 200 GB database, reading pg_stat_user_tables and tuning without disabling anything.
Oracle
Oracle on Linux: the kernel parameters nobody configures
Oracle 19c on Linux: kernel tuning for real performance. Huge Pages, THP, swappiness, I/O scheduler, ulimit — before and after numbers.
Oracle
AWR, ASH and the 10 minutes that saved a go-live
Oracle 19c performance tuning with AWR and ASH: finding a hidden full table scan in a stored procedure in 10 minutes, the eve of a go-live.
PostgreSQL
When a LIKE '%value%' Slows Everything Down: A Real PostgreSQL Optimization Case
PostgreSQL optimization: LIKE '%value%' causes full scan. Using pg_trgm and GIN index to turn a wildcard search into a fast lookup. Real case.
Oracle
Oracle Partitioning: when 2 billion rows no longer fit in a query
Oracle Partitioning on a 2-billion-row table: range, interval, partition pruning, local indexes. From hours to seconds on reporting queries.
PostgreSQL
EXPLAIN ANALYZE is not enough: how to actually read a PostgreSQL execution plan
PostgreSQL EXPLAIN ANALYZE: reading an execution plan, spotting costly nested loops and stale statistics. Real case on 2-million-row table.