Database Tuning

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

For caller-side search configuration 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).
• Search data distributed across many storage objects — larger directories spread hot data across entry types and attributes, so cache sizing matters more than one table’s size.
• Several acceleration structures per searchable attribute — equality, ordering, range, and substring support can consume substantial storage; substring-search acceleration is usually the largest part.
• 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. Preparing a new entry type also builds its search acceleration data in the background.

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 reserves 25% of server RAM for all concurrent work_mem allocations. Estimate how many sort, hash, bitmap, and parallel-worker allocations one busy query may use; four is a conservative starting point:

work_mem_budget_mb = total_ram_mb * 0.25
work_mem_ceiling_mb = min(512, work_mem_budget_mb / (active_query_connections * memory_nodes_per_query))
work_mem = largest standard size at or below work_mem_ceiling_mb

For a server with 64GB RAM, the default 43 pooled connections, and four concurrent allocations per query:

work_mem_budget_mb = 65536 * 0.25 = 16384MB
work_mem_ceiling_mb = 16384 / (43 * 4) ≈ 95MB
work_mem = 64MB
aggregate ceiling = 64MB * 43 * 4 = 11008MB, below the 16384MB budget

Use a standard size at or below the ceiling (16, 32, 64, 96, 128, 256, or 512 MB). If the calculated ceiling is below 16MB, reduce active query concurrency or allocate a larger budget only after accounting for all other server memory. Monitor temporary-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 benefits from parallel query execution for large equality searches, where PostgreSQL can share work across worker processes.

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

IdentityScribe applies these values on each query connection, so no server-wide change is required.

Do not enable partition-wise joins server-wide for IdentityScribe without a deployment-specific benchmark and support review. Other applications on the same PostgreSQL server may need different values.

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

# IdentityScribe query defaults (pin server-wide only after deployment-specific review)
enable_partitionwise_join = off
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 database 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.

Ordering support data is 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: