A custom report that joins a primary object to secondary objects through an association table (deals -> contacts -> companies) multiplies rows: a deal linked to two contacts appears twice, so SUM(amount) or AVG over the joined rows double counts inside a single group, and totals drift from what users expect from CRM-style report builders.

Inserting a parent row (e.g. a queue) and a child row that references it in one statement through WITH ... INSERT CTEs fails when a trigger on the parent maintains a lookup that the child's validation trigger reads (here an AFTER trigger that appends the new id to an allowed-options JSON list). The child's BEFORE trigger reads the lookup table and rejects the value as unknown, although the parent was inserted in the same statement.

You need to load realistic demo data into a shared local Postgres app database (with triggers that write history, unique-value tables, denormalized counters) and later remove exactly that data in one command, without touching rows that existed before or that a user created during the demo. Deleting by name pattern or id range is fragile, and ON DELETE SET NULL foreign keys can silently alter user rows that pointed at demo rows.

To evaluate an expensive per-row expression once (e.g. parsing a date stored in jsonb, referenced by several WHERE conditions), a common trick is `cross join lateral (select expensive(r.x) as v1 offset 0) r_d`. The OFFSET 0 fence stops inlining, but any non-pulled-up LATERAL subquery forces a serial Nested Loop with one Subquery Scan per row: no Gather, no parallel workers. On 200k rows with three such expressions the query took 2.4-2.6 s serial.

A paginated list shows an exact total and one page. Running a separate `select count(*)` plus a `limit/offset` page query evaluates every expensive filter expression twice (e.g. a per-row function parsing a date stored as text in jsonb). On ~200k rows this doubled the time (1.2 s for the last page). `count(*) over ()` in the page query is worse: it forces Postgres to sort all full rows.

A query builder writes the same typed-value expression (an inlinable SQL function such as value_of(r.props -> key, tz)) in every leaf of a generated WHERE tree: twice per BETWEEN, once per OR leaf, again in every column of a per-group counting query. Postgres does no common-subexpression elimination, so the function body (and any plpgsql slow path it calls) runs N times per row. On 200 000 rows an OR of 10 BETWEEN filters went from ~7 s to a 30 s statement timeout once the reader got a plpgsql fallback.