On-Premise DataHub: Elasticsearch and PostgreSQL Configuration Best Practices – DataHub Customer Support Portal

Area: Best Practices

Sub-Area: Self-Hosted Infrastructure — Storage, Observability, and Query Logging

Issue

Operators running self-hosted (on-premise) DataHub deployments are responsible for provisioning, scaling, and monitoring their own Elasticsearch and PostgreSQL infrastructure. Without proactive configuration of storage thresholds, node-level health metrics, and slow query logging, clusters can silently degrade — resulting in failed writes, undetected performance bottlenecks, and limited visibility during incidents. Unlike DataHub Cloud, self-hosted environments do not benefit from automated scaling or built-in infrastructure monitoring, making these operational configurations essential from the earliest stages of deployment.

You Might Be Asking

How much free disk space should I maintain on my Elasticsearch cluster when running DataHub?
What happens if Elasticsearch disk usage exceeds the built-in watermark thresholds?
How do I enable slow query and slow shard logging in Elasticsearch for DataHub indices?
How do I enable slow query logging in PostgreSQL (including Aurora PostgreSQL) for a DataHub deployment?
How do I gain visibility into node-level Elasticsearch health metrics in a self-hosted environment?

Solution

1. Expand Elasticsearch Storage Proactively

Elasticsearch enforces disk-based allocation watermarks that can cause write failures if free space is insufficient. DataHub's cleanup and index rollover operations cause temporary spikes in disk usage, so maintaining only ~30% free headroom leaves very little buffer before Elasticsearch applies automatic protections:

Low watermark (85% used): Stops allocating new shards to the affected node.
High watermark (90% used): Begins relocating existing shards away from the affected node.
Flood stage (95% used): Transitions all indices to read-only mode — writes will fail.

Recommendation: Maintain at least 20–30% free disk headroom as a sustained baseline, not a minimum floor. If running Elasticsearch on AWS with EBS-backed volumes, a volume resize can typically be performed with no downtime.

Verify current watermark settings and disk usage with the following API calls:

# Check current disk allocation watermarks
GET /_cluster/settings?include_defaults=true&filter_path=*.cluster.routing.allocation.disk

# Check per-node disk usage
GET /_cat/allocation?v&h=node,disk.used,disk.avail,disk.percent,shards

To temporarily adjust watermarks (apply permanently via elasticsearch.yml or ILM policy review):

PUT /_cluster/settings
{
  "transient": {
    "cluster.routing.allocation.disk.watermark.low": "80%",
    "cluster.routing.allocation.disk.watermark.high": "85%",
    "cluster.routing.allocation.disk.watermark.flood_stage": "90%"
  }
}

2. Enable Node-Level Health Metrics Visibility

Node-level metrics — including heap usage, JVM garbage collection pressure, shard count, and disk I/O — are critical for proactive incident prevention. Inability to view these metrics in a production environment (while lower environments are accessible) typically indicates an RBAC permission gap or a network policy difference, not a missing Elasticsearch configuration.

Recommended approaches:

Prometheus Elasticsearch Exporter: The preferred path for organizations already using Prometheus/Grafana. Scrapes the _nodes/stats API and exposes metrics in Prometheus format.
X-Pack Monitoring (Elastic Stack): Routes cluster metrics to a dedicated monitoring cluster or Kibana Stack Monitoring UI.
OpenSearch Dashboards: If running OpenSearch, enable the built-in monitoring plugin under Management → Stack Management → Monitoring.

At a minimum, confirm the following APIs are reachable from your monitoring tooling in all environments:

# Overall cluster health
GET /_cat/health?v

# Per-node summary
GET /_cat/nodes?v&h=name,heap.percent,ram.percent,cpu,load_1m,disk.used_percent

# Full node statistics
GET /_nodes/stats

If these calls succeed in lower environments but fail in production, audit the RBAC roles and network ACLs (e.g., security group rules, VPC policies) that differ between environments.

3. Enable Elasticsearch Slow Query and Slow Shard Logging

Elasticsearch slowlogs capture search and indexing operations that exceed configurable time thresholds, making it possible to identify and optimize expensive queries against DataHub indices. Enable slowlog on a lower environment first (e.g., feature-dev), establish performance baselines, then promote the configuration to staging and production.

Global index-level slowlog configuration (recommended starting thresholds):

PUT /_settings
{
  "index.search.slowlog.threshold.query.warn": "10s",
  "index.search.slowlog.threshold.query.info": "5s",
  "index.search.slowlog.threshold.query.debug": "2s",
  "index.search.slowlog.threshold.query.trace": "500ms",
  "index.search.slowlog.threshold.fetch.warn": "1s",
  "index.search.slowlog.threshold.fetch.info": "800ms",
  "index.indexing.slowlog.threshold.index.warn": "10s",
  "index.indexing.slowlog.threshold.index.info": "5s",
  "index.indexing.slowlog.threshold.index.debug": "2s",
  "index.indexing.slowlog.threshold.index.trace": "500ms",
  "index.indexing.slowlog.level": "info"
}

For more targeted capture, apply the same settings directly to the primary DataHub indices:

# Apply to DataHub-specific indices individually
PUT /datahubv1/_settings
{ ... }

PUT /graph_service_v1/_settings
{ ... }

PUT /system_metadata_service_v1/_settings
{ ... }

Slowlog output will appear in {cluster-name}_index_search_slowlog.log and {cluster-name}_index_indexing_slowlog.log. Tune thresholds downward once baselines are established to capture a broader set of queries.

4. Enable PostgreSQL Slow Query Logging

Slow query logging for PostgreSQL (including Aurora PostgreSQL) is configured via a custom DB parameter group. Configure in a lower environment first, validate that logs are appearing as expected, then promote the parameter group changes to higher environments in an orderly fashion.

Recommended parameter group settings:

log_min_duration_statement = 1000 — Logs all queries exceeding 1000 ms. Tune to 500 ms once baselines are established.
log_statement = ddl — Captures all DDL (schema-changing) statements regardless of duration.
shared_preload_libraries = pg_stat_statements — Enables query statistics aggregation. Note: Changing this parameter requires a database instance restart.
pg_stat_statements.track = all — Tracks all queries, including nested statements.

After enabling pg_stat_statements, use the following query to identify the most expensive statements by total execution time:

SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  stddev_exec_time,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

On Aurora PostgreSQL, logs are accessible via the AWS Console under RDS → Databases → [your instance] → Logs & events, or via the AWS CLI:

aws rds describe-db-log-files \
  --db-instance-identifier 

aws rds download-db-log-file-portion \
  --db-instance-identifier  \
  --log-file-name error/postgresql.log \
  --output text

Recommended Promotion Strategy

Apply all logging and metric configurations to your lowest environment (e.g., feature-dev) first.
Validate that logs are being generated, metrics are visible, and no performance regressions are introduced by the logging overhead.
Tune thresholds based on observed baselines in the lower environment.
Promote the validated configurations to staging, then to production, with change-control approval at each stage.

Additional Notes

These recommendations apply to all self-hosted DataHub deployments regardless of whether Elasticsearch or OpenSearch is used as the search and graph backend. For OpenSearch deployments, the slowlog API syntax is identical to Elasticsearch; monitoring tooling (e.g., OpenSearch Dashboards) differs. The pg_stat_statements extension requires a database restart when added to shared_preload_libraries for the first time — plan for a maintenance window before enabling it in production. Disk watermark values set via the cluster transient settings are lost on cluster restart; persist them in elasticsearch.yml or apply via persistent settings for durability. None of these configurations require changes to the DataHub application itself — they are purely infrastructure-level operational settings.