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, prefix, and substring searches, IdentityScribe adjusts execution automatically based on live directory size, attribute distribution, and recent production traffic.

• Narrow-match searches finish quickly when only a small share of rows match.
• Sorted searches that narrow to a small set of entries finish quickly even over large directories — a sorted or virtual-list-view browse selected by entry distinguished names (a single one or a long list), a unique identifier (uuid/uoid), or a selective attribute value applies that narrowing before sorting, instead of ordering the whole result set first.
• Broad-match searches stay predictable when a large share of rows match.
• Prefix searches reuse recent measurements for repeated patterns.
• Range searches use maintained coverage data when that is expected to reduce work.

No manual tuning is required for standard deployments. The default behaviour is conservative during startup and becomes more specific as normal traffic provides evidence.

Leave adaptive search tuning enabled unless you see a repeatable regression on a specific attribute or search pattern. If that happens:

1. Capture a Query Diagnostic Report for the affected request.
2. Compare the affected request against the Health and Monitoring dashboard for database load, queueing, and timeout signals.
3. Contact Kenoxa support with the report and the time window. Support can provide deployment-specific overrides when a temporary safety valve is needed.

Do not tune the advanced search thresholds from symptoms alone. The same latency symptom can come from stale database statistics, undersized memory, connection pressure, or an attribute distribution that changed after a bulk import.

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.

Common IdentityScribe database configuration environment variables: