PostgreSQL Performance for SaaS — 8 Query Optimizations From a Real Slow Dashboard

The dashboard took eight seconds to load. The client was, reasonably, unhappy. We did not shard the database or add a caching layer or rewrite the application. We added one index, fixed an N+1 the ORM was hiding, and replaced a correlated subquery with a JOIN. Eight seconds became 340 milliseconds. Most PostgreSQL performance problems on a SaaS dashboard are query problems wearing a scaling costume.

This post is the eight PostgreSQL performance optimizations we applied to turn that dashboard around — and the ones you should check before reaching for a read replica or a cache. They are ordered by impact: start at the top and stop when your dashboard is fast enough.

PostgreSQL Performance: Use EXPLAIN ANALYZE to Find Bottlenecks

Before changing anything, you need to know what the database is actually doing. PostgreSQL's EXPLAIN ANALYZE executes the query and shows the real execution plan with actual timings and row counts. Run it on your dashboard's slowest query:

1EXPLAIN (ANALYZE, BUFFERS)
2SELECT o.id, o.total, o.status, u.name
3FROM orders o
4JOIN users u ON u.id = o.user_id
5WHERE o.tenant_id = 'abc123'
6  AND o.status = 'pending'
7  AND o.created_at > NOW() - INTERVAL '30 days'
8ORDER BY o.created_at DESC
9LIMIT 50;

The output tells you three things. First, whether the planner's row estimates match reality — a huge gap means stale statistics or a missing index. Second, whether the query uses a sequential scan when it should use an index scan. Third, which node in the plan tree consumes the most time.

We found our dashboard's bottleneck in the first EXPLAIN ANALYZE run. (The PostgreSQL documentation on using EXPLAIN is thorough, but the short version is: look for sequential scans on large tables, bad row estimates, and sort nodes that spill to disk.)

Optimization 1: The Wrong Column Was Indexed

The dashboard filtered orders by tenant_id and status, then sorted by created_at. The table had an index on tenant_id alone. That sounds like it should help — and it did, marginally. PostgreSQL used the index to find the tenant's rows, then scanned all of them to filter by status and sort by date.

The fix was a composite index matching the query's exact filter-and-sort pattern:

1CREATE INDEX idx_orders_tenant_status_date
2ON orders (tenant_id, status, created_at DESC);

The column order matters. Leftmost columns should match the equality filters first (tenant_id, then status), then the sort column (created_at DESC). With this index, PostgreSQL walks the B-tree directly to the matching rows in the correct order — no scan, no separate sort.

Query time on this one change: ~2 seconds to ~8 milliseconds.

Optimization 2: The N+1 Query in TypeORM

This one was hiding in plain sight. The dashboard endpoint used TypeORM's find with lazy relation loading:

1// Before: issues N+1 queries
2const orders = await this.ordersRepository.find({
3  where: { tenantId, status: 'pending' },
4  relations: ['user'], // This loads users in a separate query per order
5});

When the dashboard displayed 50 orders, TypeORM executed 1 query for the orders and 50 queries for the user of each order — 51 queries total. Each individual query was fast. Fifty-one of them, serially, were not.

The fix uses a single JOIN via the QueryBuilder:

1// After: one query with JOIN
2const orders = await this.ordersRepository
3  .createQueryBuilder('order')
4  .innerJoinAndSelect('order.user', 'user')
5  .where('order.tenantId = :tenantId', { tenantId })
6  .andWhere('order.status = :status', { status: 'pending' })
7  .orderBy('order.createdAt', 'DESC')
8  .limit(50)
9  .getMany();

One query. Same data. The N+1 is the single most common performance bug in ORM-heavy NestJS applications, and it hides behind the relations option that looks innocent in the code review. We wrote about this pattern more in our multi-tenant database architecture post — the same tenant-isolation concerns apply, but the performance cost compounds with N+1.

Optimization 3: Replacing a Correlated Subquery With a JOIN

The dashboard showed a "customer lifetime value" column — the total of all previous orders for that customer. The original query used a correlated subquery:

1SELECT o.id, o.total, o.created_at,
2  (SELECT SUM(total) FROM orders
3   WHERE user_id = o.user_id
4     AND created_at < o.created_at) as lifetime_value
5FROM orders o
6WHERE o.tenant_id = 'abc123';

PostgreSQL executes the correlated subquery once per row of the outer query. For a dashboard with 50 orders, that is 50 additional executions. The fix is a lateral JOIN or a window function:

1SELECT o.id, o.total, o.created_at,
2  COALESCE(SUM(o2.total) FILTER (WHERE o2.created_at < o.created_at), 0) as lifetime_value
3FROM orders o
4LEFT JOIN orders o2 ON o2.user_id = o.user_id
5WHERE o.tenant_id = 'abc123'
6GROUP BY o.id, o.total, o.created_at;

In our case, a window function actually worked better:

1SELECT id, total, created_at,
2  SUM(total) OVER (PARTITION BY user_id ORDER BY created_at
3                   ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) as lifetime_value
4FROM orders
5WHERE tenant_id = 'abc123';

One pass over the data instead of N+1 passes. The correlated subquery is the kind of pattern that looks elegant in a textbook and punishes you at dashboard scale.

Optimization 4: Partial Indexes for Filtered Queries

Most SaaS dashboards query a subset of data: active subscriptions, pending orders, failed payments. A full index on the entire table wastes space and slows writes. A partial index indexes only the rows that match a filter:

1CREATE INDEX idx_orders_pending_recent
2ON orders (tenant_id, created_at DESC)
3WHERE status = 'pending';

This index is significantly smaller than a full index on (tenant_id, status, created_at) because it excludes all non-pending rows. Smaller index means faster index scans, less write overhead, and less cache pressure. We applied this to the three most common dashboard filters — pending, active, and failed — and the index sizes dropped by roughly 60% compared to full indexes.

Optimization 5: Materialized Views for Reporting Queries

Some dashboard panels aggregate data across thousands of rows: total revenue by month, active user counts, churn rates. These queries ran on every page load, scanning the same millions of rows to produce the same summaries.

Materialized views store the query result as a physical table, refreshed on a schedule:

1CREATE MATERIALIZED VIEW mv_dashboard_monthly_revenue AS
2SELECT
3  tenant_id,
4  DATE_TRUNC('month', created_at) as month,
5  COUNT(*) as order_count,
6  SUM(total) as total_revenue,
7  SUM(total) FILTER (WHERE status = 'completed') as completed_revenue
8FROM orders
9GROUP BY tenant_id, DATE_TRUNC('month', created_at)
10ORDER BY tenant_id, month DESC;
11
12CREATE UNIQUE INDEX idx_mv_monthly_rev_tenant_month
13ON mv_dashboard_monthly_revenue (tenant_id, month);

The dashboard queries the materialized view instead of the source table. The data is slightly stale (refreshed every 15 minutes via a scheduled job), but the reporting panel specifically said "no more than 30 minutes stale" in the requirements. The PostgreSQL documentation on materialized views explains the concurrent refresh option, which we use to avoid locking reads during refresh:

1REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dashboard_monthly_revenue;

Query time on the reporting panel: ~4 seconds scanning orders to ~12 milliseconds on the materialized view. The tradeoff — accepting staleness for speed — was an easy call because the panel already showed "data as of" timestamps.

Optimization 6: Connection Pooling With PgBouncer

This one was not a query problem. NestJS, like most Node.js frameworks, opens many short-lived database connections under load. Each new connection requires a TCP handshake, SSL negotiation, and a backend process fork on PostgreSQL. Under moderate traffic, we hit the max_connections limit and queued requests backed up.

PgBouncer sits between the application and the database, maintaining a small pool of persistent connections:

1[pgbouncer]
2pool_mode = transaction
3default_pool_size = 25
4max_client_conn = 200
5listen_addr = 127.0.0.1
6listen_port = 6432

Transaction-pooling mode is the right choice for NestJS: a connection is returned to the pool after each transaction completes, so 25 database connections serve hundreds of application connections. The connection timeout that was causing occasional 5-second dashboard loads disappeared.

The configuration guide at pgbouncer.org covers the settings thoroughly, but the most important decision is pool_mode. Session pooling is the default and wastes connections in Node.js apps. Transaction pooling is what you want.

Optimization 7: Avoiding SELECT * in Large Table Queries

The dashboard's data-access layer had a utility function that queried SELECT * FROM orders WHERE ... and then mapped to a DTO. This seems harmless — until the orders table has 25 columns and the dashboard displays 6.

SELECT * forces PostgreSQL to read every column from disk, transmit every column to the application, and prevents index-only scans when the index contains all the columns the query actually needs. A covering index with INCLUDE lets PostgreSQL satisfy the query entirely from the index:

1CREATE INDEX idx_orders_dashboard
2ON orders (tenant_id, status, created_at DESC)
3INCLUDE (id, total, user_id);

With this index and a query that selects only id, total, user_id, PostgreSQL performs an index-only scan — it never touches the table heap. Query time dropped from 45ms to 3ms just from eliminating heap lookups.

(The rule: specify the columns your frontend actually renders. SELECT * is a code smell that costs real money at database scale. For more on this principle applied to schema changes, see our database migration strategy post — the same discipline of intentional column selection applies to both queries and migrations.)

Optimization 8: Batching Writes Instead of One-by-One Inserts

The dashboard had a background sync that imported data from an external API. It inserted rows one at a time: 500 inserts, 500 round trips, 500 transaction commits. Each insert was fast. Five hundred of them took 12 seconds.

The fix is a single batch insert:

1// Before: one insert at a time
2for (const row of importedRows) {
3  await this.dataSource
4    .createQueryBuilder()
5    .insert()
6    .into(Order)
7    .values(row)
8    .execute();
9}
10
11// After: batch insert
12await this.dataSource
13  .createQueryBuilder()
14  .insert()
15  .into(Order)
16  .values(importedRows)  // TypeORM accepts an array
17  .execute();

One round trip. One transaction. 12 seconds became ~200ms for 500 rows. The same pattern applies to updates: use UPDATE ... FROM (VALUES ...) to batch updates into a single statement.

Final Result: 8 Seconds to 340 Milliseconds

After these eight optimizations, the dashboard that took eight seconds to load rendered in 340ms. Here is what each change contributed:

None of these involved a read replica. None required a cache. None needed a database migration that locked a table for 45 minutes. These are query-level and schema-level optimizations — the kind that exist between your application code and the database, invisible to the user but immediately measurable in response time.

Conclusion

The most expensive PostgreSQL performance problem is the one you solve by adding infrastructure before you have ruled out query problems. A read replica hides the symptom of a missing index. A caching layer hides the symptom of an N+1. Both add operational cost and latency to your stack, and both leave the underlying problem untouched.

The eight optimizations in this post — composite indexes, N+1 fixes, correlated subquery elimination, partial indexes, materialized views, connection pooling, column-specific SELECTs, and batch writes — solve roughly 90% of slow dashboard problems we see in production SaaS applications. Start at the top. Run EXPLAIN ANALYZE first. Add the composite index second. The other six are for the cases where those two are not enough.

If your dashboard is slow right now and you are staring at a query plan you cannot decipher — that is the kind of problem we look at every day. We will read the plan with you.