Status. pg_scalpel is on GitHub at dbgreen/pg_scalpel — Linux, x86-64, PostgreSQL 19. It’s superuser-only and won’t let you make it otherwise; the last section says why. Issues and patches welcome.
Here’s a question that comes up constantly: why did the planner pick that plan?
And the honest answer, most of the time, is that you can’t see it. EXPLAIN
shows you the plan the planner picked. That’s it. The planner built a dozen other
paths, costed every one of them, and threw them away, and none of that survives.
auto_explain and pg_store_plans give you the same winner, just later.
You can attach gdb. Now that backend is frozen, along with anyone waiting on a
lock it holds, and you’re typing p *path one pointer at a time at a prompt that can’t loop
over a list. You can rebuild with -DOPTIMIZER_DEBUG and grep the log, which
costs you a recompile, a restart, and an afternoon reading text.
Every one of these sees a piece of it. None of them lets you go into the running backend and ask.
So that’s what I wanted. Not a better log. A real language, running inside the backend process, where I can say: give me the address of this function. Show me this struct, laid out the way this build laid it out. Call this. Replace that. On a server that’s already up, without recompiling anything.
One function
pg_scalpel is an extension. You install it as a superuser, and everything in this
post goes through one SQL function, janet_eval(text). It takes a string of
Janet and runs it inside the backend.
CREATE EXTENSION pg_scalpel;
SELECT janet_eval('(+ 40 2)');
-- 42
There’s no shared_preload_libraries entry and no restart. The Janet VM gets
created the first time you call the function, and it lives as long as your
connection does. That matters: anything you define in one statement is still
there in the next one, as long as you’re in the same session. Every multi-step
example below depends on it.
Why Janet? Because it’s a real language — closures, data structures, a garbage
collector — and the whole thing compiles from one C file. You get something you
can think in, embedded in a process that was never designed to host one. Compare
that to gdb’s expression evaluator, which is fine for printing a field and
miserable for anything else.
The catch is that Janet comes with os/execute, os/shell, and file I/O out of
the box. Keep that in mind. It’s the reason this whole thing is superuser-only,
and I’ll come back to it.
Finding things
The first thing you want inside a running backend is to find stuff. Where’s this function? What does it take? How big is this struct, in this binary?
SELECT janet_eval($j$ (scalpel/resolve "int4pl") $j$);
-- <pointer 0x...>
SELECT janet_eval($j$ (string/format "%q" (get (scalpel/sig "add_path") :call)) $j$);
-- (:void (:pointer :pointer))
SELECT janet_eval($j$ (get (scalpel/struct "Path") :size) $j$);
-- 80
These aren’t doing the same thing, and the difference matters. resolve is just
dlsym plus a walk of the symbol table. It finds anything in the symbol table —
exported functions on any build, and statics too if the binary isn’t stripped —
no debug info needed.
sig and struct read DWARF, the debug info, so they only work if it’s there: a
debug build, a -dbgsym package, or a postgres.debug file next to the binary.
If it isn’t, they return nil. They don’t guess. You get nothing instead of
something wrong.
And that 80 is the point. A Path is 80 bytes in this build. Change the
version or the compiler flags and that number can move. Anything that hardcodes
struct offsets is quietly betting it won’t. This doesn’t bet. It reads the layout
out of the binary that’s running.
Calling into the server
An address is only interesting if you can do something with it. So:
scalpel/call. You give it a pointer, the return type, the argument types, and
the arguments. It builds the call with libffi and makes it.
SELECT janet_eval($j$ (string/format "%q"
(scalpel/call (scalpel/resolve "strlen") :uint64 [:cstring] "hello, scalpel")) $j$);
-- (:ok <core/u64 14>)
Look at what came back. Not 14. A 14 wrapped in (:ok ...). The tag is there for
a reason.
When you call into the server’s C, that C can throw an ereport, and in
Postgres an ereport(ERROR) doesn’t return. It longjmps. If nothing catches it,
it unwinds straight out through your Janet code, aborts your transaction, and
leaves whatever Janet was in the middle of half-finished. So every call runs inside a subtransaction, wrapped in PG_TRY/PG_CATCH. If the
function errors, you get (:error {...}) back as a value, and your connection is
still there.
What it can’t do is save you from a segfault. Call a function with the wrong
signature and you hand it garbage, and if it dereferences the garbage, the
process dies. There’s no subtransaction for that. The guard turns an error into
a value. It doesn’t turn a crash into anything. Get the signature from sig
and you mostly won’t have this problem.
Replacing a function
Finding and calling is half of it. The other half is where it gets interesting: replacing a function while the server is running.
scalpel/closure takes a Janet function and gives you back a real C function
pointer — something you could hand to any C code that wants a callback.
scalpel/hook takes a function in the server and rewrites its first few
instructions to jump to that pointer instead.
So here’s int4pl, the C function behind + for integers. Let’s make it add 100:
SELECT janet_eval($j$
(def d (scalpel/closure :uint64 [:pointer]
(fn [fc] (+ (scalpel/getarg fc 0 :int) (scalpel/getarg fc 1 :int) 100))))
(scalpel/hook (scalpel/resolve "int4pl") d) "installed" $j$);
SELECT 2 + 3; -- 105
SELECT janet_eval($j$ (scalpel/unhook (scalpel/resolve "int4pl")) $j$);
SELECT 2 + 3; -- 5, back to normal
Think about what happened there. Postgres evaluated 2 + 3 the way it always
does, by calling int4pl. Nothing about that call changed. What changed is the
first few bytes of int4pl. funchook overwrote them with a jump to our closure,
and moved the instructions it overwrote into a trampoline, so the original can
still be called.
The closure pulls the two arguments out of the FunctionCallInfo with getarg,
adds them, adds 100, and returns.
unhook puts the saved original bytes back, and 2 + 3 is 5 again.
On a live server. On the function + is built on. No restart.
Watching the planner
Now back to the question from the top: why that plan?
The planner works by building candidate paths. For each relation, and each join,
it builds the ways it could produce that thing — a seq scan, an index scan, a
hash join, a merge join — and hands each one to add_path. add_path decides
whether the new path is worth keeping or whether something already on the list
beats it. Then set_cheapest picks the winner.
So if you hook add_path, you see every candidate. Not the winner. All of them.
And you’re looking at the actual Path structs, so you get more than a cost. A
join path holds pointers to the outer and inner paths it was built from, and
they’re right there in memory. You can see what each join would have run.
The repo has this as a worked example, planner-observatory.janet. It hooks
add_path and set_cheapest (plus get_relation_info, to learn the table
names), reads each path straight out of the Path and
RelOptInfo structs — type, row estimate, startup and total cost — records
whether it survived, and for every join, the two inputs under it. Give the
planner something to choose between. Autovacuum is off on this table on purpose,
and you’ll see why:
CREATE TABLE t(a int, b int) WITH (autovacuum_enabled = off);
INSERT INTO t SELECT g % 100, g FROM generate_series(1, 5000) g;
CREATE INDEX ON t(a);
ANALYZE t;
SELECT janet_eval($j$
(os/setenv "PGJ_NODETAGS" "/your/pg/install/include/postgresql/server/nodes/nodetags.h")
(def obs (scalpel-load "janet-lib/planner-observatory"))
(obs :arm) $j$);
EXPLAIN (COSTS OFF) SELECT count(*) FROM t t1 JOIN t t2 USING (a) WHERE t1.b < 200;
SELECT janet_eval($j$ (obs :dump) $j$);
Two bits of setup in there. PGJ_NODETAGS points the observatory at the build’s
nodetags.h, which is how it turns node tag numbers into names like HashJoin.
Without it you get Tag#302, and it can’t tell which paths are joins. And
scalpel-load finds the file relative to ~/pg_scalpel in the server’s home
directory; if your checkout is somewhere else, (set scalpel-root "...") first.
EXPLAIN says Hash Join. Here are three of the seven join paths the planner
built (t{1} and t{2} are the two sides of the self-join):
{1,2} (7 considered, 1 kept, 6 rejected)
* HashJoin rows=9950 cost= 135.50..333.44
outer SeqScan t{1} rows=199 cost=0.00..85.50
inner SeqScan t{2} rows=5000 cost=0.00..73.00
x NestLoop rows=9950 cost= 0.29..366.39
outer SeqScan t{1} rows=199 cost=0.00..85.50
inner Memoize t{2} rows=50 cost=0.29..1.77 param
<- IndexOnlyScan t_a_idx on t{2} rows=50 cost=0.28..1.76 param
…
x NestLoop rows=9950 cost= 0.28..535.34
outer SeqScan t{1} rows=199 cost=0.00..85.50
inner IndexOnlyScan t_a_idx on t{2} rows=50 cost=0.28..1.76 param
…
Now you can actually read the decision. Both plans start the same way: seq scan
t1, keep the 199 rows with b < 200. Where they differ is how they find the
matches in t2. The hash join reads all 5,000 rows of t2 once and builds a
hash table out of them. That’s the 135.50 startup cost — paid before the first
row comes out. The nested loop doesn’t build anything. For each of the 199
outer rows it goes into t2’s index and pulls the 50 matching rows. Each trip
into the index is costed at 1.76.
Look at the last one first. That’s the plain nested loop, 199 real trips into
the index, and it comes in at 535.34. Nowhere close. The one that almost wins
puts a Memoize in front of the index. Those 199 rows only have about 87
distinct values of a between them, so once a value has been looked up, the
next row with the same a gets its 50 matches out of a cache. The planner
expects 56% of the lookups to be hits (EXPLAIN on that plan prints the
estimate), which is what knocks it down to 366.39.
So “hash join or nested loop” comes down to one question: is building a hash
table over t2 cheaper than about 87 trips into its index plus a cache for the
rest? At 1.76 a trip, it is — 333.44 to 366.39.
Then why is a trip 1.76? It’s an index-only scan, and the only column it needs is
a, which is in the index. It shouldn’t need the table at all. But an
index-only scan can only skip the table for pages the visibility map says are
all-visible, and this table has never been vacuumed:
SELECT relpages, relallvisible FROM pg_class WHERE relname = 't';
-- 23 | 0
Zero of 23 pages. So the planner charges every probe for the heap pages it would have to visit, as if it were a plain index scan. That’s the input that decided the plan.
Now vacuum it, clear the observatory’s log, and plan the same query again:
VACUUM t; -- relallvisible: 23 of 23
SELECT janet_eval($j$ (obs :clear) $j$);
EXPLAIN (COSTS OFF) SELECT count(*) FROM t t1 JOIN t t2 USING (a) WHERE t1.b < 200;
-- Aggregate -> Nested Loop -> Seq Scan on t t1, Memoize -> Index Only Scan on t t2
{1,2} (10 considered, 1 kept, 9 rejected)
* NestLoop rows=9950 cost= 0.29..325.91
outer SeqScan t{1} rows=199 cost=0.00..85.50
inner Memoize t{2} rows=50 cost=0.29..1.31 param
<- IndexOnlyScan t_a_idx on t{2} rows=50 cost=0.28..1.30 param
x HashJoin rows=9950 cost= 135.50..333.44
outer SeqScan t{1} rows=199 cost=0.00..85.50
inner SeqScan t{2} rows=5000 cost=0.00..73.00
…
Look at what moved and what didn’t. The hash join is 333.44, exactly what it was, because it never touches the index. The probe went from 1.76 to 1.30. The nested loop went from 366.39 to 325.91, and now it wins.
Same query. Same data. Same row estimates — I didn’t re-ANALYZE. The plan changed
because VACUUM set bits in the visibility map. On a real server you don’t run
that VACUUM. Autovacuum does, whenever it gets to the table, and that’s how you
get “the plan changed overnight and nobody touched anything.” It happened to me
while I was putting this post together: the first version of this example had
autovacuum on, and the same query came back with a different plan half a minute
later.
That’s the answer to “why that plan.” Not “the hash join was cheapest” — EXPLAIN
already told you that. The hash join won because the trips into t2’s index were
priced as heap visits, because the visibility map was empty. EXPLAIN can’t tell
you that. It never shows you the plan that lost, let alone what the loser was
built from.
(This was also the first thing I built on pg_scalpel that wasn’t about breaking something.)
Try it yourself
Everything is at github.com/dbgreen/pg_scalpel. The README has the build steps, what you need installed, and the examples from this post, and docs/reference.md covers every function.
If you want sig and struct, the Postgres you load it into needs its debug
info. Without it, resolve, call, and hooking still work. You just lose the
DWARF half.
What it costs
This is the part to take seriously.
janet_eval runs code inside the backend process, as whatever OS user Postgres
runs as. Janet alone can already run shell commands and read and write files.
pg_scalpel adds reading and writing arbitrary memory and rewriting machine code.
Give that to a role you don’t trust and you haven’t given them a database
feature. You’ve given them the machine.
So it’s locked to superuser three separate ways, because each one alone is one
mistake from not working. The control file says superuser = true and
trusted = false, so it can’t be installed for a non-superuser. The install
script revokes execute on janet_eval from PUBLIC. And janet_eval checks
superuser() itself and errors if you aren’t one, so a GRANT somebody adds
later still doesn’t let anyone in.
Be clear about what that is, though. It’s a lock on the door. It is not a sandbox. There’s nothing to sandbox — reading memory and patching code is the entire point of the tool. Don’t install it on a server where people you don’t trust can connect.
Where it stands
Right now it runs on Linux, x86-64, Postgres 19. The symbol and struct reading is elfutils and DWARF, the disassembler is capstone, and the hooking is funchook. A Windows port is almost done, and that’s the next platform.
What it needs most is testing. Then more testing. It’s been run hard against the builds I work on, but “works on my builds” is not the same thing as “works,” and a tool that rewrites machine code in a live backend is going to find every edge it hasn’t been tested on.
So be clear about what it’s for. It’s an exploration tool. I use it to learn how Postgres works on the inside — watching the planner, reading structs out of a backend while it runs — and for security testing, where poking at a live server is the whole job. It is not something you run against a database you care about.