[Improvement] Create supporting index on iceberg_tables for PostgreSQL JDBC catalogs at scale
- Dominant language
- Java
- Stars
- 3.2k
- Forks
- 935
- Avg merge
- 1d 16h
- Merged PRs (30d)
- 298
Description
### What would you like to be improved?
`JdbcCatalog`'s namespace/table existence checks (used by namespace create/drop, and by every
table create/drop/exists check) query `iceberg_tables` with a predicate of the shape:
```sql
... WHERE catalog_name = ? AND (table_namespace = ? OR table_namespace LIKE ?)
```
`iceberg_tables`'s only index is its primary key, `(catalog_name, table_namespace, table_name)`.
Under PostgreSQL's default locale-aware b-tree operator class, a b-tree index cannot bound a
`LIKE`-prefix range scan (the sort order of a locale-collated index does not match byte-prefix
order), so this predicate degrades to a sequential scan of the entire table once it holds a
non-trivial number of rows. This isn't a hypothetical: we measured it directly.
Load-testing a JDBC-backed Iceberg REST catalog (Gravitino) at 100,000 tables across 1,000
namespaces on PostgreSQL 16, we found:
- `EXPLAIN ANALYZE` on the live query: ~19.5ms per call (sequential scan), vs. ~0.1-0.2ms after
adding this index.
- End-to-end benchmark throughput for `NAMESPACE_CREATE`/`NAMESPACE_DROP` recovered by
**100-570x** after adding the index (from ~1.2-1.5 rps to consistent with other operations);
`TABLE_LIST` improved 113-329x; `TABLE_CREATE`/`TABLE_DROP` improved ~4x.
- The degradation is *silent* - it doesn't show up at small scale (empty or lightly-loaded
catalogs never trigger a sequential scan slow enough to notice), only appears once a catalog
accumulates enough tables, and gets linearly worse from there. Anyone running a JDBC-backed
Iceberg REST catalog against PostgreSQL in production at meaningful scale is likely paying this
cost today without realizing it, because nothing about it produces an error - just a slow,
worsening namespace/table existence check.
### How should we improve?
Patched `JdbcCatalogWithMetadataLocationSupport` (the JDBC catalog implementation the Iceberg REST
server uses for the `jdbc` backend) now creates a supporting index on the shared `iceberg_tables`
control table right after schema initialization, when the backend is PostgreSQL:
```sql
CREATE INDEX IF NOT EXISTS gravitino_iceberg_tables_namespace_pattern
ON iceberg_tables (catalog_name, table_namespace text_pattern_ops)
```
This is gated by a new property, `jdbc.create-namespace-index` (`gravitino.iceberg-rest.jdbc.create-namespace-index`
in the REST server config), default `true`. Detection of PostgreSQL is done at runtime via
`DatabaseMetaData#getDatabaseProductName()`, so this is a no-op for MySQL, SQLite, H2, and any
other JDBC backend - no behavior changes for non-PostgreSQL deployments. If index creation fails
for any reason (e.g. the configured database role lacks `CREATE INDEX` privileges), a warning is
logged and catalog initialization proceeds normally; this is a performance fix, not a correctness
one, so it must never block startup.
Contributor guide
Research direction
Start in JdbcCatalogWithMetadataLocationSupport at schema initialization and trace the existing JDBC configuration and DatabaseMetaData handling. Check how jdbc.create-namespace-index is exposed, then verify PostgreSQL-only index creation, warning-only failure handling, and no behavior change for other JDBC backends. Done means initialization remains successful when index creation is unavailable.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java, postgresql
- Domain
- backend, databases
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 68/100