PRACTICE 05 — DATABASE OPTIMIZATION
The database is where a system slows down first.
Before buying a bigger server or rewriting the application, look at the database. In most systems we review, the cheapest performance win is in badly written queries, missing indexes and default settings nobody ever revisited. Specialist work on PostgreSQL, with experience across other relational databases.
DIAGNOSIS
Performance diagnosis
Before touching anything, measure. The output is a report with bottlenecks ranked by impact and by effort to fix.
- Analysis of the most expensive queries with pg_stat_statements and real execution plans (EXPLAIN ANALYZE)
- Investigation of locks, waits, idle connections and resource contention — CPU, memory, disk and network
- Correlation between what users perceive as slow and what the database is doing at that moment
- A report on what to tackle first, the expected gain and the risk of each change
INDEXES
Indexes and queries
Most of the big wins come from here. Every change is measured before and after in a test environment before it reaches production.
- Composite, partial and covering indexes designed for the real queries; GIN and GiST for text, JSON and geospatial data
- Removal of duplicate or never-used indexes that only cost writes and space
- Rewriting critical queries and fixing patterns ORMs generate silently, such as N+1 and SELECT *
- Efficient pagination, pre-computed aggregations and queries that hit the right plan
MODELLING
Modelling and growth
For systems that have outgrown what the database was designed for.
- Partitioning of large tables by date or by customer, with rotation and archiving of historical data
- Data model review: normalisation where it is missing, controlled denormalisation where it helps
- Read replicas, materialized views and caching to separate analytical load from transactional
- A strategy for event, log and time-series tables that never stop growing
OPERATIONS
Configuration and maintenance
Default configuration is for getting started, not for production. The database has to be tuned to the real hardware and load.
- Memory, parallelism and I/O parameters tuned to the server and the usage profile
- Autovacuum, statistics and bloat control so performance does not degrade over time
- Connection pooling (PgBouncer or equivalent) for applications with many short-lived connections
- Documented, automated maintenance routines so your team can keep what got fixed
RESILIENCE
Backup, recovery and upgrades
A backup that has never been restored is not a backup. And an out-of-support version is a risk waiting for a date.
- Continuous backup with point-in-time recovery and periodically tested restores
- Major version upgrades with pg_upgrade or logical replication, with a minimal window and a way back
- High availability with replicas and failover when the business cannot stop
- Migration between providers — RDS, Aurora, Azure Database for PostgreSQL or self-hosted — without losing data
SECURITY
Security and observability
Who accesses what, and how you find out when something goes off the rails.
- Least-privilege roles and permissions; row-level security (RLS) when several customers share one database
- Encryption in transit and at rest, secrets out of the code and access auditing
- Monitoring with alerts for slow queries, abnormal growth, replica lag and disk space
- Dashboards your team understands, with the metrics that matter to the business