React
React Native
Node.js
Python
Rust
PostgreSQL
Solana
Claude
AI Agents
Go
Containers
AWS SDKs

PostgreSQL SME Cookbook

Sections

PostgreSQL Core & SQL

PostgreSQL Foundations PostgreSQL Basics Postgres Architecture System Catalogs Database, Schema & Search Path Versioning & Upgrade Policy PostgreSQL vs MySQL vs SQLite When to Use Postgres / When Not To PostgreSQL Fundamentals Best Practices Highlights Summary

Select and Filtering Joins and Subqueries Aggregates and Group By Data Types and Casts Insert Update Delete Indexes and Explain Transactions and Locking CTEs and Window Functions JSONB and Arrays Constraints and Schema Functions and PL/pgSQL psql and Admin Basics Highlights Summary

The SQL Query SQL Querying Basics JOIN Types Aggregates & GROUP BY Subqueries & EXISTS UNION, INTERSECT, EXCEPT NULL Semantics SQL Querying Best Practices Highlights Summary

PostgreSQL Data Types In Depth Data Types Basics Numeric & Text Types Date, Time & Timestamptz UUID, Enum & Domain Types Arrays & Composite Types JSONB Operators & Indexing Data Types Best Practices Highlights Summary

The Schema & Constraints Blueprint Schema Design Basics Primary & Foreign Keys CHECK & UNIQUE Constraints Generated & Identity Columns DDL Transactions Soft Deletes vs Hard Deletes Schema Design Best Practices Highlights Summary

How Indexes Work Indexes Basics B-tree Indexes Partial & Covering Indexes GIN & GiST BRIN Index-Only Scans Indexes Best Practices Highlights Summary

MVCC Key Points Transactions Basics Isolation Levels MVCC Visibility Row-Level Locks Deadlocks Serialization Anomalies Long Transactions Concurrency Best Practices Highlights Summary

Advanced SQL Demystified Advanced SQL Basics Common Table Expressions Window Functions Recursive CTEs LATERAL Joins Pivot & Crosstab Patterns Advanced SQL Best Practices Highlights Summary

PL/pgSQL Explained Functions Basics PL/pgSQL Essentials Stored Procedures & CALL Volatility & STRICT SECURITY DEFINER Risks Testing Database Functions Functions Best Practices Highlights Summary

The Trigger Model Triggers Basics Transition Tables Audit Triggers When Not to Use Triggers Rules (Legacy)Triggers Best Practices Highlights Summary

Tooling, Clients & Schema Workflow

The PostgreSQL Cluster Installation Basics initdb & Data Directory postgresql.conf Essentials pg_hba.conf & Authentication Systemd & Service Management Installation Best Practices Highlights Summary

The psql Client psql Basics psql Scripting pgAdmin & DBeaver COPY & Bulk Load Client Encoding & Locale psql Best Practices Highlights Summary

Roles & RLS In Depth Roles Basics GRANT/REVOKE Patterns Default Privileges Row-Level Security Bypass RLS & Superuser RLS Performance Permissions Best Practices Highlights Summary

The Schema Change Blueprint Migrations Basics Flyway & Liquibase Expand/Contract Pattern Zero-Downtime DDL Rollback Strategy Large Table Migrations Migrations Best Practices Highlights Summary

PostgreSQL Rules Overview Postgres Project Rules Checklist Naming & Style Rules Index & Query Rules Migration Safety Rules Security Rules ADR Template for Postgres PostgreSQL Rules Best Practices Highlights Summary

Data Modeling & Architecture

Data Modeling Foundations Data Modeling Basics Entity-Relationship Modeling Domain-Driven Schema Boundaries ADR: Single Database vs Per-Service DB Schema Evolution Strategy Data Modeling Best Practices Highlights Summary

Normalization vs Denormalization Normalization Basics 3NF & BCNF Intentional Denormalization Materialized Views Denormalization Best Practices Highlights Summary

The Partitioning Model Partitioning Basics Declarative Partitioning DDL Partition Pruning Retention & Detach Partitioning Pitfalls Partitioning Best Practices Highlights Summary

Multi-Tenant Patterns Explained Multi-Tenant Basics Shared Schema + tenant_id Row-Level Security Tenancy Schema-per-Tenant Database-per-Tenant Multi-Tenant Best Practices Highlights Summary

Modeling Defect Scenarios Key Points Defect Scenarios Basics Fan-Out Join Explosion Missing Foreign Keys Enum Migration Pain Timestamp Without Timezone Bugs Defect Scenarios Best Practices Highlights Summary

Performance & Query Tuning

How EXPLAIN Works EXPLAIN Basics Seq Scan vs Index Scan Nested Loop, Hash Join, Merge Join Sort & Hash Aggregates Bad Plan Case Studies EXPLAIN Best Practices Highlights Summary

The Query Planner Statistics Basics ANALYZE & Autoanalyze Extended Statistics Planner GUCs Statistics Best Practices Highlights Summary

Index Design In Detail Index Design Basics Multicolumn Index Order Duplicate & Redundant Indexes REINDEX & REINDEX CONCURRENTLY Index Maintenance Best Practices Highlights Summary

Vacuum & Bloat Demystified Vacuum Basics Autovacuum Tuning Transaction ID Wraparound Table & Index Bloat TOAST & Large Values Vacuum Best Practices Highlights Summary

The Connection Pooling Blueprint Pooling Basics PgBouncer Modes Prepared Statements & Pooling Role & Timeout per Pool Pooling Best Practices Highlights Summary

OLTP vs OLAP Tuning Workload Basics Read Replicas for Reporting Parallel Query work_mem & sort/hash tuning OLTP/OLAP Best Practices Highlights Summary

Replication, HA & Backup

Streaming Replication Explained Streaming Replication Basics Synchronous vs Asynchronous Replication Slots Replication Monitoring Cascading Replicas Streaming Replication Best Practices Highlights Summary

The Logical Replication Model Logical Replication Basics Publications & Subscriptions Logical vs Physical Upgrade with Logical Replication Logical Replication Best Practices Highlights Summary

HA & Failover Key Points HA Basics Patroni & etcd/Consul Connection Proxies Split-Brain Prevention HA Testing HA Best Practices Highlights Summary

Backup & Restore Foundations Backup Basics pg_dump & pg_restore Object-Level Backup Backup Verification Backup Best Practices Highlights Summary

Point-in-Time Recovery In Depth PITR Basics archive_mode & archive_command Recovery Target Options PITR Drill Runbook Cloud PITR PITR Best Practices Highlights Summary

RTO & RPO Demystified DR Basics Cross-Region Replicas DR Runbook & Comms DR Game Days DR Best Practices Highlights Summary

Extensions & Specialized Workloads

The Extension System Extensions Basics Trusted vs Untrusted Extensions Extension Upgrade Path Extension Allowlist Policy Extensions Best Practices Highlights Summary

How Full-Text Search Works Full-Text Search Basics tsvector & tsquery GIN Indexes for FTS Hybrid Search Full-Text Search Best Practices Highlights Summary

pgvector In Depth pgvector Basics IVFFlat vs HNSW Indexes RAG Storage Schema pgvector at Scale pgvector Best Practices Highlights Summary

PostGIS Foundations PostGIS Basics Geometry vs Geography Spatial Indexes PostGIS Performance PostGIS Best Practices Highlights Summary

Time-Series Patterns Explained Time-Series Basics TimescaleDB Continuous Aggregates OLAP Offload ADR Time-Series Best Practices Highlights Summary

Security, Operations & Cloud

The Security Hardening Blueprint Security Basics SSL/TLS Configuration SCRAM Authentication pgaudit & Compliance Logging Encryption at Rest Security Best Practices Highlights Summary

Monitoring & Metrics Key Points Monitoring Basics pg_stat_activity & Locks pg_stat_statements postgres_exporter & Grafana SLO Dashboards Monitoring Best Practices Highlights Summary

Logging & Audit Trails Explained Logging Basics csvlog & JSON Logs Audit Triggers vs pgaudit Forensic Queries Logging Best Practices Highlights Summary

Managed PostgreSQL Overview Managed Postgres Basics AWS RDS & Aurora Postgres Google Cloud SQL & AlloyDB Neon & Supabase Cloud Selection ADR Managed Postgres Best Practices Highlights Summary

Capacity Planning In Depth Capacity Basics Connection Math Storage Growth Forecasting Scale-Up vs Scale-Out Sharding Last Resort Capacity Planning Best Practices Highlights Summary

Ecosystem & Team

The PostgreSQL Toolchain Tools Basics pgBackRest pg_repack pgbadger pganalyze / OtterTune Essential Tools Best Practices Highlights Summary

The Agent Skill Model Agent Skills Basics EXPLAIN Review Skill Migration Safety Skill Failover Drill Skill Incident Triage Skill Agent Skills Best Practices Highlights Summary

Git for Database Work Git for Database Work Basics Code Review for SQL Git Best Practices Highlights Summary

The Linux CLI Linux CLI for DBAs Basics IO & Memory Tools CLI Best Practices Highlights Summary

Team Collaboration Key Points Onboarding Basics App Dev → DBA Path Skills Matrix Pairing on Query PRs Team & Onboarding Best Practices Highlights Summary

Tech-Lead Craft

Troubleshooting Foundations Incident Basics Lock Storm Triage Replication Lag Emergency Disk Full on PGDATA Corruption & Index Invalidation Incident Response Best Practices Highlights Summary

Enterprise Delivery Explained Change Management Basics Maintenance Windows Uptime SLOs for Datastores Rollback for Failed Migrations Change Management Best Practices Highlights Summary

Tech Leadership Key Points Tech Lead Basics on Postgres Query Review Standards Mentoring App Teams Handling Index Requests Technical Leadership Best Practices Highlights Summary

The Product Collaboration Blueprint Stakeholder Basics Data Retention & Legal Holds Cost Conversations Working with Engineering Best Practices Highlights Summary

Case Studies In Depth Reference: Multi-Tenant SaaS Reference: Analytics Replica Topology Reference: pgvector RAG Store Before/After: Major Version Upgrade Before/After: Query Optimization Win Case Studies Best Practices Highlights Summary

Governance Demystified Governance Basics Naming & Documentation Standards Extension & Plugin Governance Upgrade Calendar PostgreSQL Roadmap Tracking Career Growth: DBA → Staff Data Engineer Governance Best Practices Highlights Summary

/

/

© 2026 @csjcode|SystemsArchitect.io: Cloud Systems Tools|Terms

An independent, unofficial educational resource. Not affiliated with, endorsed, or sponsored by the brand/trademark maintainers/owners.

This site is in beta testing and may have inaccuracies or errors.

All code examples are generic for learning and for a dev/test environment only. No warranty is provided to users. See terms.

Search Documentation

Search across all documentation pages

PostgreSQL SME/Indexes/Indexes Best Practices

Sql Indexes Access Paths Best Practices List

Indexes Best Practices

Indexes are not free decorations. Prove each one with workload evidence.

Highlights / Indexes Best Practices

Prove each index with workload evidence before deploying to production
Match multicolumn index order to query predicates and join sequences
Use EXPLAIN ANALYZE before and after to measure index performance gains
Create indexes concurrently in production to avoid blocking writes
Drop unused indexes after monitoring period and document in migrations
Most OLTP tables benefit from 3-8 indexes matching access paths

How to Use This List

Capture query patterns from pg_stat_statements.
Review unused indexes quarterly.

A - Design

Index predicates and join keys you actually filter. Not every column.
Match multicolumn order to queries. Leading column rule.
Use partial indexes for hot subsets. Smaller and faster.

B - Validation

EXPLAIN ANALYZE before and after. Prove benefit.
Check index bloat and invalid indexes. REINDEX when needed.
Drop idx_scan = 0 indexes after observation window. Document drop in migration.

C - Operations

CREATE INDEX CONCURRENTLY in prod. Avoid blocking writes.
Plan fillfactor on heavy UPDATE tables. Reduce page splits.
Keep autovacuum healthy for index-only scans. Visibility map matters.

FAQs

How many indexes per table?

As few as possible meeting SLA; typical OLTP 3-8 depending on access paths.

Related

B-tree Indexes - multicolumn
Index Design Best Practices - lifecycle

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Reviewed by Chris St. John·Last updated Jul 18, 2026

Was this article helpful?

On this page

How to Use This List A - Design B - Validation C - Operations FAQs Related