Postgres storage: bucket_parameters DISTINCT ON lookup falls back to seq scan/sort under many parameterized buckets

[mnstry]

Summary

We're seeing a repeatable initial checkpoint delay on self-hosted PowerSync with Postgres storage and many parameterized bucket definitions.

The hot query shape in the storage database is a SELECT DISTINCT ON (...) against powersync.bucket_parameters. On our staging deployment, the default planner path fell back to a large scan + sort, and a local composite index changed the plan from ~384ms to ~33ms for the same representative lookup.

This did not eliminate all startup latency on its own, but it materially improved the specific bucket_parameters lookup path and looks like something PowerSync may want to handle upstream in the storage schema or query shape.

Environment

• PowerSync service: powersync-js/1.48.0 clients hitting self-hosted powersync-service
• Storage backend: Postgres (powersync_storage)
• Active sync rule: 1
• Bucket definitions: 89 total, 78 parameterized

Observed runtime pattern

On the service side, the repeated timing pattern was:

• Sync stream started
• ~5.5s to ~6.5s later: New checkpoint

The active checkpoint log included:

• buckets: 988
• param_results: 975

That suggested bucket-parameter fan-out, so we inspected the storage DB.

Dominant storage query

From pg_stat_statements on the storage DB, the dominant lookup was:

SELECT DISTINCT ON (lookup, source_table, source_key)
  lookup, source_table, source_key, id, bucket_parameters
FROM bucket_parameters
WHERE group_id = $1
  AND lookup = ANY (...)
  AND id <= $3
ORDER BY lookup, source_table, source_key, id DESC

Representative aggregated stats before the workaround:

• calls: 47073
• mean_exec_time: 56.1ms
• max_exec_time: 1206.0ms

Because the active rule had 89 bucket definitions (78 parameterized), that fan-out alone was already enough to explain several seconds of checkpoint setup time.

Representative EXPLAIN before index

For one real live lookup key, EXPLAIN (ANALYZE, BUFFERS) showed:

Unique  (actual time=275.878..382.311 rows=480 loops=1)
  ->  Gather Merge
        ->  Sort
              Sort Method: external merge  Disk: ~7MB per worker
              ->  Parallel Seq Scan on bucket_parameters
Execution Time: 384.211 ms

Additional shape details:

• raw rows matched: 102894
• distinct (source_table, source_key) pairs returned: 480
• temp spill occurred

Existing indexes on powersync.bucket_parameters were:

• (group_id, lookup, id DESC)
• (group_id, source_table, source_key)

Even with enable_seqscan = off, Postgres still chose a broad bitmap path + sort instead of a path that matched the full dedup/order shape.

Local workaround index

We added this index locally as an operational workaround:

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_bucket_parameters_lookup_source_latest
  ON powersync.bucket_parameters (group_id, lookup, source_table, source_key, id DESC);

Representative EXPLAIN after index

The same representative lookup changed to:

Result  (actual time=0.032..32.928 rows=480 loops=1)
  ->  Unique
        ->  Index Only Scan using idx_bucket_parameters_lookup_source_latest
Execution Time: 32.999 ms

Notable differences:

• execution time: 384ms -> 33ms
• no external merge sort
• no parallel seq scan
• index-only scan chosen

Important caveat

This did not eliminate all initial checkpoint latency for us. There is still residual startup work after this lookup path is improved.

So this report is not claiming "this one index fixes all startup delay".

What it does show is:

1. bucket_parameters lookups can dominate startup wall clock through fan-out even when no single query looks catastrophically slow in isolation.
2. The current default index shape on bucket_parameters does not support this DISTINCT ON ... ORDER BY ... id DESC access pattern well enough on our dataset.
3. A composite index on (group_id, lookup, source_table, source_key, id DESC) materially improves that hot path.

Question

Would PowerSync accept either:

• an upstream storage-schema index along these lines, or
• a query/storage change that avoids the large scan + sort path for bucket_parameters lookups under many parameterized buckets?

Happy to provide more detail if useful.

[mnstry]

Update from follow-up A/B on the same staging actor (a0000000-0000-0000-0000-000000000202): the proposed storage index improved the targeted bucket_parameters lookup locally, but made the real server-side checkpoint phase slower overall.

Representative query-level result:

• before: 384ms (Parallel Seq Scan + external merge sort + temp spill)
• after index: 33ms (Index Only Scan on (group_id, lookup, source_table, source_key, id DESC))

But the metric that matters for the app is PowerSync server-side Sync stream started -> New checkpoint.

Pre-index exact samples:

• 2026-04-21T17:58:54.840Z -> 2026-04-21T17:59:01.398Z = 6.558s
• 2026-04-21T18:05:12.626Z -> 2026-04-21T18:05:18.444Z = 5.818s
• 2026-04-21T17:52:26.827Z -> 2026-04-21T17:52:32.649Z = 5.822s

With the index in place:

• 2026-04-21T18:48:22.056Z -> 2026-04-21T18:48:30.254Z = 8.198s
• 2026-04-21T18:49:58.409Z -> 2026-04-21T18:50:06.821Z = 8.412s
• 2026-04-21T18:50:30.473Z -> 2026-04-21T18:50:38.864Z = 8.391s

To attribute that cleanly, I temporarily dropped the index, ran a fresh login for the same actor, and measured again:

• drop test: 2026-04-21T18:58:43.784Z -> 2026-04-21T18:58:50.456Z = 6.672s

So the result is decisive:

• the index makes the hot lookup faster
• but the overall checkpoint phase gets ~2s slower
• dropping the index returns the checkpoint window to the old ~6s band

That suggests the architectural bottleneck is not solved at the single-query plan level. Possible mechanisms on the PowerSync side include extra index maintenance on bucket_parameters writes during checkpoint, adjacent queries changing plan choices after the new index appears, or broader checkpoint assembly/cache effects.

I reverted the index from staging after the test. If helpful, I can collect more service-side timing evidence around the checkpoint assembly path, but at this point I would not recommend this index as a workaround.

[rkistner]

@mnstry Thanks for the report.

I can confirm there are some inefficiencies in the Postgres implementation currently, especially around how bucket_parameters are used. We have some plans to overhaul the data storage structure to be more in line with how we use MongoDB storage, which should help to fix these inefficiencies.

In the meantime, two workarounds I can suggest:

1. Is it feasible to switch to MongoDB storage for this? Our MongoDB storage implementation is currently more optimized than the Postgres one.
2. Alternatively, can you reduce usage of tables in parameter queries or sync stream subqueries/joins? For example, you could include arrays of ids in the JWT and reference that in your queries, instead of querying from tables. If you can provide some example queries we can give more specific recommendations here.