Skip to content
Sarj Sarj Sarj Standards

no-analytical-aggregation-in-postgres-store

Potentially analytical PostgreSQL store queries require review.

SARJ020 Rule source Tests Catalog JSON

Why

Unbounded reporting and statistical scans can compete with transactional reads. Transactional invariants, queue coordination, bounded hydration, and other strongly consistent operational aggregates remain valid PostgreSQL work.

Fix

Review the query bounds and execution plan. Prefer ClickHouse for reporting, historical analysis, and broad aggregations, with measured performance and an explicit freshness contract. For PostgreSQL, document why the aggregate must remain transactional, with EXPLAIN evidence. Do not add reporting-specific indexes without evaluating ClickHouse.

Examples

Before — flagged PostgreSQL computes a time-series reporting rollup
app/event_store.py
import psycopg


QUERY = "SELECT DATE_TRUNC('day', occurred_at), COUNT(*), AVG(latency_ms) FROM event GROUP BY 1"
After — preferred PostgreSQL preserves a bounded transactional invariant
app/account_store.py
import psycopg


QUERY = "SELECT SUM(amount) FROM ledger WHERE account_id = %s FOR UPDATE"

Formerly: no-aggregation-in-store-query