MySmartCoPilot
Ask Passes Your pass 7 days left 1 day left Last day

Your country

Tools that support it use your country for local currency, number formats, units and paper size. Your choice is saved only in this browser.

PostgreSQL in Depth Module 2

PostgreSQL data types, chosen well interview questions

Interview questions on postgreSQL data types, chosen well: warm-ups, then what each harder question tests and how a good answer starts.

15 questions: 5 warm-ups and 10 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

  1. Fresher

    When is text the right type, and when do varchar(n) or char(n) earn their place?

    Answered in the lesson Text, collations and case-insensitive matching

  2. Fresher

    Why does PostgreSQL store the int4range [1,4] as [1,5)?

    Answered in the lesson Ranges, multiranges and WITHOUT OVERLAPS keys

  3. Fresher to mid

    What does a PostgreSQL domain add over a CHECK constraint written on the column?

    Answered in the lesson Arrays, enums, domains and composite types

  4. Fresher

    PostgreSQL has both json and jsonb. How do they differ, and which one should a table normally use?

    Answered in the lesson JSONB at scale: jsonpath, indexes, update costs

  5. Fresher to mid

    An identity column holds ids 1, 4 and 5, and no row was ever deleted. Where did 2 and 3 go, and when does that matter?

    Answered in the lesson Numbers, identity, UUIDv7 and generated columns

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

  1. Mid to senior

    An application needs to store email as the user typed it, keep it case-insensitively unique and search it with LIKE 'ASHA%' and the position functions. On PostgreSQL 18, which design do you ship, and why?

    What it tests
    Picking between an expression index on lower()/casefold(), citext and an ICU nondeterministic collation; knowing what each one covers, which indexes they can serve, and the trade-offs they bring.
    First step
    List the three choices and judge them against the three requirements (store as typed, UNIQUE case-insensitive, LIKE case-insensitive). Say which built-in design meets all three on PostgreSQL 18, how the citext extension compares with it, and the main cost of each; then describe the shape that two columns and an expression index would take.
    Follow-ups

    From the lesson Text, collations and case-insensitive matching

    Opening the model answers with your pass…

    Model answers open with any MySmartCoPilot pass.

    • Pro pass · 1 week₹79$2.99
    • 30 days₹179$5.99
    • 1 year₹1,699$45

    One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

    Choose a passI have a licence key

    It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 5 minutes

  2. Mid to senior

    A Linux distribution upgrade has moved glibc from 2.17 to 2.28 under a 24/7 PostgreSQL 18 cluster. A payroll report misses rows that are clearly in the table. How do you detect which indexes are affected and repair them without extended downtime, and what would you have done differently to avoid the problem?

    What it tests
    Knowing why glibc 2.28 is dangerous for libc indexes, auditing with pg_collation and pg_index, repairing with REINDEX CONCURRENTLY and REFRESH VERSION, and the long-term choice of a provider that does not drift.
    First step
    State the risk in one sentence (glibc 2.28 rewrote many UTF-8 locale rules, so B-trees sorted by them may now be out of order), then outline the audit (pg_database.datcollversion, pg_collation.collversion against pg_collation_actual_version, pg_index.indcollation) and the repair (REINDEX CONCURRENTLY, then ALTER COLLATION REFRESH VERSION and ALTER DATABASE REFRESH COLLATION VERSION). Close with the choice of the builtin provider for future columns to avoid the recurrence.
    Follow-ups

    From the lesson Text, collations and case-insensitive matching

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 5 minutes

  3. Mid to senior

    A coworking service double-booked two desks in Pune last week. How do you prevent two overlapping reservations of the same desk in PostgreSQL, and what did PostgreSQL 18 change about the way you write it?

    What it tests
    Modelling a booking as a range, choosing between an EXCLUDE constraint and a WITHOUT OVERLAPS primary key, and knowing what each form supports.
    First step
    Model the reservation as a tstzrange column with half-open bounds, add btree_gist for scalar comparisons, and use PRIMARY KEY (room, during WITHOUT OVERLAPS) in PostgreSQL 18. Before 18, write it as EXCLUDE USING gist (room WITH =, during WITH &&), which the key behaves like; both raise SQLSTATE 23P01 on a conflict.
    Follow-ups

    From the lesson Ranges, multiranges and WITHOUT OVERLAPS keys

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 4 minutes

  4. Mid to senior

    Why are half-open bounds [start, end) the right default for a schedule, and what breaks when the schedule is stored with closed bounds [start, end]?

    What it tests
    Reasoning about range bounds in a working schedule, understanding PostgreSQL's canonical form on discrete types, and predicting the overlap errors of a closed-bounds schema.
    First step
    Half-open bounds let one booking end at the instant the next begins without the two overlapping: the end of the first and the start of the next can be the same clock time because ) excludes it and [ includes it, so a WITHOUT OVERLAPS key accepts them. Closed bounds make the shared instant belong to both ranges, so the overlap check refuses every back-to-back pair; the schedule either leaves gaps or stops filling.
    Follow-ups

    From the lesson Ranges, multiranges and WITHOUT OVERLAPS keys

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  5. Mid to senior

    A social app stores each post's hashtags in a text[] column with a GIN index. At 3,000 new posts a second, hashtag searches slow down at peak, and the product team now wants hourly trending hashtags. Do you keep the array or move to a join table, and how do you index what you keep?

    What it tests
    Choosing between an array and a join table from a workload's reads and writes, and knowing how the pending list of a GIN index trades insert speed for search speed.
    First step
    Write down the three workloads with their rates: 3,000 inserts a second, containment searches for one or two hashtags, and an hourly count per hashtag. Then measure where the slow searches spend their time, with EXPLAIN (ANALYZE, BUFFERS) at a peak and the size of the index's pending list, because a long pending list and a missing access path need different cures.
    Follow-ups

    From the lesson Arrays, enums, domains and composite types

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 4 minutes

  6. Mid to staff

    An orders table of 40 million rows has a status column of type order_status (pending, on_hold, paid, shipped, cancelled). This release adds partially_refunded between paid and shipped and retires on_hold, which nothing may write again. Your migration tool runs each file in one transaction. Plan both changes without blocking orders.

    What it tests
    Knowing what ALTER TYPE can and cannot do to an enum, ordering migrations and deploys around the commit a new value needs, and retiring a value with a constraint, a new type or a lookup table.
    First step
    Split the work by what ALTER TYPE allows. ADD VALUE 'partially_refunded' AFTER 'paid' changes only the catalog, but nothing may use the label in the transaction that adds it, so it gets a migration file of its own, committed before any code writes it. on_hold cannot be dropped, so retiring it means stopping every write of it first and then moving the rows that hold it.
    Follow-ups

    From the lesson Arrays, enums, domains and composite types

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 5 minutes

  7. Mid to senior

    A sign-in service keeps each user's profile as one jsonb document of about 40 kB and updates a last-seen timestamp inside it on every request, about 2,000 times a second at peak. WAL volume and replication lag have grown far faster than traffic. Explain the cause and how you would restructure the table without downtime.

    What it tests
    Seeing that one changed key rewrites and logs the whole document and its TOAST chunks, putting a number on that WAL, and planning a move of the hot key into a column while the service keeps running.
    First step
    Measure before changing anything: the WAL each statement writes (pg_stat_statements has a wal_bytes column) and the document's stored size (pg_column_size). An update that rewrites 40 kB, 2,000 times a second, is about 80 MB of WAL a second for one timestamp. Then plan to move the timestamp into a column of its own, or a narrow table, so each request writes a small row version instead of the whole document.
    Follow-ups

    From the lesson JSONB at scale: jsonpath, indexes, update costs

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 5 minutes

  8. Mid to senior

    An orders table of 20 million rows keeps its attributes in jsonb. A support query filters on doc ->> 'status' = 'disputed', about 300 rows; EXPLAIN estimates 100,000 and the query scans the table for 9 seconds. A colleague proposes a GIN index on the column. What would you do instead, and how would you prove it works?

    What it tests
    Knowing that a GIN index on the document cannot serve a filter written with the arrow operator, that keys inside a document have no statistics by default, and fixing both the estimate and the access path, proved with EXPLAIN ANALYZE.
    First step
    Read the plan first. An estimate of 100,000 for 20 million rows is exactly 0.5 %, the planner's default for an equality on an expression it has no statistics for, and the condition compares the text that the arrow operator extracts, which a GIN index on the column cannot serve, so it would not change this query as written. Index the expression the query uses, analyse the table, and compare the plans before and after.
    Follow-ups

    From the lesson JSONB at scale: jsonpath, indexes, update costs

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 4 minutes

  9. Mid to senior

    A wallet service on PostgreSQL 18 has kept balances and 80 million transaction amounts in double precision for two years, and month-end totals now differ from the bank statement by a few paise. What do you change in the schema, how do you migrate the live tables, and what do you do about the stored amounts?

    What it tests
    Knowing why binary floats drift and which exact types fix it, why the money type is no answer, how to change a column's type on a large live table without one long rewrite, and how to repair stored amounts.
    First step
    Name the cause in one sentence: double precision keeps the nearest binary value of each decimal amount, so sums and roundings drift. Choose the target, numeric(12,2) or a bigint of paise, and say why not money. Then outline an expand and contract migration: a new exact column filled in batches with round(amount::numeric, 2), a check against the transactions and the bank's records, and a switch of writes and reads.
    Follow-ups

    From the lesson Numbers, identity, UUIDv7 and generated columns

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 5 minutes

  10. Mid to senior

    An events table keyed by gen_random_uuid() takes 3,000 inserts a second. Past 200 million rows its insert latency and WAL volume keep climbing, while a table keyed by a bigint identity stays flat. Explain the cause, what you would measure to confirm it, and how you would change the key without stopping writes.

    What it tests
    Knowing how random and time-ordered keys use the pages of a B-tree, how that shows in disk reads, page splits and full-page writes, and how to move new rows to uuidv7() without rewriting the table.
    First step
    Explain that random keys send each insert to a random leaf of the primary key index, so once the index outgrows memory most inserts read a page from disk, splits leave pages part empty, and the first change to each page after a checkpoint writes the whole page to WAL. Name the measurements: index size and leaf fill, blocks read and WAL bytes per insert. Then propose ALTER COLUMN id SET DEFAULT uuidv7(), a change to the catalog only.
    Follow-ups

    From the lesson Numbers, identity, UUIDv7 and generated columns

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 5 minutes

All PostgreSQL in Depth interview questions · The PostgreSQL in Depth track