Database Tuning

You have a PostgreSQL server for IdentityScribe. These settings size memory, storage, and WAL for IdentityScribe’s workload.

For caller-side index selection and query-shape guidance, see Query performance.

All formulas assume a dedicated database server. If PostgreSQL shares a host with IdentityScribe, reduce memory fractions accordingly.

IdentityScribe’s database workload has distinct characteristics that inform tuning:

• Three isolated connection pools — a default total of ~43 connections serving different workloads: write-heavy transcription tasks, schema maintenance, and latency-sensitive query serving (LDAP, REST, GraphQL).
• Partition-pruned leaf tables — entries_data is sub-partitioned by entry type and attribute, producing dozens of leaf partitions. At 10M entries, a typical deployment has 27+ indexes across 16 partitions.
• Multiple index types per attribute — equality and range B-tree indexes, sort-order B-tree indexes, and GIN trigram indexes for substring search. GIN indexes dominate disk usage (the largest can exceed 1 GB per attribute).
• Read-dominant steady state — equality lookups, range scans with cursor pagination, substring search, and sorted result sets. All served concurrently from LDAP, REST, and GraphQL channels.
• Bursty writes — event-sourced single-row inserts at steady state; multi-row bulk writes during reconciliation. When IdentityScribe prepares a new entry type, GIN trigram indexes are built at runtime.

Settings are parameterized by server RAM and CPU cores.

The table above recommends server-level work_mem for postgresql.conf. IdentityScribe also sets work_mem = 16MB per query connection via database.connection-hints.session-flags.work-mem. This connection-level setting overrides the server default for IdentityScribe’s connections.

16MB works for most deployments. If query performance degrades over time or under load, insufficient work_mem can cause PostgreSQL to spill sort and hash operations to disk — which is orders of magnitude slower than in-memory processing.

Observe detects spills automatically. When it finds disk spilling, it recommends a work_mem value and provides a ready-to-use config snippet. You can also calculate manually:

A safe starting point for work_mem given your server’s total RAM and connection count:

work_mem = max(16MB, min(512MB, total_ram_mb / max_connections / 4))

For a server with 32GB RAM and 100 connections:

work_mem = max(16MB, min(512MB, 32768 / 100 / 4)) ≈ 82MB → round to 64MB or 96MB

Start at the next standard size above the formula result (16, 32, 64, 96, 128, 256, 512 MB). Monitor temp file counts after each increase — if they stop growing, you have enough.

Override per-channel or globally in IdentityScribe config:

database.connection-hints.session-flags {
  work-mem = "64MB"
}

Or per-channel (e.g., only for REST API queries):

channels.rest.connection-hints.session-flags {
  work-mem = "128MB"
}

The value must match <number><unit> where unit is B, kB, MB, GB, or TB (e.g., "256MB", "1GB").

Raising work_mem increases memory pressure. A single query can use work_mem multiple times — once per sort or hash operation, and once per parallel worker. On a server running many concurrent queries, doubling work_mem across the board can trigger out-of-memory errors. Raise gradually and verify in Observe that temporary-file pressure falls before moving higher.

For HDD storage, use random_page_cost = 2.0 and effective_io_concurrency = 4.

IdentityScribe uses parallel query execution for equality lookups at scale — each equality filter is translated to a direct join predicate that PostgreSQL can split across worker processes via Gather Merge.

Maintenance parallelism is higher to speed up index creation when new entry types are prepared.

These settings are off by default in PostgreSQL because they add query-preparation overhead for non-partitioned schemas. IdentityScribe enables both per-session automatically on every query connection, so they work out of the box without server configuration.

Setting them server-level in postgresql.conf is still recommended — it avoids the per-connection SET overhead and ensures all connections (including ad-hoc psql sessions and monitoring queries) benefit.

For a 32 GB RAM / 8-core SSD server with default IdentityScribe pool sizes:

# Memory
shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 32MB
maintenance_work_mem = 1536MB

# Storage (SSD)
random_page_cost = 1.1
effective_io_concurrency = 200

# Parallelism
max_parallel_workers_per_gather = 2
max_parallel_maintenance_workers = 4
max_parallel_workers = 4

# Partition-wise operations (required for IdentityScribe)
enable_partitionwise_join = on
enable_partitionwise_aggregate = on

# WAL and Checkpoints
max_wal_size = 4GB
checkpoint_timeout = 10min

# Connections
max_connections = 60

# Autovacuum (tuned for partitioned tables)
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02

After restarting PostgreSQL, confirm buffer allocation with SHOW shared_buffers and monitor query performance on the Health and Monitoring dashboard.

IdentityScribe manages three separate HikariCP connection pools to isolate workloads. These are configured in the IdentityScribe config (not postgresql.conf).

The total connection count is the sum of all three pools. Ensure max_connections in postgresql.conf can accommodate the total (see the Connections table above).

A semaphore (default: channel pool size) caps concurrent query connections across all channels. HTTP and GraphQL queries that exceed this wait up to query-http-acquisition-timeout (default: 5s) and return 503 with a Retry-After header. LDAP queries block up to their query time limit instead.

For filtered, sorted queries, IdentityScribe adapts between a narrow-match strategy and a broad-match strategy based on how many rows PostgreSQL expects to qualify.

• Narrow-match searches finish quickly when only a small share of rows match.
• Broad-match searches stay predictable when a large share of rows match.

These controls influence when IdentityScribe changes between those search modes. Configurable via:

Range conditions on sortable attributes (e.g., sn >= 'M') can use a pre-computed min/max index instead of scanning the full attribute data. The index stores the minimum and maximum value per entry range, allowing the system to skip entries whose range doesn’t overlap with the filter condition.

IdentityScribe uses the estimated result size to decide when this index is worth using:

• Small result sets use the sort index to avoid reading attribute data for rows that cannot match.
• Large result sets stay on the standard attribute-data path.

These estimates use PostgreSQL statistics that IdentityScribe refreshes during maintenance.

For prefix searches (startsWith-style patterns), IdentityScribe first measures how many entries match a new pattern, then chooses the faster execution mode for that pattern. The result is cached per (entry type, attribute, prefix) combination and reused on later requests, so only the first search for a previously unseen prefix performs the measurement.

This applies only to prefix-shaped patterns. Contains and suffix patterns use the standard behaviour.

Substring filters (cn=*hugo*), prefix scans, and multi-term substring searches can perform very differently across attributes and directory sizes. A baseline that works well for cn may be too aggressive or too conservative for mail, and it can become stale as the directory grows.

IdentityScribe now learns per-attribute behaviour from real traffic. It starts from conservative baseline values, observes how many entries match incoming searches, and adjusts automatically. No manual tuning is required for standard deployments.

database.gate-learning {
  enabled = true

  persistence.flush-interval = 5m

  defaults {
    single-substring          = 6.0
    substring-or              = 15.0
    same-attribute-mixed-or   = 3.0
  }
}

The defaults.* values are the startup baselines used until enough traffic has accumulated for the learned per-attribute state to stabilise. In most deployments this happens within minutes of first production queries, and the starting behaviour is already suitable for the majority of workloads.

The primary metrics track which search mode was chosen and whether calibration is healthy:

• scribe.gate.decision.total — counts every adaptive search decision. Labels include scribe.gate.routing_path (the selected search mode), scribe.gate.routing_source (whether the choice came from a startup baseline, learned state, or a direct measurement), and scribe.gate.confidence (calibration confidence: COLD, LOW, MEDIUM, or HIGH). A sustained routing_source="DEFAULT" on a busy attribute means the system has not yet seen enough traffic for that attribute to move beyond cold start. This is normal for newly added attributes or rarely searched ones.

• scribe.gate.misroute_regret.total — counts cases where later feedback showed a different search mode would likely have been faster. A non-zero value is expected during warm-up. If this counter climbs steadily for the same kind of search after 24 h of production traffic, the learning has not converged. Contact support.

• scribe.gate.probe.failures.total — counts background calibration failures. A short burst can happen while a new attribute is still being indexed; sustained failures mean the system cannot collect fresh calibration data for that kind of search.

If you observe a search-performance regression on a specific attribute and need immediate relief, disable learning:

database.gate-learning.enabled = false

With learning disabled, all adaptive decisions use the fixed defaults.* values shown above, which are the same startup baselines used on cold start. Behaviour becomes deterministic and matches the pre-learning mode. Re-enable once the regression is understood.

IdentityScribe dynamically computes the PostgreSQL statistics target before each maintenance refresh, adjusting it based on directory size. This keeps filter and sort performance more consistent at scale without the overhead of a blanket high target.

The computed target is logged at the start of each maintenance window.

Set to 0 to disable dynamic tuning and use PostgreSQL’s default.

Sort indexes store a compact min/max range per entry for each sortable attribute. These indexes are built in the background at startup; the service remains fully operational during the build window.

Backfill concurrency auto-scales from available system pool resources, reserving enough connections for maintenance and health checks.

All IdentityScribe database configuration environment variables: