Production patterns

Everything so far has been about getting the right answer. This lesson is about the queries that are correct on your laptop and cause an incident at scale.

Upsert

“Insert, or update if it already exists.” Both engines have it and neither spells it the same way.

-- PostgreSQL
INSERT INTO product (sku, name, cents)
VALUES ('A-1', 'Widget', 1200)
ON CONFLICT (sku) DO UPDATE
  SET name = EXCLUDED.name,
      cents = EXCLUDED.cents
WHERE product.cents <> EXCLUDED.cents;      -- skip a no-op write

-- MySQL
INSERT INTO product (sku, name, cents)
VALUES ('A-1', 'Widget', 1200)
ON DUPLICATE KEY UPDATE
  name = VALUES(name),                      -- or: name = new.name  (8.0.20+)
  cents = VALUES(cents);

Points that matter:

• ON CONFLICT names the constraint it is resolving, so you know which uniqueness rule you meant. ON DUPLICATE KEY reacts to any unique constraint, which becomes ambiguous the moment a table has two.
• The WHERE on PostgreSQL’s DO UPDATE skips writing a row that has not changed. That saves a row version, an index update, and a WAL record per unchanged row — which on a large sync is most of the work.
• INSERT IGNORE in MySQL and ON CONFLICT DO NOTHING in PostgreSQL are not equivalent: INSERT IGNORE downgrades every error to a warning, including type errors and truncation, not just the duplicate you had in mind.

This site’s content seeding is exactly this pattern, with one addition worth stealing: a WHERE origin = 'seed' on the update, so the importer can only modify rows it wrote and never overwrites something a person edited.

Keyset pagination

OFFSET gets slower on every page, because the engine produces and discards the skipped rows. Keyset pagination — also called cursor or seek pagination — asks for “the next 20 after this point” and stays flat.

-- Page 1
SELECT id, placed_at FROM "order"
ORDER BY placed_at DESC, id DESC
LIMIT 20;

-- Page 2: pass the last row of page 1 back in
SELECT id, placed_at FROM "order"
WHERE (placed_at, id) < (:last_placed_at, :last_id)     -- row comparison
ORDER BY placed_at DESC, id DESC
LIMIT 20;

Row comparison — (a, b) < (x, y) — is supported by both engines and is exactly the right tool: it compares lexicographically, so it means “earlier timestamp, or the same timestamp and a smaller id”. Writing it by hand as placed_at < :t OR (placed_at = :t AND id < :i) means the same thing and is easier to get wrong.

With an index on (placed_at DESC, id DESC) this reads 20 rows on page 1 and 20 rows on page 5000. The trade-off is real and worth stating: you lose the ability to jump to an arbitrary page number. For a feed or an API that is no loss; for a table with numbered page buttons, it is a product decision.

Deleting or updating a lot of rows

A single DELETE of ten million rows holds locks for its whole duration, generates one enormous transaction, and cannot be interrupted without losing all the work. Do it in batches, each its own transaction.

-- PostgreSQL: returning ids makes the loop's progress observable
WITH doomed AS (
  SELECT id FROM "order"
   WHERE placed_at < '2020-01-01'
   ORDER BY id
   LIMIT 5000
)
DELETE FROM "order" WHERE id IN (SELECT id FROM doomed);

-- MySQL
DELETE FROM `order` WHERE placed_at < '2020-01-01' ORDER BY id LIMIT 5000;

Repeat until zero rows are affected. Keep the batch small enough that each transaction finishes in well under a second, and pause between batches so replication can keep up.

If you are deleting most of a table, copying the survivors into a new table and swapping names is usually faster than any batching, and it leaves no bloat behind.

Changing a schema under live traffic

The rule: never make one change that both writes data and blocks readers. Split it into steps that are each safe to stop after. This is often called expand / migrate / contract.

Adding a NOT NULL column with a default, done wrong, rewrites the whole table while holding an exclusive lock. Done as a sequence:

1. Expand — add the column, nullable, no default. Fast in both engines.
2. Backfill — set values in batches, as above.
3. Constrain — add the NOT NULL and the default.
4. Contract — once no code reads it, drop the old column.

Between each step the application works, with old and new code both able to run. That is what makes the deploy independent of the migration.

Engine specifics:

CREATE INDEX CONCURRENTLY cannot run inside a transaction, and if it fails it leaves an invalid index behind that you must drop by hand. Check pg_index.indisvalid after any concurrent build that errored.

Querying JSON

-- PostgreSQL, jsonb
SELECT payload->>'status'                      AS status,   -- ->> gives text
       payload->'items'->0->>'sku'             AS first_sku
FROM event
WHERE payload @> '{"status":"paid"}';                        -- containment, GIN-indexable

CREATE INDEX ix_event_payload ON event USING gin (payload);

-- MySQL
SELECT payload->>'$.status'                    AS status,
       payload->>'$.items[0].sku'              AS first_sku
FROM event
WHERE payload->>'$.status' = 'paid';

-- Indexable only via a generated column
ALTER TABLE event
  ADD COLUMN status VARCHAR(32) AS (payload->>'$.status') STORED,
  ADD INDEX ix_event_status (status);

PostgreSQL’s @> with a GIN index searches inside the document. MySQL has no equivalent whole-document index, so every field you filter on needs its own generated column — which is a good prompt to ask whether it should have been a column all along.

The N+1 query

Not an SQL feature, but the most common performance bug in applications that use one. Fetching a list and then one query per row turns 1 request into 201.

-- What an ORM does by accident
SELECT * FROM "order" WHERE customer_id = 7;      -- 200 rows
SELECT * FROM order_item WHERE order_id = 1;      -- then 200 more queries
...

-- What it should do
SELECT * FROM order_item WHERE order_id = ANY(:ids);   -- PostgreSQL
SELECT * FROM order_item WHERE order_id IN (...);      -- MySQL

Every ORM has the fix — eager loading, JOIN FETCH, includes, select_related. The skill is noticing: log the query count per request, and alert on it. A count that scales with the size of a result set is the signature.

What to take away

• ON CONFLICT in PostgreSQL, ON DUPLICATE KEY UPDATE in MySQL. Avoid INSERT IGNORE — it hides unrelated errors.
• Keyset pagination with a row comparison stays flat; OFFSET does not.
• Batch large deletes and updates, one transaction each.
• Expand, backfill, constrain, contract — every step safe to stop after.
• CREATE INDEX CONCURRENTLY can leave an invalid index; check after a failure.
• JSON is queryable in both, but only PostgreSQL indexes the whole document.
• Watch the query count per request, not just the duration of each query.