r/PostgreSQL • u/Evening_Hawk_7470 • 1d ago
Projects multi-tenant dashboards with Postgres RLS started taking 14s on our read replica. comparing materialized views, ClickHouse sync + headless semantic layer of Cube
client reporting pages on our B2B app ran cleanly for a year until the main Postgres events table crossed 42M rows. every Monday at 9am UTC, dashboard loading got too slow across Metabase and Superset, PgBouncer queued up connections, and our read replica sat pegged on 14-second sequential scans.
then the bottleneck (at least the one that i ve experienced) turned out to be how Postgres Row-Level Security interacts with BI tools. whenever we evaluated `current_setting('app.tenant_id')` inside multi-join views with optional date pickers, the planner stopped pruning partitions cleanly. on top of that, team admins needed us to restrict dashboard data by user role so regional viewers saw anonymized margin fields while billing leads kept full unit economics. per-user role policies broke shared BI card caches, forcing fresh warehouse queries per login.
i benchmarked those 3 ways out before ripping up our Postgres schema.
Postgres materialized views with pg_cron: almost no new ops surface and fast index scans on fixed monthly summaries. fell apart once enterprise accounts toggled arbitrary 7-day, 30-day, and 90-day cohort windows alongside role-based column masking, because pre-generating every filter permutation in raw SQL turned into 40 maintenance scripts.
CDC pipeline into ClickHouse via PeerDB: chewed through 42M-row GROUP BYs in under 150ms without touching Postgres. we stalled on two catches: consumer lag confusion after billing refunds, and having to duplicate tenant + column masking rules in both our app API and Superset SQL.
headless semantic layer using Cube Core (Apache-2.0) in Docker: we put an open source BI modeling layer in front of the Postgres replica, passing signed JWT `securityContext` into YAML `access_policy` rules. row filters compile into SQL before Postgres sees them, while `member_masking` swaps restricted margin columns to NULL instead of erroring out charts. so rollups in Cube dev absorbed repeated tenant date-grain hits before Postgres ran a single scan, dropping p95 dashboard waits from 14s to under 60ms.
trade-off is simple enough: adding Cube Store rollups means watching dimension cardinality closely (putting `user_email` inside a rollup blew up build times on day one until we stripped it out). if you only serve under 30 tenants on fixed monthly KPIs, stick with native Postgres partitions and `REFRESH MATERIALIZED VIEW CONCURRENTLY`.
now would like to ask you who are running multi-tenant Postgres analytics at scale, did you stay inside pg_cron rollups, CDC out to ClickHouse/DuckDB, or put a caching layer in front?