pg-tracer: Full-Stack eBPF Observability for PostgreSQL

Executive Summary & Value Proposition

In modern database reliability engineering, traditional observability tools operate exclusively in user space inside the database SQL engine. While these tools answer what queries are running, they leave a critical Observability Gap: they cannot reveal why a PostgreSQL backend process stalls when the performance bottleneck occurs below the database engine inside the Linux kernel.

pg-tracer bridges this gap. Using Extended Berkeley Packet Filter (eBPF) technology, kernel tracepoints, kernel probes (kprobes/kretprobes), user probes (uprobes), User-Space Statically Defined Tracing (USDT), and eBPF stack sampling, pg-tracer captures dynamic, full-stack metrics directly from the kernel and PostgreSQL binary without requiring source code modifications, database extensions, server restarts, or high CPU overhead.

flowchart TD
    subgraph UserSpacePostgres ["User Space (PostgreSQL Engine)"]
        A["SQL Queries / Transactions"] --> B["Traditional Tools: pg_stat_statements & pg_stat_activity"]
        B --> C["Observability Gap: Sees query text & wait names, BUT misses OS kernel stalls"]
        A --> D["Engine Probes: exec_simple_query, LockAcquire, XLogInsert, ReadBuffer_common"]
    end

    subgraph KernelSpace ["Kernel Space (Linux Subsystems)"]
        D --> E["Syscalls Subsystem: read, write, pread64, pwrite64, fsync, fdatasync, openat, close"]
        E --> F["VFS Subsystem: vfs_read, vfs_write, vfs_fsync"]
        F --> G["Block I/O Subsystem: block_rq_issue, block_rq_complete"]
        E --> H["CPU Scheduler Subsystem: sched_switch, sched_wakeup, sched_migrate_task"]
        E --> I["Memory Management: page_fault_user, mm_page_alloc, mm_page_free, oom_score"]
        E --> J["Network Subsystem: tcp_sendmsg, tcp_recvmsg, tcp_retransmit_skb"]
    end

    subgraph Correlation ["Cross-Layer Correlation Engine"]
        K["Unified Correlation Key: Process ID (PID) & Thread ID (TID)"]
        E --> K
        D --> K
    end

Loading

---

Theoretical Foundation: The Observability Gap

Database administrators (DBAs) and Site Reliability Engineers (SREs) frequently encounter performance anomalies that standard SQL stat views fail to explain. Standard SQL stat views (pg_stat_statements, pg_stat_activity, pg_locks) operate at the top of the stack and cannot report kernel-level stalls:

• Kernel Scheduling Preemption: A PostgreSQL backend thread is ready to run but CPU cores are saturated by background system tasks or other database backends.
• Physical Block Device Latency: A query issues an I/O request, but disk queue depth or storage controller saturation causes long wait times.
• VFS Cache Misses: Read requests pass through the Virtual File System but fail to hit the kernel page cache, forcing physical disk access.
• Memory Pressure & Page Fault Stalls: Minor and major page faults stalling query execution during large memory allocations or sorting operations.
• Network Transport Latency & Retransmissions: TCP packet loss or socket buffer bottlenecks during client query execution or Write-Ahead Log (WAL) replication.
• Off-CPU Lock Contention: Backends sleeping on kernel futexes or waiting for I/O operations while off-CPU.

pg-tracer solves this problem by using a 3-Layer Unified Tracing Model:

1. Layer 1 (User Space / PostgreSQL Internals): Traces PostgreSQL internal functions (exec_simple_query, LockAcquire, XLogInsert, XLogFlush, ReadBuffer_common) using uprobes and USDT probes.
2. Layer 2 (Kernel Space / OS Subsystems): Traces syscalls, VFS functions, block device queues, CPU scheduler task switches, memory page allocations/faults, and TCP sockets using eBPF tracepoints and kprobes.
3. Layer 3 (Cross-Layer Correlation Engine): Correlates high-level SQL queries with lower-level OS bottlenecks using the backend Process ID (PID) and Thread ID (TID) as unifying keys.

---

Dual-Space System Architecture

pg-tracer is built around a dual-space decoupled architecture separating in-kernel C/eBPF probes from user-space Go analytics and visualization.

flowchart LR
    subgraph Kernel ["Kernel Space (C / eBPF)"]
        direction TB
        KP["kprobes / tracepoints / USDT / perf_event"] --> MAPS["Inflight Hash Maps & Stack Trace Maps"]
        KP --> LOG2["In-Kernel Log2 Histogram Maps (Sub-us Buckets)"]
        KP --> RB["BPF Ring Buffer (Lock-Free Event Queue)"]
    end

    subgraph UserSpace ["User Space (Go Application)"]
        direction TB
        RB --> RING["RingBufferReader (Zero-Alloc Async Stream)"]
        MAPS --> FLT["PIDFilter Manager & VMA Symbolizer"]
        DISC["procfs Auto-Discovery & ELF Inspector"] --> FLT
        RING --> AGG["Analytics Engine (Percentiles & Latency Distributions)"]
        LOG2 --> AGG
    end

    subgraph Presentation ["Presentation Layer (Output)"]
        AGG --> CLI["CLI Stream & Stats Engine (JSON, Log2 Histograms, Periodic Summaries)"]
        MAPS --> PROF["Brendan Gregg HTML/SVG Flame Graph Generator"]
    end

Loading

Technology Stack

• Kernel Space: C11 eBPF bytecode compiled via Clang/LLVM with BPF CO-RE (Compile Once - Run Everywhere) utilizing vmlinux.h BTF metadata definitions.
• User Space: Go application loading eBPF ELF objects using cilium/ebpf and auto-generated bpf2go bindings.
• Presentation Layer:
  • CLI Tracing Engine supporting text event streaming, JSON event output, periodic statistics summaries, and in-kernel log2 ASCII histogram rendering.
  • Interactive Brendan Gregg HTML/SVG Flame Graph Profiler with VMA-to-ELF PID symbol resolution.

---

Shared Event Layout & Memory Alignment

Event data streams from kernel space to user space via a BPF ring buffer (BPF_MAP_TYPE_RINGBUF). Both C and Go define identical 128-byte event structures with explicit compile-time alignment assertions.

Offset  Size  Field          Description
  0      8    timestamp_ns   Kernel ktime in nanoseconds
  8      4    pid            Process ID
 12      4    tid            Thread ID
 16      4    cpu            CPU core ID
 20      2    event_type     Subsystem identifier
 22      2    event_subtype  Specific event / tracepoint ID
 24      8    latency_ns     Operation duration in nanoseconds
 32      8    return_value   Syscall or function return code
 40      8    arg1           Subsystem-specific argument 1 (e.g. fd, offset, bytes)
 48      8    arg2           Subsystem-specific argument 2 (e.g. runq_latency_ns)
 56     16    comm           Process command name (16 bytes)
 72     56    detail         Human-readable context detail string (56 bytes)
Total: 128 bytes

C & Go Static Size Guards

Compile-time assertions in both C and Go prevent memory layout drift:

• C BPF Guard: _Static_assert(sizeof(struct event) == 128, "struct event must be 128 bytes");
• Go Guard: var _ = [1]struct{}{}[1-int(unsafe.Sizeof(Event{}))/128]

---

Probe Latency Measurement & Map Patterns

eBPF probes measure duration using entry/exit probe pairs connected by BPF Hash maps.

sequenceDiagram
    autonumber
    participant PG as PostgreSQL Backend Thread
    participant KP1 as sys_enter_* Tracepoint / Kprobe
    participant MAP as inflight BPF Hash Map
    participant KP2 as sys_exit_* Tracepoint / Kretprobe
    participant RB as BPF Ring Buffer
    participant GO as Go User-Space Collector

    PG->>KP1: Issue Syscall / Kernel Operation
    KP1->>KP1: Check if PID is in target_pids
    KP1->>MAP: Store entry timestamp & arguments [Key: TID]
    PG->>PG: Kernel Operation Execution
    PG->>KP2: Syscall / Kprobe Completion
    KP2->>MAP: Lookup entry timestamp by TID
    KP2->>KP2: Calculate duration (latency = now - start_ts)
    KP2->>MAP: Delete entry from inflight map
    KP2->>RB: Reserve & submit 128-byte Event struct
    RB->>GO: Asynchronous ringbuf read event stream

Loading

---

Detailed Subsystem Tracing Specifications

1. Core Syscall Tracer

• Tracepoints: 18 enter and exit handlers covering read, write, pread64, pwrite64, fsync, fdatasync, sync_file_range, openat, and close.
• Inflight Map: Keyed by Thread ID (TID) to prevent collisions across concurrent PostgreSQL backends.
• Captured Data: File descriptor, I/O byte count, file offset, syscall return code, and latency in nanoseconds.
• Tracing Command:

  sudo ./build/bin/pg-tracer trace --probes syscall --log-level debug
  sudo ./build/bin/pg-tracer trace --probes syscall --stats --interval 2s

2. Virtual File System (VFS) Tracer

• Kernel Probes: kprobe/vfs_read, kretprobe/vfs_read, kprobe/vfs_write, kretprobe/vfs_write, kprobe/vfs_fsync, kretprobe/vfs_fsync.
• Purpose: Measures VFS latency directly beneath syscalls, allowing pg-tracer to isolate file system cache hits from underlying physical block storage requests.
• Tracing Command:

  sudo ./build/bin/pg-tracer trace --probes vfs --log-level debug
  sudo ./build/bin/pg-tracer trace --probes syscall,vfs --stats --interval 2s

3. Block I/O Tracer

• Tracepoints: block/block_rq_issue and block/block_rq_complete.
• Request Pointer Keying: Uses the kernel request structure pointer (struct request *) as key in the block_inflight map. This guarantees accurate matching even when completion executes in interrupt context (irq) on a different thread/CPU.
• In-Kernel Log2 Histogram: Bucketizes block device latencies directly in kernel space into log2 power-of-two microsecond buckets, reducing ring buffer event overhead to zero during summary statistics collection.
• Tracing Command:

  sudo ./build/bin/pg-tracer trace --probes block --log-level debug
  sudo ./build/bin/pg-tracer trace --probes block --histogram --interval 2s

4. CPU Scheduler Tracer

• Tracepoints: sched/sched_switch, sched/sched_wakeup, sched/sched_wakeup_new, sched/sched_migrate_task.
• Off-CPU Time: Calculated when a target PostgreSQL thread switches off CPU (sched_switch prev_pid) and switches back on (sched_switch next_pid).
• Run Queue Latency (Scheduling Delay): Measures duration between thread wakeup (sched_wakeup / sched_wakeup_new) and CPU assignment (sched_switch). Aggregated in-kernel in sched_runq_hist.
• Counters: Tracks real-time context switches per second, CPU migrations across cores/NUMA sockets, and run queue latency averages.
• Tracing Command:

  sudo ./build/bin/pg-tracer trace --probes sched --log-level debug
  sudo ./build/bin/pg-tracer trace --probes sched --stats --histogram --interval 2s

5. Memory Management Tracer

• Tracepoints: exceptions/page_fault_user, exceptions/page_fault_kernel, kmem/mm_page_alloc, kmem/mm_page_free, oom/oom_score_adj_update.
• Page Fault Classification: Inspects error_code bit 1 to differentiate minor page faults from major page faults that require physical disk reads.
• Page Throughput Calculation: Computes exact page counts using order bit-shifts (1 << order), reporting page allocation rate, page free rate, and net memory allocation deltas.
• OOM Monitoring: Tracks Out-Of-Memory score adjustments triggered by system managers or container runtimes.
• Tracing Command:

  sudo ./build/bin/pg-tracer trace --probes memory --log-level debug
  sudo ./build/bin/pg-tracer trace --probes memory --stats --interval 2s

6. Network Transport Tracer

• Kernel Probes: kprobe/tcp_sendmsg, kretprobe/tcp_recvmsg, kprobe/tcp_recvmsg, kretprobe/tcp_recvmsg, kprobe/tcp_retransmit_skb.
• Metrics: Socket send/recv execution latency, message counts, total bytes transferred, and TCP retransmission rates per PostgreSQL backend.
• Tracing Command:

  sudo ./build/bin/pg-tracer trace --probes network --log-level debug
  sudo ./build/bin/pg-tracer trace --probes network --stats --interval 2s

7. PostgreSQL Engine Query Tracer (uprobes)

• User Probes: uprobe/exec_simple_query and uretprobe/exec_simple_query.
• Metrics: Captures full SQL query execution latency (latency_ns) and query text (up to 56 bytes in e->detail).
• Container Support: Resolves containerized PostgreSQL binaries via /proc/[pid]/exe auto-discovery without requiring host binary mounts.
• Tracing Command:

  sudo ./build/bin/pg-tracer trace --probes pg-query --log-level debug
  sudo ./build/bin/pg-tracer trace --probes pg-query --format json

8. PostgreSQL Lock Tracer (uprobes)

• User Probes: uprobe/LockAcquire, uretprobe/LockAcquire, uprobe/LockRelease, uprobe/LWLockAcquire, uretprobe/LWLockAcquire, uprobe/LWLockRelease.
• Metrics: Heavyweight lock wait duration, lock hold time, lock mode (ExclusiveLock, ShareLock, etc.), and LWLock wait duration.
• Tracing Command:

  sudo ./build/bin/pg-tracer trace --probes pg-locks --log-level debug
  sudo ./build/bin/pg-tracer trace --probes pg-locks --format json

9. PostgreSQL Write-Ahead Log (WAL) Tracer (uprobes)

• User Probes: uprobe/XLogInsert, uretprobe/XLogInsert, uprobe/XLogFlush, uretprobe/XLogFlush.
• Metrics: WAL record insert rates/s (XLogInsert), WAL flush call rates and flush duration (XLogFlush), and in-kernel log2 latency histograms (wal_flush_hist).
• Tracing Command:

  sudo ./build/bin/pg-tracer trace --probes pg-wal --log-level debug
  sudo ./build/bin/pg-tracer trace --probes pg-wal --stats --histogram --interval 2s

10. PostgreSQL Shared Buffer Tracer (uprobes)

• User Probes: uprobe/ReadBuffer_common (or ReadBuffer), uretprobe/ReadBuffer_common, uprobe/FlushBuffer, uretprobe/FlushBuffer.
• Metrics: Buffer reads/s, buffer hits/s, buffer misses/s, buffer cache hit ratio %, and dirty buffer flush count (FlushBuffer).
• Tracing Command:

  sudo ./build/bin/pg-tracer trace --probes pg-buffer --log-level debug
  sudo ./build/bin/pg-tracer trace --probes pg-buffer --stats --interval 2s

11. PostgreSQL Checkpoint Tracer (uprobes)

• User Probes: uprobe/CreateCheckPoint, uretprobe/CreateCheckPoint, uprobe/BufferSync, uretprobe/BufferSync.
• Metrics: Full checkpoint execution latency (CreateCheckPoint), dirty buffer sync duration (BufferSync), and checkpoint count.
• Tracing Command:

  sudo ./build/bin/pg-tracer trace --probes pg-checkpoint --log-level debug
  sudo ./build/bin/pg-tracer trace --probes pg-checkpoint --format json

12. PostgreSQL USDT Probe Support (DTrace)

• USDT Tracepoints: postgresql:query__start, postgresql:query__done, postgresql:transaction__start, postgresql:transaction__commit, postgresql:lock__wait__start, postgresql:lock__wait__done, postgresql:buffer__read__start, postgresql:buffer__read__done.
• Pure Go Note Parser: Zero-dependency .note.stapsdt SystemTap note metadata parser resolving probe locations and reference counter semaphores (RefCtrOffset).
• Tracing Command:

  sudo ./build/bin/pg-tracer trace --probes pg-usdt --log-level debug
  sudo ./build/bin/pg-tracer trace --probes pg-usdt --stats --interval 2s

13. PostgreSQL Process Auto-Discovery & Symbol Inspection

flowchart TD
    Start["Start Process Discovery"] --> ProcScan["Scan /proc for running processes"]
    ProcScan --> CmdCheck["Inspect /proc/[pid]/cmdline & exe link"]
    CmdCheck --> FilterPG["Identify PostgreSQL processes (Exclude non-postgres binaries)"]
    FilterPG --> RoleClass["Classify Process Roles (Postmaster, Checkpointer, WALWriter, Backend, etc.)"]
    RoleClass --> ElfCheck["Inspect /proc/[pid]/exe ELF Binary"]
    ElfCheck --> DwarfVal["Validate DWARF Debug Info & Section Headers"]
    ElfCheck --> UsdtVal["Count USDT Probes (.note.stapsdt section)"]
    ElfCheck --> SymVal["Verify Engine Symbol Table (exec_simple_query, LockAcquire, XLogInsert, ReadBuffer_common)"]
    SymVal --> PopulateMap["Populate target_pids BPF Map for Kernel Filtering"]

Loading

• Process Scanner: Reads /proc to discover PostgreSQL processes and determine parent-child hierarchies and process roles (postmaster, checkpointer, background writer, walwriter, autovacuum launcher, logical replication launcher, backend), filtering out host tool binaries (docker, bash, grep, pgbench).
• Container-Aware Symbol Inspection: Resolves /proc/[pid]/exe to inspect the PostgreSQL binary via Go debug/elf to count USDT probes (.note.stapsdt section) and verify DWARF debug symbols regardless of container boundaries.
• Engine Symbol Verification: Verifies presence of key internal symbols required for uprobe attachment: exec_simple_query, LockAcquire, LWLockAcquire, XLogInsert, XLogFlush, ReadBuffer_common, FlushBuffer, smgrread, smgrwrite, CreateCheckPoint, BufferSync.
• Tracing Command:

  sudo ./build/bin/pg-tracer trace --auto-discover --probes all --log-level debug

---

Command Line Usage & Options

Build Commands

# Check build dependencies
make check-deps

# Generate eBPF Go bindings using bpf2go
make generate

# Build binary
make build

The compiled binary will be placed at build/bin/pg-tracer.

Flame Graph Profiler Execution

Sample PostgreSQL execution for 15 seconds and generate On-CPU & Off-CPU interactive flame graphs:

sudo ./build/bin/pg-tracer profile --duration 15s
# Outputs flamegraph_oncpu.html and flamegraph_offcpu.html

Open generated Flame Graphs in your browser:

xdg-open flamegraph_oncpu.html
xdg-open flamegraph_offcpu.html

Trace Mode Execution

Run tracer with auto-discovery:

sudo ./build/bin/pg-tracer trace --auto-discover

Run in combined statistics and histogram mode:

sudo ./build/bin/pg-tracer trace --probes syscall,vfs,block,sched,memory,network --stats --histogram --interval 5s

---

License

Dual-licensed under MIT and GPL-2.0. See the LICENSE file for details.