PERF-SQL-002 — Manage Hive Metastore capacity and availability end-to-end

Field	Value
Status	Open finding; deployed topology and source verified, load, failover, and recovery behavior not tested
Severity	High
Affected surface	Catalog browsing, SQL planning, DDL, Spark session startup, concurrent compute clusters, metadata recovery, and Iceberg readiness
Implementation issue	Not opened
Components	sparqd/platform-stacks; Hive Metastore; KubeBlocks PostgreSQL; Kubernetes; Prometheus; Trino, Spark, notebook, workflow, and data-quality consumers

Where the problem appears

Hive Metastore is a shared metadata plane for Trino and Spark consumers. Adding query workers, Trino clusters, notebooks, or workflows can increase concurrent schema, table, partition, statistics, and transaction requests even when the underlying data scan capacity is isolated.

Concrete observed case

On 3 September 2026, the dev-north-alicloud deployment used by realm gj76anadpsc, workspace w-t5e2kcxjd, had:

• two fixed Hive Metastore replicas behind one Kubernetes Service;
• no HMS autoscaler, PodDisruptionBudget, affinity, or topology-spread rule;
• one PostgreSQL replica with 20 GiB storage, requests of 250 millicores and 256 MiB, and limits of 500 millicores and 512 MiB;
• HMS connecting directly to the PostgreSQL primary service on port 5432;
• a PostgreSQL service port and configuration for PgBouncer, but no use of that pooled endpoint by HMS and no explicit HMS pool budget in reviewed source;
• HMS JMX monitoring enabled, while the PostgreSQL exporter was disabled.

Platform source already defines an installer-facing Size selector with small, medium, large, and xlarge options. It changes fixed HMS replicas, CPU, memory, and JVM heap. It does not size PostgreSQL resources, replicas, storage, or connection capacity with HMS, and a safe post-deployment resize workflow was not evidenced.

Both HMS pods were healthy and happened to run on different nodes. This is a topology observation, not evidence of an enforced disruption or zone boundary.

Expected condition

Metadata capacity and availability should be managed as one dependency chain:

1. Keep at least two stateless HMS replicas and enforce failure-domain placement and disruption protection.
2. Expose an administrator-facing metadata capacity profile. Each supported tier must jointly size HMS, PostgreSQL, pool and connection limits, and storage; publish its workload or partition boundary; show the current tier; and provide observable apply, failure, and rollback states.
3. Use managed HMS autoscaling when reliable signals and safe database headroom exist. When autoscaling is unavailable or unsafe, allow an administrator to change the fixed profile without exposing raw pods, JVM, or database knobs.
4. Set a total database connection budget across all HMS replicas and clients; use and verify pooling where it improves connection management.
5. Provide a highly available PostgreSQL topology or managed equivalent with measured failover, durable backups, point-in-time recovery where required, storage growth and exhaustion controls, and tested schema-upgrade recovery.
6. Monitor HMS request latency, errors, threads, and pool use together with PostgreSQL connections, transactions, locks, latency, CPU, memory, storage, replication, backup age, and restore success.
7. Surface metadata unavailable or overloaded distinctly from query execution failure, with safe retry guidance.

Cause and evidence

• The installer exposes a fixed HMS Size preset providing one, two, or four HMS replicas; no HMS HPA is declared. The preset does not scale the database tier with the service tier.
• Apache Hive documents the remote metastore as stateless and supports multiple service instances. The current Kubernetes Service distributes traffic across two endpoints, so the primary unresolved bottleneck is not lack of process replication alone.
• The backing PostgreSQL deployment has one small fixed replica and is reached directly rather than through the available pooled service port.
• Database exporter configuration is disabled, and no explicit backup, retention, restore, connection-limit, or capacity contract was identified in the reviewed feature source.
• Trino metadata caches can reduce repeated lookups, but cache misses, invalidation, DDL, partition-heavy planning, and Spark consumers still reach the shared metadata plane.

Why it matters

Scaling HMS replicas without budgeting their database pools can create more connections and move the bottleneck to PostgreSQL. A slow or unavailable metadata database can block catalog browsing and query planning across every Trino cluster and Spark consumer at once. A single database replica also makes future multi-compute isolation incomplete: compute failures are isolated, but metadata failure is still shared.

The same concern applies if an Iceberg connector later uses this HMS. Adding a connector or compute cluster is therefore a capacity input, not just a catalog configuration change.

Existing controls and workarounds

• Deployment administrators can choose a fixed HMS Size preset with one, two, or four replicas.
• Kubernetes Service routing distributes calls across ready HMS pods.
• HMS JMX metrics and a ServiceMonitor are configured.
• Trino metadata caching reduces some repeated calls.
• KubeBlocks exposes PostgreSQL and PgBouncer service ports.

These controls do not prove database HA, pooled client use, a safe aggregate connection budget, elastic capacity, backup recovery, or overload behavior.

Alternatives considered

1. Recommended minimum: expand the existing Size selector into a logical, administrator-facing metadata capacity profile that jointly covers HMS, PostgreSQL, pooling, storage, HA, and observable resize/rollback behavior.
2. Add managed HMS autoscaling after saturation signals and database headroom are proven. This improves elasticity but is not required when safe scaling signals or database coordination are unavailable.
3. Increase only the HMS replica preset. Rejected as a complete solution because every replica can add database connections while the single database remains the shared bottleneck.
4. Increase only PostgreSQL resources. This may raise the ceiling but does not provide failover, restore evidence, HMS elasticity, or overload feedback.
5. Replace HMS with another catalog service. This is a larger migration and is unnecessary unless feature, interoperability, or operating-cost requirements cannot be met by a properly operated HMS deployment.

Component involvement

• Platform stacks currently defines the installer-facing fixed HMS Size presets, Kubernetes Service, one PostgreSQL replica, storage, HMS metrics, and direct database routing. The profile would need to cover both tiers and add approved resize, rollback, HA, pooling, placement, backup, and telemetry behavior.
• Hive Metastore supplies the stateless metadata API and its service metrics.
• KubeBlocks PostgreSQL supplies persistence and would need a validated HA, connection, backup, restore, and storage-growth configuration.
• Trino and Spark consumers need bounded metadata retries, caches, timeouts, and distinguishable failure states.
• Oqullus and its BFF dependencies should present catalog or metadata unavailability without mislabeling it as a completed query failure.

Reproduction and verification

Follow SQLC-S051. Current topology is verified without load or disruption. Capacity, failover, and recovery claims require an approved environment, bounded metadata-heavy fixtures, and restorable test data.

Resolution conditions

Close when metadata SLOs and capacity budgets are documented; an administrator can inspect and safely change an end-to-end metadata capacity profile; HMS placement, disruption, optional autoscaling, and aggregate database connections are controlled; PostgreSQL HA, storage, backup, restore, and upgrade recovery are tested; both tiers are observable; and Trino/Spark clients have verified overload and failure behavior.

Validation and blockers

Metadata request rates, partition-cardinality limits, connection-pool behavior, PostgreSQL maximum connections, failover time, backup age, restore time and point, storage-growth rate, schema-upgrade recovery, alerts, and runbooks remain unverified.