Your dashboards time out at 3 PM when every customer checks their numbers. The query takes 14 seconds. The index you added last week did nothing. You stare at the EXPLAIN ANALYZE output and spot a sequential scan across 47 million rows. Your team discusses migrating to ClickHouse. You do not need ClickHouse. You need to tune PostgreSQL.
PostgreSQL handles analytical workloads. The engine has supported window functions since 9.4, parallel query since 9.6, partitioning since 10, and covering indexes since 11. Every release ships query planner improvements. The database you use for transactions handles dashboards when you treat it with respect. You need indexes that match your query patterns, materialized views that pre-compute the heavy lifts, and a connection pooler that keeps 500 dashboard users from consuming 500 database connections.
The problem is not PostgreSQL. The problem is the schema you designed for an OLTP workload and the queries you wrote for a dashboard that refreshes every 30 seconds.
1. Why PostgreSQL for BI
Your application runs on PostgreSQL. Your team knows the schema, the operators, the backup procedures, and the migration tooling. Adding ClickHouse or InfluxDB means adding a second persistence layer: new operators, new monitoring, new deployment scripts, new security reviews, new failure modes. Your on-call rotation now covers two databases. Your CI pipeline must support two stateful services. Your data engineering team writes and maintains an ETL pipeline that syncs operational data into the analytics store. That pipeline breaks. It drifts. It adds latency between the event and the dashboard. It costs engineering hours every month.
PostgreSQL is already there. You already pay for the instance. You already run backups. Your application already writes to it. The operational data and the dashboard data live in the same place with zero sync latency. One source of truth. One set of operators. One backup strategy.
The counterargument cites row-oriented storage, the lack of columnar compression, and the overhead of MVCC under heavy concurrent reads. Those are real concerns. For dashboards that query fewer than 500 million rows, proper indexing solves them. For dashboards that aggregate pre-computed rollups, materialized views solve them. For dashboards that need sub-second page loads under concurrency, PgBouncer solves them. You reach for a columnar engine when the data volume crosses into the billions of rows and the query patterns demand full-table aggregation. Before that threshold, PostgreSQL delivers.
Cross-check your dashboard requirements against the resources in 10 SaaS Metrics Every Dashboard Needs. Your queries pull from a known set of patterns. Time-series rollups. Funnel analysis. Cohort retention. Top-N by dimension. Each pattern maps to a PostgreSQL optimization. You do not need a new database. You need the right indexes and the right materialization strategy.
2. Indexing for Dashboards
Your ORM created indexes on every foreign key column. Those indexes serve OLTP queries that look up one row by primary key or join two tables by a foreign key. Dashboard queries scan ranges of rows, aggregate them, and join across three or more tables. Your OLTP indexes sit unused while the planner chooses a sequential scan.
Partial Indexes
A partial index indexes the subset of rows your dashboard queries. You reduce index size and write overhead. The planner prefers a partial index when the WHERE clause matches.
CREATE INDEX CONCURRENTLY idx_events_active_tenant
ON events (tenant_id, created_at)
WHERE is_deleted = false;The index above excludes deleted rows. If your events table holds 100 million rows and 15 million are deleted, the index covers 85 million rows. The query SELECT count(*) FROM events WHERE tenant_id = 42 AND is_deleted = false uses the partial index and skips the filter step on the deleted flag. Smaller index. Faster scan. Less maintenance during writes.
Partial indexes shine for multi-tenant dashboards. Each tenant queries a small slice of a large table. A partial index on the active tenant filter combined with a BRIN index on the timestamp gives you range scans that touch few pages and skip irrelevant blocks.
Covering Indexes
A standard B-tree index stores the indexed column values and a pointer to the heap tuple. Queries that select columns not in the index perform a heap fetch for every matching row. A covering index adds those columns to the index leaf. The planner runs an index-only scan and never touches the heap.
CREATE INDEX CONCURRENTLY idx_orders_covering
ON orders (tenant_id, created_at DESC)
INCLUDE (revenue, status, plan_type);The query SELECT date_trunc('day', created_at) AS day, sum(revenue) FROM orders WHERE tenant_id = 5 GROUP BY 1 ORDER BY 1 now runs as an index-only scan. The planner reads 300 pages instead of 14,000. Execution time drops from 1,200 ms to 45 ms.
The tradeoff: covering indexes consume more disk and slow down writes. Add INCLUDE columns that your dashboard queries select from the table. Do not add columns you occasionally filter on; those belong in the key.
BRIN Indexes for Time-Series
A B-tree index on created_at for a table that ingests 2 million rows per day grows to 400 MB within a month. Inserts update random index pages. The index fragments. Maintenance windows stretch.
BRIN (Block Range INdex) stores one summary row per range of heap pages. For a table with a natural insertion order on created_at, a BRIN index occupies a few kilobytes and scans remain fast.
CREATE INDEX CONCURRENTLY idx_events_brin_time
ON events USING brin (created_at)
WITH (pages_per_range = 32);A BRIN index on a 500 GB events table occupies 64 KB. A B-tree on the same column occupies 12 GB. The BRIN index scans every block in the matching ranges and discards blocks outside the timestamp filter. The overhead per block is low because PostgreSQL stores rows in insertion order. Rows with adjacent timestamps sit in adjacent heap pages.
BRIN loses effectiveness when the table updates frequently or when you insert rows with timestamps that scatter across the key space. For append-only event tables, BRIN is the right choice.
Measure the difference. Before BRIN:
Seq Scan on events (cost=0.00..154830.00 rows=5000 width=120)
(actual time=0.045..2341.892 rows=4823 loops=1)
Filter: (created_at >= '2025-12-01'
AND created_at < '2026-01-01')
Rows Removed by Filter: 4955177
Planning Time: 0.152 ms
Execution Time: 2342.107 msAfter BRIN:
Bitmap Heap Scan on events (cost=52.00..8400.00 rows=5000 width=120)
(actual time=0.523..42.891 rows=4823 loops=1)
Recheck Cond: (created_at >= '2025-12-01'
AND created_at < '2026-01-01')
Heap Blocks: exact=1340
-> Bitmap Index Scan on idx_events_brin_time
(cost=0.00..50.75 rows=5000 width=0)
(actual time=0.312..0.313 rows=4823 loops=1)
Index Cond: (created_at >= '2025-12-01'
AND created_at < '2026-01-01')
Planning Time: 0.187 ms
Execution Time: 43.210 ms2342 ms to 43 ms. The index change required one CREATE INDEX CONCURRENTLY statement. Your dashboard users notice the difference.
3. Materialized Views
A materialized view stores the result of a query on disk. The dashboard queries the snapshot instead of recomputing the aggregation. For daily revenue rollups, funnel summaries, and cohort tables, materialized views turn 8-second aggregation queries into 30-millisecond lookups.
CREATE MATERIALIZED VIEW mv_daily_metrics AS
SELECT
tenant_id,
date_trunc('day', created_at) AS day,
event_type,
count(*) AS event_count,
count(DISTINCT user_id) AS unique_users
FROM events
WHERE is_deleted = false
GROUP BY tenant_id, date_trunc('day', created_at), event_type;
CREATE UNIQUE INDEX CONCURRENTLY idx_mv_daily_metrics
ON mv_daily_metrics (tenant_id, day, event_type);The dashboard queries the view:
SELECT day, event_type, sum(event_count) AS total
FROM mv_daily_metrics
WHERE tenant_id = 42
AND day >= '2026-01-01'
GROUP BY day, event_type
ORDER BY day DESC;The raw events table query scans and aggregates 8 million rows and takes 2.7 seconds. The materialized view query reads 365 pre-aggregated rows and returns in 9 ms.
REFRESH CONCURRENTLY
A standard REFRESH blocks readers. The view disappears, the query recomputes, the view reappears. During the refresh window (30 seconds to 2 minutes, depending on data volume), dashboard queries hang or error.
REFRESH CONCURRENTLY builds a new snapshot in the background while the old snapshot continues serving reads. It requires a unique index on the materialized view. Readers see consistent data and never wait.
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_metrics;Schedule refreshes with pg_cron:
SELECT cron.schedule(
'refresh-daily-metrics',
'*/5 * * * *',
'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_metrics'
);A 5-minute refresh cadence means dashboards show data up to 5 minutes stale. For daily revenue summaries and weekly cohort tables, that freshness tradeoff costs nothing. For real-time operational dashboards, combine materialized views for historical data with direct queries for the current hour. The dashboard unions the two result sets and no user sees a delay.
The Storage Cost
Each materialized view duplicates data. A daily rollup on a table with 200 million events might occupy 200 MB on disk. Ten materialized views add 2 GB. Monitor pg_total_relation_size() and prune views that dashboards no longer use. The storage cost is low compared to the CPU and I/O savings from eliminating repeated aggregation queries.
SELECT
schemaname || '.' || matviewname AS matview,
pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'm'
ORDER BY pg_total_relation_size(c.oid) DESC;4. EXPLAIN ANALYZE
You read an EXPLAIN ANALYZE plan from the innermost node outward. Each node shows estimated cost (the planner’s guess), actual time (the measured milliseconds), actual rows (what the node produced), and loops (how many times the executor ran that node). The gap between estimate and actual tells you where the planner went wrong. The product of actual rows × loops tells you where the work went.
You look for three things: sequential scans on large tables, nested loop joins where the inner relation has no index, and estimates that miss actual row counts by an order of magnitude.
Spot the Sequential Scan
A Seq Scan on a table with more than a few hundred rows is usually a missing index. The planner chose no index because none existed or because the existing index did not cover the filter columns in the right order.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT tenant_id,
date_trunc('day', created_at) AS day,
count(*)
FROM events
WHERE created_at >= now() - interval '90 days'
AND event_type = 'purchase'
GROUP BY tenant_id, 2;GroupAggregate (cost=28934.11..29234.11 rows=10000 width=24)
(actual time=3891.234..3912.445 rows=1247 loops=1)
Group Key: tenant_id, (date_trunc('day'::text, created_at))
Buffers: shared hit=434 read=127632
-> Sort (cost=28934.11..29034.11 rows=20000 width=16)
(actual time=3891.221..3898.112 rows=89234 loops=1)
Sort Key: tenant_id, (date_trunc('day'::text, created_at))
Sort Method: external merge Disk: 8192kB
-> Seq Scan on events (cost=0.00..28312.00 rows=20000 width=16)
(actual time=0.123..3112.445 rows=89234 loops=1)
Filter: ((created_at >= (now() - '90 days'::interval))
AND (event_type = 'purchase'::text))
Rows Removed by Filter: 3910766
Buffers: shared hit=434 read=127632
Planning Time: 0.432 ms
Execution Time: 3924.891 msThe Seq Scan reads 127,632 buffers and discards 3.9 million rows. The sort spills to disk. The query runs for 3.9 seconds. Every dashboard refresh hits this cost.
Fix it with a composite index that matches the filter and the group:
CREATE INDEX CONCURRENTLY idx_events_type_time
ON events (event_type, created_at);After the index:
GroupAggregate (cost=0.44..15432.22 rows=10000 width=24)
(actual time=0.234..42.112 rows=1247 loops=1)
Group Key: tenant_id, (date_trunc('day'::text, created_at))
Buffers: shared hit=4312 read=1021
-> Index Scan using idx_events_type_time on events
(cost=0.44..14832.22 rows=20000 width=16)
(actual time=0.045..23.889 rows=89234 loops=1)
Index Cond: (event_type = 'purchase'::text)
Filter: (created_at >= (now() - '90 days'::interval))
Buffers: shared hit=4312 read=1021
Planning Time: 0.223 ms
Execution Time: 42.345 ms3924 ms to 42 ms. Buffers dropped from 128,000 to 5,333. The sort fits in memory. The index solved it.
Fix the Nested Loop
A nested loop join with a high row count on the outer side amplifies the inner scan. If the inner relation has no index on the join column, the planner runs a sequential scan for every outer row.
Nested Loop (cost=0.00..1567234.00 rows=10000 width=120)
(actual time=0.823..12445.231 rows=8923 loops=1)
-> Seq Scan on orders (cost=0.00..8234.00 rows=10000 width=64)
(actual time=0.045..234.112 rows=8923 loops=1)
-> Index Scan using events_pkey on events
(cost=0.56..15.78 rows=10 width=56)
(actual time=0.034..1.367 rows=1 loops=8923)The outer scan produces 8,923 rows. For each row, PostgreSQL probes events_pkey with an index scan. The inner scan costs 1.3 ms per row. 8,923 × 1.3 ms = 11.6 seconds. The fix is a hash or merge join. Check your work_mem setting. If the planner chose a nested loop while a hash join fits in memory, raise work_mem for the session:
SET work_mem = '256MB';If the nested loop persists because of a missing join column index, add it:
CREATE INDEX CONCURRENTLY idx_events_order_id
ON events (order_id);The planner switches to a hash join. Execution drops from 12.4 seconds to 210 ms.
5. Connection Pooling
A dashboard user loads a page and triggers five queries. Each query takes 200 ms. The database connection stays open during query execution and for the duration of the user’s session. If 200 users load dashboards at the same time, PostgreSQL spawns 200 backends. Each backend consumes around 5 MB of work_mem plus overhead. At 500 concurrent users, PostgreSQL runs out of memory or hits max_connections and rejects new connections.
PgBouncer sits between your application and PostgreSQL. It holds a pool of persistent database connections and multiplexes client connections onto them. When a client connection finishes a transaction, PgBouncer returns the server connection to the pool. The next client reuses it.
Transaction Pooling vs Session Pooling
Session pooling assigns one server connection to each client for the client’s lifetime. The client holds the connection even when idle between queries. Transaction pooling assigns a server connection only for the duration of a transaction. Between transactions, the client holds no server connection. Transaction pooling supports far more concurrent clients than you have database connections because clients spend most of their time between queries, not inside them.
For embedded analytics, use transaction pooling. Your dashboard queries run as single transactions. The client waits in application code between queries. During that wait, PgBouncer reuses the server connection for another client’s query.
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 25
max_client_conn = 1000
max_db_connections = 30
server_idle_timeout = 600
client_idle_timeout = 0With pool_mode = transaction and default_pool_size = 25, 1,000 dashboard users share 25 database connections. PostgreSQL runs 25 backends instead of 1,000. Memory usage stays predictable. Query latency stays flat under load.
Connection Churn and Prepared Statements
Dashboard tools like Metabase and Grafana use prepared statements. PgBouncer supports prepared statements in transaction pooling mode, but it deallocates them between transactions. Set server_reset_query = DISCARD ALL or, for pgbouncer 1.21+, use server_reset_query_always = 1 to reset session state between uses. Prepared statements add a round trip per new plan. If your queries rely on generic plans, enable plan_cache_mode = force_custom_plan for the database user that runs dashboard queries.
ALTER ROLE metabase_readonly SET plan_cache_mode = 'force_custom_plan';Measure connection wait times during peak load:
SELECT count(*) AS waiting
FROM pg_stat_activity
WHERE wait_event_type = 'Client' AND wait_event = 'ClientRead';If waiting exceeds your pool size, add PgBouncer instances or increase default_pool_size. Never raise max_connections on PostgreSQL as a substitute for pooling. More backends mean more context switching, more lock contention, and higher memory pressure. One PgBouncer instance between your application and your database handles more concurrency than a mountain of PostgreSQL backends.
6. Partitioning
An events table grows by 2 million rows per day. After six months, it holds 365 million rows. Even with a BRIN index, queries that scan one month of data touch every block in the table because the planner cannot skip blocks outside the month range at the storage level. BRIN skips blocks after reading the range map, which is fast but still reads every range summary. Partitioning makes the planner skip entire tables.
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY,
tenant_id integer NOT NULL,
event_type text NOT NULL,
payload jsonb,
created_at timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2025_12
PARTITION OF events
FOR VALUES FROM ('2025-12-01') TO ('2026-01-01');
CREATE TABLE events_2026_01
PARTITION OF events
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02
PARTITION OF events
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE INDEX ON events_2025_12 (tenant_id, created_at);
CREATE INDEX ON events_2026_01 (tenant_id, created_at);
CREATE INDEX ON events_2026_02 (tenant_id, created_at);A query on a date range that falls within one month now scans only that partition:
EXPLAIN SELECT count(*) FROM events
WHERE created_at >= '2026-01-15'
AND created_at < '2026-01-16';Aggregate (cost=342.00..342.01 rows=1 width=8)
-> Seq Scan on events_2026_01 events
(cost=0.00..340.00 rows=800 width=0)
Filter: ((created_at >= '2026-01-15'::date)
AND (created_at < '2026-01-16'::date))The planner appends only events_2026_01 to the scan. It ignores events_2025_12 and events_2026_02. The query reads 340 pages instead of the 120,000 pages a full table scan would touch. Partition pruning removes irrelevant partitions at plan time.
Automate partition creation with pg_partman:
SELECT partman.create_parent(
p_parent_table := 'public.events',
p_control := 'created_at',
p_type := 'native',
p_interval := '1 month',
p_premake := 3
);pg_partman creates partitions for the next three months and drops partitions older than your retention window. Your dashboard queries never scan irrelevant data and your maintenance scripts run unattended.
Partitioning also trims index size. Each partition carries its own indexes. A partition older than your dashboard’s time window carries indexes that the query planner never touches. You can drop old partitions with DROP TABLE events_2024_06 instead of running DELETE FROM events WHERE created_at < '2024-07-01', which generates 60 GB of WAL and locks rows for the duration.
7. Dashboard Query Patterns and Their Optimizations
Time-Series Aggregation
The most common dashboard query. You group events by hour, day, or week and count or sum a metric.
SELECT
date_trunc('day', created_at) AS day,
count(*) AS events,
count(DISTINCT user_id) AS users
FROM events
WHERE tenant_id = 42
AND created_at >= now() - interval '30 days'
GROUP BY 1
ORDER BY 1;Optimization: a covering index on (tenant_id, created_at) with INCLUDE (event_type) plus a materialized view for lookback windows longer than 7 days.
CREATE INDEX CONCURRENTLY idx_events_ts_cover
ON events (tenant_id, created_at)
INCLUDE (user_id, event_type);For the long tail, pre-compute daily rollups into a materialized view. The dashboard queries the materialized view for days 8 through 30 and queries the raw table for the current day. A UNION ALL merges the two:
SELECT day, events, users FROM (
SELECT date_trunc('day', created_at) AS day,
count(*) AS events,
count(DISTINCT user_id) AS users
FROM events
WHERE tenant_id = 42
AND created_at >= current_date
GROUP BY 1
UNION ALL
SELECT day, event_count AS events, unique_users AS users
FROM mv_daily_metrics
WHERE tenant_id = 42
AND day >= current_date - interval '30 days'
AND day < current_date
) AS combined
ORDER BY day DESC;Funnel Queries
A signup funnel tracks users through a sequence of events: page view, signup start, signup complete, payment. You want the count of users who reached each step and the conversion rates between them.
WITH funnel AS (
SELECT
session_id,
bool_or(event_type = 'page_view') AS viewed,
bool_or(event_type = 'signup_start') AS started,
bool_or(event_type = 'signup_complete') AS completed,
bool_or(event_type = 'payment') AS paid
FROM events
WHERE tenant_id = 42
AND created_at >= now() - interval '7 days'
GROUP BY session_id
)
SELECT
count(*) FILTER (WHERE viewed) AS viewed,
count(*) FILTER (WHERE started) AS started,
count(*) FILTER (WHERE completed) AS completed,
count(*) FILTER (WHERE paid) AS paid,
round(
count(*) FILTER (WHERE completed)::numeric
/ nullif(count(*) FILTER (WHERE viewed), 0) * 100, 1
) AS signup_rate,
round(
count(*) FILTER (WHERE paid)::numeric
/ nullif(count(*) FILTER (WHERE completed), 0) * 100, 1
) AS payment_rate
FROM funnel;Optimization: a materialized view that pre-computes funnel steps by day. The dashboard pulls one row per day instead of scanning 7 days of raw events. If your funnel spans 30-minute attribution windows, pre-aggregate by session on write using an INSERT trigger that updates a funnel progress table.
CREATE TABLE funnel_progress (
session_id uuid PRIMARY KEY,
tenant_id integer NOT NULL,
viewed_at timestamptz,
started_at timestamptz,
completed_at timestamptz,
paid_at timestamptz
);
CREATE OR REPLACE FUNCTION update_funnel_progress()
RETURNS trigger AS $$
BEGIN
INSERT INTO funnel_progress (session_id, tenant_id, viewed_at)
VALUES (NEW.session_id, NEW.tenant_id, NEW.created_at)
ON CONFLICT (session_id) DO UPDATE SET
started_at = CASE WHEN NEW.event_type = 'signup_start'
THEN coalesce(funnel_progress.started_at, NEW.created_at)
ELSE funnel_progress.started_at END,
completed_at = CASE WHEN NEW.event_type = 'signup_complete'
THEN coalesce(funnel_progress.completed_at, NEW.created_at)
ELSE funnel_progress.completed_at END,
paid_at = CASE WHEN NEW.event_type = 'payment'
THEN coalesce(funnel_progress.paid_at, NEW.created_at)
ELSE funnel_progress.paid_at END;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;Cohort Retention
Cohort retention groups users by their signup week and counts how many return in subsequent weeks. The query self-joins on user_id across time windows.
WITH cohorts AS (
SELECT
user_id,
date_trunc('week', min(created_at)) AS cohort_week
FROM events
WHERE event_type = 'signup'
GROUP BY user_id
),
activity AS (
SELECT DISTINCT
user_id,
date_trunc('week', created_at) AS activity_week
FROM events
WHERE event_type = 'login'
)
SELECT
c.cohort_week,
count(DISTINCT c.user_id) AS signups,
round(
count(DISTINCT a.user_id) FILTER (
WHERE a.activity_week = c.cohort_week + interval '1 week'
)::numeric / count(DISTINCT c.user_id) * 100, 1
) AS week_1_pct,
round(
count(DISTINCT a.user_id) FILTER (
WHERE a.activity_week = c.cohort_week + interval '4 weeks'
)::numeric / count(DISTINCT c.user_id) * 100, 1
) AS week_4_pct
FROM cohorts c
LEFT JOIN activity a ON c.user_id = a.user_id
AND a.activity_week >= c.cohort_week
AND a.activity_week <= c.cohort_week + interval '12 weeks'
GROUP BY c.cohort_week
ORDER BY c.cohort_week DESC;Optimization: a materialized view that rebuilds weekly. Cohort analysis changes only when new weeks complete. Schedule a weekly REFRESH CONCURRENTLY and serve the dashboard from the snapshot.
CREATE MATERIALIZED VIEW mv_cohort_retention AS
-- cohort query above
WITH DATA;
CREATE UNIQUE INDEX ON mv_cohort_retention (cohort_week);The dashboard query becomes SELECT * FROM mv_cohort_retention ORDER BY cohort_week DESC. Execution drops from 18 seconds to 3 ms.
8. When PostgreSQL Is Not Enough
PostgreSQL serves dashboards up to a point. You hit that point when three signals coincide:
The data volume exceeds 500 million rows per table and your queries aggregate across the full dataset. Partitioning and BRIN indexes help up to a billion rows. Beyond that, the planner overhead and the I/O cost of scanning hundreds of partitions dominate. A columnar engine compresses data by 10× to 50× and scans only the columns your query references. ClickHouse excels here. So does TimescaleDB, which runs inside PostgreSQL and adds columnar compression and automatic partitioning.
Your dashboards refresh faster than your materialized views. If users expect sub-second refresh on live data and your materialized view refresh takes 45 seconds, the freshness gap frustrates users. InfluxDB or TimescaleDB handles continuous aggregation where the database updates rollup tables on ingest. Every write updates a pre-computed bucket. Reads touch one bucket row.
You run analytical queries during peak OLTP traffic and they compete for I/O. You can mitigate this with read replicas, but at some scale the cost of replicating and storing the full operational dataset for analytics exceeds the cost of a separate columnar store. A data warehouse (BigQuery, Redshift, Snowflake) solves this with a dedicated compute layer that never touches your operational database.
If you recognize your dashboards in the patterns above, start with PostgreSQL and the techniques in this article. You get months of serviceable dashboard performance from the database your team runs. Many teams get years. When the query planner explanations fill your terminal and the materialized view refreshes overlap with the next scheduled refresh, look at the dedicated analytics engines.
For real-time pipeline architectures that feed dashboards with sub-second latency, read Real-Time Data Pipelines. For choosing the BI layer that sits on top of your tuned PostgreSQL instance, see Metabase vs Grafana vs Superset.
The database you have beats the database you do not. Tune it first. Migrate only when the numbers prove you must.