{"title":"PostgreSQL Deep Dive","description":"","content":"PostgreSQL is a strong default for the system of record: it gives you transactions, constraints, joins, mature indexing, and enough extensibility to handle many product requirements without adding another datastore.\n\n\n\n\n\nThe important part is knowing where that default stops being enough. Payment flows need correct isolation and idempotency. JSONB fields need the right indexes. Large tables need partitioning choices tied to query patterns. Read replicas help read throughput, but they also introduce lag and failover questions.\n\nThis chapter focuses on those decisions.\n\n---\n\n# PostgreSQL Architecture Overview\n\nThe diagram traces the path a client request takes from application servers through PgBouncer, into the primary's query pipeline, and out to storage and streaming replicas, showing how each layer fits together:\n\n\n```mermaid\nflowchart TD\n subgraph Client[\"Client Applications\"]\n A1[App Server 1]:::primary\n A2[App Server 2]:::primary\n A3[App Server N]:::primary\n end\n\n subgraph Pool[\"Connection Pooling\"]\n PG[PgBouncer]:::orange\n end\n\n subgraph Primary[\"PostgreSQL Primary\"]\n QP[Query Parser]:::green\n QO[Query Optimizer]:::green\n EX[Executor]:::green\n BM[Buffer Manager]:::teal\n WAL[WAL]:::teal\n ST[(Storage)]:::teal\n end\n\n subgraph Replicas[\"Read Replicas\"]\n R1[Replica 1]:::teal\n R2[Replica 2]:::teal\n end\n\n A1 --> PG\n A2 --> PG\n A3 --> PG\n PG -->|Pooled connections| QP\n QP --> QO --> EX\n EX --> BM\n BM --> ST\n BM --> WAL\n WAL -->|Streaming| R1\n WAL -->|Streaming| R2\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n```\n\n\nClient applications (App Server 1..N) often do not connect to PostgreSQL directly. They go through **PgBouncer**, which maintains a smaller pool of database connections and multiplexes many client requests onto them. This keeps connection overhead low and improves throughput under high concurrency.\n\nPgBouncer is a connection pooler, not a SQL-aware read/write router. If an architecture uses read replicas, the application or a separate proxy layer decides whether a query should use the primary or a replica.\n\nInside the primary, each SQL statement flows through the core execution pipeline:\n\n- **Query Parser**: validates SQL and builds an internal representation (parse tree).\n- **Query Optimizer**: picks an efficient plan (index scan vs seq scan, join strategy, etc.).\n- **Executor**: runs the chosen plan and produces results.\n\nAs the executor reads/writes data, it interacts with the **Buffer Manager**, which serves pages from memory when possible and fetches from **Storage** when needed. For durability, changes are recorded in the **WAL** (Write-Ahead Log): PostgreSQL ensures the WAL is safely written before considering a transaction committed.\n\nFor scaling reads, applications can route safe read-only queries to replicas (Replica 1/2). The replicas stay up to date via **streaming replication**, where a WAL sender process on the primary ships **WAL records** to replicas. This lets you offload read traffic, while keeping the primary as the source of truth for writes.\n\n---\n\n# 1. When to Choose PostgreSQL\n\nChoose PostgreSQL when correctness and query flexibility matter more than easy horizontal write scaling. It is usually a good fit for transactional product data, relational models, reporting-style queries over operational data, and workloads that benefit from constraints.\n\n### 1.1 Choose PostgreSQL When You Have\n\n#### Complex queries and relationships\n\nMost real-world data is relational. Users have orders. Orders have products. Products have categories. When your queries need to traverse these relationships, PostgreSQL's full SQL support with JOINs, subqueries, CTEs, and window functions makes complex questions straightforward. \n\nA query like \"find all users who ordered a product in category X but never in category Y\" is natural in SQL but awkward in most NoSQL databases.\n\n#### ACID transaction requirements\n\nWhen a payment transfer must either complete fully or not happen at all, you need atomicity. When your inventory count must never go negative, you need consistency. \n\nPostgreSQL provides these guarantees by default, making it the natural choice for financial systems, inventory management, and booking platforms.\n\n#### Flexible querying needs\n\nNoSQL databases often require you to know your query patterns upfront and design your data model around them. PostgreSQL inverts this: you model your data naturally, and with proper indexing, the database handles whatever queries you throw at it. \n\nThis flexibility proves invaluable as requirements evolve.\n\n#### JSON and semi-structured data\n\nPostgreSQL's JSONB type bridges the gap between rigid schemas and document flexibility. You can store complex nested objects, query into them efficiently, and index specific fields, all while maintaining the relational guarantees you need for the rest of your data.\n\n#### Full-text search\n\nFor many applications, PostgreSQL's built-in text search is sufficient, eliminating the operational complexity of maintaining a separate search engine. \n\nWhen your search needs grow beyond what PostgreSQL handles well, you can add Elasticsearch incrementally.\n\n#### Geospatial data\n\nThe PostGIS extension transforms PostgreSQL into a powerful geospatial database. If you are building anything location-aware, from delivery routing to store finders, PostGIS provides the primitives you need.\n\n\n```mermaid\nflowchart TD\n A[Need complex queries or JOINs?]:::primary\n A -->|Yes| B[Need ACID guarantees?]:::primary\n A -->|No| C[Consider NoSQL]:::orange\n B -->|Yes| D[Data fits on single machine?]:::primary\n B -->|No| E[Consider eventual consistency DBs]:::orange\n D -->|Yes| F[PostgreSQL is excellent]:::green\n D -->|No| G[PostgreSQL with sharding or NewSQL]:::orange\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n```\n\n\n### 1.2 When PostgreSQL Is Not the Right Fit\n\nPostgreSQL is a strong default, but not a universal answer. Call out the cases where another system is simpler or scales more naturally.\n\n#### Extreme write throughput\n\nPostgreSQL's ACID guarantees come with overhead. The database must coordinate transactions, maintain indexes, and ensure durability on every write. \n\nFor append-only workloads with millions of writes per second, such as event logging or IoT telemetry, databases like Cassandra or ClickHouse that sacrifice some consistency for write performance are better suited.\n\n#### Massive horizontal scaling\n\nPostgreSQL was designed as a single-node database. While you can shard it manually or with extensions like Citus, this adds significant operational complexity. \n\nIf your data will grow to hundreds of terabytes and require automatic horizontal scaling, native distributed databases like CockroachDB, Cassandra, or DynamoDB handle this more gracefully.\n\n#### Simple key-value access patterns\n\nIf your access pattern is purely \"get value by key\" and \"set key to value,\" PostgreSQL's query parser, planner, and executor are unnecessary overhead. \n\nRedis provides sub-millisecond latency for these patterns, and DynamoDB offers managed key-value storage at scale.\n\n#### High-volume time-series data\n\nPostgreSQL can store time-series data, but it was not optimized for the append-heavy, time-windowed query patterns common in observability and IoT applications. \n\nTimescaleDB (a PostgreSQL extension) or purpose-built databases like InfluxDB handle these workloads more efficiently.\n\n#### Caching layer\n\nPostgreSQL reads from disk. Even with aggressive caching, it cannot match the microsecond latencies of in-memory stores. \n\nWhen you need sub-millisecond reads for hot data, Redis or Memcached belong in front of PostgreSQL, not instead of it.\n\n### 1.3 Common Interview Systems Using PostgreSQL\n\n\n| System | Why PostgreSQL Works |\n|--------|---------------------|\n| Payment System | ACID transactions prevent double-spending |\n| E-commerce (orders, inventory) | Complex relationships, transactions |\n| User Management | Flexible queries, relationships |\n| Booking System | Prevents double-booking with transactions |\n| Financial Ledger | Audit trails, consistency guarantees |\n| Content Management | JSONB for flexible content, full-text search |\n| Multi-tenant SaaS | Row-level security, schemas for isolation |\n\n\n---\n\n# 2. ACID Transactions Deep Dive\n\nACID describes the guarantees PostgreSQL gives around transactions. For payments, bookings, inventory, and ledgers, these guarantees are the reason the database can prevent partial updates, invalid states, and unsafe concurrent changes.\n\n### 2.1 What ACID Means\n\n**Atomicity** ensures that transactions are all-or-nothing. Consider a money transfer: deducting from one account and crediting another. If the system crashes between these operations, atomicity guarantees you will never end up with money deducted but not credited. Either both operations complete, or neither does.\n\n\n```sql\nBEGIN;\nUPDATE accounts SET balance = balance - 100 WHERE user_id = 1;\nUPDATE accounts SET balance = balance + 100 WHERE user_id = 2;\nCOMMIT; -- Both succeed or both fail\n```\n\n\n**Consistency** means the database moves from one valid state to another. PostgreSQL enforces this through constraints: foreign keys that prevent orphaned records, check constraints that keep values in valid ranges, unique constraints that prevent duplicates. A transaction that would violate any constraint is rejected entirely.\n\n**Isolation** addresses what happens when multiple transactions run concurrently. Without isolation, two transactions reading and modifying the same data could corrupt it. Isolation ensures each transaction operates as if it were the only one running, even when thousands execute simultaneously.\n\n**Durability** promises that committed data survives failures. PostgreSQL achieves this through the Write-Ahead Log (WAL): before acknowledging a commit, the database writes the changes to durable storage. If the server crashes immediately after returning success, the data is safe.\n\n### 2.2 Isolation Levels\n\nIsolation is where things get interesting, and where interviews often probe your understanding.\n\nPerfect isolation (every transaction behaves as if it ran alone) is expensive. Weaker isolation improves performance but allows certain anomalies. Understanding these trade-offs is essential for choosing the right level for each use case.\n\nPostgreSQL supports four isolation levels, arranged from weakest to strongest:\n\n\n```mermaid\nflowchart TB\n subgraph Isolation[\"Isolation Levels\"]\n A[Read Uncommitted]:::red\n B[Read Committed]:::orange\n C[Repeatable Read]:::green\n D[Serializable]:::teal\n end\n\n A --> B --> C --> D\n\n classDef red fill:#ff8787,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n```\n\n\nBefore examining each level, let us understand the anomalies they prevent:\n\n\n```mermaid\nflowchart TD\n subgraph Anomalies[\"Isolation Anomalies\"]\n D1[Dirty Read]:::red\n D2[Non-Repeatable Read]:::orange\n D3[Phantom Read]:::teal\n end\n\n D1 --- E1[\"Reading uncommitted changes
from another transaction\"]:::secondary\n D2 --- E2[\"Same query returns different
values within a transaction\"]:::secondary\n D3 --- E3[\"New rows appear that match
a previously-run query\"]:::secondary\n\n classDef red fill:#ff8787,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n classDef secondary fill:#38d9a9,stroke:#000,color:#000\n```\n\n\n\n| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read | Performance |\n|----------------|------------|---------------------|--------------|-------------|\n| Read Uncommitted | No* | Possible | Possible | Same as Read Committed |\n| Read Committed (default) | No | Possible | Possible | Fast |\n| Repeatable Read | No | No | No* | Medium |\n| Serializable | No | No | No | Slowest |\n\n\n*PostgreSQL accepts `READ UNCOMMITTED`, but internally treats it as `READ COMMITTED`, so dirty reads are not possible. PostgreSQL's Repeatable Read also prevents phantom reads, going beyond the SQL standard requirement.\n\nThe three anomalies above are the SQL standard's list, but two more matter in practice and explain why the stronger levels exist:\n\n- **Lost update:** two transactions read the same row, both modify it based on the value they read, and the second commit overwrites the first. Repeatable Read catches this by aborting the second transaction with a serialization error. This is the anomaly the optimistic-locking pattern (covered later) guards against at the application level.\n- **Write skew:** two transactions read an overlapping set of rows, each checks a condition that still holds, and each writes a different row. Individually both look valid, but together they violate an invariant (for example, two on-call engineers each marking themselves off-duty because the other is still on). Repeatable Read does **not** prevent write skew. Only Serializable does.\n\n**Read Committed (Default):** Each statement sees only committed data, but different statements within the same transaction may see different database states as other transactions commit.\n\n\n```sql\n-- Transaction 1\nBEGIN;\nSELECT balance FROM accounts WHERE user_id = 1; -- Returns 100\n-- Transaction 2 commits: UPDATE balance to 150\nSELECT balance FROM accounts WHERE user_id = 1; -- Returns 150 (different!)\nCOMMIT;\n```\n\n\n**Repeatable Read:** Transaction sees a consistent snapshot from start. No changes from other transactions visible.\n\n\n```sql\n-- Transaction 1\nBEGIN ISOLATION LEVEL REPEATABLE READ;\nSELECT balance FROM accounts WHERE user_id = 1; -- Returns 100\n-- Transaction 2 commits: UPDATE balance to 150\nSELECT balance FROM accounts WHERE user_id = 1; -- Still returns 100\nCOMMIT;\n```\n\n\n**Serializable:** The strongest level. PostgreSQL ensures transactions execute as if they ran one at a time, in some serial order. It accomplishes this by detecting potential conflicts and aborting transactions that would cause anomalies. This is the only level that prevents write skew, which is why it is the right choice for invariants that span multiple rows.\n\n\n```sql\n-- Imagine two concurrent transactions, each computing a total\n-- and storing it. Without serializable isolation, they could\n-- each miss the other's changes.\nBEGIN ISOLATION LEVEL SERIALIZABLE;\nSELECT SUM(balance) FROM accounts;\nUPDATE totals SET sum = ...; -- May fail with serialization error\nCOMMIT;\n```\n\n\nWhen PostgreSQL detects that two serializable transactions would conflict, it aborts one with a serialization error. Your application must be prepared to retry.\n\n### 2.3 When to Use Each Isolation Level\n\n\n| Use Case | Isolation Level | Why |\n|----------|----------------|-----|\n| Most read queries | Read Committed | Default, good performance |\n| Reports requiring consistency | Repeatable Read | Consistent snapshot |\n| Balance transfers | Repeatable Read or Serializable | Prevent inconsistencies |\n| Inventory management | Serializable | Prevent overselling |\n| Audit logs | Read Committed | Append-only, no conflicts |\n\n\n### 2.4 Handling Serialization Failures\n\nSerializable isolation may abort transactions due to conflicts. Your application must retry:\n\n\n```python\nimport time\nimport random\n\ndef transfer_funds(from_id, to_id, amount):\n max_retries = 3\n for attempt in range(max_retries):\n try:\n with connection.cursor() as cur:\n # Serializable handles concurrency conflicts on its own,\n # so no explicit row locking (FOR UPDATE) is needed here.\n cur.execute(\"BEGIN ISOLATION LEVEL SERIALIZABLE\")\n cur.execute(\"SELECT balance FROM accounts WHERE id = %s\", (from_id,))\n balance = cur.fetchone()[0]\n if balance < amount:\n cur.execute(\"ROLLBACK\")\n raise InsufficientFunds()\n cur.execute(\"UPDATE accounts SET balance = balance - %s WHERE id = %s\", (amount, from_id))\n cur.execute(\"UPDATE accounts SET balance = balance + %s WHERE id = %s\", (amount, to_id))\n cur.execute(\"COMMIT\")\n return True\n except SerializationFailure:\n cur.execute(\"ROLLBACK\")\n if attempt == max_retries - 1:\n raise\n time.sleep(random.uniform(0.01, 0.1)) # Backoff before retry\n```\n\n\n### 2.5 SELECT FOR UPDATE\n\nFor explicit row-level locking, use `SELECT FOR UPDATE`:\n\n\n```sql\nBEGIN;\nSELECT * FROM inventory WHERE product_id = 123 FOR UPDATE;\n-- Row is locked, other transactions wait\nUPDATE inventory SET quantity = quantity - 1 WHERE product_id = 123;\nCOMMIT; -- Lock released\n```\n\n\n**Variants:**\n\n\n| Lock Type | Behavior |\n|-----------|----------|\n| FOR UPDATE | Exclusive lock, blocks all other locks |\n| FOR NO KEY UPDATE | Blocks updates but allows foreign key checks |\n| FOR SHARE | Shared lock, allows reads, blocks writes |\n| FOR KEY SHARE | Weakest, only blocks exclusive locks |\n\n\n\n> **TIP**\n>\n> **Design note:** For critical financial operations like balance transfers, Serializable isolation can remove race conditions at the database level. The trade-off is retry logic for serialization failures.\n>\n> In most workloads, these failures are rare because truly conflicting transactions are uncommon. The few milliseconds of retry latency are acceptable for the guarantee that money never disappears or appears from nowhere.\n\n\n### 2.6 MVCC and VACUUM\n\nThe consistent snapshots that isolation levels rely on are not magic. They come from **MVCC (Multi-Version Concurrency Control)**, which is the mechanism behind almost everything in this section, and a frequent interview topic in its own right.\n\nThe core idea: PostgreSQL never overwrites a row in place. An `UPDATE` writes a **new version** of the row and marks the old version as expired. A `DELETE` only marks the existing version as expired.\n\nEach version carries the transaction IDs that created and expired it, so every transaction can be shown the version that was valid as of its snapshot.\n\nThis is why readers and writers do not block each other in PostgreSQL. A long-running `SELECT` keeps reading the old row versions while a concurrent `UPDATE` writes new ones. There is no shared read lock to contend on.\n\n\n```mermaid\nflowchart LR\n U[UPDATE row id=1
price 10 to 12]:::primary\n V1[Version A
price=10
expired by txn 105]:::orange\n V2[Version B
price=12
created by txn 105]:::green\n\n U --> V1\n U --> V2\n\n R1[Old snapshot reads]:::teal --> V1\n R2[New snapshot reads]:::teal --> V2\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n```\n\n\nThe cost of this design is **dead tuples**. Every update and delete leaves behind an expired row version that still occupies space on disk. Without cleanup, tables and indexes grow indefinitely even when the live row count is flat.\n\nThis wasted space is called **bloat**, and it slows down scans because the database reads more pages to find the same live rows.\n\n**VACUUM** reclaims this space by removing dead tuples that are no longer visible to any active transaction. PostgreSQL runs **autovacuum** in the background to do this automatically, but on large, frequently updated tables it can fall behind, which is a common production issue worth naming in an interview.\n\nTwo related points come up often:\n\n- **Long-running transactions block cleanup.** VACUUM can only remove a dead tuple once no transaction can still see it. A single transaction left open for hours holds back cleanup across the whole database, causing bloat to accumulate.\n- **Transaction ID wraparound.** Transaction IDs are a finite 32-bit space. VACUUM also \"freezes\" old rows to keep these IDs from wrapping around. If autovacuum falls far enough behind, PostgreSQL forces a protective shutdown to avoid data corruption. This rarely surfaces day to day, but it is the reason VACUUM is not optional.\n\nThe practical takeaway for a design discussion: PostgreSQL trades extra background maintenance for lock-free reads. When you mention update-heavy tables, also mention autovacuum tuning and bloat as the operational cost.\n\n---\n\n# 3. Indexing Strategies\n\nA query without an appropriate index forces PostgreSQL to examine every row in the table. On a million-row table, that means reading millions of rows to find potentially only one.\n\nProper indexing transforms this from a sequential scan taking seconds to an index lookup taking milliseconds.\n\nIndexes improve reads by adding work to writes. PostgreSQL must update every relevant index when the row changes, and each index consumes storage. Create indexes for real query patterns and verify them with query plans.\n\n### 3.1 B-Tree Index (Default)\n\nB-tree is the workhorse index type, suitable for the vast majority of use cases. It organizes data in a balanced tree structure that supports both equality lookups and range queries efficiently.\n\n\n```sql\nCREATE INDEX idx_users_email ON users(email);\n\n-- All of these queries benefit from the B-tree index:\nSELECT * FROM users WHERE email = 'john@example.com'; -- Equality\nSELECT * FROM users WHERE email LIKE 'john%'; -- Prefix match\nSELECT * FROM users WHERE email > 'a' AND email < 'b'; -- Range\nSELECT * FROM users ORDER BY email LIMIT 10; -- Sorting\n```\n\n\n**How B-tree works:**\n\n\n```mermaid\nflowchart LR\n subgraph BTree[\"B-Tree Index Structure\"]\n R[Root: M]:::primary\n L1[A-L]:::orange\n L2[N-Z]:::orange\n D1[A,B,C]:::green\n D2[D,E,F]:::green\n D3[N,O,P]:::green\n D4[X,Y,Z]:::green\n end\n\n R --> L1\n R --> L2\n L1 --> D1\n L1 --> D2\n L2 --> D3\n L2 --> D4\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\nThe tree structure ensures that lookups, inserts, and deletes all complete in O(log N) time. For a table with a million rows, that means roughly 20 comparisons to find any value.\n\n### 3.2 Hash Index\n\nWhen you know you will only ever need equality lookups, hash indexes offer slightly faster performance than B-trees.\n\nThe trade-off is: hash indexes cannot support range queries, ordering, or prefix matching.\n\n\n```sql\nCREATE INDEX idx_users_uuid ON users USING hash(uuid);\n\n-- Uses hash index:\nSELECT * FROM users WHERE uuid = 'abc-123';\n\n-- Does NOT use hash index:\nSELECT * FROM users WHERE uuid > 'abc'; -- Falls back to seq scan\n```\n\n\nB-tree indexes are usually the better choice. The performance difference is small, and you keep the option to support range queries later without recreating the index.\n\n### 3.3 GIN Index (Generalized Inverted Index)\n\nB-tree indexes work for scalar values. But what about columns containing arrays, JSON documents, or text that needs full-text search? These require a different approach.\n\nGIN (Generalized Inverted Index) indexes are designed for composite values. They work by indexing each element within the value separately, allowing efficient queries like \"find all documents containing this key\" or \"find all posts with this tag.\"\n\n\n```sql\n-- JSONB indexing\nCREATE INDEX idx_products_attrs ON products USING gin(attributes);\n\n-- Query JSONB\nSELECT * FROM products WHERE attributes @> '{\"color\": \"red\"}';\nSELECT * FROM products WHERE attributes ? 'size';\n\n-- Full-text search\nCREATE INDEX idx_articles_search ON articles USING gin(to_tsvector('english', content));\n\n-- Array containment\nCREATE INDEX idx_tags ON posts USING gin(tags);\nSELECT * FROM posts WHERE tags @> ARRAY['postgresql', 'database'];\n```\n\n\nThe trade-off with GIN indexes is write performance. When you insert or update a row, PostgreSQL must update the index for every element in the array or JSON document. For write-heavy workloads with large composite values, this overhead can be significant.\n\n### 3.4 GiST Index (Generalized Search Tree)\n\nSome queries ask questions that B-tree cannot answer: \"Do these two time ranges overlap?\" or \"Which locations are within 5 kilometers of this point?\" GiST indexes support these geometric and range-based queries.\n\n\n```sql\n-- Range types\nCREATE INDEX idx_reservations_during ON reservations USING gist(during);\n\n-- Overlap query\nSELECT * FROM reservations\nWHERE during && '[2024-01-15, 2024-01-20]'::daterange;\n\n-- Geospatial (PostGIS)\nCREATE INDEX idx_locations_point ON locations USING gist(point);\n\nSELECT * FROM locations\nWHERE ST_DWithin(point, ST_MakePoint(-122.4, 37.8), 1000);\n```\n\n\n### 3.5 BRIN Index (Block Range Index)\n\nFor time-series data, there is often a natural correlation between when data was inserted and its value. Events from January are stored near other January events because they were inserted around the same time. BRIN indexes exploit this physical ordering.\n\nInstead of indexing every row, BRIN stores the minimum and maximum values for each block of pages. When you query for a date range, PostgreSQL can skip entire blocks that cannot contain matching data.\n\n\n```sql\n-- Time-series data (naturally ordered by time)\nCREATE INDEX idx_events_time ON events USING brin(created_at);\n\n-- Query benefits from BRIN\nSELECT * FROM events\nWHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';\n```\n\n\nBRIN indexes shine when three conditions are met: the data is physically ordered by the indexed column (typically true for timestamp columns), the table is large (millions of rows), and some imprecision is acceptable (BRIN may scan a few extra blocks).\n\nThe size advantage is dramatic:\n\n\n```shell\nTable: 100 million rows, timestamp column\n\nB-tree index: ~2 GB\nBRIN index: ~200 KB (roughly 10,000x smaller)\n```\n\n\nThis makes BRIN ideal for time-series data where you rarely query by exact timestamp but frequently query by time ranges.\n\n### 3.6 Composite Indexes\n\nReal queries often filter on multiple columns. A composite index on those columns can be far more effective than separate indexes on each.\n\n\n```sql\nCREATE INDEX idx_orders_user_date ON orders(user_id, created_at);\n\n-- Uses index efficiently (leftmost columns)\nSELECT * FROM orders WHERE user_id = 123;\nSELECT * FROM orders WHERE user_id = 123 AND created_at > '2024-01-01';\n\n-- Does NOT use this index efficiently\nSELECT * FROM orders WHERE created_at > '2024-01-01'; -- Missing user_id\n```\n\n\nColumn order matters critically. PostgreSQL can use a composite index when you query by the leftmost columns, but not when you skip them. Think of it like a phone book: you can find all Smiths, or all Smiths named John, but you cannot efficiently find all Johns regardless of last name.\n\nThe general rule: place equality columns first, then range columns. Place the most selective column (the one that eliminates the most rows) first.\n\n### 3.7 Partial Indexes\n\nWhy index rows you will never query? Partial indexes include only a subset of table rows, making them smaller and faster.\n\n\n```sql\n-- Only index active users (90% of queries target active users)\nCREATE INDEX idx_active_users ON users(email) WHERE status = 'active';\n\n-- Only index recent orders (old orders rarely queried)\nCREATE INDEX idx_recent_orders ON orders(user_id, created_at)\nWHERE created_at > '2024-01-01';\n\n-- Only index non-null values (most phones are null)\nCREATE INDEX idx_phone ON users(phone) WHERE phone IS NOT NULL;\n```\n\n\nPartial indexes are particularly powerful when your queries always include a specific filter. If 90% of your queries look for active users, a partial index on active users is smaller, faster to maintain, and faster to query.\n\n### 3.8 Covering Indexes (Index-Only Scans)\n\nAfter finding a row through an index, PostgreSQL normally needs to read the actual table row to get other column values. A covering index includes additional columns, allowing PostgreSQL to answer queries entirely from the index.\n\n\n```sql\n-- Include columns needed by query\nCREATE INDEX idx_orders_covering ON orders(user_id) INCLUDE (total, status);\n\n-- Index-only scan (no table access needed)\nSELECT user_id, total, status FROM orders WHERE user_id = 123;\n```\n\n\nThis eliminates random I/O to fetch table rows, which can dramatically improve performance for queries that only need a few columns.\n\n### 3.9 Index Selection Guide\n\nChoosing the right index type comes down to understanding your query patterns:\n\n\n```mermaid\nflowchart TD\n Q[What is your query pattern?]:::primary\n Q --> E{Equality or Range?}:::yellow\n E -->|Equality only| H[Hash Index]:::orange\n E -->|Range or both| B[B-tree Index]:::green\n\n Q --> J{JSONB/Array/Text?}:::yellow\n J -->|Contains element?| GIN[GIN Index]:::teal\n J -->|Full-text search?| GIN\n\n Q --> G{Geometric/Overlap?}:::yellow\n G -->|Range overlap?| GIST[GiST Index]:::teal\n G -->|Spatial query?| GIST\n\n Q --> T{Time-series data?}:::yellow\n T -->|Naturally ordered?| BRIN[BRIN Index]:::orange\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n classDef yellow fill:#ffd43b,stroke:#000,color:#000\n```\n\n\n\n| Query Pattern | Index Type | Example |\n|---------------|------------|---------|\n| Equality (=) | B-tree or Hash | `WHERE email = 'x'` |\n| Range (<, >, BETWEEN) | B-tree | `WHERE date > '2024-01-01'` |\n| Prefix match (LIKE 'x%') | B-tree | `WHERE name LIKE 'John%'` |\n| JSONB containment | GIN | `WHERE attrs @> '{\"a\":1}'` |\n| Full-text search | GIN | `WHERE tsv @@ query` |\n| Array containment | GIN | `WHERE tags @> ARRAY['x']` |\n| Geometric/Range overlap | GiST | `WHERE range && other_range` |\n| Naturally ordered data | BRIN | Time-series `created_at` |\n\n\n---\n\n# 4. Partitioning for Scale\n\nAs tables grow into hundreds of millions of rows, several problems emerge. Queries slow down even with indexes because the indexes themselves become massive. Maintenance operations like VACUUM take hours. Deleting old data requires scanning the entire table.\n\nPartitioning addresses these problems by dividing a large table into smaller, more manageable pieces. From the application's perspective, it is still one table. PostgreSQL automatically routes queries to the relevant partitions and combines results transparently.\n\n### 4.1 Why Partition?\n\nPartitioning replaces one large, unwieldy table with a set of smaller physical pieces that PostgreSQL manages as a unit. The diagram contrasts a single monolithic table against a quarterly range-partitioned version of the same data:\n\n\n```mermaid\nflowchart LR\n\n subgraph After[\"After Partitioning\"]\n P[orders table]:::primary\n P1[orders_2024_q1
40M rows]:::green\n P2[orders_2024_q2
45M rows]:::green\n P3[orders_2024_q3
50M rows]:::green\n P4[orders_2024_q4
55M rows]:::green\n end\n\t\n subgraph Before[\"Before Partitioning\"]\n T1[orders table
500M rows]:::red\n end\t\n\n P --> P1\n P --> P2\n P --> P3\n P --> P4\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef red fill:#ff8787,stroke:#000,color:#000\n```\n\n\nThe benefits compound as data grows:\n\n#### Query performance\n\nWhen queries filter on the partition key, PostgreSQL skips irrelevant partitions entirely. A query for January's data touches only the January partition, not the entire year.\n\n#### Maintenance efficiency\n\nOperations like VACUUM, REINDEX, and backup work on individual partitions. Vacuuming a 10 million row partition takes minutes; vacuuming a 500 million row table takes hours.\n\n#### Data lifecycle management\n\nDeleting old data becomes trivial: drop the partition. This is instantaneous regardless of partition size, compared to DELETE which must scan and log every row.\n\n#### Parallel operations\n\nPostgreSQL can parallelize queries across partitions, using multiple CPU cores to scan different partitions simultaneously.\n\n### 4.2 Partition Types\n\nPostgreSQL supports three partitioning strategies, each suited to different access patterns.\n\n**Range Partitioning** divides data by ranges of values, most commonly dates. This is the natural choice for time-series data where queries typically filter by time periods.\n\n\n```sql\nCREATE TABLE orders (\n id BIGSERIAL,\n user_id BIGINT,\n total DECIMAL,\n created_at TIMESTAMP\n) PARTITION BY RANGE (created_at);\n\nCREATE TABLE orders_2024_q1 PARTITION OF orders\n FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');\n\nCREATE TABLE orders_2024_q2 PARTITION OF orders\n FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');\n```\n\n\n**List Partitioning** groups data by discrete values. This works well for multi-region deployments or categorical data where queries typically target specific categories.\n\n\n```sql\nCREATE TABLE customers (\n id BIGSERIAL,\n name TEXT,\n region TEXT\n) PARTITION BY LIST (region);\n\nCREATE TABLE customers_us PARTITION OF customers\n FOR VALUES IN ('us-east', 'us-west');\n\nCREATE TABLE customers_eu PARTITION OF customers\n FOR VALUES IN ('eu-west', 'eu-central');\n```\n\n\n**Hash Partitioning** distributes data evenly across a fixed number of partitions using a hash function. This is useful when you have a high-cardinality column (like user_id) and want balanced partition sizes without natural range boundaries.\n\n\n```sql\nCREATE TABLE events (\n id BIGSERIAL,\n user_id BIGINT,\n event_type TEXT\n) PARTITION BY HASH (user_id);\n\nCREATE TABLE events_0 PARTITION OF events\n FOR VALUES WITH (MODULUS 4, REMAINDER 0);\n\nCREATE TABLE events_1 PARTITION OF events\n FOR VALUES WITH (MODULUS 4, REMAINDER 1);\n-- ... partitions 2 and 3\n```\n\n\nHash partitioning ensures even distribution, but unlike range or list partitioning, you cannot drop a single partition to remove a subset of data.\n\n### 4.3 Partition Key Selection\n\n\n| Data Pattern | Partition Strategy | Key |\n|--------------|-------------------|-----|\n| Time-series (logs, events) | Range by time | created_at (monthly/quarterly) |\n| Multi-tenant | List or Hash | tenant_id |\n| Geographic | List | region |\n| High cardinality | Hash | user_id, entity_id |\n\n\n#### Guidelines\n\n1. **Partition by query pattern:** Most queries should filter on partition key\n2. **Avoid too many partitions:** Each partition has overhead. Aim for <1000 partitions.\n3. **Partition size:** Each partition should be 10GB-100GB for optimal performance\n\n### 4.4 Partition Maintenance\n\n**Adding partitions:**\n\n\n```sql\n-- Create future partition before data arrives\nCREATE TABLE orders_2024_q3 PARTITION OF orders\n FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');\n```\n\n\n**Dropping old data:**\n\n\n```sql\n-- Instant deletion (no row-by-row delete)\nDROP TABLE orders_2022_q1;\n\n-- Or detach and archive\nALTER TABLE orders DETACH PARTITION orders_2022_q1;\n```\n\n\n### 4.5 Partition Pruning\n\nPostgreSQL automatically skips irrelevant partitions:\n\n\n```sql\nEXPLAIN SELECT * FROM orders WHERE created_at = '2024-03-15';\n\n-- Output shows only orders_2024_q1 is scanned\nAppend\n -> Seq Scan on orders_2024_q1\n Filter: (created_at = '2024-03-15')\n```\n\n\nWithout partition key in WHERE, all partitions are scanned.\n\n### 4.6 Partitioning Limitations\n\n- **Unique constraints and primary keys** must include all partition key columns, so there is no global uniqueness on a non-key column (enforce that in application logic if needed)\n- **Updating the partition key** moves the row to a different partition. PostgreSQL handles this automatically (as an internal delete plus insert), but it is more expensive than an in-place update\n- **Query complexity** increases with partition count, and too many partitions add planning overhead\n\n---\n\n# 5. Replication and High Availability\n\nA single PostgreSQL server is a **single point of failure**. And servers do fail. When the primary goes down, your application goes down with it.\n\n**Replication** solves this by keeping one or more **replicas** in sync with the primary. If the primary fails, a replica can be promoted and the system can continue operating.\n\nReplication also helps with **read scaling**. One PostgreSQL instance can serve only so many queries. By sending **read traffic** to replicas while keeping **writes** on the primary, you can scale read capacity roughly with the number of replicas.\n\n### 5.1 Streaming Replication\n\nPostgreSQL streaming replication continuously ships **WAL (Write-Ahead Log)** records from the primary to replicas. Every change is first written to WAL on the primary; replicas receive the WAL stream and replay it to apply the same changes locally.\n\n\n```mermaid\nflowchart TD\n subgraph Primary[\"Primary Node\"]\n P[PostgreSQL Primary]:::primary\n W[WAL Sender]:::orange\n end\n\n subgraph Replicas[\"Replica Nodes\"]\n R1[Replica 1]:::green\n R2[Replica 2]:::green\n R3[Replica 3]:::green\n end\n\n C[Clients]:::teal --> P\n P --> W\n W -->|WAL Stream| R1\n W -->|WAL Stream| R2\n W -->|WAL Stream| R3\n C -->|Read queries| R1\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n```\n\n\nThe key tradeoff is commit semantics: **when does PostgreSQL consider a transaction committed?**\n\n**Asynchronous replication (default)** commits transactions as soon as they are durable on the primary. The primary does not wait for replicas to acknowledge. This provides the best write latency but risks data loss: if the primary fails before WAL records reach the replica, recent transactions may be lost.\n\n**Synchronous replication** waits for at least one replica to confirm receiving and writing the WAL records before acknowledging the commit. This guarantees no data loss on primary failure but increases write latency by the round-trip time to the replica.\n\nFor systems where data loss is unacceptable (financial transactions, for example), synchronous replication is essential despite the latency cost.\n\n### 5.2 Failover Strategy\n\nA strong HA answer does not stop at \"use replicas.\" A primary/replica topology needs an answer for how a replica is promoted when the primary fails, how applications discover the new primary, whether any acknowledged writes can be lost, and how long writes are unavailable during failover.\n\nManual failover is simple but slow and error-prone. Managed Postgres, Patroni-style automation, or a database proxy can reduce recovery time, but they add operational complexity. If the system cannot tolerate data loss, pair failover with synchronous replication or a managed HA setup.\n\n### 5.3 Logical Replication\n\nPhysical (streaming) replication copies the **entire cluster**. Logical replication is more granular: it replicates **selected tables** (or sets of tables) via publications/subscriptions.\n\nThis makes it useful for upgrading PostgreSQL versions with minimal downtime, replicating to different PostgreSQL versions, and selective replication of only specific tables.\n\nThe distinction is what matters: streaming replication is the normal HA/read-replica mechanism, while logical replication is useful when you need selective table-level movement or version/shape changes.\n\n### 5.4 Read Scaling with Replicas\n\nA common pattern is: write to primary and route read queries to replicas:\n\n\n```mermaid\nflowchart TD\n A[Application]:::primary\n LB[App routing / proxy]:::orange\n\n subgraph DB[\"Database Cluster\"]\n P[Primary
Writes]:::green\n R1[Replica 1
Reads]:::teal\n R2[Replica 2
Reads]:::teal\n end\n\n A --> LB\n LB -->|Writes| P\n LB -->|Reads| R1\n LB -->|Reads| R2\n\n classDef primary fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n```\n\n\n#### Implementation options\n\n- Application-level routing (separate connection pools)\n- A SQL-aware proxy/load balancer such as Pgpool-II, HAProxy with separate endpoints, or another routing layer\n- PgBouncer on each target endpoint for connection pooling\n\n**Replication lag consideration:** replicas are usually slightly behind the primary. If a user must read their own write immediately, route that user's reads to the primary for a short window or wait until the replica has caught up.\n\n### 5.5 High Availability Comparison\n\n\n| Approach | Interview takeaway |\n|----------|--------------------|\n| Manual failover | Simple, but recovery is slower and more error-prone |\n| Automated failover | Better recovery time, but adds coordination and operational complexity |\n| Managed HA | Good default when operating the database is not the core problem |\n| Synchronous replication | Reduces data-loss risk, but increases write latency |\n\n\n---\n\n# 6. Connection Pooling\n\nPostgreSQL follows a **process-per-connection** model: every client connection is served by a dedicated backend process. This design gives strong isolation and predictable behavior, but it comes with real overhead. Each connection consumes memory, often ~10MB+ baseline depending on workload and settings. Opening a new connection is also relatively expensive because it involves process setup and authentication, often tens of milliseconds.\n\nAt small scale, you don't notice. At microservices scale, it becomes a bottleneck fast.\n\nConsider a system with **50 services**. Each service runs **10 instances**, and each instance keeps a modest pool of **10 connections**. That works out to 50 × 10 × 10 = **5,000 connections**.\n\nThat's *before* bursts, admin tools, cron jobs, migrations, and retries. PostgreSQL will struggle long before this point and even if it survives, the memory footprint is wasteful.\n\n**Connection pooling** fixes the problem by letting thousands of client connections share a much smaller number of actual database connections. Instead of \"one app connection = one DB process,\" you get **multiplexing**.\n\n### 6.1 What to Explain\n\nThe design point is simple: do not let every service instance open an unbounded number of database connections. Use local application pools for per-process reuse and a fleet-level pooler such as PgBouncer when connection pressure grows.\n\nPgBouncer has three pool modes:\n\n\n| Mode | Description | Use Case |\n|------|-------------|----------|\n| Session | Connection held for entire session | Session variables needed |\n| Transaction | Connection returned after transaction | Most applications (recommended) |\n| Statement | Connection returned after each statement | Simple queries, maximum sharing |\n\n\nIn most designs, \"use transaction pooling unless the application depends on session state\" is enough. The main trade-off is queueing: too small a pool increases latency, while too large a pool overwhelms PostgreSQL.\n\n---\n\n# 7. Common Patterns and Use Cases\n\nOnce you understand PostgreSQL fundamentals, the next level is knowing the **patterns that show up repeatedly in real systems and system design interviews**.\n\nThese patterns solve the same handful of problems again and again:\n\n- **Concurrency control**: prevent updates from silently overwriting each other\n- **Coordination**: ensure only one worker/process performs a critical action\n- **Idempotency**: make writes safe to retry\n- **Correctness guarantees**: prevent invalid states (like double-bookings)\n- **Operational visibility**: track changes to sensitive data\n\n### 7.1 Optimistic Locking\n\nWhen two users update the same row at the same time, the \"last write wins\" model can silently overwrite someone's work. **Optimistic locking** detects this by attaching a **version number** to the row.\n\n\n```sql\n-- Schema\nCREATE TABLE products (\n id BIGSERIAL PRIMARY KEY,\n name TEXT,\n price DECIMAL,\n version INT DEFAULT 1\n);\n\n-- Read\nSELECT id, name, price, version FROM products WHERE id = 123;\n-- Returns: id=123, name=\"Widget\", price=10.00, version=5\n\n-- Update with version check\nUPDATE products\nSET price = 12.00, version = version + 1\nWHERE id = 123 AND version = 5;\n\n-- If 0 rows affected, another transaction updated it first\n-- Application should reload and retry\n```\n\n\nThe core idea: **conflicts are detected, not prevented**. Most updates succeed with no blocking. If a conflict happens, the application must reload and retry (or show the user a merge/conflict message). This works best when conflicts are **rare**.\n\n### 7.2 Advisory Locks\n\nNot all coordination maps neatly to \"lock a row.\" Sometimes the thing you need to protect is **business logic**, not a specific record. PostgreSQL's **advisory locks** let you define your own lock keys without locking any actual data.\n\n\n```sql\n-- Acquire lock (blocks if held)\nSELECT pg_advisory_lock(hashtext('process_user_123'));\n\n-- Do work...\n\n-- Release lock\nSELECT pg_advisory_unlock(hashtext('process_user_123'));\n\n-- Non-blocking version\nSELECT pg_try_advisory_lock(hashtext('process_user_123'));\n-- Returns true if acquired, false if already held\n```\n\n\nCommon use cases include ensuring only one worker processes a job, serializing access to a logical resource so there is only one in-flight operation per key, and preventing concurrent workflows on the same entity such as \"payout_user_123\".\n\n### 7.3 UPSERT (INSERT ON CONFLICT)\n\n**UPSERT** makes writes idempotent by combining insert + update into a single atomic statement. It's a go-to tool for \"safe retries.\"\n\n\n```sql\n-- Insert or update user\nINSERT INTO users (email, name, updated_at)\nVALUES ('john@example.com', 'John Doe', NOW())\nON CONFLICT (email)\nDO UPDATE SET\n name = EXCLUDED.name,\n updated_at = EXCLUDED.updated_at;\n\n-- Insert or ignore\nINSERT INTO page_views (page_id, user_id, viewed_at)\nVALUES (123, 456, NOW())\nON CONFLICT DO NOTHING;\n```\n\n\nThis pattern shows up everywhere: user upserts, event ingestion, deduplication, idempotency keys.\n\n### 7.4 RETURNING Clause\n\nThe `RETURNING` clause lets you get results from `INSERT/UPDATE/DELETE` **without an extra query**. Useful for generated IDs, timestamps, and updated values.\n\n\n```sql\n-- Get inserted row with generated ID\nINSERT INTO orders (user_id, total)\nVALUES (123, 99.99)\nRETURNING id, created_at;\n\n-- Get all deleted rows\nDELETE FROM expired_sessions\nWHERE expires_at < NOW()\nRETURNING session_id, user_id;\n\n-- Get updated values\nUPDATE inventory\nSET quantity = quantity - 1\nWHERE product_id = 123\nRETURNING quantity;\n```\n\n\nIt's cleaner, faster, and avoids race conditions between \"write\" and \"read back.\"\n\n### 7.5 CTEs for Complex Queries\n\nComplex SQL often becomes unreadable when everything is nested. **CTEs (WITH clauses)** turn the query into a pipeline of named steps.\n\n\n```sql\n-- Calculate user stats with multiple steps\nWITH user_orders AS (\n SELECT user_id, COUNT(*) as order_count, SUM(total) as total_spent\n FROM orders\n WHERE created_at > NOW() - INTERVAL '1 year'\n GROUP BY user_id\n),\nuser_segments AS (\n SELECT user_id,\n CASE\n WHEN total_spent > 10000 THEN 'platinum'\n WHEN total_spent > 1000 THEN 'gold'\n ELSE 'standard'\n END as segment\n FROM user_orders\n)\nSELECT u.id, u.name, us.segment, uo.order_count\nFROM users u\nJOIN user_orders uo ON u.id = uo.user_id\nJOIN user_segments us ON u.id = us.user_id;\n```\n\n\nCTEs are especially useful in analytics-style queries, reporting, and multi-step transformations.\n\n### 7.6 Preventing Double-Booking\n\nFor booking systems (rooms, seats, appointments), the hard requirement is: **no overlapping reservations**.\n\nPostgreSQL can enforce this at the database level using range types + an exclusion constraint.\n\n\n```sql\n-- Schema with exclusion constraint\nCREATE EXTENSION btree_gist;\n\nCREATE TABLE reservations (\n id BIGSERIAL PRIMARY KEY,\n resource_id BIGINT NOT NULL,\n time_range TSTZRANGE NOT NULL,\n user_id BIGINT NOT NULL,\n EXCLUDE USING GIST (resource_id WITH =, time_range WITH &&)\n);\n\n-- Attempt to book\nINSERT INTO reservations (resource_id, time_range, user_id)\nVALUES (\n 123,\n '[2024-01-15 10:00, 2024-01-15 11:00)'::tstzrange,\n 456\n);\n-- Fails if overlapping reservation exists\n```\n\n\nThis is powerful because it makes correctness **non-negotiable**. Even if two requests race, the database guarantees that only one wins.\n\n### 7.7 Audit Logging with Triggers\n\nFor sensitive tables (accounts, permissions, payouts), you often need an immutable history of changes. Triggers can automatically log writes into an audit table.\n\n\n```sql\n-- Audit table\nCREATE TABLE audit_log (\n id BIGSERIAL PRIMARY KEY,\n table_name TEXT,\n operation TEXT,\n old_data JSONB,\n new_data JSONB,\n changed_by TEXT,\n changed_at TIMESTAMP DEFAULT NOW()\n);\n\n-- Trigger function\nCREATE OR REPLACE FUNCTION audit_trigger()\nRETURNS TRIGGER AS $$\nBEGIN\n INSERT INTO audit_log (table_name, operation, old_data, new_data, changed_by)\n VALUES (\n TG_TABLE_NAME,\n TG_OP,\n CASE WHEN TG_OP = 'DELETE' THEN row_to_json(OLD)::jsonb ELSE NULL END,\n CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN row_to_json(NEW)::jsonb ELSE NULL END,\n current_user\n );\n RETURN COALESCE(NEW, OLD);\nEND;\n$$ LANGUAGE plpgsql;\n\n-- Attach to table\nCREATE TRIGGER accounts_audit\nAFTER INSERT OR UPDATE OR DELETE ON accounts\nFOR EACH ROW EXECUTE FUNCTION audit_trigger();\n```\n\n\nThis gives you a consistent audit trail without relying on every application code path to \"remember to log.\"\n\n### 7.8 Pattern Summary\n\n\n| Pattern | Use Case | Key Feature |\n|---------|----------|-------------|\n| Optimistic locking | Concurrent updates | Version column |\n| Advisory locks | Cross-process coordination | pg_advisory_lock |\n| UPSERT | Idempotent writes | ON CONFLICT |\n| RETURNING | Avoid extra query | Get result inline |\n| CTEs | Complex queries | Readable, composable |\n| Exclusion constraint | Prevent overlaps | Range types |\n| Audit triggers | Change tracking | Automatic logging |\n\n\n---\n\n# 8. Performance Optimization\n\nWhen queries slow down, \"add an index\" is rarely the right first move. Use a simple workflow: **observe -> explain -> fix -> verify**.\n\n### 8.1 EXPLAIN ANALYZE\n\nBefore you tune anything, you need to know **where time is spent**. `EXPLAIN ANALYZE` executes the query and shows the real plan PostgreSQL used, along with timings. Adding `BUFFERS` tells you whether time is going into **CPU** or **I/O**.\n\n\n```sql\nEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)\nSELECT * FROM orders\nWHERE user_id = 123 AND created_at > '2024-01-01';\n```\n\n\n**Key metrics to examine:**\n\n\n| Metric | Meaning | Action |\n|--------|---------|--------|\n| Seq Scan | Full table scan | Add index |\n| Index Scan | Using index | Good |\n| Bitmap Heap Scan | Index + table lookup | Normal for many rows |\n| Nested Loop | O(n*m) join | Check join conditions |\n| Hash Join | Build hash table | Good for large joins |\n| Sort | Sorting in memory/disk | Check memory settings |\n| actual time | Real execution time | Optimize slow steps |\n| rows | Actual vs estimated | Update statistics |\n\n\nA practical rule: optimize the step with the biggest **actual time**, not the one that \"looks suspicious.\" If row estimates are far off, mention stale statistics and `ANALYZE`; if scans are slow despite indexes, mention table/index bloat and vacuum.\n\n### 8.2 Common Performance Anti-Patterns\n\n\n| Anti-Pattern | Problem | Solution |\n|--------------|---------|----------|\n| SELECT * | Unnecessary data transfer | Select only needed columns |\n| N+1 queries | Many small queries | Use JOINs or batch queries |\n| Missing indexes | Slow queries | Add appropriate indexes |\n| Over-indexing | Slow writes | Remove unused indexes |\n| Long transactions | Lock contention | Keep transactions short |\n| Large IN lists | Poor optimization | Use ANY(ARRAY[...]) or temp table |\n| OFFSET pagination | Scans skipped rows | Use keyset pagination |\n\n\n**Keyset pagination (cursor-based):**\n\n\n```sql\n-- Bad: OFFSET pagination\nSELECT * FROM products ORDER BY id LIMIT 20 OFFSET 10000;\n-- Must scan 10,000 rows to skip them\n\n-- Good: Keyset pagination\nSELECT * FROM products WHERE id > 12345 ORDER BY id LIMIT 20;\n-- Directly seeks to correct position\n```\n\n\n### 8.3 Performance Checklist\n\nA concise answer should cover:\n\n- find high-impact queries with aggregate query stats, not only one-off slow requests\n- inspect query plans with `EXPLAIN ANALYZE`\n- add or adjust indexes based on real access patterns\n- avoid `SELECT *`, N+1 queries, large offsets, and long transactions\n- watch for bloat and stale statistics on large, frequently updated tables\n\n\n> **TIP**\n>\n> **Design note:** Start optimization with the queries that consume the most total time. `pg_stat_statements` is usually more useful than chasing the single slowest query because frequency matters: a query running 10,000 times at 50 ms can cost more than one query running once at 2 seconds.\n>\n> For the top offenders, use EXPLAIN ANALYZE to understand execution plans, then add indexes or rewrite queries as needed. Monitor the impact after changes to verify improvements.\n\n\n---\n\n# 9. PostgreSQL vs Other Databases\n\nUse this comparison to justify PostgreSQL against common alternatives:\n\n\n| Alternative | Prefer PostgreSQL when... | Prefer the alternative when... |\n|-------------|---------------------------|--------------------------------|\n| MySQL | You need richer SQL, JSONB indexing, extensions, or stricter data integrity | The workload is simple, read-heavy, and the team already operates MySQL well |\n| MongoDB | Relationships, joins, and transactions are central | The data is naturally document-shaped and schema changes frequently |\n| DynamoDB | Queries are flexible and transactional correctness matters | Access patterns are simple, predictable, and need managed horizontal scale |\n| CockroachDB / Citus | A single PostgreSQL primary is enough | You need distributed SQL or horizontal write scaling |\n\n\n---\n\n# Summary\n\nUse PostgreSQL when the system needs relational queries, strong consistency, and room for requirements to evolve. The main trade-offs are connection management, write scaling, vacuum/maintenance, and replica lag.\n\nAnchor the answer on four points: transaction guarantees, query/index strategy, scaling plan, and failure handling. Mention PgBouncer for connection pressure, replicas for read scaling, partitioning for large tables, and retries when using Serializable isolation.\n\n---\n\n# Quiz","pageType":"system-design-interviews"}

Get Premium