complex-postgres-query-requires-architecture-review · Python

Why

Join-heavy, deeply staged, or wide joined reads can obscure cardinality, bounds, ordering, and locking semantics and can signal repeated read-time reconstruction. Syntax alone does not establish runtime cost, a bad data model, or the right datastore.

Fix

Review whether the schema exposes the operational fact directly, together with production-like cardinality and EXPLAIN output. Simplify the query or read model when warranted, but do not mechanically replace database joins with application joins. Keep atomic coordination in one statement; consider a columnar store only for measured repeated analytical reads with an explicit freshness contract.

Examples

Before — flagged A read repeatedly discovers a derived root through multiple joins

import psycopg

cursor.execute(
    "SELECT call.id FROM simulated_batch JOIN batch_call ON batch_call.batch_id = simulated_batch.batch_id JOIN call ON call.batch_call_id = batch_call.id JOIN campaign_call ON campaign_call.call_id = call.id JOIN campaign ON campaign.id = campaign_call.campaign_id WHERE simulated_batch.batch_id = %s"
)

After — preferred A read uses a root resolved and stored by its owning write workflow

import psycopg

cursor.execute("SELECT root_call_id FROM canary_run WHERE id = %s")

Before — flagged A queue claim hides ranking inside nested derived relations

import psycopg

cursor.execute(
    "SELECT due.id FROM call AS due JOIN (SELECT * FROM (SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rank FROM call) ranked WHERE rank <= %s) picked ON picked.id = due.id FOR UPDATE OF due SKIP LOCKED"
)

After — preferred A queue claim exposes ranking as named stages

import psycopg

cursor.execute(
    "WITH ranked AS (SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rank FROM call), picked AS (SELECT id FROM ranked WHERE rank <= %s) SELECT due.id FROM call AS due JOIN picked ON picked.id = due.id FOR UPDATE OF due SKIP LOCKED"
)