SQL Hacks: Eight Tricks That Replace Infrastructure With Queries

Not "tips" — load-bearing patterns that delete dependencies. Postgres 16 syntax throughout; MySQL divergences noted. Every one of these fits in a single statement, which is the point: one round trip, one transaction, one thing to format and review. Ugly one-liners included on purpose — paste them into the formatter before you trust them.

1. A job queue in plain SQL: FOR UPDATE SKIP LOCKED

You do not need Redis to run a work queue if Postgres is already in the stack:

WITH job AS (
  SELECT id
  FROM jobs
  WHERE status = 'queued'
  ORDER BY priority DESC, created_at
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE jobs AS j
SET status = 'running', started_at = NOW()
FROM job
WHERE j.id = job.id
RETURNING j.*;

SKIP LOCKED makes concurrent workers skip rows another worker has locked instead of blocking or erroring — N workers drain the table in parallel with zero duplicate grabs, no advisory locks, no queue product, no operational story about it. NOWAIT is the variant when you'd rather error than skip. Two honest caveats: it's Postgres/Oracle-ism (MySQL 8 does NOWAIT/SKIP LOCKED on FOR UPDATE too, but without the CTE-update idiom — you'll need two statements), and high-churn queues need vacuum attention (fillfactor lower than 100 on the jobs table).

2. RETURNING everywhere

RETURNING isn't just id for a single insert. It works on multi-row inserts, UPDATE, and DELETE, and composes with CTEs:

WITH ins AS (
  INSERT INTO tags (name) VALUES ('sql'), ('postgres'), ('hacks')
  ON CONFLICT (name) DO NOTHING
  RETURNING id, name
)
INSERT INTO post_tags (post_id, tag_id)
SELECT 42, id FROM ins
RETURNING post_id, tag_id;

Read-modify-write round trips die here: insert-or-find, mutate-and-get-the-new-row, delete-and-log-what-was-deleted — all one statement. The boundary is real: MySQL 8 has no RETURNING at all (MariaDB does); port the pattern to LAST_INSERT_ID() arithmetic and accept you've lost the multi-row case.

3. ON CONFLICT DO NOTHING as idempotent replay

A webhook delivered twice is only a bug if your handler assumes once:

INSERT INTO webhook_events (delivery_id, payload, received_at)
VALUES ('evt_9f31', '{"type":"charge.succeeded"}', NOW())
ON CONFLICT (delivery_id) DO NOTHING
RETURNING 1;

Zero rows back means "already seen" — the handler returns 200 and moves on. Retries, at-least-once queues, and cron re-runs all become safe. DO UPDATE SET updated_at = NOW(), payload = EXCLUDED.payload is the upsert flavor; EXCLUDED is the would-be-inserted row and the only honest way to reference it. Requires a unique constraint to conflict on — no constraint, no idempotency, just a quieter duplicate.

4. Top-N queries without touching the app: pg_stat_statements

The slow-query list you never had, assuming the extension is loaded (shared_preload_libraries):

SELECT round(total_exec_time::numeric / 1000, 1) AS total_sec,
       calls,
       round(mean_exec_time::numeric, 1) AS avg_ms,
       left(query, 110)
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

total_exec_time finds your aggregate hog (usually a boring frequent query); mean_exec_time finds your single worst offender. The query column is normalized and parameterized, so it arrives as exactly the kind of one-line monster that needs the formatter before it's readable. pg_stat_statements_reset() clears counters after you fix one, so the next top-10 is honest.

5. UPDATE ... FROM — the join-update

UPDATE orders o
SET shipped_at = s.shipped_at
FROM shipments s
WHERE s.order_id = o.id
  AND o.shipped_at IS NULL;

One statement, set-based, no cursor, no loop. MySQL's spelling is UPDATE orders o JOIN shipments s ON s.order_id = o.id SET o.shipped_at = s.shipped_at — same idea, different grammar, and Postgres' FROM silently becomes a cross join if you forget the WHERE: test the predicate on a copy first.

6. Top-N per group: LATERAL

Window-function ranking works, but LATERAL can use an index per group and reads as intent:

SELECT c.name, t.*
FROM customers AS c
JOIN LATERAL (
  SELECT id, total, created_at
  FROM orders
  WHERE customer_id = c.id
  ORDER BY created_at DESC
  LIMIT 3
) t ON true;

Three most-recent orders per customer, and with an index on (customer_id, created_at DESC) each inner side is an index scan eating 3 rows. The window version sorts the entire orders table; at 10M orders you'll feel the difference in BUFFERS. JOIN LATERAL ≡ CROSS JOIN LATERAL; comma-join syntax works but is hard to review formatted.

7. Gap-free reports: generate_series

Empty buckets are a reporting bug. Generate the axis, then join facts onto it:

SELECT d.day, COALESCE(SUM(o.total), 0) AS revenue
FROM generate_series(DATE '2026-09-01', DATE '2026-10-01', INTERVAL '1 day') AS d(day)
LEFT JOIN orders o ON o.created_at::date = d(day)
GROUP BY d.day
ORDER BY d.day;

The dashboard chart that "skips Tuesdays" is this. Note LEFT JOIN — an inner join reintroduces the gaps. MySQL has no generate_series; the port is a recursive CTE that emits the same series, one line longer.

8. The deferred join (LIMIT-then-join pagination)

-- slow: joins 200,040 rows, then throws all but 20 away
SELECT o.* FROM orders o JOIN users u ON u.id = o.user_id
WHERE u.region = 'eu' ORDER BY o.id DESC LIMIT 20 OFFSET 200000;

-- fast: page the skinny index first, hydrate 20 rows
SELECT o.*
FROM (
  SELECT o.id
  FROM orders o JOIN users u ON u.id = o.user_id
  WHERE u.region = 'eu'
  ORDER BY o.id DESC
  LIMIT 20 OFFSET 200000
) AS page
JOIN orders o ON o.id = page.id
ORDER BY o.id DESC;

The inner query touches only index-sized columns, so OFFSET skips cheaply; the outer join hydrates the twenty survivors. The ORDER BY on the outside is mandatory — the join can scramble the inner order. For truly deep pages, stop using OFFSET: keyset pagination (WHERE o.id < $last_seen ORDER BY o.id DESC LIMIT 20) is O(index seek), and it's covered with its tiebreaker trap in patterns and gotchas.

FAQ

Are these portable? The pagination trick, UPDATE-with-join (different grammar), and gap-filling (via recursive CTE) port. SKIP LOCKED with CTEs, RETURNING, and ON CONFLICT are Postgres-flavored; MySQL 8 answers with ON DUPLICATE KEY UPDATE and no RETURNING at all. Write the Postgres version, check it against the dialect-flag table, then port deliberately.

Does formatting change what these do? No — but ON CONFLICT, FOR UPDATE, and the deferred join's outer ORDER BY are exactly the clauses that vanish in a minified one-liner during review. The formatter is part of the hack.

SKIP LOCKED vs a SELECT then UPDATE in app code? That's two round trips and a race you'll paper over with a retry loop. The single CTE statement locks, claims, and returns in one atom; the app-code version is the hack's bug report, not its alternative.