PostgreSQL essentials: 50 cards on indexes, MVCC and JSONB

All 50 cards

1. 1
   text vs varchar(n) in PostgreSQL
   Same storage and speed; varchar(n) only adds a length check. Use text unless you want that limit

   char(n) pads values with spaces and is rarely what you want.
2. 2
   timestamp vs timestamptz
   timestamptz stores an absolute instant (in UTC) and displays it in the session’s time zone; timestamp is a wall-clock value with no zone. Prefer timestamptz
3. 3
   numeric vs double precision
   numeric does exact decimal arithmetic, right for money; double precision is fast binary floating point, where 0.1 + 0.2 isn’t exactly 0.3
4. 4
   Declare an auto-incrementing primary key the modern way
   sql

   CREATE TABLE users (
     id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
     email text NOT NULL UNIQUE
   );

   SQL-standard identity columns are preferred over serial. Sequence values are never rolled back, so gaps in IDs are normal.
5. 5
   Generate UUIDs in PostgreSQL
   gen_random_uuid() makes random v4 UUIDs (built in since PostgreSQL 13); uuidv7() (PostgreSQL 18) makes time-ordered ones that are friendlier to B-tree indexes
6. 6
   What does SELECT 5 / 2; return?
   2: dividing two integers truncates. Write 5 / 2.0 or 5::numeric / 2 to get 2.5
7. 7
   What does this create?

   sql

   CREATE TABLE Users (userId int);

   A table users with a column userid: unquoted names are folded to lowercase. Quoted "userId" keeps its case but must then always be quoted, so use snake_case
8. 8
   What does now() return inside a long transaction?
   The transaction’s start time, the same for every call in that transaction; clock_timestamp() returns the actual current time
9. 9
   What is the default index type good for?
   B-tree indexes handle equality and ranges (=, <, BETWEEN), ORDER BY, IS NULL and prefix searches like LIKE 'abc%'

   Prefix LIKE needs the C collation or an index built with text_pattern_ops.
10. 10
    When do you use a GIN index?
    For values that contain many elements: jsonb documents, arrays, full-text search tsvectors and trigram search with pg_trgm
11. 11
    What is a BRIN index for?
    Huge tables whose values follow the physical row order, like append-only timestamps; it stores only min and max per block range, so it is tiny
12. 12
    Does PostgreSQL index foreign key columns automatically?
    No. Primary keys and unique constraints get indexes, but the referencing column doesn’t; index it to speed up joins and deletes on the parent table
13. 13
    What is a partial index?
    An index over only the rows matching a WHERE clause: smaller and faster, used when a query’s filter implies that condition

    sql

    CREATE INDEX ON orders (created_at) WHERE status = 'pending';

14. 14
    Why doesn’t WHERE lower(email) = $1 use the index on email?
    The index stores email, not lower(email). Create an expression index: CREATE INDEX ON users (lower(email));
15. 15
    What does CREATE INDEX CONCURRENTLY do?
    Builds the index without blocking writes to the table; it is slower, can’t run inside a transaction block, and a failed build leaves an invalid index to drop
16. 16
    What does INCLUDE add to an index?
    Extra columns stored in the index but not searched on, so queries that read only those columns can use an index-only scan

    sql

    CREATE INDEX ON orders (customer_id) INCLUDE (total);

17. 17
    Why can’t WHERE name LIKE '%son' use a B-tree index?
    A B-tree is sorted from the first character, so a leading wildcard can’t narrow the search. Use a trigram GIN index (pg_trgm) or full-text search
18. 18
    Seq Scan, Index Scan, Index Only Scan, Bitmap Heap Scan
    Reads the whole table; follows the index to each row; answers from the index alone; collects matches from an index, then reads those table pages in order
19. 19
    What do these numbers mean in EXPLAIN output?

    sql

    Index Scan using users_pkey on users  (cost=0.29..8.31 rows=1 width=72)

    Planner estimates: startup cost..total cost in arbitrary units, expected rows returned, and average row width in bytes
20. 20
    Why wrap EXPLAIN ANALYZE of a DELETE in a transaction?
    EXPLAIN ANALYZE actually runs the statement. Use BEGIN; EXPLAIN ANALYZE …; ROLLBACK; to measure it without keeping the changes
21. 21
    The planner expected 10 rows but got 500,000. What should you check first?
    Stale statistics: run ANALYZE table_name; so the planner has fresh row counts and value distributions

    Autovacuum normally runs ANALYZE for you.
22. 22
    Is a Seq Scan in a plan always bad?
    No. For small tables, or queries that return a large share of the rows, reading the table sequentially is cheaper than many random index lookups
23. 23
    What is PostgreSQL’s default isolation level?
    Read Committed: each statement sees the data committed before that statement began

    Read Uncommitted behaves the same in PostgreSQL: dirty reads never happen.
24. 24
    What does Repeatable Read guarantee in PostgreSQL?
    The whole transaction sees one snapshot taken at its first query, which also prevents phantom reads; updating a row another transaction changed meanwhile fails with a serialization error
25. 25
    What must an app do when using Serializable isolation?
    Retry transactions that fail with SQLSTATE 40001: PostgreSQL aborts one side of any conflict so results match running them one at a time
26. 26
    Two sessions read a balance, subtract in app code and write it back. How do you prevent the lost update?
    Update atomically (SET balance = balance - 10), lock the row first with SELECT … FOR UPDATE, or use Repeatable Read and retry
27. 27
    What does this do when several workers run it?

    sql

    SELECT id FROM jobs
    WHERE status = 'queued'
    ORDER BY id
    LIMIT 1
    FOR UPDATE SKIP LOCKED;

    Each worker locks a different queued job, skipping rows other transactions have locked: a simple job queue
28. 28
    Can you roll back CREATE TABLE or ALTER TABLE in PostgreSQL?
    Yes. DDL is transactional, so a migration can run in one transaction and roll back cleanly; exceptions include CREATE INDEX CONCURRENTLY and CREATE DATABASE
29. 29
    What is MVCC?
    Multi-version concurrency control: an UPDATE writes a new row version and leaves the old one as a dead tuple, so each transaction reads a consistent snapshot

    Readers don’t block writers and writers don’t block readers.
30. 30
    VACUUM vs VACUUM FULL
    VACUUM marks dead tuples’ space reusable and runs alongside normal traffic, but rarely shrinks the file; VACUUM FULL rewrites the table compactly under an exclusive lock that blocks all access
31. 31
    What does autovacuum do?
    Vacuums and analyzes tables in the background as they change, and freezes old rows to prevent transaction ID wraparound; don’t turn it off
32. 32
    Why are long-running or idle-in-transaction sessions harmful?
    Their old snapshot stops VACUUM from removing any dead tuples newer than it, so tables and indexes bloat
33. 33
    json vs jsonb
    json stores the input text verbatim; jsonb stores a parsed binary form (no whitespace or key order, last duplicate key wins) that is faster to query and can be indexed. Use jsonb
34. 34
    -> vs ->> on JSON
    -> returns jsonb (to keep navigating); ->> returns text

    sql

    SELECT data->'address'->>'city' FROM users;

35. 35
    Find events whose JSONB payload has "type": "signup", using an index
    sql

    CREATE INDEX ON events USING gin (payload);
    
    SELECT * FROM events
    WHERE payload @> '{"type": "signup"}';

    @> means “contains”. The default GIN operator class also supports key-exists checks like payload ? 'email'.
36. 36
    Are CTEs an optimization fence in PostgreSQL?
    Not since PostgreSQL 12: a non-recursive, side-effect-free CTE referenced once is inlined into the main query. Write AS MATERIALIZED to force it to run separately
37. 37
    Move finished tasks to an archive table in one statement
    sql

    WITH moved AS (
      DELETE FROM tasks WHERE done RETURNING *
    )
    INSERT INTO tasks_archive SELECT * FROM moved;

38. 38
    List an employee and everyone below them in the org chart
    sql

    WITH RECURSIVE team AS (
      SELECT id, name FROM employees WHERE id = 1
      UNION ALL
      SELECT e.id, e.name
      FROM employees e JOIN team t ON e.manager_id = t.id
    )
    SELECT * FROM team;

39. 39
    Get the 3 most expensive products per category
    sql

    SELECT * FROM (
      SELECT *, ROW_NUMBER() OVER (
        PARTITION BY category ORDER BY price DESC
      ) AS rn
      FROM products
    ) ranked
    WHERE rn <= 3;

    Window functions can’t appear in WHERE, hence the subquery.
40. 40
    What does DISTINCT ON do?
    PostgreSQL-specific: keeps the first row of each group, as decided by ORDER BY

    sql

    -- latest login per user
    SELECT DISTINCT ON (user_id) *
    FROM logins
    ORDER BY user_id, created_at DESC;

41. 41
    Why do rows with the same day get the same running total?

    sql

    SELECT day, amount,
      SUM(amount) OVER (ORDER BY day) AS running
    FROM sales;

    With ORDER BY, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes every peer with an equal day. Use ROWS BETWEEN … to go row by row
42. 42
    Does CHECK (price > 0) reject a NULL price?
    No. A check fails only when the expression is false, and NULL > 0 is unknown. Add NOT NULL as well
43. 43
    What does this constraint prevent?

    sql

    ALTER TABLE bookings
      ADD EXCLUDE USING gist (room_id WITH =, during WITH &&);

    Two bookings for the same room with overlapping during time ranges

    Using = on a plain column in a GiST index needs the btree_gist extension.
44. 44
    What does DEFERRABLE INITIALLY DEFERRED do on a constraint?
    Checks it at COMMIT instead of after each statement, so rows can be temporarily inconsistent inside the transaction, e.g. for circular foreign keys
45. 45
    Users vs groups in PostgreSQL
    Both are roles. CREATE USER is CREATE ROLE with LOGIN, and granting one role to another (GRANT readers TO alice) makes it a group
46. 46
    Create a read-only role that also covers future tables
    sql

    CREATE ROLE readonly;
    GRANT USAGE ON SCHEMA public TO readonly;
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
    ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT SELECT ON TABLES TO readonly;

    ON ALL TABLES only covers existing tables; default privileges cover tables the granting role creates later.
47. 47
    What changed about the public schema in PostgreSQL 15?
    Ordinary users can no longer create objects in it by default; the CREATE privilege must be granted explicitly
48. 48
    Restrict rows per user with row-level security
    sql

    ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
    
    CREATE POLICY owner_only ON documents
      USING (owner_id = current_setting('app.user_id')::int);

    Superusers, BYPASSRLS roles and, unless you add FORCE ROW LEVEL SECURITY, the table owner bypass policies.
49. 49
    Why put PgBouncer in front of PostgreSQL?
    Each connection is a separate server process with its own memory, so thousands of connections hurt; a pooler shares a small number of them
50. 50
    Essential psql meta-commands
    \l lists databases, \c db connects, \dt lists tables, \d name describes a table, \x toggles expanded output, \q quits