[Performance] Partitioned filter causes N+1 catalog listings for cold or uncached buckets

[hsballoon]

Description

When HashPartitionTable.filter() scans multiple physical partitions, dldb repeatedly calls db_conn.list_tables() while opening the buckets. This cause extremly slow response to the caller. 1w+ items costs 7-8mins.

For an S3-backed database, list_tables() appears to enumerate the database catalog/root prefix. A single catalog listing currently takes approximately 16–17 seconds in our environment. Repeating it once per physical bucket amplifies a normal query into a multi-minute operation.

This affects both full-table scans and explicitly pruned scans when the relevant bucket objects have not yet been cached.

Environment

• dldb: 1.0.0
• Backend: LanceDB on S3-compatible object storage
• Logical table partitioning: HASH(job_id), 128 configured buckets
• Example table: 23 currently materialized physical buckets

In a local call-count reproduction with 23 materialized buckets:

Cold full scan:       24 list_tables() calls
Warm full scan:        1 list_tables() call
Warm explicit scan:    0 list_tables() calls

With a catalog listing latency of approximately 17 seconds, the cold full-scan path can spend roughly:

24 × 17 seconds ≈ 408 seconds

before accounting for the actual Lance row scan.

When It Is Triggered

The severe amplification occurs when all of the following are true:

1. The logical table uses HASH or VALUE partitioning.
2. filter() needs to scan multiple physical partitions.
3. The physical bucket objects are not already cached in the current table wrapper.
4. The catalog is backed by object storage where list_tables() is relatively expensive.

Typical triggers include:

• the first query after a process restart;
• a newly created dldb client or logical table wrapper;
• the first access to previously unopened buckets;
• cache invalidation after a table is recreated or replaced;
• services that create a new client for each request;
• full-table filters without a partition-key predicate;
• explicit multi-bucket filters where some buckets are uncached.

The existing opened-bucket cache avoids repeated listings after buckets have been opened. However, it does not eliminate the cold-path amplification. The improvement related to #79 therefore appears to cover cached bucket reuse, but not the initial multi-bucket opening path.

Current Execution Flow

HashPartitionTable.filter(partitions=None)

The current flow is effectively:

filter(partitions=None)
  -> list_partitions()
       -> db_conn.list_tables()                 # 1 catalog listing

  -> for each materialized partition:
       -> open_table([partition])
            -> bucket is not cached
            -> db_conn.list_tables()             # 1 listing per bucket
            -> db_conn.open_table(bucket)

For N uncached materialized buckets, this results in approximately:

N + 1 catalog listings

HashPartitionTable.filter(partitions=[...])

For explicitly selected HASH buckets, the uncached path first calls list_partitions() to reject valid-but-unmaterialized buckets, then calls open_table([partition]) inside the loop.

For K uncached selected buckets, the current path may therefore perform approximately:

K + 1 catalog listings

ValuePartitionTable.filter()

ValuePartitionTable.filter() has the same per-partition open_table([partition]) loop. A cold scan therefore has the same general amplification problem.

Warm Cache Behavior

open_table() returns immediately when all requested partitions already exist in self.tables.

Therefore:

• a warm explicit-partition query may perform zero catalog listings;
• a warm full scan still calls list_partitions() once to discover current partitions;
• the worst N+1 behavior mainly appears during cold or partially warmed access.

Why dldb Lists the Database Root

A dldb partitioned logical table is represented by multiple physical LanceDB tables. The logical metadata defines the partitioning scheme, such as HASH(job_id) with 128 possible buckets, but physical bucket tables are materialized lazily.

dldb therefore calls db_conn.list_tables() to discover which physical bucket tables currently exist. On an object-store-backed LanceDB database, this appears to require listing the database catalog/root prefix.

This discovery requirement is understandable, but the same catalog result should not be fetched again for every bucket in one operation.

There are consequently two related but distinct performance issues:

1. A single LanceDB/S3 catalog listing is relatively expensive.
2. dldb multiplies that cost by repeatedly listing the same unchanged catalog within one filter() call.

Fixing the dldb amplification is useful even if the underlying single-listing latency remains unchanged.

Please also confirm whether LanceDB must perform a root-prefix listing on every list_tables() call for this storage backend, or whether a catalog API/cache can avoid repeated object-store listings.

Impact

SAfactory Data Rollout

Data Platform Dashboard

Operational Related Queries

Expected Behavior

A single logical operation should reuse one catalog snapshot when discovering and opening multiple partitions.

Suggested approaches:

1. Call open_table(partitions) once before iterating over the partitions instead of calling open_table([partition]) inside the loop.
2. Reuse the table-name set already obtained by list_partitions() when opening the discovered buckets.
3. Allow open_table() to accept a known catalog/table-name snapshot.
4. Optionally add a bounded or generation-aware catalog cache, while preserving discovery of newly materialized buckets.
5. Apply the same correction to both HASH and VALUE partitioned tables.

A minimal improvement would reduce a cold full scan from approximately:

N + 1 listings

to:

2 listings

A better implementation that passes the discovery snapshot into the batch-open operation should require only:

1 listing

Acceptance Criteria

• A cold full-table HASH filter() performs at most one catalog listing for partition discovery and opening.
• A cold explicit multi-bucket filter does not list the catalog once per bucket.
• ValuePartitionTable.filter() receives equivalent behavior.
• Warm explicit-bucket queries continue to avoid catalog listing when all requested buckets are cached.
• Newly materialized buckets remain discoverable.
• Concurrent bucket creation does not cause valid buckets to be skipped or accidentally create incorrect physical tables.
• Existing filtering, limits, ordering, and checkout_latest behavior remain unchanged.
• Add a regression test that counts db_conn.list_tables() calls for cold and warm multi-partition filters.