↓ Skip to main content
Database DevPro
  1. Database DevPro
  2. >
  3. Guides
  4. >
  5. PostgreSQL Guides

PostgreSQL Guides

Scope
#

This section focuses on how PostgreSQL works and how to reason about it, not on benchmarks alone.

Guides here explain:

  • How the PostgreSQL planner and executor make decisions
  • How to design indexes that match real access patterns
  • How to interpret EXPLAIN and EXPLAIN ANALYZE
  • How to reason about performance, concurrency, and reliability
  • Where PostgreSQL is a better fit than MySQL — and where it is not

If you want measured evidence and reproducible experiments, see the Labs section.

→ PostgreSQL Labs

Topics covered
#

Query planning and execution
#

  • Planner vs executor: what decisions are made where
  • Cost estimation, selectivity, and statistics
  • EXPLAIN vs EXPLAIN ANALYZE: what each can and cannot tell you
  • Common plan shapes: Seq Scan, Index Scan, Bitmap Scan, Parallel plans

Index design
#

  • Composite index ordering and access patterns
  • Index-only scans and covering strategies
  • When indexes help — and when they don’t
  • Trade-offs: write cost, bloat, and maintenance

Performance and concurrency
#

  • Filtering, sorting, and top‑N queries
  • Locking, blocking, and long-running transactions
  • Understanding plan variance and run‑to‑run differences
  • Practical diagnostics with buffers and timing

Data modeling and features
#

  • JSONB usage and indexing strategies
  • When PostgreSQL can replace document-style workloads
  • Trade-offs between flexibility and performance

How to use these guides
#

  1. Start with a guide to understand the principle or decision criteria
  2. Jump to a lab to see the same idea validated with real execution plans

For example:

  • Guide: Composite index design for filtering + ORDER BY
  • Lab: Index vs Full Scan with EXPLAIN ANALYZE (100k / 1M / 5M rows)

Philosophy
#

  • Prefer reasoning about plans over memorizing rules
  • Focus on access patterns, not just schemas
  • Treat performance as an engineering discipline, not a tuning trick

These guides are written for engineers and architects who need to explain why a solution works, not just that it works.