This page needs a newer browser to run its tools. You can still read everything here.

Applications, scaling and what comes next interview questions

Interview questions on applications, scaling and what comes next: warm-ups, then what each harder question tests and how a good answer starts.

7 questions: 2 warm-ups and 5 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

Fresher to mid

Why does a COPY of many rows beat INSERT statements sent one at a time?

Answered in the lesson From app code: COPY, pipelining, RETURNING
Mid

Why do parameters stop SQL injection when escaping quotes by hand does not?

Answered in the lesson From app code: COPY, pipelining, RETURNING

Questions with model answers

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

Mid to senior

A sync job loads 50,000 rows with COPY in one transaction every 10 minutes. Once a day the network drops after the job has sent COMMIT and before the answer arrives, and the job retries the whole batch. Some days the table ends up with 100,000 rows. How do you make the retry safe?
What it tests

Whether you know that a lost answer leaves the commit unknown, and can design an idempotent load with a batch identity, natural keys and a check before retrying.

First step

State the problem first: after a lost answer the client cannot know whether COMMIT happened. So the retry must be harmless either way. Give every batch an id the source can repeat, and make the load record that id in the same transaction as the rows.
Follow-ups
- How does the job find out, after reconnecting, whether the batch committed?
- Why is ON CONFLICT DO NOTHING on a natural key not enough on its own?
- What changes if the rows have no natural key at all?
From the lesson From app code: COPY, pipelining, RETURNING
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
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.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I 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
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

A monthly COPY of a 40 GB CSV file into a table with six indexes and two foreign keys takes 9 hours on a server that otherwise sits idle that night. The business wants it under 2 hours. What do you change, in which order, and what does each change give up?
What it tests

Whether you can tell the costs of a big load apart (index maintenance, constraint checks, WAL, checkpoints) and pick changes from the manual's advice on populating a database, with their risks.

First step

Measure where the 9 hours go before changing anything: load a sample into the table as it is and into a copy without indexes and foreign keys, and compare the time and the WAL written. That tells you whether index maintenance, constraint checks or WAL volume dominates.
Follow-ups
- Why is it faster to build an index after the load than to maintain it during the load?
- What does COPY FREEZE need, and what does it save later?
- Which settings would you change only for the night of the load, and why not for good?
From the lesson From app code: COPY, pipelining, RETURNING
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A shop moved all reads to two streaming replicas. Now some customers add an item to the cart, land on the cart page and see it empty; a reload a second later shows the item. Replication lag is usually under 200 ms. What is happening, and how do you fix it without giving up the replicas?
What it tests

Whether you recognise a read-your-writes problem caused by replica lag, and can choose between routing to the primary and waiting for the replica, with the cost of each.

First step

Confirm the cause: compare the time between the cart write and the cart read with the replicas' replay lag, and check that the empty pages come only from replicas. Then decide which reads must see the user's own write.
Follow-ups
- How long should reads stay on the primary after a write, and how do you decide?
- How does the application know the replica has replayed the write?
- What changes on PostgreSQL 19?
From the lesson Scaling PostgreSQL: replicas, caches, shards
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A sales dashboard reads a materialized view of totals per store and hour, built from a 60 million row orders table. A job runs REFRESH MATERIALIZED VIEW every 5 minutes, and dashboard requests time out for about 40 seconds each time. What do you change, and what does each option cost?
What it tests

Whether you know what a plain refresh locks, what a concurrent refresh needs and what it costs, and when incremental maintenance or a summary table kept by the application fits better.

First step

Check what the dashboard waits for while the refresh runs, in pg_stat_activity and pg_locks, and how long the refresh itself takes. Then compare how often the totals change with how often the dashboard reads them.
Follow-ups
- What does REFRESH ... CONCURRENTLY cost compared with a plain refresh?
- When does incremental maintenance pay, and when does it not?
- How would you keep a summary current without any extension?
From the lesson Scaling PostgreSQL: replicas, caches, shards
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

An invoicing service holds 4 TB for 30,000 tenants on one tuned PostgreSQL primary at 85 % CPU, mostly from writes. Five tenants produce 35 % of the writes, and finance runs reports across all tenants each month. Plan the move to several servers.
What it tests

Whether you can choose a shard key, route queries, plan for big tenants, cross-tenant reports and transactions, and migrate without downtime.

First step

Confirm that the writes are the limit and that the statements are already tuned, from pg_stat_statements and the server's own metrics. Then check that nearly every table and query carries the tenant id, because that decides whether the tenant can be the shard key.
Follow-ups
- Modulo or a lookup table for routing, and why?
- What happens to the five big tenants?
- How do the monthly reports and transfers between tenants work after the split?
From the lesson Scaling PostgreSQL: replicas, caches, shards
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes

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

Warm-up questions

Why does a COPY of many rows beat INSERT statements sent one at a time?

Why do parameters stop SQL injection when escaping quotes by hand does not?

Questions with model answers

A sync job loads 50,000 rows with COPY in one transaction every 10 minutes. Once a day the network drops after the job has sent COMMIT and before the answer arrives, and the job retries the whole batch. Some days the table ends up with 100,000 rows. How do you make the retry safe?

A monthly COPY of a 40 GB CSV file into a table with six indexes and two foreign keys takes 9 hours on a server that otherwise sits idle that night. The business wants it under 2 hours. What do you change, in which order, and what does each change give up?

A shop moved all reads to two streaming replicas. Now some customers add an item to the cart, land on the cart page and see it empty; a reload a second later shows the item. Replication lag is usually under 200 ms. What is happening, and how do you fix it without giving up the replicas?

A sales dashboard reads a materialized view of totals per store and hour, built from a 60 million row orders table. A job runs REFRESH MATERIALIZED VIEW every 5 minutes, and dashboard requests time out for about 40 seconds each time. What do you change, and what does each option cost?

An invoicing service holds 4 TB for 30,000 tenants on one tuned PostgreSQL primary at 85 % CPU, mostly from writes. Five tenants produce 35 % of the writes, and finance runs reports across all tenants each month. Plan the move to several servers.