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

Prefer ClickHouse for reporting, historical analysis, and broad aggregations, with measured performance and an explicit freshness contract. For transactional work, simplify the query or document why PostgreSQL is required, with query bounds and production-like EXPLAIN evidence. Review whether the schema exposes the operational fact directly; preserve atomic coordination and do not mechanically replace database joins with application joins. Do not add reporting-specific indexes without evaluating ClickHouse.

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"
)