# Chapter 2 — PostgreSQL for Backend By the end of this chapter you will: - Choose the right Postgres datatype every time - Use JSONB without losing your SQL skills - Read EXPLAIN ANALYZE output - Bridge to the SQL-Postgres module --- ## 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. Most databases do one thing well. MySQL stores rows. Redis caches. Elasticsearch searches. PostgreSQL does all of them — not perfectly, but well enough that many companies run Postgres *only*. Need caching? PostgreSQL has `UNLOGGED` tables and materialized views. Need full-text search? PostgreSQL has `tsvector` and GIN indexes. Need a queue? PostgreSQL has `SKIP LOCKED` and `LISTEN/NOTIFY`. Need document storage? PostgreSQL has `JSONB` with GIN indexes that perform like MongoDB. You don't need to *use* all of these on day one. But knowing they exist means you reach for a new service only when PostgreSQL genuinely can't do the job — which is rarer than you think. This chapter assumes you know basic SQL (SELECT, INSERT, JOINs). If you're new to SQL, start with the free **[SQL & PostgreSQL Fundamentals](/docs/SQL-Fundamentals)** course first — it covers SQL syntax from scratch in 10 modules. For advanced PostgreSQL internals (MVCC, vacuum, WAL), dive into the **[SQL & PostgreSQL — Advanced](/docs/SQL-Postgres)** course. --- ## 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. | Use case | Type | Why not VARCHAR? | |----------|------|-------------------| | Auto-incrementing ID | `BIGSERIAL` or `UUID` | VARCHAR IDs fragment indexes; integers sort faster | | Short text (name, email, title) | `VARCHAR(n)` or `TEXT` | `VARCHAR(255)` is fine but `TEXT` has no length penalty in Postgres | | Long text (body, bio, description) | `TEXT` | No difference from VARCHAR internally — just no length cap | | True/false | `BOOLEAN` | Don't use `INTEGER` 0/1 — BOOLEAN enforces `true`/`false`/`NULL` | | Date only | `DATE` | `TIMESTAMP` includes time you don't need; harder to compare | | Timestamp | `TIMESTAMPTZ` (always with timezone) | `TIMESTAMP` without TZ silently loses timezone info; `TIMESTAMPTZ` stores UTC and converts on read | | Money | `NUMERIC(10,2)` or `INTEGER` (cents) | Never `FLOAT` for money — rounding errors. `NUMERIC` is exact. | | JSON documents | `JSONB` | `JSON` is stored as text (re-parsed every query). `JSONB` is binary indexed. | | Arrays | `TEXT[]`, `INTEGER[]` | Normalize first. Use arrays only for small, fixed-size lists (tags). | | Enums | `CREATE TYPE status AS ENUM (...)` | Better than VARCHAR for fixed sets — type-safe, smaller storage | | Binary data | `BYTEA` | Don't store files in the DB. Use S3 + store the URL. BYTEA is for hashes, small blobs. | Always use `TIMESTAMPTZ`, never `TIMESTAMP` (aka `TIMESTAMP WITHOUT TIME ZONE`). `TIMESTAMPTZ` stores everything as UTC internally and converts to the client's timezone on read. `TIMESTAMP` stores whatever you give it with no timezone awareness — your 3 PM IST and your user's 9 AM UTC are the same indistinguishable value. Debugging this at 3 AM is a rite of passage you can skip. ### 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. ```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}'); -- Query inside JSON SELECT * FROM events WHERE payload->>'action' = 'purchase' AND (payload->>'amount')::numeric > 20; -- Index the action field for fast lookups CREATE INDEX idx_events_action ON events USING GIN ((payload->'action')); ``` **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: ```sql 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) DEFAULT 'user', -- sensible default created_at TIMESTAMPTZ NOT NULL DEFAULT now(), -- automatic timestamp updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), CONSTRAINT chk_email_format CHECK (email ~* '^[^@]+@[^@]+\.[^@]+$') ); -- Foreign key constraint: CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE, total NUMERIC(10,2) NOT NULL CHECK (total >= 0) ); ``` Validate in the application (nice error messages). Constrain in the database (actual safety). If your application validation has a bug, the database constraint catches it. If the constraint is missing, a buggy deploy can corrupt your data permanently. Both layers. Always. ### 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. ```sql 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 DESC LIMIT 20; ``` 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:** | Pattern | What it means | Action | |---------|--------------|--------| | `Seq Scan` on large table | No usable index, scanning everything | Add an index on the filter column | | `cost` much lower than `actual time` | Planner underestimated rows (stale statistics) | Run `ANALYZE table_name;` | | `Buffers: shared read=...` large | Reading from disk, not cache | Increase `shared_buffers` or add RAM | | `Sort Method: external merge` | Sort spilled to disk (query needs more `work_mem`) | Increase `work_mem` for this query: `SET work_mem = '256MB';` | | `rows=1` in estimate, `rows=50000` actual | Planner thinks column is unique but it isn't | Check `n_distinct` in `pg_stats` | --- ## 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 | Mistake | Why it's wrong | What to do instead | |---------|---------------|-------------------| | Using `TIMESTAMP` instead of `TIMESTAMPTZ` | Timezone data silently lost; queries return wrong times for users in different zones | Always `TIMESTAMPTZ`. Always. | | Using `VARCHAR` without a limit for everything | No space savings, but worse: no semantic meaning. `VARCHAR email` tells nobody what "email" looks like. | Use `VARCHAR(n)` with a sensible limit, or create a domain: `CREATE DOMAIN email AS VARCHAR(255) CHECK (VALUE ~* '...')` | | Not indexing foreign keys | Every JOIN on that FK does a full scan on the child table. Every DELETE on the parent does a full scan to check FK constraints. | Index every foreign key column. Every one. | | Using `JSONB` for core entities | Loses type safety, foreign key enforcement, and makes every query harder to write and slower to run | Normalize core entities into proper tables. JSONB is for flexible/auxiliary data. | | Ignoring `work_mem` | Default is 4 MB. A sort or hash with 100k rows needs 10–50 MB. When `work_mem` is too low, sorts spill to disk — 100x slower. | Set `work_mem = '64MB'` (or higher on a dedicated DB server). Test with `EXPLAIN (ANALYZE, BUFFERS)`. | | Connecting as the superuser | `postgres` role can drop databases, read all tables, bypass RLS | Create an `app_user` role with minimum permissions | | Using `SERIAL` instead of `BIGSERIAL` | `SERIAL` = 32-bit integer = max 2.1 billion. Sounds like a lot until your events table hits it at 3 AM. | `BIGSERIAL` = 64-bit = 9 quintillion. Use it by default. | --- ## 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.**

Chapter 2 — PostgreSQL for Backend

Part of L2