temporal-postgres-visibility

July 11, 2026 · View on GitHub

Community maintenance tooling for the Temporal PostgreSQL visibility store (executions_visibility). It addresses the index bloat reported in temporalio/temporal#10145 by converting the pre-allocated, mostly-unused search-attribute indexes from ordinary btree indexes to partial indexes (WHERE <column> IS NOT NULL), plus an operational runbook for REINDEX and autovacuum.

Not affiliated with Temporal Technologies. This is an out-of-band, community maintenance option for self-hosted PostgreSQL visibility deployments. Temporal recommends Elasticsearch for high-volume production visibility, and the Temporal maintainers chose to keep the upstream SQL schema unchanged (see #10145). Use this only if you run the SQL visibility store and understand the trade-offs below.

The problem

On high-volume SQL visibility deployments the executions_visibility index footprint grows far beyond the table itself and keeps growing regardless of retention. From the issue:

            table             | total_size | table_size | indexes_size
------------------------------+------------+------------+--------------
 public.executions_visibility | 8745 MB    | 463 MB     | 8282 MB

REINDEX TABLE CONCURRENTLY executions_visibility reclaims a large fraction of it.

Root cause

Every pre-allocated custom / CHASM search attribute has its own nullable generated column and its own btree index, e.g.:

CREATE INDEX by_keyword_01 ON executions_visibility
  (namespace_id, Keyword01, (COALESCE(close_time, '9999-12-31 23:59:59')) DESC, start_time DESC, run_id);

PostgreSQL btree indexes store NULL keys, so a search attribute that a deployment never sets still gets one index entry for every row. With ~35 pre-allocated scalar attributes, most of them unused, the index set carries tens of full-table-sized indexes of almost pure NULL entries. Workflow-close upserts (INSERT ... ON CONFLICT ... DO UPDATE) churn these indexes; VACUUM reclaims heap space but does not compact btree index bloat — only REINDEX does, which is exactly the reported symptom.

The fix

Convert the 35 pre-allocated custom + CHASM scalar search-attribute indexes to partial indexes that only index rows where the attribute is actually set, keeping the exact key order of the upstream schema:

CREATE INDEX by_keyword_01 ON executions_visibility
  (namespace_id, Keyword01, (COALESCE(close_time, '9999-12-31 23:59:59')) DESC, start_time DESC, run_id)
  WHERE Keyword01 IS NOT NULL;

Kept full on purpose (do not convert these):

Index	Why it stays full
`default_idx`	null-sensitive; serves the default List/Count ordering
`by_status`	on a `NOT NULL` column — a partial would index every row anyway (no-op)
`by_parent_workflow_id`, `by_parent_run_id`	support `IS NULL` absence queries
`by_temporal_namespace_division`	critical — default visibility queries add `TemporalNamespaceDivision IS NULL`, which a `WHERE ... IS NOT NULL` partial index cannot serve
10 other pre-defined scalar indexes	conservative first pass (see Scope)
all GIN text / keyword-list indexes	not btree; not affected the same way

Benchmark

200,000 sparse rows (one namespace) against the upstream v12 visibility schema on PostgreSQL 16. Full detail and the reproduction harness are in bench/ (RESULTS.md).

Measurement	Full (upstream)	Partial	Reduction
Total index size	4176 MB	1738 MB	−58%
The 35 converted indexes (aggregate)	2439 MB	1.48 MB	−99.9%
`by_int_02` — attribute never used	70 MB	8 KB	−99.99%
`by_keyword_01` — attribute in use (~2%)	69 MB	800 KB	−98.9%
`by_status` / `default_idx` (kept full)	~67 MB	unchanged	—

Churn + VACUUM grew total indexes to 4380 MB (VACUUM can't compact btree bloat); REINDEX TABLE reclaimed it to 987 MB — reproducing the issue's symptom.

Reproduce:

cd bench
./run.sh                 # full benchmark (needs Docker; spins up a throwaway PG16)
./verify-online.sh       # asserts the online up/down migrations apply & revert cleanly

How to apply (online, no downtime)

The migrations use CREATE INDEX CONCURRENTLY / DROP INDEX CONCURRENTLY so they run against a live table without blocking reads or writes.

1. Confirm your schema matches. These migrations target the upstream v12 visibility schema (VisibilityVersion = 1.14). Compare your live index definitions to schema/postgresql/v12/visibility/schema.sql:

SELECT indexname, indexdef FROM pg_indexes
WHERE tablename = 'executions_visibility' ORDER BY indexname;

If your index set differs (older/newer schema, custom attributes), review and adjust the migration before running.

2. Apply. Run outside a transaction block (do not wrap in BEGIN/COMMIT; psql -f is autocommit by default):

psql "$TEMPORAL_VISIBILITY_DSN" -v ON_ERROR_STOP=1 -f migrations/partial-indexes.up.sql

3. (Optional) One-time REINDEX of the indexes you keep full, to reclaim existing bloat:

REINDEX TABLE CONCURRENTLY executions_visibility;

Revert at any time:

psql "$TEMPORAL_VISIBILITY_DSN" -v ON_ERROR_STOP=1 -f migrations/partial-indexes.down.sql

If a CONCURRENTLY build is interrupted it leaves an INVALID index; drop the *_partial / *_full leftover and re-run. See the runbook for details.

Compatibility & risks

The migration changes query planning only, never results. SQL results stay correct. The trade-off is that a WHERE col IS NOT NULL partial index cannot serve col IS NULL predicates:

Keyword01 IS NULL-style absence queries on a converted attribute will no longer use that attribute's index and may fall back to a different plan.
Equality / range / IS NOT NULL queries on a converted attribute do use the partial index.
This is why by_temporal_namespace_division and the parent-workflow indexes are kept full — the default query converter injects TemporalNamespaceDivision IS NULL into ordinary List/Count queries.
Count and paginated queries follow the same rule; validate representative queries with EXPLAIN ANALYZE against your workload before rolling out widely.

Scope

PostgreSQL only. The same nullable-btree mechanism affects the MySQL SQL visibility schema; a MySQL variant is not included here.
Conservative index set (35). The 10 pre-defined scalar SA indexes (by_batcher_user, by_temporal_scheduled_*, …) are also nullable and each cost ~70 MB in the benchmark. Converting the safe subset (everything except by_temporal_namespace_division) would recover several hundred MB more; left out of the default migration to stay low-risk.
Out of band. This does not modify temporal-sql-tool or the tracked schema_version. A future upstream schema upgrade that redefines these indexes could recreate them as full; re-apply the migration if so.