1 of 19

Beyond Execution Hooks

Designing Low-Overhead Telemetry and Diagnostic Architectures

Speaker : Kanishk Verma

2 of 19

Agenda

00:00 - 03:00	The Hook Paradigm: Power and Ubiquity
06:00 - 12:00	The Anti-Pattern: Inline Processing & Network I/O (With simple use case)
12:00 - 17:00	Simple on demand signal and poll architecture.
17:00 - 23:00	Custom Signals and Interrupts
23:00 - 27:00	Case study: pg_query_state
27:00 - 30:00	Summary & Takeaways

3 of 19

What is a hook?

The Definition

In Postgres, a "hook" is fundamentally a global function pointer found in the core server code (e.g., src/backend/executor/execMain.c).

Execution Logic

Postgres checks if the pointer is set. If null, it runs the standard function. If set, it redirects to the custom function registered by the extension.

Lifecycle Stages

Parsing

Planning

Exec Start

Exec Run

Exec End

warning

Crucial Detail: Synchronous Execution

Execution runs inline within the backend process handling the client connection. If your hook takes time, the client blocks.

4 of 19

The Hook Paradigm: Power and Ubiquity

Standard Hooks: (e.g., ExecutorEnd_hook, ProcessUtility_hook) allowing extensions to hijack execution.

The Success Pattern:

Purely CPU-bound operations.
Ultra-low memory footprints.
Pre-allocated shared memory structures.

"Let’s start with the basics: how do extensions actually hook into Postgres?

In Postgres, a 'hook' is fundamentally a global function pointer. If you look at the core server code—for example, in src/backend/executor/execMain.c—you’ll find definitions like ExecutorRun_hook. During a standard query lifecycle, Postgres checks if this pointer is set. If it is null, it executes standard_ExecutorRun. If it is set, it redirects execution to the custom function registered by the extension.

This happens at every critical stage of the query lifecycle: parsing, planning, execution start, run, and end. It’s simple, elegant, and highly effective. But notice one crucial detail: this execution is completely synchronous and runs inline within the backend process handling the client connection. If your hook takes time, the client blocks."

5 of 19

Hook Chaining Internals & "Broken Chains"

Registration & Setup

// Global pointer to previous hook

static ExecutorRun_hook_type prev_ExecutorRun = NULL;

void _PG_init(void) {

/* Save the next step in the chain */

prev_ExecutorRun = ExecutorRun_hook;

/* Insert our custom hook at the head */

ExecutorRun_hook = my_custom_ExecutorRun;

}

Execution Logic

static void my_custom_ExecutorRun(...) {

// 1. Pre-execution logic (CPU bound only!)

...

// 2. Call next link in chain

if (prev_ExecutorRun)

prev_ExecutorRun(...);

else

standard_ExecutorRun(...);

// 3. Post-execution logic

...

}

6 of 19

The Anti-Pattern: Inline Processing & Network I/O

dangerous

Real-World Use Case: Telemetry to Remote Store

How to send the telemetry data such as the query execution time or the query statistics to the remote store such as Spanner or BigQuery or Another DataStore.

7 of 19

Operationalizing Telemetry: Design Solution

settings_input_component

Solution 1: Integrated Inline Telemetry

Implementation of a network call for telemetry directly within the inline hook to persist metrics to highly available datastores.

Target Datastore: Spanner or BigQuery or Another DataStore.
Hook Placement: Inline within execution lifecycle

warning

Note: Inline processing introduces network I/O latency hazards.

Direct network calls in hooks can cause 100x query latency spikes.

8 of 19

9 of 19

Mathematical Proof of Connection Exhaustion

Scenario A: Baseline

Latency: 0.1 ms

10,000 QPS

Max throughput per connection

Scenario B: 10ms Inline Block

Latency: 10.1 ms

~99 QPS

Max throughput per connection

With a max_connections pool of 100, the total system capacity suffers a catastrophic drop:

1,000,000 QPS -> 9,900 QPS

warning

Result: Denial of Service (DoS)

Connection pool exhaustion occurs. Clients timeout, backends saturate, and the database becomes unavailable.

10 of 19

Solution 2: Asymmetric Offloading

Instead of sending metrics over the network inline, the backend process writes metrics into a shared memory segment.

Specific Implementation

Ring Buffer

Ensures high-performance write operations without the overhead of traditional locking mechanisms.

11 of 19

"How do we solve this? We decouple the query path from the processing path. We must never do I/O inside the backend process handling a client's query. Instead, the backend process writes the telemetry payload directly into a pre-allocated, lock-free shared memory buffer. Writing to RAM takes nanoseconds. If the buffer is full, we do not block. We gracefully drop the data and increment a counter. It is far better to lose a few metrics than to crash your database. To process this data, we spin up a Dynamic Background Worker. This is an independent process managed by Postgres that runs in the background. It polls the shared memory buffer, aggregates the data in batches, and handles all the expensive network calls and disk writes asynchronously, completely isolated from the query execution loop."

12 of 19

What happens when the system is under extreme load and the Background-worker is too slow?

1. Data Store Slow

The remote log data store slows down, causing the Background Worker (BGW) to block on network write calls.

2. High Qps

Client queries execute quickly, writing metrics to shared memory. The write pointer catches up to the BGW read pointer.

priority_high

The Critical Decision

The system must choose between two suboptimal outcomes:

Block query execution (Direct impact on user latency)
Lose telemetry records (Data loss for monitoring/billing)

Solution : Lossy Drop (Load-Shedding)

Effect : Incomplete metrics, but database stays online

13 of 19

Solution 2.1: On-Demand Diagnostics

Beyond Telemetry Logging: Solving for Interactive Diagnostics

The Challenge

A query is stuck (e.g., running for 10 minutes), and you need to identify the specific join node causing the delay.

Why existing methods fail:

Background logging pools are too heavy.
Logging every node transition creates gigabytes of memory overhead.
You only need diagnostics for this specific query, right now.

The Requirement

Inspect execution state on-demand

Zero Overhead Goal:

Eliminate performance penalties when diagnostics are not actively being performed.

"We need a way to inspect the execution state of an active backend on-demand, without paying any overhead when we aren't looking."

14 of 19

Custom Signals and Interrupts Architecture

15 of 19

Let’s understand the real world example of pg_query_state But what is it?

The pg_query_state module provides facility to know the current state of query execution on working backend.

To enable this extension, you have to patch the stable version of PostgreSQL, recompile it, and deploy new binaries. All patch files are located in the patches/ directory.

Key Use Cases

Detect long-running queries (along with other monitoring tools)
Overwatch query execution in real-time

Note: Extension requires patching stable PostgreSQL versions based on the specific version number suffix.

16 of 19

17 of 19

Production case study :

pg_query_state

"Now let's examine the actual runtime execution of pg_query_state under the hood. To make this clear, we will separate this architecture into a Control Plane and a Data Plane.

Think of the Control Plane as our static coordination layer. We have a pre-allocated table in global shared memory called the SharedControlArray, with one slot per backend PID. When you run a query like SELECT pg_query_state(200), the requester backend scans this array, locks the slot for PID 200 using an atomic Compare-and-Swap instruction, and writes its request metadata there.

But how does the requester pass a large

100

KB

100 KB query plan JSON back to the client without locking up this control table? It sets up a Data Plane.

Because Postgres backends are separate processes, they map RAM differently. A pointer in my memory is garbage to your memory. To solve this, the requester creates a Dynamic Shared Memory segment, or DSM, and gets a global integer ID called a dsm_handle. It writes this handle ID into the target's control slot.

Then, we ring the doorbell. The requester sends SIGUSR1 to target PID 200. The target catches the signal, sets a volatile flag, and continues its loop until it hits CHECK_FOR_INTERRUPTS(). Once safe, it reads its control slot, grabs the dsm_handle address, and attaches to the DSM data plane.

To move the plan data, we initialize an shm_mq—Postgres's internal Shared Memory Message Queue. Here is where the synchronization magic happens:

Developers don't need to write custom mutexes or sleep loops. The shm_mq API uses PostgreSQL Latches under the hood. When the requester calls shm_mq_receive(), it sees the queue is empty, calls WaitLatch, and goes to sleep. It consumes 0% CPU while waiting.

The target backend attaches, serializes its plan, and calls shm_mq_send(). This copies the plan bytes into the queue and automatically triggers SetLatch to wake the requester. The queue acts as a streaming pipe: if the plan is larger than the queue buffer, the target will safely block and sleep until the requester consumes data and frees up space.

Once the target finishes, it detaches. The requester reads the EOF marker, detaches, and completely destroys the DSM segment, returning all memory back to the OS.

This Control-vs-Data plane separation gives us secure, synchronized, high-throughput diagnostics with absolute memory efficiency and zero baseline overhead."

18 of 19

Summary & Takeaways

Do Not Block the Executor: Hooks must stay lightweight and CPU-bound.
Correct datastructures and locks : Ring buffer in asymmetric offloading.
Isolate I/O in BG Workers: Keep disk and network out of the query execution path.
Prioritize the Database: Always drop telemetry before stalling query backends.

"No architectural pattern comes for free. When designing extensions, choose your trade-offs carefully. Decoupled Signal & Poll is fantastic for telemetry, monitoring, and logging, where the database's primary job is query execution, and minor metric loss under extreme load is acceptable. However, if you are writing security audit logs, you might require a guarantee that every query is recorded before it returns. In that case, you have no choice but to use a synchronous hook. But you must accept the massive latency overhead, heavily optimize your writes, and size your connection pools accordingly. Also, beware of signal storms! Waking up a BGW via SendProcSignal for every single simple SELECT query will overwhelm the CPU with context switches. Coalesce signals: write to the queue, but only send the signal when the buffer is half full or at set time intervals."

19 of 19

References and further Readings