Keycloak Database Tuning: PostgreSQL Optimization Guide

Last updated: March 2026

Keycloak’s performance is directly tied to its database. Every login, token validation, session lookup, and admin operation translates into SQL queries against your PostgreSQL instance. A misconfigured database will eventually become the bottleneck that throttles your entire identity platform, often surfacing as slow login times, token endpoint timeouts, or admin console sluggishness during peak hours.

This guide covers the key areas of PostgreSQL tuning for Keycloak: connection pooling, index optimization, vacuum and autovacuum configuration, query performance analysis, session table management, and event store sizing with partitioning strategies.

Understanding Keycloak’s Database Access Patterns

Before tuning, it helps to understand how Keycloak uses the database:

• Authentication flows: Read-heavy. User lookups, credential validation, and realm configuration reads dominate during login.
• Session management: Write-heavy. Every login creates session records, and every token refresh updates them. See our session management feature overview for how Skycloak handles this.
• Admin operations: Mixed read/write. Creating clients, roles, and users involves both reads (to check existing state) and writes.
• Event logging: Append-only writes. If you enable audit logging, every authentication event and admin action generates an insert into the event tables.

This means your PostgreSQL tuning needs to balance read performance (caching, indexes) with write throughput (WAL configuration, autovacuum frequency).

Connection Pooling

The Problem

Keycloak uses HikariCP as its internal JDBC connection pool. Each Keycloak node maintains its own pool of database connections. In a clustered deployment with 3 nodes and a default pool size of 100, you need 300 simultaneous connections to PostgreSQL. PostgreSQL’s default max_connections is 100, which is far too low.

But simply increasing max_connections is not the answer. Each PostgreSQL connection consumes memory (roughly 5-10 MB per connection depending on work_mem settings), and beyond a certain point, connection overhead degrades performance.

PgBouncer Configuration

PgBouncer sits between Keycloak and PostgreSQL, multiplexing many application connections over fewer database connections:

# pgbouncer.ini
[databases]
keycloak = host=127.0.0.1 port=5432 dbname=keycloak

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# Transaction-level pooling is essential for Keycloak
pool_mode = transaction

# Connection limits
max_client_conn = 500
default_pool_size = 40
min_pool_size = 10
reserve_pool_size = 10
reserve_pool_timeout = 3

# Timeouts
server_idle_timeout = 300
client_idle_timeout = 600
query_timeout = 30

# Logging
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60

Key points:

• pool_mode = transaction: This is critical for Keycloak. Session-level pooling would defeat the purpose since Keycloak keeps connections open. Transaction-level pooling allows connections to be returned to the pool after each transaction completes.
• default_pool_size = 40: The actual number of PostgreSQL connections. This is what matters for PostgreSQL memory usage.
• max_client_conn = 500: How many Keycloak connections PgBouncer accepts. These are cheap since PgBouncer handles them efficiently.

Keycloak HikariCP Settings

Configure Keycloak’s connection pool in keycloak.conf or via environment variables:

# keycloak.conf
db=postgres
db-url=jdbc:postgresql://pgbouncer-host:6432/keycloak
db-username=keycloak
db-password=secret

# HikariCP pool settings
db-pool-initial-size=10
db-pool-min-size=10
db-pool-max-size=50

Or with environment variables:

KC_DB=postgres
KC_DB_URL=jdbc:postgresql://pgbouncer-host:6432/keycloak
KC_DB_POOL_INITIAL_SIZE=10
KC_DB_POOL_MIN_SIZE=10
KC_DB_POOL_MAX_SIZE=50

With PgBouncer in front, you can set Keycloak’s pool sizes generously since PgBouncer handles the actual connection multiplexing to PostgreSQL.

PostgreSQL Connection Settings

With PgBouncer handling connection pooling, PostgreSQL itself can use a lower max_connections:

# postgresql.conf
max_connections = 100
superuser_reserved_connections = 3

This keeps PostgreSQL’s per-connection memory overhead manageable.

PostgreSQL Memory Configuration

Tune these critical memory parameters for a Keycloak workload:

# postgresql.conf

# Shared buffer pool - typically 25% of total RAM
# For a dedicated 16 GB database server:
shared_buffers = 4GB

# Working memory per operation (sorts, hash joins)
# Be conservative - this is per-operation, not per-connection
work_mem = 16MB

# Memory for maintenance operations (VACUUM, CREATE INDEX)
maintenance_work_mem = 512MB

# OS cache hint - total RAM minus shared_buffers
effective_cache_size = 12GB

# WAL configuration for write performance
wal_buffers = 64MB
min_wal_size = 1GB
max_wal_size = 4GB

# Checkpoint tuning
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min

Explanation of Key Settings

shared_buffers = 4GB: The main buffer cache. Keycloak reads the same realm configurations, client definitions, and role mappings repeatedly. A well-sized buffer pool means these reads come from shared memory instead of disk.

work_mem = 16MB: Used for in-memory sorts and hash operations. Keycloak’s queries are generally simple, so 16 MB is usually sufficient. Do not set this too high because it is allocated per operation, and a complex query can use multiple work_mem allocations.

effective_cache_size = 12GB: Not an allocation but a hint to the query planner about how much memory is available for caching (including OS page cache). This influences whether PostgreSQL chooses index scans vs. sequential scans.

Index Optimization

Keycloak’s schema includes indexes for its primary access patterns, but there are additional indexes that can improve performance for common queries.

Identifying Missing Indexes

Use this query to find tables with high sequential scan ratios:

SELECT
    schemaname,
    relname AS table_name,
    seq_scan,
    idx_scan,
    CASE WHEN (seq_scan + idx_scan) > 0
        THEN round(100.0 * seq_scan / (seq_scan + idx_scan), 2)
        ELSE 0
    END AS seq_scan_pct,
    n_live_tup AS estimated_rows
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY seq_scan_pct DESC;

Recommended Additional Indexes

Based on common Keycloak query patterns, these indexes often help:

-- Speed up user searches by email (case-insensitive)
CREATE INDEX CONCURRENTLY idx_user_entity_email_lower
    ON user_entity (LOWER(email));

-- Speed up session lookups by user
CREATE INDEX CONCURRENTLY idx_user_session_user_id
    ON user_session (user_id);

-- Speed up event queries by date range
CREATE INDEX CONCURRENTLY idx_event_entity_time
    ON event_entity (event_time DESC);

-- Speed up admin event queries by resource type and date
CREATE INDEX CONCURRENTLY idx_admin_event_resource_time
    ON admin_event_entity (resource_type, event_time DESC);

-- Speed up credential lookups
CREATE INDEX CONCURRENTLY idx_credential_user_type
    ON credential (user_id, type);

-- Speed up group membership queries
CREATE INDEX CONCURRENTLY idx_user_group_membership_user
    ON user_group_membership (user_id);

-- Speed up role mapping lookups
CREATE INDEX CONCURRENTLY idx_user_role_mapping_user
    ON user_role_mapping (user_id);

Use CREATE INDEX CONCURRENTLY to avoid locking tables during index creation on production databases.

Monitoring Index Usage

After creating indexes, verify they are being used:

SELECT
    indexrelname AS index_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_tup_read AS rows_read,
    idx_tup_fetch AS rows_fetched
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

Remove indexes that are never used, as they add write overhead without benefit:

-- Find unused indexes (check after at least a week of production traffic)
SELECT
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
    AND indexrelname NOT LIKE '%_pkey'
    AND indexrelname NOT LIKE '%_unique%'
ORDER BY pg_relation_size(indexrelid) DESC;

Vacuum and Autovacuum Configuration

PostgreSQL’s MVCC architecture means that updates and deletes create dead tuples that must be cleaned up by VACUUM. Keycloak’s session tables are particularly write-heavy, generating dead tuples rapidly.

Autovacuum Tuning for Keycloak

# postgresql.conf

# Global autovacuum settings
autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 30s

# More aggressive thresholds for Keycloak's write-heavy tables
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.02

# Allow autovacuum to work faster
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_cost_limit = 1000

Per-Table Autovacuum for Session Tables

Keycloak’s session tables (user_session, client_session, offline_user_session, offline_client_session) churn heavily. Configure more aggressive autovacuum for them:

-- More frequent vacuum on session tables
ALTER TABLE user_session SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 100,
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_vacuum_cost_delay = 0
);

ALTER TABLE client_session SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 100,
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_vacuum_cost_delay = 0
);

ALTER TABLE offline_user_session SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_vacuum_threshold = 200,
    autovacuum_analyze_scale_factor = 0.02
);

ALTER TABLE offline_client_session SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_vacuum_threshold = 200,
    autovacuum_analyze_scale_factor = 0.02
);

Monitoring Autovacuum

Check when autovacuum last ran and how many dead tuples are pending cleanup:

SELECT
    relname AS table_name,
    n_live_tup,
    n_dead_tup,
    CASE WHEN n_live_tup > 0
        THEN round(100.0 * n_dead_tup / n_live_tup, 2)
        ELSE 0
    END AS dead_pct,
    last_autovacuum,
    last_autoanalyze,
    autovacuum_count,
    autoanalyze_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 100
ORDER BY n_dead_tup DESC;

If dead_pct exceeds 20% on any table, your autovacuum is not keeping up and needs more aggressive tuning.

Query Performance Analysis

Enabling pg_stat_statements

The pg_stat_statements extension tracks query execution statistics. This is essential for identifying slow queries:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 5000
pg_stat_statements.track = all

Finding Slow Queries

SELECT
    substring(query, 1, 100) AS short_query,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(max_exec_time::numeric, 2) AS max_ms,
    rows
FROM pg_stat_statements
WHERE dbname = 'keycloak'
ORDER BY mean_exec_time DESC
LIMIT 20;

Finding Queries by Total Time

SELECT
    substring(query, 1, 120) AS short_query,
    calls,
    round(total_exec_time::numeric / 1000, 2) AS total_seconds,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round((100.0 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct_total
FROM pg_stat_statements
WHERE dbname = 'keycloak'
ORDER BY total_exec_time DESC
LIMIT 20;

This identifies queries that consume the most cumulative database time, which is often more important than the slowest individual query.

Using EXPLAIN ANALYZE

For specific slow queries, use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) to understand the execution plan:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM user_entity
WHERE realm_id = 'my-realm'
AND LOWER(email) = '[email protected]';

Look for sequential scans on large tables, high buffer reads, and nested loop joins that could benefit from indexes.

Session Table Management

Keycloak sessions are a primary source of database load. There are several strategies to manage this.

Monitoring Session Volume

-- Current session counts
SELECT
    realm_id,
    COUNT(*) AS active_sessions
FROM user_session
GROUP BY realm_id
ORDER BY active_sessions DESC;

-- Offline session counts (these persist across restarts)
SELECT
    realm_id,
    COUNT(*) AS offline_sessions
FROM offline_user_session
GROUP BY realm_id
ORDER BY offline_sessions DESC;

-- Session table sizes
SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS data_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size,
    n_live_tup AS rows
FROM pg_stat_user_tables
WHERE relname LIKE '%session%'
ORDER BY pg_total_relation_size(relid) DESC;

Configuring Session Timeouts

Reduce session table bloat by configuring appropriate timeouts in Keycloak:

• SSO Session Idle: How long a session can be idle before expiring (default: 30 minutes)
• SSO Session Max: Maximum session lifetime regardless of activity (default: 10 hours)
• Offline Session Idle: Idle timeout for offline sessions (default: 30 days)

Shorter timeouts mean fewer rows in session tables and less database load. Balance this against user experience requirements.

External Session Storage

For high-traffic deployments, consider moving session storage out of PostgreSQL entirely. Keycloak supports Infinispan for distributed session caching, which can reduce database writes significantly. In a Kubernetes deployment, Infinispan clustering handles session replication across pods.

Event Store Sizing and Partitioning

If you enable Keycloak’s event logging for audit trails, the event_entity and admin_event_entity tables grow continuously. Without management, they will eventually degrade query performance and consume excessive disk space.

Estimating Event Volume

A rough formula: each authentication event generates approximately 1 KB of data. If you have 10,000 logins per day, that is about 10 MB per day or 3.6 GB per year of event data.

Native Table Partitioning

PostgreSQL’s declarative partitioning is the best approach for managing event data:

-- Create partitioned event table (for new installations)
-- Note: This requires modifying Keycloak's schema, so it's best done
-- during initial setup or major version upgrades.

CREATE TABLE event_entity_partitioned (
    id VARCHAR(36) NOT NULL,
    client_id VARCHAR(255),
    details_json TEXT,
    error VARCHAR(255),
    ip_address VARCHAR(255),
    realm_id VARCHAR(255),
    session_id VARCHAR(255),
    event_time BIGINT,
    type VARCHAR(255),
    user_id VARCHAR(255),
    PRIMARY KEY (id, event_time)
) PARTITION BY RANGE (event_time);

-- Create monthly partitions
-- event_time is in milliseconds since epoch
-- January 2026
CREATE TABLE event_entity_2026_01 PARTITION OF event_entity_partitioned
    FOR VALUES FROM (1735689600000) TO (1738368000000);

-- February 2026
CREATE TABLE event_entity_2026_02 PARTITION OF event_entity_partitioned
    FOR VALUES FROM (1738368000000) TO (1740787200000);

-- Create a default partition for any unmatched values
CREATE TABLE event_entity_default PARTITION OF event_entity_partitioned DEFAULT;

Automated Partition Management

Use pg_partman or a cron job to automate partition creation and old partition cleanup:

-- Install pg_partman
CREATE EXTENSION pg_partman;

-- Configure automatic partitioning
SELECT partman.create_parent(
    p_parent_table := 'public.event_entity_partitioned',
    p_control := 'event_time',
    p_type := 'range',
    p_interval := '1 month',
    p_premake := 3
);

-- Configure retention (drop partitions older than 12 months)
UPDATE partman.part_config
SET retention = '12 months',
    retention_keep_table = false
WHERE parent_table = 'public.event_entity_partitioned';

Event Cleanup Without Partitioning

If you cannot partition the tables, schedule regular cleanup:

-- Delete events older than 90 days
-- Run this during low-traffic periods
DELETE FROM event_entity
WHERE event_time < (EXTRACT(EPOCH FROM NOW() - INTERVAL '90 days') * 1000)::BIGINT;

-- Run VACUUM after large deletes
VACUUM (VERBOSE, ANALYZE) event_entity;

Schedule this with pg_cron or an external scheduler.

Monitoring PostgreSQL for Keycloak

Key Metrics to Watch

Set up monitoring for these PostgreSQL metrics:

-- Connection usage
SELECT
    count(*) AS total_connections,
    count(*) FILTER (WHERE state = 'active') AS active,
    count(*) FILTER (WHERE state = 'idle') AS idle,
    count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_stat_activity
WHERE datname = 'keycloak';

-- Cache hit ratio (should be > 99%)
SELECT
    round(100.0 * sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)), 2) AS cache_hit_ratio
FROM pg_statio_user_tables;

-- Transaction throughput
SELECT
    xact_commit AS commits,
    xact_rollback AS rollbacks,
    tup_inserted,
    tup_updated,
    tup_deleted,
    tup_returned,
    tup_fetched
FROM pg_stat_database
WHERE datname = 'keycloak';

A cache hit ratio below 99% usually means shared_buffers is too small for your working set. For production insights into your Keycloak cluster’s health, Skycloak provides built-in monitoring dashboards.

pg_stat_bgwriter for Checkpoint Tuning

SELECT
    checkpoints_timed,
    checkpoints_req,
    round(100.0 * checkpoints_req / (checkpoints_timed + checkpoints_req), 2) AS pct_requested,
    buffers_checkpoint,
    buffers_backend,
    maxwritten_clean
FROM pg_stat_bgwriter;

If pct_requested is above 10%, your checkpoints are being triggered by WAL pressure rather than the timer. Increase max_wal_size to give PostgreSQL more breathing room.

Backup Considerations

For Keycloak databases, continuous archiving with Point-in-Time Recovery (PITR) is recommended:

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

Use pg_basebackup for initial base backups and WAL archiving for continuous protection. This ensures you can recover to any point in time, not just the last backup.

Conclusion

PostgreSQL tuning for Keycloak is not a one-time task. As your user base grows and authentication patterns change, you need to revisit connection pooling limits, index effectiveness, autovacuum aggressiveness, and event store management. The Keycloak Server Administration Guide covers database configuration options that affect performance at the application level.

The most impactful optimizations for most deployments are:

1. PgBouncer with transaction-level pooling to handle connection multiplexing
2. Proper shared_buffers to keep frequently accessed realm data in memory
3. Aggressive autovacuum on session tables to prevent dead tuple buildup
4. Event table partitioning to maintain query performance as audit data grows

If you would rather focus on building your application instead of tuning databases, Skycloak’s managed Keycloak hosting handles PostgreSQL optimization, monitoring, and backups as part of the service. Check our SLA for uptime guarantees and our pricing to get started.