Skip to content

Phase 08: Extensions & Modern Ecosystem

Chapter 32: Essential Production Extensions

32.1 pg_stat_statements (The DBA's Primary Radar)

If you manage a PostgreSQL database without pg_stat_statements, you are flying blind. It tracks execution statistics across all SQL queries, parameterizing arguments (WHERE id = ?) so identical query patterns aggregate into a single metric.

-- Enable in postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

-- Query the Top 5 most CPU-intensive queries across the entire database:
SELECT 
    queryid,
    substring(query, 1, 50) AS short_query,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct_load,
    shared_blks_hit,
    shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

32.2 pg_trgm (Fuzzy Search & Fast LIKE '%word%')

A standard B-Tree index is completely useless for wildcard queries starting with %: SELECT * FROM products WHERE name LIKE '%laptop%'; (Forces a full table Seq Scan).

The Trigram Solution:

pg_trgm breaks text into 3-character substrings ("trigrams"): * "laptop" -> {" l", " la", "lap", "apt", "pto", "top", "op "} * It builds a GIN index over these trigrams:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops);

-- This query now uses a blazing fast GIN Index Scan:
EXPLAIN ANALYZE 
SELECT * FROM products WHERE name ILIKE '%thinkpad%';

32.3 hypopg (Hypothetical Indexes)

Before creating a 50GB index on a production table, how do you know if the query planner will actually choose it? hypopg creates in-memory hypothetical indexes that exist only for the duration of your session and take 0 disk space and 0 creation time: 

CREATE EXTENSION hypopg;
SELECT * FROM hypopg_create_index('CREATE INDEX ON orders (customer_id, created_at)');
-- Run EXPLAIN:
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC;
-- The planner will show if it would use the hypothetical index!

Chapter 33: Specialized Workloads: Time-Series & Geospatial

33.1 TimescaleDB (Time-Series Hypertables)

  • The Problem: High-frequency metrics (IoT, financial ticks) bloat tables to billions of rows. B-Tree inserts degrade as indexes exceed RAM.
  • The Hypertables Solution:
  • Automatically chunks the table into physical partitions by time intervals (e.g., 1 day or 1 week).
  • Directs writes only to the current active chunk, ensuring the index stays pinned in L2/L3 CPU cache.
  • Native columnar compression achieving 90%+ disk reduction for older chunks.

33.2 PostGIS (Geospatial Dominance)

PostGIS turns PostgreSQL into the world's most powerful spatial engine: * Spatial data types: GEOMETRY (flat Cartesian plane) and GEOGRAPHY (spherical Earth geoid). * R-Tree / GiST Indexing: Indexes 2D/3D bounding boxes. 

CREATE EXTENSION postgis;
-- Find all drivers within 5km of a rider:
SELECT driver_id, ST_Distance(driver_location, ST_MakePoint(-73.985, 40.748)::geography) AS distance_meters
FROM drivers
WHERE ST_DWithin(driver_location, ST_MakePoint(-73.985, 40.748)::geography, 5000)
ORDER BY distance_meters ASC;

Chapter 34: Foreign Data Wrappers & AI Vectors (pgvector)

34.1 postgres_fdw (Federated Multi-Database Queries)

Allows Postgres to mount tables located on remote PostgreSQL instances across the network and query them transparently: 

CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_billing FOREIGN DATA WRAPPER postgres_fdw 
    OPTIONS (host 'billing-db.internal', dbname 'billing', port '5432');
CREATE USER MAPPING FOR current_user SERVER remote_billing 
    OPTIONS (user 'app_user', password 'secret');

IMPORT FOREIGN SCHEMA public FROM SERVER remote_billing INTO remote_billing_schema;

-- Query remote and local tables in a single JOIN:
SELECT u.name, b.invoice_total 
FROM local_users u 
JOIN remote_billing_schema.invoices b ON u.id = b.user_id;

34.2 pgvector (Vector Search for Generative AI & RAG)

Stores dense floating-point vector embeddings from OpenAI, Anthropic, or HuggingFace models: 

CREATE EXTENSION vector;

CREATE TABLE documents (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    content TEXT,
    embedding vector(1536) -- OpenAI text-embedding-3-small dimension
);

-- Fast Approximate Nearest Neighbor (ANN) HNSW Index:
CREATE INDEX idx_docs_embedding ON documents 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Query the 5 most semantically similar documents:
SELECT id, content, (embedding <=> '[0.012, -0.045, ...]'::vector) AS cosine_distance
FROM documents
ORDER BY embedding <=> '[0.012, -0.045, ...]'::vector
LIMIT 5;
* Why Use Postgres for Vectors over Dedicated Vector DBs? * ACID transactions and standard backups. * Single database: You can perform hybrid filtering in a single query: WHERE category = 'finance' AND created_at > now() - interval '30 days' ORDER BY embedding <=> query_vector LIMIT 10; * Eliminates complex sync pipelines between your relational database and an external vector database like Pinecone.