[Improvement] [MINOR] docs(iceberg): document jdbc.options for PostgreSQL plan_cache_mode tuning
- Dominant language
- Java
- Stars
- 3.2k
- Forks
- 935
- Avg merge
- 1d 17h
- Merged PRs (30d)
- 339
Description
### What would you like to be improved?
PostgreSQL automatically downgrades a repeatedly-executed prepared statement from a per-call
"custom plan" (built using the actual bind parameter values) to a cached "generic plan" (built
without them) after its 5th execution, when the generic plan's estimated cost looks close enough
to the custom plan's. `JdbcCatalog`'s namespace/table existence check - used on essentially every
request - re-executes the same prepared statement over and over, so it crosses this threshold
almost immediately in any real deployment.
We hit this directly while load-testing a JDBC-backed Iceberg REST catalog (Gravitino) against
PostgreSQL 16 at 100,000 tables. Confirmed via `pg_stat_statements`
(`shared_preload_libraries='pg_stat_statements'`, aggregates real traffic with exact timings): the
*exact same, already-indexed* query was averaging **55.7ms** under real application traffic, vs.
~0.1ms when run as a one-off `EXPLAIN ANALYZE`/`psql` test - a >500x gap explained entirely by the
custom-plan → generic-plan downgrade, reproduced directly via `psql`'s own `PREPARE`/`EXECUTE`.
Setting `plan_cache_mode=force_custom_plan` closed that gap immediately: end-to-end
`NAMESPACE_CREATE`/`NAMESPACE_DROP` benchmark throughput went from ~1.4 rps (pinned there since
the very first empty-catalog runs, long before this was root-caused) to ~95-100 rps.
This is a real, silent trap: nothing about the slow path produces an error or a log line pointing
at the cause, small/lightly-loaded catalogs don't reliably trigger it during initial testing (the
5-execution threshold can be crossed or not depending on how the app happens to batch/order its
first few requests), and the standard fix (`ALTER DATABASE ... SET plan_cache_mode =
force_custom_plan`) requires database-owner/superuser privileges and affects every other
application sharing that database - which is why this doc calls out the driver-level `options`
connection property instead: it achieves the identical session-level effect, but scoped only to
Gravitino's own connections, with no elevated privileges required.
### How should we improve?
Adds a short subsection to `docs/iceberg-rest-service.md`, under **Additional Iceberg Catalog
Properties**, documenting a specific, high-value use of that section's already-existing
pass-through mechanism: setting
```
gravitino.iceberg-rest.jdbc.options = -c plan_cache_mode=force_custom_plan
```
for JDBC catalog backends running on PostgreSQL at scale. No code changes - this property already
works today, it just isn't documented anywhere as a recommended production setting.
Contributor guide
Research direction
Start in docs/iceberg-rest-service.md and locate the Additional Iceberg Catalog Properties section and its existing JDBC pass-through documentation. Add the PostgreSQL plan_cache_mode example using gravitino.iceberg-rest.jdbc.options, then verify that the subsection clearly explains the production use case and session-scoped setting.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java, postgresql
- Domain
- databases, documentation
- Issue type
- Documentation
- Difficulty
- 1/5
- Estimated time
- Under an hour
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 90/100