PostgreSQL Tutorial: Tuning Database Connections Under Linux

Scaling Postgres’s high-concurrency handling capability can never rely on simply throwing more hardware at the problem. Each database connection incurs a hidden system tax, including Linux-level memory and CPU overhead, as well as Postgres-level MVCC (Multi-Version Concurrency Control) and locking constraints. To handle high-performance workloads, we need to stop guessing and start precisely tuning our systems by deeply understanding these underlying limitations.

Table of Contents

Postgres Multi-Process Architecture and Its Impact

• Architectural Characteristics: Postgres chose a multi-process isolation architecture in the 1990s to ensure high stability. While those processes are completely isolated and very safe, this architecture inherently causes higher CPU and memory overhead, and heavily relies on Linux’s shared memory mechanisms (such as tmpfs) for inter-process communication (IPC).
• Future Evolution: The community is actively discussing transitioning Postgres to a thread-based or a mixed architecture (similar to Oracle). However, a thread-based architecture means the heap memory is shared among all threads running inside the process, requiring developers to be exceptionally cautious to avoid overwriting shared memory spaces during concurrent execution.

Core Tuning Directions and Comparative Analysis

1. Memory Management: Huge Pages are a Must

• Key Conclusion: You must use Huge Pages to map Shared Buffers.

• Page Table Overhead Comparison (Example: 8GB buffers, 1,000 active connections):

• ❌ Standard 4KB Pages: Every 1GB of buffers mapped in standard pages requires up to 2MB of Page Table Entries (PTE). In the scenario above, the system’s PTE tables could theoretically bloat to a staggering 16GB, causing massive memory waste.

• ✅ 2MB Huge Pages: Mapping 1GB of buffers requires just 4KB of the Page Middle Directory (PMD) table. For the same scenario, it grows only up to 32MB, representing a massive reduction in overhead.

• Pitfall Prevention Guide: Make sure to disable Transparent Huge Pages (THP) in the Linux kernel, as it can cause severe performance anomalies and latency spikes for Postgres.

2. CPU Scheduling: Linux Scheduler Evolution Dividends

• Old Completely Fair Scheduler (CFS): Aims for absolute average fairness. However, this average fairness actually hurts performance for extremely short, bursty database transactions (short queries), easily dragging down overall throughput and increasing latency.
• New Scheduler (EEVDF): Starting from Linux kernel 6.6 (adopted by modern distributions like Debian 13, RHEL 10, etc.), a new scheduler called “Earliest Eligible Virtual Deadline First” (EEVDF) replaces CFS.
• Advantages: The EEVDF scheduler prioritizes processing short, bursty connections to minimize latency based on calculated needs. This means that simply upgrading your Linux OS version can directly yield Postgres performance and latency improvements, even without making any changes to Postgres configurations.

3. NUMA Architecture Configuration Strategies

Modern multi-core servers predominantly use NUMA (Non-Uniform Memory Access) architecture, where remote cross-node resource access introduces theoretical latency penalties.

• Benchmark Conclusion: For general Postgres workloads, production testing indicates that actual cross-node NUMA latency penalties are often exaggerated by firmware distance tables and are relatively minor in practice.
• Strategic Recommendations:
• Memory Allocation: It is recommended to use the interleave NUMA policy (either across the whole machine or dedicated nodes) so that processes and their memory are distributed evenly using a round-robin approach, preventing single-node bottlenecks.
• Node Pinning: Pinning Postgres to specific nodes is primarily useful for workload isolation (e.g., running multiple independent database instances on one large machine and pinning separate instances to separate NUMA nodes).

System Capacity Planning Guide (Core Metrics Checklist)

1. Active Connection Configuration (Per CPU Core)

When evaluating connection capacity limits, you must calculate based on “active connections” doing real work rather than idle connections:

• Typical Transactional Workloads (with modern SSD storage and Huge Pages enabled): Up to 4 active connections per CPU core is perfectly safe.
• High-frequency/Very Small Transactions: Even up to 6 to 8 active connections per core can be acceptable depending on the workload.
• Analytical Workloads (OLAP): Strictly 1 connection per CPU core. If parallel workers are utilized, a dedicated core must be allocated for each worker.
• Note: With older hardware or without Huge Pages enabled, throughput expectations must be adjusted downward.

2. Memory Usage Assessment and the Pitfalls of work_mem

• Reject Distorted Memory Metrics: The memory footprint shown for a connection in the standard top command is heavily distorted due to the mapping of shared buffers. To understand real memory usage, you must inspect the kernel’s smaps interface and filter out shared buffer mapping lines (such as /dev/zero (deleted)) to extract the true per-connection private memory.
• **The “Multiplier Effect” of work_mem**: In a single query execution, work_mem might not be allocated just once; it can be utilized multiple times (e.g., 2 or even 2.2 times), making capacity calculations based on a single work_mem unit unsafe.
• Memory Resident Bloat: Due to the nature of heap allocation (the heap can only shrink from the top down), small objects created at the very end of query processing might sit at the peak of the heap. Even though Postgres marks the deallocated chunks below it as free, Linux still tracks these pages as dirty/resident (RSS). This behavior often causes unexpected resident memory bloat, especially with smaller work_mem settings.

Two Critical Postgres Internal Bottlenecks

1. MVCC Snapshot Scalability: This was significantly optimized in Postgres 14 to stabilize throughput. However, under extremely high concurrent connections, the internal management of snapshots remains a notable performance bottleneck.
2. Locked Objects Limit per Session: In Postgres 17 and older, only up to 16 objects per session can be locked locally via the fast-path mechanism. Anything beyond this threshold forces access to the central shared lock table, triggering massive spikes in lightweight locks (LWLocks). Postgres 18 introduces significant improvements to this mechanism, allowing customization based on workload needs—making a version upgrade highly recommended for environments handling heavy locking workloads.

Reference

Linux and PostgreSQL in the Multiverse of Connections