fix(inkless:control-plane): drop the batches timestamp index [KC-612] by jeqo · Pull Request #828 · aiven/inkless

jeqo · 2026-10-15T13:14:18Z

#799 added an index on batch_timestamp(...) (V30) and rewrote the list_offsets_v1 timestamp lookups (V31). The index changes the plan of retention's DELETEs and the retention recount: on partitions with high offsets the planner picks the new index, which cannot filter on last_offset, so each DELETE fetches every batch of the partition while holding the logs row locks that commit_file waits for. Applying the index by hand on a large control plane stalled produce until it was dropped. This PR removes the index and keeps the rewrite as V30. The rewrite also redefines batch_timestamp in SQL so the planner inlines it, which cuts the scan by about 3x. Nothing was released with V30 or V31. See the commit message for the planner reasoning.

Impact of the revert

Requests affected

Only requests with a concrete timestamp or MAX_TIMESTAMP on diskless topics. EARLIEST, LATEST, and LATEST_TIERED read one logs row and are unchanged. In client terms:

KafkaConsumer.offsetsForTimes and seeks by time.
kafka-get-offsets.sh --time <ms> and --time -3.
kafka-consumer-groups.sh --reset-offsets --to-datetime and --by-duration.
Admin listOffsets with OffsetSpec.forTimestamp and OffsetSpec.maxTimestamp.

What changes against #799 and against main before it

Against perf(inkless:control-plane): index and rewrite list_offsets_v1 timestamp lookups [KC-537] #799 as merged: these requests are slower. The index made them a range scan. Without it, the rewrite reads the partition's entries of batches_by_last_offset_covering_idx, which is index-only. Benchmark with 8 partitions and 100k batches per partition: about 8 ms against 2.4 ms for a seek near the end, 9 ms against 1.9 ms past the end, and 15 ms against 0.9 ms for MAX_TIMESTAMP.
Against main before perf(inkless:control-plane): index and rewrite list_offsets_v1 timestamp lookups [KC-537] #799: these requests are about 9x faster in the same benchmark (8 ms against 73 ms near the end, 9 ms against 80 ms past the end, 15 ms against 135 ms for MAX_TIMESTAMP). Cost now follows the batches of that partition, not the table, so the gap grows with the number of partitions that share batches.
Other callers of batch_timestamp: V30 redefines it in SQL (same result, IMMUTABLE, PARALLEL SAFE). Commit and retention call it too, and they get the same value.
Not affected: produce, fetch, retention, and DeleteRecords. No index is added, so write cost and storage stay the same.

Workarounds

Raise timeouts for timestamp lookups on partitions with a deep backlog: default.api.timeout.ms and request.timeout.ms for clients, timeout for Admin calls. The control plane's statement_timeout still bounds the query.
Use offsets where a timestamp is not required: --to-offset, --shift-by, --to-earliest, and --to-latest avoid the lookup.
Run autovacuum on batches: the scan is index-only only on pages the visibility map marks all-visible.
Drain the backlog: cost is proportional to the batches a partition retains, so a partition that keeps more than its retention window pays more.
Do not create the index by hand. It restores the fast lookup and brings back the retention stall.

Mitigation for KC-537 stays

fix(inkless:consume): stop a slow ListOffsets from hanging the broker [KC-537] #798 removed the lock that serialized listOffsets and bounded the fetch offset and delete records pools. A slow lookup no longer hangs the broker. A full queue returns the retriable REQUEST_TIMED_OUT.
The V30 rewrite removes the table-wide walk of batches_pkey that made lookups time out. A timestamp lookup works again, within one partition's cost.

Databases that applied main's V30 and V31

Only databases built from main since 2026-10-12 (CI, development, staging) have the index and a history row for the old V31. They fail Flyway validation after this change. Drop the index, then reset the schema or run flyway repair:

DROP INDEX CONCURRENTLY IF EXISTS batches_by_timestamp_idx;

Testing

BatchesPlanRegressionTest.migrationsDoNotChangeExistingStatementPlans: loads a skewed fixture of many low-offset partitions and a few near 10^10, interleaved in the heap, with the covering index at the default fill factor and at a low one to emulate bloat. It captures the plan of the retention and read statements on the baseline schema, applies the remaining migrations, and requires the same plans. Statistics come from every row, so the plans do not vary between runs. The plans come from EXPLAIN with literal values and do not show how PL/pgSQL plans inside the functions.
BatchesPlanRegressionTest.anIndexSharingTheKeyPrefixChangesRetentionPlans: creates the dropped index and requires the plans to change, so the check above cannot pass vacuously.
ListOffsetsTimestampScanBenchmarkTest (@Tag("benchmark"), not run in CI): 8 interleaved partitions, baseline against the rewrite. The benchmark runs VACUUM at each depth, so the scan is index-only (Heap Fetches: 0).

100k batches per partition, 8 partitions	baseline	V30 rewrite
`MAX_TIMESTAMP`, microseconds	135,340	15,491
`ts@start`, microseconds	571-4,832	664
`ts@90%`, microseconds	73,226	8,283
`ts@end+1`, microseconds	79,533	8,938
bulk commit, WAL bytes per batch	521	521
heap and indexes of `batches`, MB	245.6	245.6

Single run per schema on a local container. Differences below 10 to 30% are noise.

Follow-ups

A faster timestamp lookup needs a design that does not add an index sharing the (topic_id, partition) prefix, or that makes the retention statements immune. BatchesPlanRegressionTest is the gate for any such change.

Copilot

🟡 Changes recommended

The recovery instructions skip the SQL optimization, and the regression test needs connection-pool cleanup and an accurate retention probe.

2 open findings

What changed in this PR

Removes the timestamp index that causes retention to stall produce, while preserving partition-scoped timestamp lookups in Inkless’s PostgreSQL control plane.

Changes:

Consolidates the lookup rewrite into V30 and converts batch_timestamp to SQL.
Adds retention-plan regression tests and updates the multi-partition benchmark.
Updates generated jOOQ schema-version annotations to V30.

Generated Java paths are relative to storage/inkless/src/main/jooq/org/jooq/generated/. Other paths are repository-relative.

File	Description
`storage/inkless/src/test/java/io/aiven/inkless/control_plane/postgres/ListOffsetsTimestampScanBenchmarkTest.java`	Benchmarks V29 against V30 across interleaved partitions.
`storage/inkless/src/test/java/io/aiven/inkless/control_plane/postgres/BatchesPlanRegressionTest.java`	Adds migration plan-regression checks.
`storage/inkless/src/main/resources/db/migration/V30__List_offsets_min_offset_lookup.sql`	Retains lookup rewrite and adds SQL timestamp evaluation.
`storage/inkless/src/main/resources/db/migration/V30__Batches_by_timestamp_index.sql`	Removes timestamp-index migration.
`UDTs.java`	Updates V30 annotation.
`udt/RepairDisklessLogResponseV1.java`	Updates V30 annotation.
`udt/RepairDisklessLogRequestV1.java`	Updates V30 annotation.
`udt/records/RepairDisklessLogResponseV1Record.java`	Updates V30 annotation.
`udt/records/RepairDisklessLogRequestV1Record.java`	Updates V30 annotation.
`udt/records/PurgeDeletedLogsResponseV1Record.java`	Updates V30 annotation.
`udt/records/PruneBatchesBelowHighestTieredOffsetResponseV1Record.java`	Updates V30 annotation.
`udt/records/PruneBatchesBelowHighestTieredOffsetRequestV1Record.java`	Updates V30 annotation.
`udt/records/ListOffsetsResponseV1Record.java`	Updates V30 annotation.
`udt/records/ListOffsetsRequestV1Record.java`	Updates V30 annotation.
`udt/records/InitDisklessLogResponseV1Record.java`	Updates V30 annotation.
`udt/records/InitDisklessLogRequestV1Record.java`	Updates V30 annotation.
`udt/records/InitDisklessLogProducerStateV1Record.java`	Updates V30 annotation.
`udt/records/FindBatchesResponseV1Record.java`	Updates V30 annotation.
`udt/records/FindBatchesRequestV1Record.java`	Updates V30 annotation.
`udt/records/EnforceRetentionResponseV1Record.java`	Updates V30 annotation.
`udt/records/EnforceRetentionRequestV1Record.java`	Updates V30 annotation.
`udt/records/DeleteRecordsResponseV1Record.java`	Updates V30 annotation.
`udt/records/DeleteRecordsRequestV1Record.java`	Updates V30 annotation.
`udt/records/CommitBatchResponseV1Record.java`	Updates V30 annotation.
`udt/records/CommitBatchRequestV1Record.java`	Updates V30 annotation.
`udt/records/BatchMetadataV1Record.java`	Updates V30 annotation.
`udt/records/BatchInfoV1Record.java`	Updates V30 annotation.
`udt/records/AdvanceCrossTierLogStartResponseV1Record.java`	Updates V30 annotation.
`udt/records/AdvanceCrossTierLogStartRequestV1Record.java`	Updates V30 annotation.
`udt/PurgeDeletedLogsResponseV1.java`	Updates V30 annotation.
`udt/PruneBatchesBelowHighestTieredOffsetResponseV1.java`	Updates V30 annotation.
`udt/PruneBatchesBelowHighestTieredOffsetRequestV1.java`	Updates V30 annotation.
`udt/paths/RepairDisklessLogResponseV1Path.java`	Updates V30 annotation.
`udt/paths/RepairDisklessLogRequestV1Path.java`	Updates V30 annotation.
`udt/paths/PurgeDeletedLogsResponseV1Path.java`	Updates V30 annotation.
`udt/paths/PruneBatchesBelowHighestTieredOffsetResponseV1Path.java`	Updates V30 annotation.
`udt/paths/PruneBatchesBelowHighestTieredOffsetRequestV1Path.java`	Updates V30 annotation.
`udt/paths/ListOffsetsResponseV1Path.java`	Updates V30 annotation.
`udt/paths/ListOffsetsRequestV1Path.java`	Updates V30 annotation.
`udt/paths/InitDisklessLogResponseV1Path.java`	Updates V30 annotation.
`udt/paths/InitDisklessLogRequestV1Path.java`	Updates V30 annotation.
`udt/paths/InitDisklessLogProducerStateV1Path.java`	Updates V30 annotation.
`udt/paths/FindBatchesResponseV1Path.java`	Updates V30 annotation.
`udt/paths/FindBatchesRequestV1Path.java`	Updates V30 annotation.
`udt/paths/EnforceRetentionResponseV1Path.java`	Updates V30 annotation.
`udt/paths/EnforceRetentionRequestV1Path.java`	Updates V30 annotation.
`udt/paths/DeleteRecordsResponseV1Path.java`	Updates V30 annotation.
`udt/paths/DeleteRecordsRequestV1Path.java`	Updates V30 annotation.
`udt/paths/CommitBatchResponseV1Path.java`	Updates V30 annotation.
`udt/paths/CommitBatchRequestV1Path.java`	Updates V30 annotation.
`udt/paths/BatchMetadataV1Path.java`	Updates V30 annotation.
`udt/paths/BatchInfoV1Path.java`	Updates V30 annotation.
`udt/paths/AdvanceCrossTierLogStartResponseV1Path.java`	Updates V30 annotation.
`udt/paths/AdvanceCrossTierLogStartRequestV1Path.java`	Updates V30 annotation.
`udt/ListOffsetsResponseV1.java`	Updates V30 annotation.
`udt/ListOffsetsRequestV1.java`	Updates V30 annotation.
`udt/InitDisklessLogResponseV1.java`	Updates V30 annotation.
`udt/InitDisklessLogRequestV1.java`	Updates V30 annotation.
`udt/InitDisklessLogProducerStateV1.java`	Updates V30 annotation.
`udt/FindBatchesResponseV1.java`	Updates V30 annotation.
`udt/FindBatchesRequestV1.java`	Updates V30 annotation.
`udt/EnforceRetentionResponseV1.java`	Updates V30 annotation.
`udt/EnforceRetentionRequestV1.java`	Updates V30 annotation.
`udt/DeleteRecordsResponseV1.java`	Updates V30 annotation.
`udt/DeleteRecordsRequestV1.java`	Updates V30 annotation.
`udt/CommitBatchResponseV1.java`	Updates V30 annotation.
`udt/CommitBatchRequestV1.java`	Updates V30 annotation.
`udt/BatchMetadataV1.java`	Updates V30 annotation.
`udt/BatchInfoV1.java`	Updates V30 annotation.
`udt/AdvanceCrossTierLogStartResponseV1.java`	Updates V30 annotation.
`udt/AdvanceCrossTierLogStartRequestV1.java`	Updates V30 annotation.
`tables/RepairDisklessLogV1.java`	Updates V30 annotation.
`tables/records/RepairDisklessLogV1Record.java`	Updates V30 annotation.
`tables/records/PurgeDeletedLogsV1Record.java`	Updates V30 annotation.
`tables/records/PruneBatchesBelowHighestTieredOffsetV1Record.java`	Updates V30 annotation.
`tables/records/ProducerStateRecord.java`	Updates V30 annotation.
`tables/records/LogsRecord.java`	Updates V30 annotation.
`tables/records/ListOffsetsV1Record.java`	Updates V30 annotation.
`tables/records/InitDisklessLogV1Record.java`	Updates V30 annotation.
`tables/records/FindBatchesV2Record.java`	Updates V30 annotation.
`tables/records/FindBatchesV1Record.java`	Updates V30 annotation.
`tables/records/FilesRecord.java`	Updates V30 annotation.
`tables/records/EnforceRetentionV2Record.java`	Updates V30 annotation.
`tables/records/EnforceRetentionV1Record.java`	Updates V30 annotation.
`tables/records/DeleteRecordsV1Record.java`	Updates V30 annotation.
`tables/records/CommitFileV2Record.java`	Updates V30 annotation.
`tables/records/CommitFileV1Record.java`	Updates V30 annotation.
`tables/records/BatchesRecord.java`	Updates V30 annotation.
`tables/records/AdvanceCrossTierLogStartV1Record.java`	Updates V30 annotation.
`tables/PurgeDeletedLogsV1.java`	Updates V30 annotation.
`tables/PruneBatchesBelowHighestTieredOffsetV1.java`	Updates V30 annotation.
`tables/ProducerState.java`	Updates V30 annotation.
`tables/Logs.java`	Updates V30 annotations.
`tables/ListOffsetsV1.java`	Updates V30 annotation.
`tables/InitDisklessLogV1.java`	Updates V30 annotation.
`tables/FindBatchesV2.java`	Updates V30 annotation.
`tables/FindBatchesV1.java`	Updates V30 annotation.
`tables/Files.java`	Updates V30 annotations.
`tables/EnforceRetentionV2.java`	Updates V30 annotation.
`tables/EnforceRetentionV1.java`	Updates V30 annotation.
`tables/DeleteRecordsV1.java`	Updates V30 annotation.
`tables/CommitFileV2.java`	Updates V30 annotation.
`tables/CommitFileV1.java`	Updates V30 annotation.
`tables/Batches.java`	Updates V30 annotations.
`tables/AdvanceCrossTierLogStartV1.java`	Updates V30 annotation.
`Tables.java`	Updates V30 annotation.
`routines/MarkFileToDeleteV1.java`	Updates V30 annotation.
`routines/FlushCommitRunV2.java`	Updates V30 annotation.
`routines/DeleteTopicV1.java`	Updates V30 annotation.
`routines/DeleteFilesV1.java`	Updates V30 annotation.
`routines/DeleteBatchV1.java`	Updates V30 annotation.
`routines/BatchTimestamp.java`	Updates V30 annotation.
`Routines.java`	Updates V30 annotation.
`Keys.java`	Updates V30 annotation.
`Indexes.java`	Updates V30 annotation.
`enums/PruneBatchesBelowHighestTieredOffsetErrorV1.java`	Updates V30 annotation.
`enums/ListOffsetsResponseErrorV1.java`	Updates V30 annotation.
`enums/InitDisklessLogErrorV1.java`	Updates V30 annotation.
`enums/FindBatchesResponseErrorV1.java`	Updates V30 annotation.
`enums/FileStateT.java`	Updates V30 annotation.
`enums/FileReasonT.java`	Updates V30 annotation.
`enums/EnforceRetentionResponseErrorV1.java`	Updates V30 annotation.
`enums/DeleteRecordsResponseErrorV1.java`	Updates V30 annotation.
`enums/CommitBatchResponseErrorV1.java`	Updates V30 annotation.
`enums/AdvanceCrossTierLogStartResponseErrorV1.java`	Updates V30 annotation.
`Domains.java`	Updates V30 annotation.
`DefaultSchema.java`	Updates V30 annotation.

🧠 Review effort: Balanced

💡 Add a code-review agent skill or configure MCP servers for context-aware, tailored reviews. Learn more in the docs.

…elease The index added for list_offsets_v1 changes the plan of retention's DELETEs. Applying it by hand on a large control plane stalled produce until it was dropped. The DELETEs in delete_records_v1, the prune function and the purge function filter on (topic_id, partition) and a last_offset range. The planner estimates that range from the table-wide histogram of last_offset, so on a partition whose offsets are at the top of the histogram the range looks unselective. It then compares full-partition scans and picks the smaller index. The timestamp index shares the (topic_id, partition) prefix but cannot filter on last_offset, so the scan fetches the heap row of every batch of the partition while enforce_retention_v2 holds the logs row locks that commit_file waits for. The retention recount and cap check switch the same way in the test fixture. EXPLAIN (GENERIC_PLAN) does not show it, because it estimates the range with a fixed selectivity. Remove V30 and renumber the list_offsets_v1 rewrite to V30. Nothing was released with the old numbers. The rewrite does not need the index: MIN(base_offset) reads the partition's entries of batches_by_last_offset_covering_idx, which stores the timestamp columns. The scan is index-only on pages the visibility map marks all-visible, and its cost follows the partition, not the table. A request at or before the oldest retained batch is answered from the first entry. Without the timestamp index, the scan evaluates batch_timestamp for every entry of the partition. V30 redefines it in SQL, so the planner inlines it into a CASE instead of calling PL/pgSQL per entry. Callers get the same result. This cuts the scan by about 3x. Databases built from main since V30 was merged have the index and a history row for the old V31. They need a reset or a flyway repair, and DROP INDEX CONCURRENTLY IF EXISTS batches_by_timestamp_idx. Testing BatchesPlanRegressionTest loads a skewed fixture: many low-offset partitions and a few near 10^10, interleaved in the heap, with the covering index at the default fill factor and rebuilt at a low one to emulate bloat. Statistics are gathered from every row, so the plans do not vary between runs. It captures the plan of the retention and read statements on the baseline schema, applies the remaining migrations, and requires the same plans. A second case creates the dropped index by hand and requires the plans to change, so the check cannot pass vacuously. Without the index, V30 passes. With the index, the check fails for both fill factors. The plans come from EXPLAIN with literal values, so they do not show how PL/pgSQL plans inside the functions. ListOffsetsTimestampScanBenchmarkTest interleaves 8 partitions, so the baseline walk of batches_pkey crosses other partitions' rows, runs VACUUM at each depth so index-only scans apply, and compares the baseline with the rewrite. With 100k batches per partition, the rewrite takes 8 ms against 73 ms for a seek near the end, 9 ms against 80 ms for a seek past the end, and 15 ms against 135 ms for MAX_TIMESTAMP. Bulk and small commits, WAL per batch, and the size of batches and its indexes are unchanged. Assisted-by: Claude Sonnet 5.5

Copilot

🔵 Needs a closer look

Production-sensitive PostgreSQL plans and migration-history repair need human validation before rollout.

0 open findings

2 resolved since last review

🧠 Review effort: Balanced

…elease (#828) The index added for list_offsets_v1 changes the plan of retention's DELETEs. Applying it by hand on a large control plane stalled produce until it was dropped. The DELETEs in delete_records_v1, the prune function and the purge function filter on (topic_id, partition) and a last_offset range. The planner estimates that range from the table-wide histogram of last_offset, so on a partition whose offsets are at the top of the histogram the range looks unselective. It then compares full-partition scans and picks the smaller index. The timestamp index shares the (topic_id, partition) prefix but cannot filter on last_offset, so the scan fetches the heap row of every batch of the partition while enforce_retention_v2 holds the logs row locks that commit_file waits for. The retention recount and cap check switch the same way in the test fixture. EXPLAIN (GENERIC_PLAN) does not show it, because it estimates the range with a fixed selectivity. Remove V30 and renumber the list_offsets_v1 rewrite to V30. Nothing was released with the old numbers. The rewrite does not need the index: MIN(base_offset) reads the partition's entries of batches_by_last_offset_covering_idx, which stores the timestamp columns. The scan is index-only on pages the visibility map marks all-visible, and its cost follows the partition, not the table. A request at or before the oldest retained batch is answered from the first entry. Without the timestamp index, the scan evaluates batch_timestamp for every entry of the partition. V30 redefines it in SQL, so the planner inlines it into a CASE instead of calling PL/pgSQL per entry. Callers get the same result. This cuts the scan by about 3x. Databases built from main since V30 was merged have the index and a history row for the old V31. They need a reset or a flyway repair, and DROP INDEX CONCURRENTLY IF EXISTS batches_by_timestamp_idx. Testing BatchesPlanRegressionTest loads a skewed fixture: many low-offset partitions and a few near 10^10, interleaved in the heap, with the covering index at the default fill factor and rebuilt at a low one to emulate bloat. Statistics are gathered from every row, so the plans do not vary between runs. It captures the plan of the retention and read statements on the baseline schema, applies the remaining migrations, and requires the same plans. A second case creates the dropped index by hand and requires the plans to change, so the check cannot pass vacuously. Without the index, V30 passes. With the index, the check fails for both fill factors. The plans come from EXPLAIN with literal values, so they do not show how PL/pgSQL plans inside the functions. ListOffsetsTimestampScanBenchmarkTest interleaves 8 partitions, so the baseline walk of batches_pkey crosses other partitions' rows, runs VACUUM at each depth so index-only scans apply, and compares the baseline with the rewrite. With 100k batches per partition, the rewrite takes 8 ms against 73 ms for a seek near the end, 9 ms against 80 ms for a seek past the end, and 15 ms against 135 ms for MAX_TIMESTAMP. Bulk and small commits, WAL per batch, and the size of batches and its indexes are unchanged. Assisted-by: Claude Sonnet 5.5

jeqo changed the title ~~fix(inkless:control-plane): drop the batches timestamp index~~ fix(inkless:control-plane): drop the batches timestamp index [KC-612] Oct 15, 2026

jeqo requested a balanced review from Copilot October 15, 2026 13:14

Copilot started reviewing on behalf of jeqo October 15, 2026 13:15

View session

Copilot AI reviewed Oct 15, 2026

View reviewed changes

Comment thread ...inkless/src/test/java/io/aiven/inkless/control_plane/postgres/BatchesPlanRegressionTest.java Resolved

Comment thread ...inkless/src/test/java/io/aiven/inkless/control_plane/postgres/BatchesPlanRegressionTest.java Resolved

jeqo force-pushed the jeqo/drop-batches-timestamp-index branch from a04e410 to 0fc50b9 Compare October 16, 2026 09:26

jeqo requested a balanced review from Copilot October 16, 2026 09:29

Copilot started reviewing on behalf of jeqo October 16, 2026 09:29

View session

Copilot AI reviewed Oct 16, 2026

View reviewed changes

jeqo marked this pull request as ready for review October 16, 2026 10:13

jeqo requested a review from viktorsomogyi October 16, 2026 10:14

viktorsomogyi approved these changes Oct 16, 2026

View reviewed changes

viktorsomogyi merged commit 6952e9b into main Oct 16, 2026
13 checks passed

viktorsomogyi deleted the jeqo/drop-batches-timestamp-index branch October 16, 2026 14:37

jeqo mentioned this pull request Oct 19, 2026

docs(inkless:release): add 0.50 changelog and cherry-pick sessions #830

Merged

Navigation Menu

Repository navigation

fix(inkless:control-plane): drop the batches timest 8000 amp index [KC-612] - #828

fix(inkless:control-plane): drop the batches timestamp index [KC-612]#828
viktorsomogyi merged 1 commit into
mainfrom
jeqo/drop-batches-timestamp-index

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Conversation

Impact of the revert

Requests affected

What changes against #799 and against main before it

Workarounds

Mitigation for KC-537 stays

Databases that applied main's V30 and V31

Testing

Follow-ups

Uh oh!

Choose a reason for hiding this comment

🟡 Changes recommended

Uh oh!

Uh oh!

Uh oh!

Choose a reason for hiding this comment

🔵 Needs a closer look

Uh oh!

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants