Database

PostgreSQL database connection and pool configuration.

Collation guidance:

Choose the database collation before the first Identity Scribe startup and keep it identical across production, standby, and restore environments. Multilingual deployments should use stable Unicode ordering. ASCII-only, performance-focused deployments can use byte-wise ordering when that matches the directory data and operator expectations.

Changing collation after data exists requires a database rebuild and full resync; do not switch it in place.

Connection pool architecture

Identity Scribe uses three separate connection pools to isolate different workloads:

• Batch pool - Used by: Transcription tasks (main workload) - Size: Configured via max-pool-size (default: concurrency + 5, max: concurrency * 1.5) - Each transcription task uses one connection for its lifetime

• System pool - Used by: System-level operations (commit coordination, migrations, maintenance, entry preparation) - Size: Configurable via system-pool-size (optional) - Default: max(transcribeCount + 4, concurrency / 4), clamped between 2 and max-pool-size / 2 - Scales with both number of transcribes and concurrency level - Isolates system operations from transcription tasks

• Channel pool - Used by: LDAP channel services (IdentityHub, LDAP channels) - Size: Configurable via channel-pool-size (optional) - Default: concurrency, minimum 2 - Can experience heavy LDAP traffic with multiple channels - Isolates channel operations from batch and commit workloads

Total maximum connections (when all pools are fully utilized): = Batch Pool + System Pool + Channel Pool = max-pool-size + system-pool-size + channel-pool-size

Example with default settings (concurrency = 16, 2 transcribes):

• Batch Pool: 16 + 5 = 21 connections (default max-pool-size)
• System Pool: max(2 + 4, 16/4) = max(6, 4) = 6 connections
• Channel Pool: 16 connections
• Total Maximum: 21 + 6 + 16 = 43 connections

Example with max-pool-size explicitly set to 50 (concurrency = 16, 2 transcribes):

• Batch Pool: 50 connections
• System Pool: max(2 + 4, 16/4) = 6 connections (capped at 50/2 = 25)
• Channel Pool: 16 connections
• Total Maximum: 50 + 6 + 16 = 72 connections

Tuning guidelines:

• Monitor pool metrics: Track scribe_db_connections_active, scribe_db_connections_pending,

and scribe_db_pool_pressure

• Signs of under-sizing: If pending connections are consistently non-zero or pool pressure

stays above 0.8, consider increasing pool size

• Signs of over-sizing: If active connections remain far below the configured total while

PostgreSQL connection headroom is scarce, consider reducing pool size

• High concurrency workloads: Many more tasks can be queued than

concurrency, but each task still uses one connection. Monitor actual connection utilization rather than task count.

Auto-create the pg_stat_statements extension at startup so the observe Queries tabs (Slow Log / Stats) and the /observe/stats/queries endpoint have data.

Requires both:

• ‘pg_stat_statements’ in PostgreSQL’s shared_preload_libraries (a server restart), and
• a database role permitted to install extensions.

When the library is not preloaded, creation is skipped with an actionable log line — query statistics simply stay unavailable and startup is never blocked.

Values: true | false | auto. “auto” enables it in development and disables it everywhere else,

so dev and staging can opt in while production stays untouched until you have assessed the impact.

Default: auto

Priority: SCRIBE_DATABASE_AUTO_CREATE_QUERY_STATS_EXTENSION > config

auto-create-query-stats-extension = auto

database.auto-create-query-stats-extension = ${?SCRIBE_DATABASE_AUTO_CREATE_QUERY_STATS_EXTENSION}

Size of the channel connection pool. This pool handles LDAP channel operations (IdentityHub, LDAP channels).

Default: concurrency

Minimum: 2

Channels can experience heavy LDAP traffic, especially with multiple active channels. Increase for high-throughput read-heavy workloads.

Example: With concurrency = 16:

Default = 16 connections

Priority: SCRIBE_DATABASE_CHANNEL_POOL_SIZE > config

database.channel-pool-size = ${?SCRIBE_DATABASE_CHANNEL_POOL_SIZE}

Connection Hints (channel query defaults)

Session-level hints applied to channel queries (REST, LDAP, GraphQL, gRPC). All keys are unset by default (PostgreSQL defaults apply). Channels can override these defaults in their own connection-hints section.

These hints control PostgreSQL session parameters for query execution. They are applied when opening a connection and restored when the query completes.

Lock acquisition timeout.

PostgreSQL will abort any statement that waits longer than this for a lock. Format: HOCON duration (e.g., 5s, 30s, 1m)

Priority: SCRIBE_DATABASE_LOCK_TIMEOUT > config

database.connection-hints.lock-timeout = ${?SCRIBE_DATABASE_LOCK_TIMEOUT}

Advanced PostgreSQL execution settings applied to every query connection. Shape: typed object (changed in v3.0). Change these only when Kenoxa support asks for them. Omitted keys fall back to the recommended default for the current PostgreSQL version.

These values are applied automatically on all query connections; no server-wide change is required. Follow the setting-specific tuning guidance before applying a server-wide override. The keys here are exposed for targeted overrides only.

Fast-start bias for cursor execution.

database.connection-hints.session-flags.cursor-tuple-fraction = ${?SCRIBE_DATABASE_SESSION_FLAGS_CURSOR_TUPLE_FRACTION}

Effective I/O concurrency. Accepts ssd (=128), hdd (=2), or a raw integer.

database.connection-hints.session-flags.effective-io-concurrency = ${?SCRIBE_DATABASE_SESSION_FLAGS_EFFECTIVE_IO_CONCURRENCY}

Pinned to PostgreSQL 17 default to prevent excessive memory use on cursor and sort workloads. Higher values can cause hash operations to spill to disk, degrading performance.

database.connection-hints.session-flags.hash-mem-multiplier = ${?SCRIBE_DATABASE_SESSION_FLAGS_HASH_MEM_MULTIPLIER}

PG JIT compilation.

database.connection-hints.session-flags.jit = ${?SCRIBE_DATABASE_SESSION_FLAGS_JIT}

Partition-wise aggregate planning.

database.connection-hints.session-flags.partitionwise-aggregate = ${?SCRIBE_DATABASE_SESSION_FLAGS_PARTITIONWISE_AGGREGATE}

Partition-wise join planning.

database.connection-hints.session-flags.partitionwise-join = ${?SCRIBE_DATABASE_SESSION_FLAGS_PARTITIONWISE_JOIN}

Prepared-statement planning mode. Change only when Kenoxa support asks for it. Values: auto, force_custom_plan, force_generic_plan.

database.connection-hints.session-flags.plan-cache-mode = ${?SCRIBE_DATABASE_SESSION_FLAGS_PLAN_CACHE_MODE}

Random page cost. Accepts ssd (=1.1), hdd (=4.0), or a raw number.

database.connection-hints.session-flags.random-page-cost = ${?SCRIBE_DATABASE_SESSION_FLAGS_RANDOM_PAGE_COST}

PostgreSQL memory size for sort/hash operations.

Units: B, kB, MB, GB, TB.

database.connection-hints.session-flags.work-mem = ${?SCRIBE_DATABASE_SESSION_FLAGS_WORK_MEM}

Statement execution timeout (prevents runaway queries). PostgreSQL will abort any statement that takes longer than this duration. Format: HOCON duration (e.g., 30s, 1m, 500ms)

Priority: SCRIBE_DATABASE_STATEMENT_TIMEOUT > config

database.connection-hints.statement-timeout = ${?SCRIBE_DATABASE_STATEMENT_TIMEOUT}

Allows eligible paginated reads to use the absolute safety cap when early results show that continuing is productive. Set this to false to restore the previous earlier-fallback behavior while keeping the base continuation safeguards enabled.

Priority: SCRIBE_DATABASE_CONTINUATION_BUDGET_EXTENSION_ENABLED > config

database.continuation.budget-extension-enabled = ${?SCRIBE_DATABASE_CONTINUATION_BUDGET_EXTENSION_ENABLED}

Allows conservative estimates for predicates without exact row-count support.

database.continuation.eligibility.allow-conservative-estimates = true

Requires at least one selective predicate before continuation safeguards can engage.

database.continuation.eligibility.require-selective-predicate = true

Enables additional safety limits for selected paginated queries. When enabled, selected paginated queries keep per-query work within the configured limits while preserving normal cursor and size-limit semantics.

Priority: SCRIBE_DATABASE_CONTINUATION_ENABLED > config

database.continuation.enabled = ${?SCRIBE_DATABASE_CONTINUATION_ENABLED}

Sets safety limits for qualifying paginated queries. These values do not change public page-size semantics.

Absolute safety cap for qualifying paginated queries. Keep this at or above max-candidates. This is the highest candidate count an eligible productive read can consume at runtime before falling back.

Priority: SCRIBE_DATABASE_CONTINUATION_WINDOW_ABSOLUTE_SCAN_CAP > config

database.continuation.window.absolute-scan-cap = ${?SCRIBE_DATABASE_CONTINUATION_WINDOW_ABSOLUTE_SCAN_CAP}

Initial maximum safety limit for qualifying paginated queries. The default is 32 times the minimum safety limit. When budget extension is enabled, a productive read may continue beyond this limit up to absolute-scan-cap before falling back.

Priority: SCRIBE_DATABASE_CONTINUATION_WINDOW_MAX_CANDIDATES > config

database.continuation.window.max-candidates = ${?SCRIBE_DATABASE_CONTINUATION_WINDOW_MAX_CANDIDATES}

Minimum safety limit for qualifying paginated queries. The effective limit also honors the requested page size.

Priority: SCRIBE_DATABASE_CONTINUATION_WINDOW_MIN_CANDIDATES > config

database.continuation.window.min-candidates = ${?SCRIBE_DATABASE_CONTINUATION_WINDOW_MIN_CANDIDATES}

Minimum safety-limit multiplier relative to the requested page size.

Priority: SCRIBE_DATABASE_CONTINUATION_WINDOW_MIN_LIMIT_MULTIPLE > config

database.continuation.window.min-limit-multiple = ${?SCRIBE_DATABASE_CONTINUATION_WINDOW_MIN_LIMIT_MULTIPLE}

Safety factor used when setting limits for eligible queries.

Priority: SCRIBE_DATABASE_CONTINUATION_WINDOW_SAFETY_FACTOR > config

database.continuation.window.safety-factor = ${?SCRIBE_DATABASE_CONTINUATION_WINDOW_SAFETY_FACTOR}

Fraction of rows modified since the last statistics refresh that also triggers a background refresh, regardless of age. A value of 0.01 means that 1 % row churn is enough to mark the cached statistics as stale.

Priority: SCRIBE_DATABASE_FILTER_SELECTIVITY_CHURN_BUDGET_RATIO > config

database.filter-selectivity.churn-budget-ratio = ${?SCRIBE_DATABASE_FILTER_SELECTIVITY_CHURN_BUDGET_RATIO}

Maximum time allowed for a background statistics query. If the query does not complete within this budget the server uses conservative handling for that request and retries on the next query.

Priority: SCRIBE_DATABASE_FILTER_SELECTIVITY_REFRESH_TIMEOUT > config

database.filter-selectivity.refresh-timeout = ${?SCRIBE_DATABASE_FILTER_SELECTIVITY_REFRESH_TIMEOUT}

Age after which cached attribute statistics are considered stale and refreshed in the background. Stale statistics are still served immediately while the refresh runs asynchronously.

Priority: SCRIBE_DATABASE_FILTER_SELECTIVITY_STALE_AGE_HORIZON > config

database.filter-selectivity.stale-age-horizon = ${?SCRIBE_DATABASE_FILTER_SELECTIVITY_STALE_AGE_HORIZON}

Maintenance scheduling for tasks like vacuuming, re-indexing, etc.

Define a cron expression (unix cron format) to run the maintenance task at a specific time Default not set, eg no cron window

Priority: SCRIBE_DATABASE_MAINTENANCE_CRON > config

cron = ‘0 5 * * *’ # 5am daily

database.maintenance.cron = ${?SCRIBE_DATABASE_MAINTENANCE_CRON}

If enabled, maintenance tasks will be scheduled Defaults to enabled unless readonly is set to true

Priority: SCRIBE_DATABASE_MAINTENANCE_ENABLED > config

database.maintenance.enabled = ${?SCRIBE_DATABASE_MAINTENANCE_ENABLED}

Used after interval, to check if system is idle enough to run maintenance Only a well-known set of threshold metrics is supported (no labels/selectors):

• scribe_ingest_tasks_active
• scribe_ingest_task_pressure
• scribe_ingest_queue_pressure
• scribe_db_connections_active
• scribe_ldap_connections_active
• scribe_query_permit_pressure

Arbitrary metric names are NOT supported. These values are queried directly from internal metrics (not Prometheus).

Priority: SCRIBE_DATABASE_MAINTENANCE_HARD_THRESHOLDS > config

The default threshold is computed from the total configured database pool size. It is more permissive than the soft threshold, so delayed maintenance can still run under moderate load.

database.maintenance.hard-thresholds = ${?SCRIBE_DATABASE_MAINTENANCE_HARD_THRESHOLDS}

Runs based on system load metrics, at least every 36 hours The default value for interval is 36 hours, if no interval or cron is set

Priority: SCRIBE_DATABASE_MAINTENANCE_INTERVAL > config

database.maintenance.interval = ${?SCRIBE_DATABASE_MAINTENANCE_INTERVAL}

Used after half of interval, to check if system is idle enough to run maintenance Only a well-known set of threshold metrics is supported (no labels/selectors):

• scribe_ingest_tasks_active
• scribe_ingest_task_pressure
• scribe_ingest_queue_pressure
• scribe_db_connections_active
• scribe_ldap_connections_active
• scribe_query_permit_pressure

Arbitrary metric names are NOT supported. These values are queried directly from internal metrics (not Prometheus).

Priority: SCRIBE_DATABASE_MAINTENANCE_SOFT_THRESHOLDS > config

The default threshold is computed from the total configured database pool size. Lower active-connection counts are treated as idle enough to run the early maintenance pass.

database.maintenance.soft-thresholds = ${?SCRIBE_DATABASE_MAINTENANCE_SOFT_THRESHOLDS}

Ratio applied to the directory size estimate to dynamically compute the PostgreSQL statistics target before each maintenance refresh. Computation: target = max(100, min(10000, ceil(estimated_entries * ratio))) Set to 0 or leave absent to disable the dynamic target (skip SET entirely).

Default: 0.00005 (5e-5), which yields targets of ~100–1,000 for directories

with 2M–20M entries.

Priority: SCRIBE_DATABASE_MAINTENANCE_STATISTICS_TARGET_RATIO > config

The maintenance task also keeps supporting range-summary statistics at the computed level. Stable statistics reduce estimate drift between maintenance windows and keep range and contains searches consistent.

database.maintenance.statistics-target-ratio = ${?SCRIBE_DATABASE_MAINTENANCE_STATISTICS_TARGET_RATIO}

Maximum size of the batch connection pool. This pool is used by transcription tasks - each task uses one connection for its lifetime.

Default: concurrency + 5

Minimum: 5

Maximum: concurrency * 1.5 (rounded up) When setting this value, remember that the total connection count includes:

• This batch pool (max-pool-size)
• System pool (configurable via system-pool-size, default: max(transcribeCount + 4, concurrency / 4))
• Channel pool (configurable via channel-pool-size, default: concurrency)

Example: With max-pool-size = 50, concurrency = 16, and 2 transcribes:

Total max connections = 50 (batch) + 6 (system) + 16 (channel) = 72 connections

Priority: SCRIBE_DATABASE_MAX_POOL_SIZE > config

database.max-pool-size = ${?SCRIBE_DATABASE_MAX_POOL_SIZE}

What to do when transcribe types are found in the database but not in the current config. Orphaned types have data in the entries table but no matching entry in the transcribes configuration block. Values: warn — (default) log a startup warning listing the orphaned types; continue normally. error — abort startup with an error message; requires cleanup or re-addition of the type. ignore — silently skip the check; preserves the pre-3.x behavior.

Priority: SCRIBE_DATABASE_ORPHANED_TRANSCRIBES_BEHAVIOR > config

database.orphaned-transcribes-behavior = ${?SCRIBE_DATABASE_ORPHANED_TRANSCRIBES_BEHAVIOR}

Priority: SCRIBE_DATABASE_PASSWORD > config

database.password = ${?SCRIBE_DATABASE_PASSWORD}

Prepared Statement Caching (advanced tuning)

Controls server-side prepared statements and per-connection caching. These improve performance by reusing query plans across executions. Memory footprint: poolSize * prepared-statement-cache-size

Example: 35 connections * 8 MiB = ~280 MiB

prepare-threshold: executions before server-side prepare (1 = immediate) prepared-statement-cache-queries: max cached statements per connection prepared-statement-cache-size: memory limit per connection (HOCON memory size syntax)

database.prepare-threshold = 1

database.prepared-statement-cache-queries = 256

database.prepared-statement-cache-size = 8 MiB

Advanced query behavior

Controls advanced query execution behavior. Distinct from connection-hints.session-flags above — these settings affect search behavior, not connection settings.

Tiebreaker for sort orderings on long-value single-valued attributes. When very long values share the same indexed prefix, the tiebreaker controls how those ties are resolved.

• deterministic (default): order tied values by uoid only. RFC 2891 §2 compliant

(server may return a deterministic order; spec is silent on tied-bucket alphabetical ordering). Fastest option for large directories.

• alphabetical: order tied values by full alphabetical value, then uoid. Adds a

small amount of per-result work. Use when byte-exact alphabetical ordering on long-value attributes (>=200 chars, e.g. description) is required. Multi-valued sort orderings are unaffected by this setting.

Priority: SCRIBE_DATABASE_QUERY_SORT_LONG_VALUE_TIEBREAKER > config

database.query.sort.long-value-tiebreaker = ${?SCRIBE_DATABASE_QUERY_SORT_LONG_VALUE_TIEBREAKER}

Query Connection Limiter

Timeout for HTTP/GraphQL queries when the system is busy. When all connections are in use, HTTP and GraphQL queries wait up to this duration before returning 503 Service Unavailable with a Retry-After header. LDAP queries block up to their query time limit instead.

Default: 5 seconds

Priority: SCRIBE_DATABASE_QUERY_HTTP_ACQUISITION_TIMEOUT > config

database.query-http-acquisition-timeout = ${?SCRIBE_DATABASE_QUERY_HTTP_ACQUISITION_TIMEOUT}

Retry policy for acquiring database connections. Unset fields inherit from the root retry block.

Initial delay between retries while waiting for database connections

Priority: SCRIBE_DATABASE_RETRY_INITIAL_DELAY > config

database.retry.initial-delay = ${?SCRIBE_DATABASE_RETRY_INITIAL_DELAY}

Randomized jitter added to each delay to avoid thundering herds

Priority: SCRIBE_DATABASE_RETRY_JITTER > config

database.retry.jitter = ${?SCRIBE_DATABASE_RETRY_JITTER}

Set to >0 to cap retries by attempt count instead of duration (0 = unlimited)

Priority: SCRIBE_DATABASE_RETRY_MAX_ATTEMPTS > config

database.retry.max-attempts = ${?SCRIBE_DATABASE_RETRY_MAX_ATTEMPTS}

Maximum delay between retries

Priority: SCRIBE_DATABASE_RETRY_MAX_DELAY > config

database.retry.max-delay = ${?SCRIBE_DATABASE_RETRY_MAX_DELAY}

Maximum time spent retrying before surfacing a timeout (leave unset to retry indefinitely)

Priority: SCRIBE_DATABASE_RETRY_MAX_DURATION > config

database.retry.max-duration = ${?SCRIBE_DATABASE_RETRY_MAX_DURATION}

Sortable coverage preparation.

Controls how many sortable attribute coverage tasks are prepared in parallel at startup. Backfill runs transparently in the background; the service remains fully operational during the backfill window. Cursor pagination on affected attributes remains correct and may be slower until backfill completes.

Default: auto-scaled from the system connection pool size using max(1, min(4, system-pool-size - 2)).

This reserves at least 2 system pool connections for maintenance, health checks, and DDL. On tiny deployments where system-pool-size < 3, the auto-scaled default is 1. Ceiling: 4 (values above 4 are clamped at startup with a WARN). Explicitly configured values above system-pool-size - 2 are also clamped with a WARN. Increase to 4 on high-spec systems to shorten the backfill window. Decrease to 1 to reduce backfill read pressure on the database during initial sync.

Priority: SCRIBE_DATABASE_SORT_INDEX_BACKFILL_MAX_CONCURRENT > config > auto-scaled default

Override: sort-index-backfill.max-concurrent = <1..4> (defaults to auto-scale formula above)

database.sort-index-backfill.max-concurrent = ${?SCRIBE_DATABASE_SORT_INDEX_BACKFILL_MAX_CONCURRENT}

Readiness verification timeout.

Bounds the final readiness check after a sortable coverage backfill attempt. Backfill batches keep the shorter maintenance statement timeout; this longer bound is for large partitions where readiness verification can legitimately take longer. If verification times out, the sort index remains out of query planning, health details report it as degraded, and the background worker retries with backoff.

Default: 10 minutes

Priority: SCRIBE_DATABASE_SORT_INDEX_BACKFILL_READINESS_TIMEOUT > config > 10m

database.sort-index-backfill.readiness-timeout = ${?SCRIBE_DATABASE_SORT_INDEX_BACKFILL_READINESS_TIMEOUT}

defaults to the root ssl configuration

The location of the root certificate for authenticating the server. File containing the root certificate when validating server (mode = “verify-ca” or “verify-full”). Default will be the file “root.crt” in “$HOME/.postgresql” (*nix) or “%APPDATA%\postgresql” (windows).

Priority: SCRIBE_DATABASE_SSL_CA > config > ssl.ca

database.ssl.ca = ${?SCRIBE_DATABASE_SSL_CA}

The location of the client’s SSL certificate File containing the SSL Certificate. Default will be the file “postgresql.crt” in “$HOME/.postgresql” (*nix) or “%APPDATA%\postgresql” (windows).

Priority: SCRIBE_DATABASE_SSL_CERT > config > ssl.cert

database.ssl.cert = ${?SCRIBE_DATABASE_SSL_CERT}

Must be set to ‘true’ to enable SSL for the database

Priority: SCRIBE_DATABASE_SSL_ENABLED > config

database.ssl.enabled = ${?SCRIBE_DATABASE_SSL_ENABLED}

The location of the client’s PKCS#8 SSL key. File containing the SSL Key. Default will be the file “postgresql.pk8” in “$HOME/.postgresql” (*nix) or “%APPDATA%\postgresql” (windows).

Priority: SCRIBE_DATABASE_SSL_KEY > config > ssl.key

database.ssl.key = ${?SCRIBE_DATABASE_SSL_KEY}

The password for the client’s ssl key.

Priority: SCRIBE_DATABASE_SSL_PASSWORD > config > ssl.password

database.ssl.password = ${?SCRIBE_DATABASE_SSL_PASSWORD}

Size of the system connection pool. This pool handles commit coordination, migrations, maintenance, and entry preparation.

Default: max(transcribeCount + 4, concurrency / 4)

Minimum: 2

Maximum: max-pool-size / 2 The default scales with both the number of configured transcribes and concurrency level to handle workloads where multiple transcribes perform system operations simultaneously.

Example: With 4 transcribes and concurrency = 16:

Default = max(4 + 4, 16/4) = max(8, 4) = 8 connections

Priority: SCRIBE_DATABASE_SYSTEM_POOL_SIZE > config

database.system-pool-size = ${?SCRIBE_DATABASE_SYSTEM_POOL_SIZE}

https://www.prisma.io/dataguide/postgresql/short-guides/connection-uris https://jdbc.postgresql.org/documentation/use/#connecting-to-the-database Multiple servers can be specified, separated by a comma, eg “postgres://server1,server2” Options can be set in the URL or as separate properties

Priority: SCRIBE_DATABASE_URL > config

url = “postgres:”${user.name}

database.url = ${?SCRIBE_DATABASE_URL}

Priority: SCRIBE_DATABASE_USER > config

user = ${user.name}

database.user = ${?SCRIBE_DATABASE_USER}