5 PostgreSQL Tricks That Made My SaaS x10 Faster (2025 Edition)

TL;DR Five copy‑pasteable fixes that turned sluggish production queries into lightning‑fast responses. All examples use generic tables—orders, users, products—so you can follow along even if you’re new to Postgres.

---

Why This Matters

• Slow queries cost money (cloud bills climb, customers churn).
• Most blog posts stop at “just add an index”—real life is messier.
• These five techniques gave my SaaS a 10× speed boost this year without bigger hardware.

If you store anything more than a to‑do list, stick around.

---

Roadmap

1. Measure First 🔬 — EXPLAIN ANALYZE BUFFERS
2. Eliminate Duplicate Rows ⚔️ — LEFT JOIN LATERAL + json_agg
3. Index Power‑Ups 🚀 — Partial & Covering Indexes
4. One‑Pass Stats 📊 — FILTER Aggregates
5. Readable + Fast 🧩 — CTEs (with a dash of LATERAL)

(Skim the headings now, then read straight through—each trick builds on the last.)

---

1. Measure First 🔬 — EXPLAIN ANALYZE BUFFERS

Rule #1 – Never tune blind. You need real numbers before you change anything.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM   orders
WHERE  created_at >= NOW() - INTERVAL '30 days';

Red Flags

• Actual vs Estimated Rows > 10× → run ANALYZE or fix statistics.
• Node Execution Time dominates (> 70 %) → add an index or rewrite the join.
• Shared Read Blocks huge on a “small” query → you’re hitting disk, add an index or more cache.

Tip: Upload the plan to https://explain.dalibo.com/ for an instant visual map of bottlenecks.

---

2. Eliminate Duplicate Rows ⚔️ — LEFT JOIN LATERAL + json_agg

Rule #2 – Aggregate as close to the data as possible. One parent row in, one parent row out.

Use Case

Return every order with its items—but keep the result set to one row per order.

Before – Classic join (row explosion)

SELECT o.id, o.total, oi.*
FROM   orders o
LEFT   JOIN order_items oi ON oi.order_id = o.id;

-- Result
 id | total | item_id | qty
----+-------+---------+-----
101 | 49.00 |  9001   |  1
101 | 49.00 |  9002   |  2
102 | 15.00 |  9003   |  1

A single order with two items becomes three rows.

After – LATERAL + json_agg

SELECT o.id,
       o.total,
       json_agg(oi.* ORDER BY oi.id) AS items
FROM   orders o
LEFT   JOIN LATERAL (
  SELECT *
  FROM   order_items
  WHERE  order_id = o.id
) oi ON TRUE
GROUP  BY o.id;

-- Result
 id | total | items
----+-------+-------------------------------------------------------
101 | 49.00 | [{"id":9001,"qty":1},{"id":9002,"qty":2}]
102 | 15.00 | [{"id":9003,"qty":1}]

One row per order; items are delivered as a tidy JSON array.

Outcome: Dashboard latency dropped 650 ms → 38 ms.

Tip: Index order_items.order_id so each per‑row sub‑query uses an index scan, not a sequential scan.

---

3. Index Power‑Ups 🚀 — Partial & Covering Indexes

Rule #3 – Build the smallest index that fully answers the query. Smaller indexes read faster and write faster.

3.1 Partial Index (only active rows)

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_user_active
  ON users(id)
  WHERE deleted = false;

Indexes just the rows you actually query—often 90 % smaller than a full index.

3.2 Covering / INCLUDE Index (avoid heap access)

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_status_inc
  ON orders(status)
  INCLUDE (total, created_at);

Postgres can now satisfy:

SELECT status, total, created_at
FROM   orders
WHERE  status = 'SHIPPED';

using only the index—no table lookup at all.

On 100 k rows: latency 120 ms → 9 ms; buffer reads 18 k → 1 k.

Tip: Prove the footprint with SELECT pg_size_pretty(pg_relation_size('idx_orders_status_inc'));—stakeholders love hard numbers.

---

4. One‑Pass Stats 📊 — FILTER Aggregates

Rule #4 – Scan once, compute many metrics. Let Postgres do the heavy lifting in a single pass.

Before – Two separate scans

SELECT COUNT(*)                   FROM orders;             -- total
SELECT COUNT(*) FROM orders WHERE status = 'SHIPPED';      -- shipped

After – One scan, multiple metrics

SELECT COUNT(*)                                           AS total_orders,
       COUNT(*) FILTER (WHERE status = 'SHIPPED')         AS shipped_orders,
       AVG(total) FILTER (WHERE status = 'SHIPPED')       AS avg_shipped_value
FROM   orders;

Dashboard latency: 45 ms → 12 ms on 500 k rows.

Tip: Combine with the covering index from Section 3—your aggregate becomes index‑only.

---

5. Readable + Fast 🧩 — CTEs (with LATERAL)

Rule #5 – Isolate heavy logic, then reuse it. CTEs make complex queries readable; Postgres 15 inlines them for free speed.

Goal

Return every product with its latest price—one row each.

Before – Correlated sub‑select (runs per product)

SELECT p.id, p.name,
       (SELECT price
        FROM   prices pr
        WHERE  pr.product_id = p.id
        ORDER  BY pr.changed_at DESC
        LIMIT 1) AS latest_price
FROM   products p;

After – CTE + DISTINCT ON + LATERAL

WITH latest_prices AS (
  SELECT DISTINCT ON (product_id) *
  FROM   prices
  ORDER  BY product_id, changed_at DESC
)
SELECT p.id, p.name, lp.price, lp.changed_at
FROM   products p
LEFT   JOIN LATERAL (
  SELECT price, changed_at
  FROM   latest_prices lp
  WHERE  lp.product_id = p.id
) lp ON TRUE;

On 10 k products: 780 ms → 54 ms.

Tip: Add an index on (product_id, changed_at DESC) in prices so DISTINCT ON is instant.

---

What’s Next?

If you found these tricks helpful:

• Smash Clap so more devs see the post.
• Follow me for deep‑dive follow‑ups on each technique.
• Drop your slowest Postgres query in the comments—I may refactor it in a future article.

Your query planner salutes you 🚀

---

This article was originally published on Medium:
5 PostgreSQL Tricks That Made My SaaS x10 Faster (2025 Edition)