APIs and data

Why is PUT a better retry than a blind POST?

PUT replaces the resource at a known id, so repeating it converges. A POST that always inserts can create a second row.

Which API change is backward compatible?

Adding a field old clients can ignore. Removing a field, renaming one, or changing a type forces every caller to ship together.

What is a timeout budget?

The caller's deadline split across the inner calls, with time left over. Inner timeouts that add up past the caller will always fail late.

What is a phantom read?

A transaction repeats a query and sees rows that another transaction inserted in between. The first read's set of rows was not stable.

What is a dirty read?

Reading a row another transaction has changed but not committed. If that transaction rolls back, you acted on a value that never landed.

What is a lost update?

Two transactions read the same row, and each writes its own result. The second write covers the first, and that change is gone.

Why does lower(email) ignore an email index?

The index stores the raw column. A query that wraps the column in a function needs an index on that expression.

Why store event time in UTC?

Local clocks and zones disagree. A single UTC timestamp, plus the zone only when you display it, keeps the order honest.

Why must a migration work with both app versions?

A rolling deploy runs old and new code at once. A column only one version understands will break the other.

What problem does an outbox solve?

Saving a row and publishing an event are two steps. Writing the event in the same transaction, then publishing it, keeps them from splitting.

What is an insecure direct object reference?

A caller changes an id in the URL and receives another user's object. The handler checked the login, not ownership of that id.

What is the difference between authn and authz?

Authentication says who the caller is. Authorization says whether that caller may act on this object.

What is cursor pagination?

The response includes a token for the last item returned. The next call asks for items after that token.

What is an N+1 query?

One query loads the parents, then one query per parent loads the children. A join or a batched load does the work in a fixed number of queries.

What does a database transaction guarantee?

The writes inside it commit together or not at all. A crash in the middle does not leave a half-applied update.

What does the isolation level change?

Which anomalies concurrent transactions are allowed to see. Stricter levels block more and allow fewer surprises.

When does an index help a query?

When the filter or the sort matches the index's column order, and the index cuts out most of the table.

What is a covering index?

An index that holds every column the query needs, so the database can answer without reading the row itself.

What is a row lock for?

Stopping a second transaction from changing a row the first one is still deciding about. Held too long, it stalls everyone else.

What is a deadlock?

Two transactions each hold a lock the other needs. The database picks one to roll back so the other can proceed.

What is optimistic concurrency?

You read a version number with the row and write only if it is unchanged. A clash becomes a retry instead of a long lock.

What is a connection pool?

A set of open database connections reused across requests. Opening a new connection per request spends more time connecting than querying.

Why can a bigger pool make a database slower?

Each connection costs memory and the database can only run so much at once. Extra waiters pile up inside the database.

What is a retry storm?

Callers retry together after a blip, and the retry traffic is larger than the failure they were recovering from.

What does exponential backoff do?

Each retry waits longer than the last, so a down dependency gets room to recover instead of a steady hammering.

Why add jitter to a retry delay?

So clients do not wake up on the same schedule and hit the service in a single wave.

Why add a column as nullable first?

Old code does not know the column and will not fill it. A required column with no default fails those writes.

What is a backfill?

Filling a new column or table for rows that already exist, usually in batches, after the new shape is in place.

What is a soft delete?

The row stays, marked as deleted, so a bug or a restore can still see it. Queries must then exclude those rows on purpose.

What is a foreign key?

A constraint that the id in this column matches a real row in another table. The database rejects an orphan.

What is a unique constraint for, besides cleanliness?

It makes a retry or a race fail the second insert, instead of storing two rows that mean the same thing.

Why can a migration lock a hot table?

Some column changes rewrite the table or take a lock that blocks writes. On a large table that pause is the outage.

What is a read replica?

A copy that serves reads and lags the primary by a little. A user who writes and immediately reads it may not see the write.

What is replication lag?

How far a copy is behind the primary. A read from that copy can return the previous value until it catches up.

What belongs in an API error body?

A stable code the client can branch on, and a message a person can read. Clients should not parse your prose.

When do you version an API?

When a change would break callers and you cannot move them all at once. Two versions run until the old callers are gone.

Why is a total count expensive?

It often scans or counts the whole match. An infinite scroll usually only needs whether another page exists.

What is a bulk endpoint for?

One request that applies the same action to many ids, so the client does not pay a round trip per item.

What is a partial failure in a bulk call?

Some items succeed and some do not. The response has to say which, or the client cannot retry safely.

Why send a content type?

So the client knows how to parse the body. A JSON body labeled as something else, or unlabeled, gets misread.

What is an ETag?

A fingerprint of a representation. The client can ask for the resource only if it changed, and can require that fingerprint on a write.

What does If-Match protect?

The write proceeds only if the resource still has the version the client read. Otherwise someone else's edit would be overwritten.

What is an ON DELETE CASCADE?

Deleting the parent deletes the rows that point at it. Convenient, and dangerous if those rows were not meant to vanish with it.

What is a query plan?

The database's choice of scans, joins, and indexes for a statement. It is how you see why a query is slow.

What is a sequential scan?

Reading the table from start to finish. It is fine for a small table and a bad sign when a selective query does it on a large one.

What is write skew?

Two transactions read an overlapping condition, then each writes a row that would have violated the condition if both had seen the other.

What makes a predicate sargable?

It compares the column itself to a value, so an index can bound the search. Wrapping the column in a function usually prevents that.

What is normalization for?

Each fact lives in one place. An update then cannot leave two copies of the same fact disagreeing.

When do you denormalize on purpose?

When a read is hot and the join is too expensive, and you accept the job of keeping the copied values in step.