apache / apache/gravitino

[Improvement] [MINOR] docs(iceberg): document jdbc.options for PostgreSQL plan_cache_mode tuning

Open Beginner friendly
#12,492 1 comment 0 reactions 0 assignees View on GitHub
improvement
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.