Chapter 2 — PostgreSQL for Backend

The why

PostgreSQL is the most loved and most trusted database in the world. It powers Instagram, Spotify, Apple, and most of the modern web. But it's not just "a place to put rows" — it's a full platform with a query planner smarter than most ORMs, a type system richer than JavaScript, and extensions that replace Redis, Elasticsearch, and SQS. If you learn PostgreSQL deeply, you need fewer tools.

Core concepts

The datatype cheat sheet

PostgreSQL has 40+ built-in types. You need about 12. Choosing the right one saves space, enforces correctness, and makes queries faster.

JSONB — the hybrid that changed everything

PostgreSQL's JSONB column lets you store schemaless documents inside a relational database. It supports indexing, querying, and partial updates — all with SQL.

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  payload JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);
 
-- Insert arbitrary JSON
INSERT INTO events (user_id, payload) VALUES
(42, '{"action": "purchase", "amount": 29.99, "items": ["sku_1", "sku_2"]}'),
(42, '{"action": "view", "page": "/products", "duration_ms": 3400}

When to use JSONB:

• Event sourcing, activity logs, audit trails (same table, different shapes)
• User preferences, feature flags (need flexibility, rarely queried by value)
• API response caching (store the exact response shape)
• When the schema is genuinely unknowable upfront

When NOT to use JSONB:

• Core entities (users, orders, products) — use proper columns with types and constraints
• Data you query by value frequently — JSONB indexes are larger and slower than B-tree on a real column
• Foreign keys — JSONB can't enforce referential integrity
• When you'd write a migration to add a real column later anyway

Constraints — the database as your last line of defense

Your application validates input. Your database enforces correctness. Constraints are how:

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,                              -- NOT NULL + UNIQUE
  email VARCHAR(255) NOT NULL UNIQUE,                    -- required + no duplicates
  age INTEGER CHECK (age > 0 AND age < 150),             -- domain constraint
  role VARCHAR(20

Reading EXPLAIN ANALYZE

The query planner is PostgreSQL's brain. EXPLAIN shows what it thinks will happen. EXPLAIN ANALYZE actually runs the query and shows you what did happen.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id
ORDER BY order_count 

Sample output (annotated):

Limit  (cost=1245.32..1245.37 rows=20 width=48)
       (actual time=34.521..34.526 rows=20 loops=1)
  Buffers: shared hit=842 read=3
  ->  Sort  (cost=1245.32..1250.32 rows=2000 width=48)
            (actual time=34.520..34.523 rows=20 loops=1)
        Sort Key: (count(o.id)) DESC
        Sort Method: top-N heapsort  Memory: 27kB
        Buffers: shared hit=842 read=3
        ->  HashAggregate  (cost=1145.00..1195.00 rows=2000 width=48)
                  (actual time=32.100..33.800 rows=1892 loops=1)
              Group Key: u.id
              Buffers: shared hit=839 read=3
              ->  Hash Left Join  (cost=45.00..1095.00 rows=10000 width=40)
                        (actual time=0.450..25.300 rows=9847 loops=1)
                    Hash Cond: (o.user_id = u.id)
                    Buffers: shared hit=839 read=3

What to look for:

Real-world example: How a backend team at a startup uses PostgreSQL

A typical SaaS startup runs PostgreSQL as its only stateful service for the first 1-2 years:

1. Core data — users, teams, subscriptions → regular tables, B-tree indexes on foreign keys
2. Activity feed — every user action as a JSONB row in events table. Index on (user_id, created_at). GIN index on payload for occasional search.
3. Background jobs — a jobs table with status, scheduled_at, attempts. Workers poll with SELECT ... FOR UPDATE SKIP LOCKED LIMIT 1.
4. Rate limiting — a rate_limits table with (key, window_start, count). INSERT ... ON CONFLICT DO UPDATE SET count = count + 1.
5. Full-text search — a tsvector column on products or articles, GIN-indexed. Good enough for the first 100k documents.

No Redis. No Elasticsearch. No RabbitMQ. One database, used well, handles all of it.

Common mistakes

Production notes

• Set statement_timeout per role. ALTER ROLE app_user SET statement_timeout = '30s'; This kills runaway queries before they take down production.
• Use pg_stat_statements. Enable the extension. It tracks every query's total execution time, calls, and rows. Find the top 5 slowest queries and fix them every week.
• idle_in_transaction_session_timeout — set it to 60s. A transaction left open locks rows indefinitely. This kills zombie transactions automatically.
• log_min_duration_statement = 1000 (or lower). Every query slower than 1 second gets logged. Review the log weekly.

Performance notes

• PostgreSQL uses one CPU core per query. Complex queries benefit from faster single-core performance, not more cores. For OLTP (many simple queries), more cores help because each query gets its own core.
• shared_buffers should be 25% of RAM (up to 8 GB on Linux). PostgreSQL relies on the OS page cache for the rest — doubling shared_buffers beyond 8 GB rarely helps.
• effective_cache_size should be ~75% of RAM. This doesn't allocate memory — it tells the planner how much OS cache is available. The planner uses it to decide between index scans and sequential scans.
• GIN indexes on JSONB are powerful but expensive. A GIN index on a JSONB column can be 2–4x larger than the data itself. Only index the specific paths you query often.

Security notes

• Enable Row-Level Security (RLS) for multi-tenant apps. ALTER TABLE projects ENABLE ROW LEVEL SECURITY; CREATE POLICY tenant_isolation ON projects USING (tenant_id = current_setting('app.current_tenant_id')::bigint);
• Use pg_hba.conf to restrict connections. Only allow connections from your application servers' IP ranges. Use scram-sha-256 authentication, never trust or password.
• Revoke CONNECT on the database from the public role. REVOKE CONNECT ON DATABASE mydb FROM PUBLIC; Then explicitly grant to your app role.
• Encrypt connections with TLS. Most managed Postgres providers (Supabase, RDS, Cloud SQL) enforce this by default. If self-hosting, configure ssl = on and get certificates.

Exercises

Beginner

1. Create a products table with: id (BIGSERIAL PK), name (VARCHAR(200) NOT NULL), price (NUMERIC(10,2) NOT NULL CHECK >= 0), tags (TEXT[]), metadata (JSONB), created_at (TIMESTAMPTZ DEFAULT now()). Insert 5 products with varied metadata. Query all products with tag 'electronics'.
2. Add a foreign key from orders.user_id to users.id. Explain what ON DELETE CASCADE does vs ON DELETE RESTRICT.

Intermediate

1. Create a table with 100k rows and a JSONB column. Create a GIN index on the JSONB column. Time a query that searches inside the JSONB with and without the index. Use EXPLAIN ANALYZE.
2. Write a query that uses INSERT ... ON CONFLICT DO UPDATE to implement an upsert. Explain why this is better than SELECT then INSERT or UPDATE in application code.

Advanced

1. Implement a job queue using SELECT ... FOR UPDATE SKIP LOCKED. Write a worker script that polls the jobs table, processes jobs, and marks them complete. Handle worker crashes (stale jobs).
2. Set up logical replication between two PostgreSQL instances. Verify that writes on the primary appear on the replica within 1 second.

Interview questions

Beginner

1. Why should you always use TIMESTAMPTZ instead of TIMESTAMP? TIMESTAMPTZ stores UTC internally and converts to the client's timezone on read. TIMESTAMP stores whatever value you give it with no timezone awareness, which leads to incorrect comparisons and displays across timezones.

2. What is a foreign key constraint and why is it important? A column that references the primary key of another table. It enforces referential integrity — you can't insert an order for user_id 999 if no user 999 exists, and you can't delete user 42 if orders reference them (without CASCADE).

3. What does EXPLAIN ANALYZE tell you? It shows the actual execution plan, timing, and row counts for a query, plus buffer usage (hits vs reads). It tells you exactly where time is spent — sequential scans, sorts, hash joins — so you know what to optimize.

Senior

4. When would you use JSONB vs normalized tables? JSONB for event logs, audit trails, user preferences, and data whose schema genuinely varies per row. Normalized tables for core entities (users, orders, products) that need type safety, foreign keys, and predictable query performance. The key question: "Will I ever want to add a WHERE clause on a field inside this JSON?" If yes, consider extracting it to a real column.

5. Explain what work_mem controls and how you'd tune it. work_mem is the memory per operation (sort, hash table) in a query. A query with 3 sorts and 2 hash joins can use up to 5 × work_mem. Default is 4 MB, which is too low for modern workloads. Set it to 32–256 MB on an application server. Monitor for external merge in EXPLAIN — that means a sort spilled to disk. On a dedicated DB server with 64 GB RAM and 100 concurrent queries, work_mem = 256MB is reasonable.

6. What are the tradeoffs of using PostgreSQL as a job queue (SKIP LOCKED) vs a dedicated queue (Redis/RabbitMQ)? PostgreSQL queue: no new infrastructure, transactional (job insert and business data in same transaction), simple. Downsides: polling overhead (wakes up every N ms), vacuum bloat from rapid inserts/deletes, lower throughput than dedicated queues. Redis/RabbitMQ: higher throughput, push-based (no polling), but separate infrastructure to maintain, and you lose transactional consistency between queue and database.

Summary

PostgreSQL is more than a database — it's a platform. Choose the right datatype for every column. Use JSONB wisely (flexible data, not core entities). Add constraints at the database level (your last line of defense). Read EXPLAIN ANALYZE output fluently — it's the single most important skill for database performance. And remember: PostgreSQL can replace Redis, Elasticsearch, and RabbitMQ for the first 1–2 years of most startups.

Quick recall

• Always TIMESTAMPTZ, never TIMESTAMP. Always BIGSERIAL, never SERIAL.
• JSONB for flexible data; normalized tables for core entities.
• Index every foreign key. Every. One.
• EXPLAIN (ANALYZE, BUFFERS) on every slow query.
• Constraints in the database are your last line of defense.
• work_mem = 64MB+, statement_timeout = 30s, shared_buffers = 25% RAM.

Quiz

1. What's the difference between JSON and JSONB in PostgreSQL? JSON is stored as text and re-parsed on every query. JSONB is stored in a binary format with indexing support. Always use JSONB.
2. What does EXPLAIN ANALYZE show that EXPLAIN alone does not? Actual execution time, actual row counts, and buffer usage (shared hit vs shared read).
3. Why should you index every foreign key column? Without an index, every JOIN, DELETE, and UPDATE involving that FK triggers a full table scan on the child table.
4. What happens when work_mem is too small for a sort? The sort spills to disk (external merge in EXPLAIN), which is ~100x slower than an in-memory sort.
5. When should you NOT use JSONB? For core entities (users, orders), for columns you frequently filter/sort by, for data that needs foreign key enforcement.
6. Name three PostgreSQL extensions and what they do. pg_stat_statements (query performance tracking), postgis (geospatial), pg_cron (scheduled jobs), uuid-ossp (UUID generation), pg_trgm (trigram fuzzy text search).
7. What is Row-Level Security (RLS)? A PostgreSQL feature that restricts which rows a user can access based on policies. Essential for multi-tenant SaaS where multiple customers share the same database.