Killing N+1: How One SQL Trick Cut Our Latency by 40x

Forma Engineering Blog · Series Part 2

TL;DR

We took database round-trips from 101 to 1, and latency from 1000ms to 25ms, a 97% improvement, using an underrated PostgreSQL feature: CTE + JSON_AGG.

If you use the EAV (Entity-Attribute-Value) pattern for flexible data storage and N+1 queries are killing you, this post is for you.

The CTE + JSON_AGG pattern works with modern data stacks, not only legacy ones. Forma uses DuckDB for cold data queries and exports to Parquet on S3, and the same query optimization principles apply whether you hit PostgreSQL directly or run federated queries across hot and cold storage. These techniques stay relevant on a lakehouse architecture.

The villain: the N+1 query nightmare

Your SaaS app has a "Contacts" feature. Different customers need different fields: Customer A wants 12, Customer B wants 30, and Customer C changes their mind every week. Rather than run ALTER TABLE every time someone adds a field, you chose the EAV pattern and store attributes as rows instead of columns.

Good call. The nightmare starts when you write the query code:

// Step 1: Query EAV table to get matching row IDs (1 query)
rowIDs := db.Query("SELECT DISTINCT row_id FROM eav_table WHERE ...")

// Step 2: Loop through each row_id to fetch main table data (N queries!)
for _, rowID := range rowIDs {
    record := db.Query("SELECT * FROM entity_main WHERE row_id = ?", rowID)
    results = append(results, record)
}

The code looks straightforward but has a fatal flaw: fetching 100 records takes 101 database round-trips.

Records	Queries	Round-trip Latency	Total Latency
10	11	10ms	110ms
50	51	10ms	510ms
100	101	10ms	1010ms

A full second. The user clicks "Search" and waits. And it gets worse. Under high concurrency, 101 queries per request drains a connection pool fast. The application layer spends most of its time waiting on network I/O inside a loop. And if the database sits in another region, a single round-trip might be 50ms, which puts 100 records at 5 seconds.

This is the N+1 query problem, a well-known performance killer in the ORM world.

The hero: let the database do what it's good at

The root cause is that the application layer assembles the data. The app loops through records asking the database one at a time for details, and the database can only answer one at a time.

Databases were built for exactly this work. They have indexes, query optimizers, and vectorized execution engines, all designed for batch data processing. So move the loop into the database and return everything in one query.

CTE + JSON_AGG

PostgreSQL's CTE (Common Table Expression) and the JSON_AGG function are the core of the solution.

Here is the complete SQL, with the mechanics explained after:

sql

WITH 
-- Step 1: Find matching record IDs
filtered_ids AS (
    SELECT DISTINCT row_id
    FROM eav_table
    WHERE schema_id = $1 AND /* filter conditions */
),

-- Step 2: Sort + Paginate
paginated AS (
    SELECT row_id
    FROM filtered_ids
    ORDER BY /* sort conditions */
    LIMIT $page_size OFFSET $offset
),

-- Step 3: Fetch main table data
main_data AS (
    SELECT m.*
    FROM paginated p
    JOIN entity_main m ON m.row_id = p.row_id
),

-- Step 4: Aggregate EAV attributes into JSON
eav_json AS (
    SELECT 
        p.row_id,
        JSON_AGG(
            JSON_BUILD_OBJECT(
                'attr_id', e.attr_id,
                'value', COALESCE(e.value_text, e.value_numeric::text)
            )
        ) AS attributes
    FROM paginated p
    JOIN eav_table e ON e.row_id = p.row_id
    GROUP BY p.row_id
)

-- Final result: main table + EAV attributes, returned in one shot
SELECT m.*, COALESCE(e.attributes, '[]') AS attributes_json
FROM main_data m
LEFT JOIN eav_json e ON e.row_id = m.row_id;

What does this SQL do?

Each WITH clause is a station on an assembly line, with data flowing from one step to the next. JSON_AGG collapses multiple EAV rows into a single JSON array, one row_id to one JSON object. And the whole thing is one database interaction, so all the data comes back in a single package.

How CTE eliminates round-trips

Without a CTE, your application plays ping-pong with the database:

App: "Give me the IDs"           → DB responds (round-trip 1)
App: "Give me details for ID 1"  → DB responds (round-trip 2)
App: "Give me details for ID 2"  → DB responds (round-trip 3)
...101 times...

With a CTE, you send one instruction:

App: "Database, figure out the IDs, fetch the details, 
      aggregate them into JSON, and give me everything 
      in one response."

The query planner then optimizes the whole pipeline as a single execution unit. It can use indexes efficiently across all steps, parallelize independent operations, and skip the serialization and deserialization overhead between steps. Computation pushdown, in other words: the database does what it was built to do.

The application layer just parses JSON:

// One query, all data
rows := db.Query(cteSQL, schemaID, pageSize, offset)
for rows.Next() {
    var record Record
    var attributesJSON string
    rows.Scan(&record, &attributesJSON)
    json.Unmarshal(attributesJSON, &record.Attributes)
    results = append(results, record)
}

Performance comparison

Before and after optimization:

Records	Queries (Before)	Queries (After)	Latency (Before)	Latency (After)	Improvement
10	11	1	110ms	~15ms	86%
50	51	1	510ms	~20ms	96%
100	101	1	1010ms	~25ms	97%

In cross-region deployments, at 50ms per round-trip:

Records	Latency (Before)	Latency (After)	Improvement
100	5050ms	~80ms	98%

Five seconds down to 80 milliseconds. The user experience goes from "is this site down?" to instant.

Summary: 100 records used to mean 101 queries and over a second of latency. Now it's 1 query and 25ms. Cross-region, 5 seconds becomes 80ms, a 98% improvement.

Why does this work?

Network round-trips are the enemy

CPU and memory speeds are measured in nanoseconds; network round-trips in milliseconds. That gap is six orders of magnitude, which is why cutting round-trips usually beats optimizing CPU work.

The database is a data processing expert

PostgreSQL's query optimizer has been refined over decades. It knows how to execute JOINs efficiently, how to use indexes, and how to parallelize operations. Handing data aggregation to it beats looping in application code by a wide margin.

JSON_AGG is underrated

Many people treat PostgreSQL as only a relational database and forget that since 9.4 it is also a good JSON database. JSON_AGG and JSON_BUILD_OBJECT can reshape complex data at the database layer instead of assembling it row by row in application code.

The CPU vs. network trade-off

CTE + JSON_AGG trades CPU for fewer network round-trips. The trade stops paying in a few specific situations.

Break-even analysis

In-database aggregation wins when:

Network_Latency × N_Records > CPU_Aggregation_Time + 1_Round_Trip

With real numbers:

Scenario	Network Round-Trip	Records	N+1 Latency	CTE Latency	Winner
Same machine (localhost)	0.5ms	10	~5ms	~8ms	N+1
Same datacenter	2ms	10	~20ms	~10ms	CTE
Same datacenter	2ms	100	~200ms	~25ms	CTE
Cross-region	50ms	10	~500ms	~60ms	CTE
Cross-region	50ms	100	~5000ms	~80ms	CTE

The crossover sits around 5-10 records when database and app share a datacenter. Cross-region, even 2 or 3 records make the CTE worthwhile.

When to keep aggregation in the app layer

Three cases where CTE + JSON_AGG isn't the answer.

The first is very small result sets with co-located services. If you fetch 3-5 records and your app runs on the same machine as the database, which is common in development and small deployments, building JSON in the database can cost more than the N+1 penalty.

// For small, co-located workloads, simple queries may be faster
if recordCount < 5 && isLocalhost {
    // Simple N+1 is fine here
}

The second is complex per-record transformations. If each record needs business logic that SQL can't express, such as calling external APIs, complex validation, or ML inference, you will loop anyway, and pushing aggregation down doesn't help.

// Example: Each record needs an external API call
for _, record := range records {
    record.EnrichedData = callExternalAPI(record.ID)  // Can't do this in SQL
}

The third is memory-constrained database servers. JSON_AGG builds the whole JSON array in memory before returning. For very large result sets (10K+ rows with complex nested data) that creates memory pressure on the database server, and streaming results row by row is safer.

In practice this rarely comes up, because Forma's architecture has three layers of protection.

The hot table removes aggregation from list queries entirely:

Query Type	Fields Needed	Data Source	JSON_AGG?
Paginated list	5-10 hot fields	`entity_main` only	No
Detail view	All fields, 1 record	`entity_main` + EAV	Yes, but tiny

More than 80% of queries are list views that need only hot fields, so no JSON_AGG runs at all.

Real records also have a limited number of fields. Even when you aggregate EAV attributes, a typical record tops out at 30-50 fields. A CRM contact with name, phone, email, company, address, and 20 custom fields is roughly 2 to 5KB of JSON. A 100-record batch is 200 to 500KB, well within any database's comfort zone.

And DuckDB handles the complex analytics. Bulk exports, aggregations across thousands of records, OLAP-style queries: those would stress JSON_AGG, and they shouldn't run on PostgreSQL in the first place. That is what the DuckDB + Parquet layer is for, covered in Part 3.

Workload	Solution	Why
List views	Hot Table (PostgreSQL)	B-tree indexed, no aggregation
Detail views	CTE + JSON_AGG (PostgreSQL)	Single record, tiny payload
Bulk export / OLAP	DuckDB + Parquet	Columnar storage, designed for this

The memory explosion scenario doesn't happen in a system laid out this way: aggregating hundreds of attributes across tens of thousands of records belongs to DuckDB, not PostgreSQL. Each layer handles what it's best at.

The hybrid approach

Forma uses a hybrid strategy in practice:

Query Type	Records	Strategy	Reason
Paginated list	20-100	CTE + JSON_AGG	Sweet spot for in-database aggregation
Single record detail	1	Direct query	No aggregation needed
Bulk export	1000+	Streaming cursor	Avoid memory pressure
Real-time dashboard	Variable	Depends on latency budget	Measure, then decide

Decision flowchart

                    ┌─────────────────┐
                    │ How many records│
                    │ are you fetching│
                    └────────┬────────┘
                             │
              ┌──────────────┼──────────────┐
              │              │              │
           1-5 records    5-500 records   500+ records
              │              │              │
              ▼              ▼              ▼
    ┌─────────────────┐ ┌─────────────┐ ┌─────────────────┐
    │ Is DB co-located│ │ Use CTE +   │ │ Use streaming   │
    │ with app?       │ │ JSON_AGG    │ │ cursor or       │
    └────────┬────────┘ │ (default)   │ │ pagination      │
             │          └─────────────┘ └─────────────────┘
        Yes  │  No
             │   │
             ▼   ▼
    ┌─────────┐ ┌─────────┐
    │ N+1 OK  │ │ Use CTE │
    └─────────┘ └─────────┘

Measuring your specific workload

Rules of thumb are a starting point. Measure:

sql

-- Add EXPLAIN ANALYZE to see actual execution time
EXPLAIN ANALYZE
WITH filtered_ids AS (...)
SELECT ...;

Compare against your N+1 implementation under real network conditions. The numbers above will shift with your actual network latency (check ping or distributed tracing), your connection pool configuration, how complex your EAV schema is, and how well your indexes cover the query.

When to use this, and when not to

It fits when attributes are stored as rows and need aggregating into records, when you fetch multiple records at once in paginated lists or bulk exports, and when the database and application are far enough apart that latency matters.

The limits are worth knowing too. You need PostgreSQL 9.4+ for JSON_AGG and JSON_BUILD_OBJECT. Deeply nested CTEs get hard to read, so consider wrapping them in views or stored procedures. And JSON_AGG builds JSON arrays in memory, so watch memory limits on very large result sets.

What's next: when data exceeds a single machine

This post solved the N+1 query problem, and the previous one covered hot table and JSON Schema design. One question is still open: when historical data reaches billions of records and a single PostgreSQL instance can't handle it, what then?

Part 3 covers building a serverless lakehouse with DuckDB, CDC, and Parquet, including the part everyone worries about: how to be sure a lakehouse query isn't reading dirty data.

Series Navigation

[Part 1] Why EAV is the Most Underrated Data Model for AI
[Part 2] Killing N+1: How One SQL Trick Cut Our Latency by 40x ← You are here
[Part 3] Zero Dirty Reads: Building a Trustworthy Lakehouse with DuckDB (Finale)

This post is based on engineering practices from the Forma project. Forma is a flexible data storage engine designed for the AI era.

Killing N+1: How One SQL Trick Cut Our Latency by 40x ​

TL;DR ​

The villain: the N+1 query nightmare ​

The hero: let the database do what it's good at ​

CTE + JSON_AGG ​

What does this SQL do? ​

How CTE eliminates round-trips ​

Performance comparison ​

Why does this work? ​

Network round-trips are the enemy ​

The database is a data processing expert ​

JSON_AGG is underrated ​

The CPU vs. network trade-off ​

Break-even analysis ​

When to keep aggregation in the app layer ​

The hybrid approach ​

Decision flowchart ​

Measuring your specific workload ​

When to use this, and when not to ​

What's next: when data exceeds a single machine ​