Tuning
Tune in this order, cheapest first. Out of order can backfire: in an earlier benchmark, widening the pool before fixing facets increased timeouts.
- Add indexes.
- Set an explicit connection pool with timeouts.
- Measure each step of the slow requests.
- 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;
Indexes for substring search
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
EXPLAINfirst 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;
},
};
| Symptom | Look at |
|---|---|
| Page-ids p99 above 200 ms under normal load | Indexes on join and sort columns, then strategy.ids |
| A facet above 1 s | The facet's path type (below), then strategy.facets |
| Row loading slow | Indexes 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 type | Example | Page-ids query | Facets | Advice |
|---|---|---|---|---|
| Root column | status | low | low | Fine for search defaults, sorting and facets. |
| Many-to-one | customer.name | correlated existence check | moderate | Fine for most uses; measure at scale. |
| One-to-many | orderLines.quantity | correlated existence check | moderate | Disable sorting; measure before allowing a facet. |
| Many-to-many | tags.name via a junction | existence check with joins | high | Disable 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.