Skip to content
Sarj Code Standards

no-analytical-aggregation-in-postgres-store

Keep DISTINCT, GROUP BY, and SQL aggregates in analytical databases.

SARJ020 Rule source Tests Catalog JSON

Why

Project architecture reserves deduplication, grouping, and aggregate queries for ClickHouse or another analytical database, so reporting work does not compete with transactional reads. This is a datastore policy; SQL syntax alone cannot establish a query's execution cost.

Fix

Move reporting and aggregation to ClickHouse or another analytical database with an explicit freshness contract. Use an indexed EXISTS or direct row lookup for operational existence checks. Preserve atomicity for transactional invariants; use an exact, explained SARJ020 exception when a reviewed invariant must remain in the transactional database.

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 performs an operational existence lookup
app/account_store.py
import psycopg


QUERY = "SELECT EXISTS(SELECT 1 FROM ledger WHERE account_id = %s)"
Before — flagged A temporary PostgreSQL table still performs deduplication
app/event_store.py
import psycopg


QUERY = "CREATE TEMP TABLE report ON COMMIT DROP AS WITH rows AS (SELECT DISTINCT team_id FROM event) SELECT team_id FROM rows"
After — preferred A temporary PostgreSQL table can materialize an operational row lookup
app/event_store.py
import psycopg


QUERY = "CREATE TEMP TABLE report ON COMMIT DROP AS WITH rows AS (SELECT team_id FROM event WHERE id = %s) SELECT team_id FROM rows"

Formerly: no-aggregation-in-store-query, no-analytical-queries-in-transactional-db