Defect Scenarios Basics

7 examples to get you started with modeling defect scenarios - 5 basic and 2 intermediate.

Prerequisites

• PostgreSQL 18.4 lab database.
• Read access to production-like query patterns (EXPLAIN, pg_stat_statements).

Basic Examples

1. Fan-Out Inflates Aggregates

A many-to-many join counted as one-to-many doubles revenue in dashboards.

-- Bad: tags join multiplies order rows
SELECT o.order_id, SUM(oi.amount) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN order_tags ot ON ot.order_id = o.order_id
GROUP BY o.order_id;
-- Each line item repeated per tag

• Modeling defect: query assumes 1:1 between orders and tags.
• Symptom: finance totals exceed payment processor by consistent factor.
• Fix: aggregate line items in subquery before joining tags.

Related: Fan-Out Join Explosion - cardinality debugging

2. Orphan Rows Without Foreign Keys

Application deletes parents; children remain forever.

CREATE TABLE comments (
  comment_id  bigint PRIMARY KEY,
  post_id     bigint NOT NULL  -- no REFERENCES clause
);
 
DELETE FROM posts WHERE post_id = 99;
-- comments with post_id = 99 still exist

• Missing FK allows orphan rows when app bugs or admin SQL skip cleanup.
• Symptom: UI shows comments on deleted posts or broken links.
• Fix: add FK with appropriate ON DELETE action.

Related: Missing Foreign Keys - integrity enforcement

3. Enum Value Blocked Under Load

Adding enum value during traffic holds ACCESS EXCLUSIVE lock briefly.

CREATE TYPE order_status AS ENUM ('draft', 'placed');
 
-- Later migration under load:
ALTER TYPE order_status ADD VALUE 'shipped';
-- Brief lock; apps queue on hot path

• Enum migrations are DDL events - painful on high-QPS status columns.
• Symptom: p99 latency spike during deploy; rollbacks complicated.
• Fix: text + CHECK or expand-contract enum strategy.

Related: Enum Migration Pain - safe enum changes

4. Timestamp Without Time Zone Drift

Stored local wall time breaks when DST shifts or users travel.

CREATE TABLE meetings (
  meeting_id    bigint PRIMARY KEY,
  starts_at     timestamp WITHOUT TIME ZONE  -- ambiguous instant
);
 
INSERT INTO meetings VALUES (1, '2026-03-08 02:30:00');
-- Which timezone? DST gap? Unknown.

• timestamp without time zone stores no offset - global apps mis-schedule meetings.
• Symptom: calendar off by one hour twice yearly; support tickets cluster around DST.
• Fix: timestamptz stored in UTC.

Related: Timestamp Without Timezone Bugs - DST incidents

5. Detect Orphans with LEFT JOIN

Standard data-quality query for missing FK enforcement.

SELECT c.comment_id, c.post_id
FROM comments c
LEFT JOIN posts p ON p.post_id = c.post_id
WHERE p.post_id IS NULL;

• Non-empty result means referential integrity broken.
• Run after migrations that drop FKs "temporarily."
• Add to nightly data quality job.

Intermediate Examples

6. EXPLAIN Reveals Cartesian Explosion

Row estimate jumps after adding an innocent join.

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN order_promotions op ON op.order_id = o.order_id;

• Compare actual row count to naive orders * items * promotions expectation.
• Nested loop on inflated intermediate set signals modeling or join order bug.
• Fix schema (junction tables) or rewrite query with subqueries.

Related: Fan-Out Join Explosion - rewrite patterns

7. Post-Mortem to Lint Rule

Turn incident into automated prevention.

-- Lint rule pseudo-check: tables ending in _id must have FK metadata
SELECT c.conrelid::regclass AS table_name, c.conname
FROM pg_constraint c
WHERE c.contype = 'f';

• Document defect class in runbook: "missing FK on *_id columns."
• CI parses migration SQL for REFERENCES on new FK-shaped columns.
• Schema review checklist references defect scenario pages.

Related: Defect Scenarios Best Practices - lint integration

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+. 3730363230327c313230316f6967637c6f692e73656469756765646f63