Performance

Tuning

What to do, in order, when a resource gets slow, and what each kind of field path costs.

Tune in this order, cheapest first. Out of order can backfire: in an earlier benchmark, widening the pool before fixing facets increased timeouts.

  1. Add indexes.
  2. Set an explicit connection pool with timeouts.
  3. Measure each step of the slow requests.
  4. Replace the page-ids step with your own query, only if step 3 shows it is the bottleneck.

1. Indexes

Index the columns the engine joins, sorts and filters on:

-- join columns
CREATE INDEX ON order_lines(order_id);
CREATE INDEX ON order_lines(product_id);
CREATE INDEX ON orders(customer_id);

-- sort columns
CREATE INDEX ON orders(created_at);
CREATE INDEX ON customers(name);

-- common filters
CREATE INDEX ON orders(status);
CREATE INDEX ON orders(deleted_at) WHERE deleted_at IS NULL;
8×throughput from indexes alone, on an older engine with about 100,000 rows, with p99 latency down 83 to 87%. No code change came close.

Search runs lower(column) LIKE '%value%', which a pg_trgm index on the same expression can serve. The extension alone does nothing; create the index in your migrations:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX customers_name_search ON customers USING gin (lower(name) gin_trgm_ops);

With those indexes in place, the engine can search each field on its own and merge the matching ids:

  • It needs an index on every searched field, at least one root field, and relation keys the schema proves unique.
  • It asks EXPLAIN first and keeps the normal plan when that is cheaper. Asking costs about 1 ms (numbers).
  • It never creates extensions or indexes itself. Restart the process after adding them so they are detected.

2. Connection pool

A default pool can behave like three or four connections under load. Set it explicitly, with timeouts:

// Example with the pg driver
const 
pool
= new
Pool
({
max
: 10, // concurrent connections
min
: 2,
idleTimeoutMillis
: 30000,
connectionTimeoutMillis
: 5000,
statement_timeout
: 10000, // stop queries after 10 s
query_timeout
: 10000,
});

Without timeouts, one slow facet query during a spike starves the other requests. Start at 8 to 10 connections, and raise max only when requests wait for a connection, never because queries are slow.

3. Measure each step

Log or trace the page-ids, rows and facet steps separately. Wrap a step to time it:

const 
strategy
= {
ids
: async ({
request
,
context
,
utils
}: any) => {
const
start
=
performance
.
now
();
const
result
= await
utils
.executeIdsQuery({
request
,
context
});
console
.
log
(`ids step: ${
performance
.
now
() -
start
}ms`);
return
result
;
}, };
SymptomLook at
Page-ids p99 above 200 ms under normal loadIndexes on join and sort columns, then strategy.ids
A facet above 1 sThe facet's path type (below), then strategy.facets
Row loading slowIndexes on primary and foreign keys

4. Your own page-ids query

On PostgreSQL, when the schema proves joins can't duplicate rows, the engine already selects the page directly. Measure that path before writing your own. When you do, reuse the engine's compiled conditions:

const 
strategy
= {
ids
: async ({
request
,
context
,
utils
}: any) => {
const
where
=
utils
.buildWhereClause({
filters
:
request
.filters,
search
:
request
.search,
}); const
orderBy
=
utils
.compileOrderBy(
request
.sorting);
return {
ids
: [],
pageInfo
:
offsetPageInfo
,
}; }, };

See Strategies.

What each field path costs

Path typeExamplePage-ids queryFacetsAdvice
Root columnstatuslowlowFine for search defaults, sorting and facets.
Many-to-onecustomer.namecorrelated existence checkmoderateFine for most uses; measure at scale.
One-to-manyorderLines.quantitycorrelated existence checkmoderateDisable sorting; measure before allowing a facet.
Many-to-manytags.name via a junctionexistence check with joinshighDisable sorting; allow a facet only with a custom step.

When a facet is too expensive

A many-to-many facet that stays above three to five seconds at normal load won't be fixed by a custom step: grouping through a junction table costs what it costs. Instead:

  • pre-aggregate it in a table or materialized view;
  • serve that one screen from a search engine such as Meilisearch or Typesense;
  • rate-limit facet requests.
Copyright © 2026