High-Concurrency Database Optimization: Query Indexing, Redis Object Caching, and Kernel Tuning Beyond Plugins

Technical deep dive into squeezing sub-millisecond execution times from relational databases under high load — where the bottleneck is rarely the query, and almost always the stack beneath it.

Introduction — The Sub-Millisecond Imperative

Enterprise platforms live or die by latency. A 50ms increase in response time costs revenue. A 200ms regression costs users. At scale, every millisecond compounds across thousands of concurrent transactions.

Most optimization efforts stop at the plugin layer: install Redis object cache, enable page caching, call it done. This works — until it doesn’t. When traffic spikes, when the database grows past a million rows, when concurrent connections saturate the connection pool, the plugin layer cannot save you.

This article is about the layers beneath:

Query execution plans, connection pooling, Redis caching topologies, FastCGI tuning, and kernel-level parameter optimization — the engineering disciplines that separate platforms that survive high concurrency from those that collapse.

The goal is not “faster.” The goal is predictable sub-millisecond execution under sustained load — the kind of performance an enterprise platform can build a business on.

Part 1 — The Optimization Stack: Where Performance Actually Lives

Before optimizing anything, the architecture must be explicit. High-concurrency performance is a layered problem:

┌─────────────────────────────────────────────────────────────────┐
│                    LAYER 7: APPLICATION                         │
│         (Query patterns, ORM behavior, N+1 elimination)         │
├─────────────────────────────────────────────────────────────────┤
│                    LAYER 6: CACHING                             │
│         (Redis object cache, page cache, fragment cache)        │
├─────────────────────────────────────────────────────────────────┤
│                    LAYER 5: DATABASE                            │
│         (Indexes, execution plans, schema design)               │
├─────────────────────────────────────────────────────────────────┤
│                    LAYER 4: CONNECTION MANAGEMENT               │
│         (Pooling, persistent connections, connection lifecycle) │
├─────────────────────────────────────────────────────────────────┤
│                    LAYER 3: WEB SERVER                          │
│         (FastCGI cache, static asset delivery, upstream routing)│
├─────────────────────────────────────────────────────────────────┤
│                    LAYER 2: PROCESS MANAGEMENT                  │
│         (PHP-FPM workers, memory allocation, request recycling) │
├─────────────────────────────────────────────────────────────────┤
│                    LAYER 1: KERNEL                              │
│         (Network buffers, file descriptors, I/O schedulers)     │
└─────────────────────────────────────────────────────────────────┘

Engineering principle: Optimizing a higher layer while a lower layer is saturated is wasted effort. The bottleneck must be identified — not assumed.

Part 2 — Query Indexing: The Execution Plan Is the Truth

The single most powerful optimization lever is the database execution plan. Not intuition. Not “best practices.” The plan.

EXPLAIN ANALYZE: Reading the Plan

PostgreSQL’s EXPLAIN ANALYZE executes the query and reports actual execution statistics — not estimates.

EXPLAIN (ANALYZE, BUFFERS, TIMING, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '7 days';

What to look for:

Real-world impact: A single missing index on a foreign key column can turn a 2ms query into a 2-second scan. At 100 queries per page load, that’s the difference between 200ms and 200 seconds .

Index Design for High-Concurrency Workloads

Indexes are not free. Every write pays the cost of every index. The engineering discipline is selective indexing — covering the queries that matter, ignoring the rest.

Critical rule: If a foreign key column is used in WHERE, JOIN, or cascading operations, index it. PostgreSQL does not do this automatically .

Monitoring Index Health

-- Unused indexes (candidates for removal)
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey';

-- Index size vs. table size
SELECT
  tablename,
  pg_size_pretty(pg_total_relation_size(tablename::regclass)) AS total_size,
  pg_size_pretty(pg_indexes_size(tablename::regclass)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::regclass) DESC;

Maintenance discipline: Index optimization is continuous. Unused indexes are pure write overhead. Missing indexes are pure read pain.

Part 3 — Connection Pooling: The Hidden Bottleneck

PostgreSQL creates a dedicated backend process for each connection. At 200 connections, that’s 200 processes consuming memory and CPU .

The Connection Explosion Problem

Traditional (no pooling):
  PHP-FPM workers: 20
  × Connections per worker: 2-3
  = Persistent connections: 40-60
  × Memory per connection: ~10MB
  = 400-600MB just for connections

  At 100 PHP-FPM workers: 200-300 connections → 2-3GB overhead
  PostgreSQL max_connections: 200 (default) → CONNECTION REFUSED

PgBouncer: Shared Connection Pool

PgBouncer sits between the application and PostgreSQL, sharing a pool of connections across all workers.

┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│ PHP-FPM     │     │             │     │             │
│ Worker 1    │────▶│             │     │             │
├─────────────┤     │   PgBouncer │     │  PostgreSQL │
│ PHP-FPM     │     │   (Pool)    │────▶│  (Limited   │
│ Worker 2    │────▶│             │     │   Backends) │
├─────────────┤     │             │     │             │
│ PHP-FPM     │     │             │     │             │
│ Worker N    │────▶│             │     │             │
└─────────────┘     └─────────────┘     └─────────────┘

Worker connections: 60     PostgreSQL backends: 20
Memory overhead: 600MB     Memory overhead: 200MB

PgBouncer Configuration

# /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

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

# Transaction pooling: connection returned after each transaction
pool_mode = transaction

# Pool sizing
max_client_conn = 500
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

# Connection health
server_idle_timeout = 600
server_lifetime = 3600

Pool mode decision:

Engineering note: LISTEN/NOTIFY (used by some ORMs for real-time features) requires session mode or a direct connection. Transaction mode breaks it .

Part 4 — Redis Object Caching: Topologies and Failure Modes

Redis as a WordPress object cache is well-documented. What’s less discussed is topology — how Redis is deployed determines whether it accelerates or destabilizes the platform .

Topology 1: Single Redis Instance (Shared)

┌─────────────────────────────────────────────────────────────┐
│                    SINGLE REDIS INSTANCE                    │
│                     (allkeys-lru policy)                    │
│  ┌───────────────────────────────────────────────────────┐  │
│  │ Key Prefix: site1:*  │  Key Prefix: site2:*  │  ...  │  │
│  └───────────────────────────────────────────────────────┘  │
└─────────────────────────────────────────────────────────────┘

Characteristics:

• Shared by all sites on the host

• Strict key prefix isolation per site 

• allkeys-lru eviction under memory pressure

• Single point of failure — but fails safe (WordPress falls back to per-request cache) 

Best for: Multi-tenant hosting, shared environments.

Topology 2: Dedicated Redis per Application

┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
│   Redis 1       │     │   Redis 2       │     │   Redis N       │
│   (App A)       │     │   (App B)       │     │   (App N)       │
└─────────────────┘     └─────────────────┘     └─────────────────┘

Characteristics:

• Full isolation — no cross-application eviction

• Independent memory allocation

• Higher operational overhead

Best for: High-value applications, dedicated infrastructure.

Topology 3: Redis with Unix Socket

# PHP-FPM pool config
env[REDIS_SOCKET] = /run/redis/redis.sock

Why this matters: Unix sockets bypass the TCP/IP stack entirely. For local Redis connections, this reduces latency and eliminates network buffer pressure .

The Failsafe Principle

Caching must never take a site down. The correct behavior under Redis failure:

Redis unreachable → WordPress continues with per-request cache
                   → Site works, just slower
                   → Admin notified of cache status
                   → No errors, no downtime

Jabali Cache implements this pattern explicitly: every read is best-effort, evictions are never errors, and connection failures fall through to the database .

Engineering principle: A cache that fails hard is worse than no cache at all.

Part 5 — Nginx FastCGI Cache: Serving Pages Without PHP

For anonymous traffic — which is the majority of traffic on content sites — the fastest PHP request is the one that never starts PHP .

The Performance Delta

At 1,000 requests/minute:

• Without cache: 1,000 × 80ms = 80 seconds of PHP CPU time

• With 90% cache hit: 100 × 80ms + 900 × 1ms = 8.9 seconds of PHP CPU time

Engineering impact: FastCGI cache reduces PHP load by up to 90% for anonymous traffic .

Nginx Configuration

http {
    # Cache zone: 256MB storage, 100MB keys zone
    fastcgi_cache_path /var/cache/nginx/wordpress
        levels=1:2 keys_zone=wp_cache:100m
        max_size=256m inactive=60m use_temp_path=off;

    fastcgi_cache_key "$scheme$request_method$host$request_uri";
    fastcgi_cache_lock on;
    fastcgi_cache_lock_timeout 5s;
    fastcgi_cache_use_stale error timeout invalid_header updating http_500 http_503;
    fastcgi_cache_background_update on;
}

Cache Bypass Logic

set $skip_cache 0;

# Never cache POST requests
if ($request_method = POST) { set $skip_cache 1; }

# Never cache query strings (search, pagination)
if ($query_string != "") { set $skip_cache 1; }

# Never cache authenticated users or cart pages
if ($http_cookie ~* "comment_author|wordpress_[a-f0-9]+|wp-postpass|wordpress_logged_in|woocommerce_items_in_cart|woocommerce_cart_hash") {
    set $skip_cache 1;
}

# Never cache admin, login, API, cart, checkout
if ($request_uri ~* "/wp-admin/|/xmlrpc.php|wp-.*.php|/feed/|index.php|/wp-json/|/cart/|/checkout/|/my-account/") {
    set $skip_cache 1;
}

Verification

curl -I https://example.com/
# First request: X-FastCGI-Cache: MISS
# Second request: X-FastCGI-Cache: HIT

Engineering note: Do not double-cache. If Nginx FastCGI cache is active, disable WordPress-level page caching. The FastCGI layer is faster and closer to the user .

Part 6 — PHP-FPM Tuning: Workers, Memory, and the OOM Killer

PHP-FPM is the process manager that executes PHP. Misconfiguration here is the most common cause of “random” downtime under load .

The pm.max_children Trap

Too high: Traffic spike → More workers spawned than RAM available
          → Kernel OOM killer terminates workers mid-request
          → Users see errors

Too low:  Requests queue while memory sits idle
          → High latency, poor throughput

Sizing Formula

Available RAM = Total RAM - (Nginx + Redis + OS overhead)
Max workers = Available RAM / Average PHP process RSS

Example (2GB VPS):

Available RAM = 2048MB - 400MB (overhead) = 1648MB
Average PHP RSS = ~80MB
Max workers = 1648 / 80 ≈ 20

Production PHP-FPM Pool Configuration

; /etc/php/8.4/fpm/pool.d/www.conf
[www]
user = www-data
group = www-data

; Unix socket (faster than TCP for local connections)
listen = /run/php/php8.4-fpm.sock
listen.owner = www-data
listen.group = www-data

; Dynamic process management
pm = dynamic
pm.max_children = 20
pm.start_servers = 5
pm.min_spare_servers = 3
pm.max_spare_servers = 8

; Recycle workers to prevent memory leaks
pm.max_requests = 500

; Slow request logging
slowlog = /var/log/php/www-slow.log
request_slowlog_timeout = 5s

; PHP settings for high-concurrency
php_admin_value[memory_limit] = 256M
php_admin_value[max_execution_time] = 120
php_admin_flag[log_errors] = on

Key parameters:

Part 7 — Kernel Tuning: The Layer Everyone Forgets

The Linux kernel is the foundation. Default kernel parameters are optimized for general-purpose computing, not high-concurrency database workloads.

Network Stack Tuning

High-concurrency applications are often network-bound before they are CPU-bound. Kernel network buffers and backlog queues determine how many connections can be handled simultaneously .

# /etc/sysctl.conf

# Maximum connections in accept queue
net.core.somaxconn = 262144

# Network device backlog
net.core.netdev_max_backlog = 262144

# TCP buffer sizes (min, default, max) in bytes
net.ipv4.tcp_rmem = 8192 87380 134217728
net.ipv4.tcp_wmem = 8192 87380 134217728

# Socket memory limits
net.core.wmem_max = 134217728
net.core.rmem_max = 134217728

# TCP memory (low, pressure, high) in pages
net.ipv4.tcp_mem = 6093984 8125312 32777216

Why this matters: Under high concurrency, the kernel must buffer incoming connections and data. Default values (often 128 or 1024 for somaxconn) cause connection drops before the application ever sees the request .

File Descriptor Limits

A database server with 1,000 concurrent connections needs at least 1,000 file descriptors — plus files, sockets, and pipes. Default limit of 1024 is insufficient .

# /etc/security/limits.conf
mysql soft nofile 65535
mysql hard nofile 65535
www-data soft nofile 65535
www-data hard nofile 65535

I/O Scheduler for SSDs/NVMe

For modern storage (SSD, NVMe), the I/O scheduler adds unnecessary overhead. none (or noop) passes I/O directly to the device .

# Check current scheduler
cat /sys/block/nvme0n1/queue/scheduler
# [none] mq-deadline kyber bfq

# Set to none for NVMe
echo none > /sys/block/nvme0n1/queue/scheduler

Engineering note: mq-deadline is the recommended alternative if none causes issues. For spinning disks, mq-deadline is preferred over none .

Virtual Memory and NUMA

# Increase max memory map areas for large databases
vm.max_map_count = 1600000

# Disable NUMA balancing on multi-node systems
kernel.numa_balancing = 0

Why: vm.max_map_count too low causes mmap failures under high connection counts. NUMA balancing adds overhead on database workloads optimized for specific memory nodes .

Part 8 — Bringing It Together: The Optimization Workflow

Performance tuning is a process, not a checklist. The workflow:

1. MEASURE
   ├─ Baseline: latency, throughput, error rate
   ├─ Identify bottleneck layer (application, cache, DB, network)
   └─ Profile: EXPLAIN ANALYZE, slow logs, system metrics

2. OPTIMIZE THE BOTTLENECK
   ├─ If DB-bound: indexes, query rewrites, connection pooling
   ├─ If cache-bound: Redis topology, eviction policy, socket
   ├─ If PHP-bound: FPM workers, max_requests, memory
   └─ If network-bound: kernel buffers, somaxconn

3. VERIFY
   ├─ Re-measure under same load
   ├─ Confirm improvement, no regression
   └─ Document change and impact

4. REPEAT
   └─ The next bottleneck is now visible

Critical principle: Never optimize two layers at once. You won’t know which change worked — or which one broke something.

Part 9 — When to Use This Optimization Stack (and When Not To)

Apply this stack when:

• ✅ The platform serves high concurrent traffic (100+ simultaneous users)

• ✅ Database queries are measurably slow (EXPLAIN ANALYZE shows seq scans)

• ✅ PHP-FPM workers are exhausted under load

• ✅ Redis is deployed but not tuned for the workload

• ✅ The infrastructure is self-managed (not shared hosting)

Do NOT apply when:

• ❌ Traffic is low — optimization effort exceeds benefit

• ❌ The bottleneck is application logic (N+1 queries, inefficient code)

• ❌ Infrastructure is managed by a provider without kernel access

• ❌ The team lacks operational capacity to maintain tuned systems

Kernel tuning without monitoring is guessing. Indexing without EXPLAIN is hoping. Optimization without measurement is superstition.

Conclusion — Performance Is a Discipline, Not a Plugin

The plugin layer offers easy wins. But when high concurrency arrives — and it always does — the plugin layer is not enough.

Sub-millisecond execution under load requires:

• Execution plans read and understood — not assumed

• Connection pooling architected — not left to default

• Redis topologies designed — not just installed

• FastCGI cache configured — not just enabled

• PHP-FPM sized to hardware — not to hopes

• Kernel parameters tuned — not left to general-purpose defaults

This is the engineering discipline that separates platforms that survive traffic from platforms that define their limits.

The database is not slow. The stack around it is untuned.

Author’s Note

This article reflects optimization patterns developed while engineering Barman News (100,000+ articles under high concurrency) and CoreBiz ERP (enterprise transaction processing). For collaboration on high-performance database architecture, reach out via the contact page.