Vibe coder or product engineer databases interview questions

As asked

You have a Next.js page that lists 50 blog posts and each post shows its author name. You are using Prisma and your server logs show 51 queries on every page load. Walk me through exactly what is happening and how you fix it without switching ORMs.

Sample answer outline

The N+1 happens because the code fetches posts in one query and then, inside a loop, fires a separate findUnique for each post's author. The fix is to add an include or select on the initial findMany so Prisma does a JOIN or a batched second query and assembles the result in memory. A strong answer also mentions Prisma's fluent relation queries, the difference between include and select for shaping the payload, and enabling Prisma query logging or using prisma.$on to surface slow queries in development.

Expect these follow-ups

• How would you verify the fix actually reduced query count in production without adding a full APM tool?

As asked

You are building a multi-tenant SaaS on Supabase where users can only read and write rows that belong to their own organisation. Walk me through how you set up Row Level Security policies, and where bugs in RLS policies typically hide.

Sample answer outline

You enable RLS on the table, then write USING and WITH CHECK expressions that compare auth.uid() or a JWT claim like auth.jwt()->>'org_id' to a column on the row. The most common bug is forgetting to enable RLS after creating a new table, which leaves it world-readable, or writing a USING clause that evaluates to false for service-role calls and locks out background jobs. A strong answer also covers the performance implication of RLS policies running on every row, the need to index the tenant column, and how to test policies using set role and set local to impersonate a JWT in psql.

Expect these follow-ups

• How do you handle a background worker that needs to bypass RLS without using the service-role key in client-side code?

As asked

You are building a product where users can define custom fields on their records, like a CRM where each company has different metadata. When do you reach for JSONB and when do you normalise into proper relational tables, and what does that decision cost you later?

Sample answer outline

JSONB is the right call when the shape of the data is truly variable per row and you are not querying into specific keys at scale. The cost is you lose referential integrity, type enforcement at the DB layer, and efficient indexed lookups unless you add GIN indexes and know your key access patterns. Normalised tables give you foreign keys, proper indexes, and clean aggregation, but a wide EAV schema becomes painful to query and migrate. A strong answer notes that a hybrid works well: a fixed schema for the core fields you always query, plus a JSONB metadata column for the long tail of custom attributes, and using generated columns or functional indexes to index specific JSONB paths you do query often.

Expect these follow-ups

• How do you migrate data from JSONB into proper columns once your query patterns solidify?

As asked

You need to rename a column in a Postgres table that has a live production app writing to it every second. Walk me through the steps to do this without taking the site down or corrupting data.

Sample answer outline

The safe pattern is expand-contract: first add the new column and write to both old and new columns in the app, then backfill the new column from the old one in batches to avoid long locks, then deploy a version of the app that reads from the new column only, then drop the old column in a separate migration. Renaming directly with ALTER TABLE RENAME COLUMN takes an ACCESS EXCLUSIVE lock that blocks all reads and writes for the duration, which is unacceptable on a hot table. A strong answer also notes that backfilling in batches with a LIMIT and a WHERE old_column IS NOT NULL prevents table bloat from transaction overhead and lets autovacuum keep up.

Expect these follow-ups

• How do you handle a migration that needs to add a NOT NULL column with a default value on a table with 50 million rows?

As asked

Your product needs search across user-created content. You already use Postgres. When does staying with Postgres full-text search make sense versus paying for Algolia, and what exactly breaks with Postgres search at scale?

Sample answer outline

Postgres full-text search with tsvector columns and GIN indexes handles most product search needs well up to tens of millions of documents, especially if you need transactional consistency between write and search. What breaks is relevance tuning: Postgres ranks by tf-idf but cannot personalise ranking, does not handle typos without pg_trgm, and cannot do faceted filtering as cleanly. Algolia gives you instant search latency under 50ms globally, typo tolerance, and facets out of the box but at the cost of sync complexity and price. A strong answer notes that keeping search in Postgres is dramatically simpler, and you should only pay for an external service when you have proven that Postgres search quality or latency is actually harming user experience.

Expect these follow-ups

• How would you keep an Algolia index in sync with a Postgres table that gets frequent writes?

As asked

I run EXPLAIN ANALYZE on a query and see a Seq Scan on a 2 million row table with cost=0.00..45000. Walk me through what that means, what you look for in the output to understand performance, and what you do next.

Sample answer outline

A Seq Scan means Postgres is reading every row in the table rather than using an index. The cost estimate in the planner is in abstract units, not milliseconds. The key number to look at is actual time in the ANALYZE output. Other things to read: the number of rows expected versus actual (large discrepancy means stale statistics), whether a Sort or Hash Join is appearing, and the total actual time at the top level node. Next steps: check if an index exists on the column used in the WHERE clause, run ANALYZE on the table to update statistics, and verify the query planner is picking up the index with a smaller test dataset. A strong answer mentions that the planner may choose a Seq Scan intentionally if it expects to return a large fraction of the table, since sequential reads are faster than random index seeks.

Expect these follow-ups

• When would you use an index hint to override the planner's choice versus trusting the planner?

As asked

Walk me through ACID using a concrete example from a product you would build: a user transferring credits between two accounts. What does each property guarantee and which ones are most likely to bite you in practice?

Sample answer outline

Atomicity: the debit and credit happen together or not at all, so you never lose credits. Consistency: foreign key constraints and check constraints are satisfied after the transaction, so credits cannot go negative if you have a constraint. Isolation: concurrent transactions do not see each other's partial changes, so two simultaneous transfers do not double-spend. Durability: once committed, the transfer survives a crash. In practice, isolation level is what bites teams: the default Read Committed allows non-repeatable reads and you need Serializable or explicit row locks to prevent race conditions on balance updates. A strong answer also names SELECT FOR UPDATE as the common pattern for locking rows during a balance check and debit.

Expect these follow-ups

• What is the difference between Serializable and Repeatable Read isolation, and when would you choose each?