Reduce Index Maintenance on Entity table

[tombrooks248]

Reduce index maintenance cost on the entity table

Background

On the 2026-08-25 production title-boundary run, the Postgres write was 62.7% of the total 2h32m: a DELETE of 15,172,240 rows in 68s followed by a single INSERT INTO entity (...) SELECT ... FROM {staging_table} of 22,740,586 rows in 5,624s. That INSERT runs server-side on one connection with Spark idle, so cluster capacity is irrelevant to it — the cost is row insertion plus index maintenance on a live table the API serves.

There are 13 indexes on entity, every one of which must be updated for all 22.8M inserted rows: the primary key, nine single-column btrees, one ten-column composite, and two GiST indexes that geoalchemy2 creates automatically for the geometry and point columns (see the comment at application/db/models.py:65 in digital-land.info).

At least two of those look like they may be doing no useful work. This ticket is to establish that from evidence and then remove what is not being used.

Part 1 — measure before changing anything

Run against production, read-only:

SELECT indexrelname, idx_scan, idx_tup_read, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'entity'
ORDER BY idx_scan;

-- how far back those counters go; a recent reset makes a low idx_scan meaningless
SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();

The stats_reset check is not optional. idx_scan counts only since the last statistics reset, so a low number on a recently reset counter proves nothing. If the window turns out to be too short to be conclusive, note the current values, leave them to accumulate, and re-check rather than proceeding on weak evidence.

This measurement is the gate for Parts 2 and 3. Both are expected to be safe, but neither should be done blind.

Part 2 — drop idx_entity_columns

repo = digital-land.info
application/db/models.py:80 defines a ten-column composite index over entity, name, entry_date, start_date, end_date, dataset, organisation_entity, prefix, reference, typology, carrying its own comment:

# add another index which is in the db. this was created initionally before the above was added
# might want to examine if it's needed or not

Two independent reasons to think it is dead weight:

• Migration 543a95beb74b (2022-06-29) explicitly dropped an index over those exact ten columns (entity__new_entity_name_entry_date_start_date_end_date_data_idx) and replaced it with the nine individual single-column indexes that exist today. The composite currently in the database is that same index, which a previous migration had already decided against.
• It is led by entity, which is the primary key. A btree led by the primary key can only help queries filtering on entity, and those are better served by the PK itself. It would only earn its place as a covering index for index-only scans, which is an expensive way to serve ten columns across a table of this size.

Assuming Part 1 confirms it is unused, drop it. This is one of thirteen indexes and by some margin the widest, so it should be a meaningful fraction of the per-row insert cost, and it is trivially reversible if a regression appears.

The index must be removed from both the Alembic migration and the ORM model in the same PR. If the migration drops it but idx_entity_columns stays defined in models.py, the next --autogenerate will simply recreate it.

Part 3 — make the geospatial indexes partial (conditional on Part 1)

Both geometry and point are nullable, and geoalchemy2 indexes both automatically. GiST indexes do store NULL entries, so every row pays for both indexes regardless of which column it actually populates — and in practice most datasets populate only one. title-boundary is polygons, so its 22.8M rows carry a populated geometry and, for the most part, a NULL point. transport-access-node is the reverse: its driver logs show geometry NULL with point populated.

Making each index partial would skip those entries entirely:

CREATE INDEX CONCURRENTLY idx_entity_point ON entity USING GIST (point) WHERE point IS NOT NULL;

Two things to be aware of, which are why this is Part 3 rather than Part 2:

• The planner has to be able to use it. A partial index is only considered when the query's predicate implies the index predicate. PostGIS operators such as && and ST_Intersects are strict, so Postgres generally can make that inference, but it must be confirmed with EXPLAIN against the actual API queries before this is merged, not assumed.
• CREATE INDEX CONCURRENTLY cannot run inside a transaction, so the Alembic migration needs with op.get_context().autocommit_block():. On a table this size a non-concurrent rebuild would lock out writes for a long time.

The model side also needs changing: set spatial_index=False on the Geometry columns so geoalchemy2 stops auto-creating the plain index, and declare the partial indexes explicitly. Same rule as Part 2 — model and migration together, or --autogenerate will undo it.

Acceptance criteria

• idx_scan figures for all 13 indexes on entity are recorded on the ticket, together with the stats_reset timestamp that gives the observation window.
• idx_entity_columns is dropped in a migration, and also removed from application/db/models.py in the same PR, assuming the measurement supports it.
• alembic upgrade head followed by alembic revision --autogenerate produces an empty migration, confirming the model and database agree and the index will not be recreated.
• If Part 3 proceeds: EXPLAIN output for the main geospatial API queries is recorded on the ticket showing the partial index still being chosen, both before and after.
• Timing for the title-boundary Postgres write is captured from the next run and compared against the 5,624s INSERT baseline from 2026-08-25.

Notes

Any change here affects every dataset's write to entity, not just title-boundary — title-boundary is simply where the cost is most visible, at 22.8M rows in a single transaction. That cuts both ways: the saving applies estate-wide, and so would a regression, which is why Part 1 gates the rest.

Out of scope

Parallelising the insert by splitting it into chunks. That is a larger change, and it conflicts with the current design in which the DELETE and INSERT share one transaction so the live table is never left partially populated — parallel chunks across separate connections cannot share that transaction. It is tracked separately and is in any case blocked on establishing where the time in the 2026-09-07 run actually went, since the log suggests it did not reach the INSERT at all.

[thecoolestcoderrushil]

A 22.7M-row production write taking 5,624 seconds is a change that deserves realistic rehearsal before it reaches a live API. Antifailure runs proposed migrations and data changes against a disposable production-shaped stack, measuring actual runtime, locks, and latency effects before release. Would you be interested in trying it?
https://antifailure.dev/