Reading one row out of SQLite in Node got about 17% faster. A loop that used to pull 1.48 million rows a second now pulls 1.73 million, same machine, same query. Across the twenty read benchmarks Node ships, most got faster and none got slower.

The patch that did it takes 35 lines out of the file. It rewrites nothing and adds nothing: the fast path was already sitting in there, used by exactly one of the three functions that wanted it. It landed in Node.js core last week. So the part worth a post isn’t what the change was - it’s how something that cheap sat in plain sight in a file I’d been living in for months.

Reading the code is not what found it. Measuring is. Nate Berkopec puts that whole discipline in one sentence in The Complete Guide to Rails Performance, and it’s the only part of that course I’d call mandatory:

Repeat after me: I will not optimize anything in my application until my metrics tell me so.

Brendan Gregg names the ways it goes wrong in Systems Performance: the Street Light Anti-Method is investigating with whatever tool you already know, and the Random Change Anti-Method is changing things until a number moves. Both produce activity. Neither produces knowledge.

So here’s the alternative, in the order I actually ran it - and the punchline lands at step 4, where it turned out that a tenth of every single-row read was going into building the names of the columns. Four strings that never change, rebuilt from scratch every time.

1. Make the baseline honest

$ git rev-list --left-right --count main...upstream/main
0	91

Ninety-one commits behind. Merge, rebuild, and then the line that matters more than it looks:

$ make -j10
$ cp out/Release/node /tmp/node-main

make leaves a ./node symlink pointing at out/Release/node. Point a benchmark at ./node and it silently follows whatever you built most recently, so you end up carefully comparing a binary against itself and calling the result a win. Copy the real binary out before you touch anything.

2. Find the noise floor

Now benchmark the baseline against itself - identical binary on both sides, so every difference that comes back is measurement noise. Whatever spread you get is your detection threshold, and any later “win” smaller than it is unfalsifiable.

Same reason you step on the bathroom scale twice before believing you lost 200 grams.

$ ./out/Release/node benchmark/compare.js \
    --old /tmp/node-main --new /tmp/node-main \
    --runs 10 --no-progress \
    --set n=20000 --set tableSeedSize=10000 \
    --filter sqlite-prepare-select-get.js \
    --filter sqlite-prepare-select-all.js sqlite > noise-floor.csv

Node ships benchmark/sqlite/ already, which is worth using: it’s the yardstick a maintainer will judge your PR by, and it removes any argument about whether your harness was fair.

improvement  p-value    old rate    new rate   config
  -1.52%     0.1832      2.31M      2.28M  select-all 'SELECT text_column, integer_column FROM foo LIMIT 1'
  -0.31%     0.8124      1.52M      1.52M  select-get 'SELECT * FROM foo LIMIT 1'
+   1.30%     0.3721      1.49M      1.51M  select-get 'SELECT text,int,real,blob FROM foo LIMIT 1'
+   1.96%     0.2159      3.30M      3.37M  select-get 'SELECT 1'

configs: 20  significant: 0
geomean speedup (all configs): 0.38%

Three things in that output, if it’s new to you, because the rest of the post leans on them. The p-value is the odds of seeing a gap that big if the change did nothing at all - so a high one, like the 0.81 up there, means “this could easily be nothing.” A result gets called significant when that probability is small enough to bet against, and later in the post you’ll see significant rows marked with stars. And geomean is the summary line: an average across all twenty configurations, built so that one spectacular number can’t carry the total on its own.

Here, zero results are significant, which is what identical binaries should produce. The whole spread, -1.52% to +1.96%, is the machine talking to itself.

So: 2%. Anything under that, I’m not allowed to call a win for the rest of this post.

“The first principle is that you must not fool yourself - and you are the easiest person to fool.” Feynman was talking about physics, but the noise floor is that sentence turned into a shell command.

3. Benchmarking and profiling answer different questions

Berkopec tells the best version of this. He had benchmarked a change, found shuffle 12x faster than sort_by { rand }, and took the number to Ryan Davis, the author of minitest. The reply:

“you benchmarked it, but did you profile it?”

A benchmark gives you one number per configuration. It will happily tell you your change made things 3% faster and be completely wrong, because 3% is inside the noise of the machine you ran it on. And it has nothing to say about why anything is slow: run Node’s whole sqlite suite on clean main and you get twenty rates and zero suspects.

A profiler gives you attribution. What it will not give you is whether fixing that attribution is worth anything, because a profile has no control group.

Profile to find a suspect, benchmark to convict it. In that order.

4. Profile, then read the call tree

sample is built into macOS. Two things bite everyone the first time: it attaches to a running process, it never launches one, and it matches partial names, so sample node with a language server running may profile something else entirely. Use the PID. And run dsymutil out/Release/node first, or you get bare addresses instead of symbols.

Don’t profile the benchmark harness either - it mixes seeding, warmup and measurement in one process. Write a workload that reaches a steady state and holds it:

const stmt = db.prepare(`SELECT ${cols} FROM foo LIMIT ${limit}`);
const run = op === 'all' ? () => stmt.all() : () => stmt.get();

for (let i = 0; i < 20000; i++) run();          // warm up
process.stderr.write(`READY pid=${process.pid}\n`);

const deadline = Date.now() + seconds * 1000;   // steady state
while (Date.now() < deadline) {
  for (let i = 0; i < 1000; i++) sink = run();
}

Two traps in reading what comes back. The biggest symbols on the page are __psynch_cvwait, kevent and semaphore_wait_trap - idle libuv threadpool threads parked in the kernel. They mean nothing. And a single cost gets spread across several symbol names, so group by category before comparing magnitudes.

Here’s get() on a four-column row, as a share of non-idle samples:

  1865   20.4%  pthread mutex
  1732   18.9%  sqlite VDBE + btree (real query work)
  1382   15.1%  V8 object construction (dictionary-mode rows)
  1067   11.7%  malloc/free
   739    8.1%  column-name interning (V8 strings)
   542    5.9%  V8 buffers (BLOB -> Uint8Array)
   411    4.5%  sqlite C API entry points
   159    1.7%  node:sqlite binding

The line I want is the fifth: column-name interning, 8.1%.

I had a suspicion about that one already. iterate() had been handing out cached column names for a while, and I’d wondered more than once whether get() and all() could pull from the same place. What I didn’t have was a reason to touch it. A hunch isn’t a number, and Berkopec’s rule is that a hunch doesn’t get to authorize a patch. I have a folder full of hunches about that file and most of them are worth nothing. 8.1% is what got this one out of the folder.

The top line is a bigger number and it is not this post. That’s SQLite’s per-connection mutex, it’s a compile-time flag away, and taking it means writing the missing lock yourself. Different investigation, different PR, and I don’t know yet if that one lands - which is why it doesn’t share a post with a patch that already did.

Now, 8.1% of samples in symbols like StringTable::LookupKey doesn’t say whose strings those are, and V8 interns strings for a dozen reasons. That’s the call tree’s job:

141 node::sqlite::StatementExecutionHelper::Get(...) + 396
  | 121 v8::String::NewFromUtf8(...)
  | : 88 v8::internal::Factory::InternalizeUtf8String(...)
  | : | 54 v8::internal::FactoryBase<...>::InternalizeString(...)
  | : | + 36 v8::internal::StringTable::LookupKey<...>(...)
  | : | 23 v8::internal::FactoryBase<...>::InternalizeString(...)
  | : | + 23 v8::internal::StringHasher::HashSequentialString<...>(...)
  | : 30 v8::internal::Factory::InternalizeUtf8String(...)
  | : | 30 v8::internal::Utf8DecoderBase<...>::Utf8DecoderBase(...)
 78 node::sqlite::StatementExecutionHelper::Get(...) + 372
  | 29 columnName  (in node)
  | : 13 _pthread_mutex_lock_init_slow  (in libsystem_pthread.dylib)
  | 21 columnName  (in node)
  | 18 columnName  (in node)

Two adjacent instruction offsets inside the same function, and they’re the two halves of one operation. +372 calls sqlite3_column_name(), whose implementation in sqlite3.c is columnName. +396 calls String::NewFromUtf8 with kInternalized, and V8 does the honest work: decode the UTF-8, hash it, look it up in the string table.

All of that per column, per call, for a prepared statement whose column names cannot change.

Here it is in the source, and there’s nothing wrong with it - it’s the obvious way to write it:

  const char* col_name = sqlite3_column_name(stmt, column);
  // ...
  return String::NewFromUtf8(
             env->isolate(), col_name, NewStringType::kInternalized)
      .As<Name>();

Nobody reading that function thinks “bottleneck”, because in isolation it isn’t. The profiler is what puts it side by side with sqlite3VdbeExec and prices it: building the keys costs a bit under half of what running the whole query costs.

5. Profile more than one workload shape

Same binary, same code, profiling all() with LIMIT 100 instead of one row. Column-name interning is gone - not smaller, gone. Every symbol in the category fell below sample’s 5-sample cutoff:

symbol get() LIMIT 1 all() LIMIT 100
StringTable::LookupKey 150 -
Utf8DecoderBase 126 -
StringHasher::HashSequentialString 102 -
columnName 88 -
String::NewFromUtf8 65 -

Nothing changed in the code. The cost is identical per call - all() builds the keys once and reuses them for 100 rows, so what’s left disappears under everything else. Meanwhile the mutex barely moved, 20.4% to 19.0%, because that one is paid per value.

It’s shipping cost. Order one book online and the postage is half of what you pay. Order a hundred and it’s a rounding error on the invoice - identical postage, both times. If you only ever look at the hundred-book invoice, you conclude that shipping is free.

So: a cost paid once per call vanishes when you profile many rows, and a cost paid once per row is invisible when you profile one. The heaviest workload is the natural one to profile, and it’s the one I’d have picked if I were picking one. Make one of your shapes small.

6. Read the source of the thing you’re changing

So, back to the suspicion. The cache was already in the file, sitting on StatementSync, keyed on SQLite’s re-prepare counter so it invalidates correctly when a schema change forces a silent re-prepare:

std::vector<v8::Global<v8::Name>> cached_column_names_;
int cached_column_names_reprepare_count_ = -1;

That counter is the part that matters. It’s the reason routing two more callers into the cache is safe rather than clever - the invalidation was already written and already shipping under iterate().

So the change writes no cache. It deletes two loops:

Before, in both get() and all()

row_keys.reserve(num_cols);
for (int i = 0; i < num_cols; ++i) {
  Local<Name> key;
  if (!ColumnNameToName(env, stmt, i)
           .ToLocal(&key)) {
    return MaybeLocal<Value>();
  }
  row_keys.emplace_back(key);
}

After, the same thing iterate() does

if (!statement->GetCachedColumnNames(
        &row_keys)) {
  return MaybeLocal<Value>();
}

Nine lines become four, in two places. The whole patch is 31 insertions against 66 deletions: it takes lines out of the file and makes reads faster.

And no profiler was going to hand me that. It pointed at the function; reading the file is what turned “this is expensive” into “this is expensive and avoidable, with code that’s already here and already trusted.”

7. Benchmark to convict

Rebuild, and measure against the baseline binary from step 1:

improvement  p-value    old rate    new rate   config
  -0.97%     0.3266      26.7k      26.4k  select-all 'SELECT * FROM foo LIMIT 100'
+   0.92%     0.1861      14.8k      15.0k  select-all 'SELECT text_8kb_column FROM foo_large LIMIT 100'
+   2.57% *   0.0245      70.0k      71.8k  select-all 'SELECT text_column FROM foo LIMIT 100'
+   8.62% *** 0.0005      3.22M      3.50M  select-get 'SELECT 1'
+  12.88% *** 0.0000      1.40M      1.58M  select-all 'SELECT * FROM foo LIMIT 1'
+  15.51% *** 0.0000      1.48M      1.71M  select-get 'SELECT text,int,real,blob FROM foo LIMIT 1'
+  17.03% *** 0.0000      1.48M      1.73M  select-get 'SELECT * FROM foo LIMIT 1'
+  17.71% *** 0.0000      1.38M      1.63M  select-all 'SELECT text,int,real,blob FROM foo LIMIT 1'

configs: 20  significant: 14
geomean speedup (all configs): 8.25%

Sorted, it splits in two: everything with three stars is LIMIT 1, everything near zero is LIMIT 100. And select-all is in both groups, so the divide isn’t get() versus all() - it’s how many rows come back per call.

That’s the profile from step 5, confirmed by measurement. Which is the real reason to run both: the benchmark didn’t just say “faster”, it said faster in the exact shape the mechanism predicts. When the two agree, you understand your own change.

The -0.97% row is not a regression, by the way. No stars, p-value 0.33, noise floor ±2%. It’s zero, and step 2 is what lets me say so without arguing.

One idea died here, and it was the one I liked most. The biggest category in the 100-row profile is V8 object construction at 22.5%, and DictionaryTemplate exists to fix that - describe the row shape once, share a map across instances. Reads got 23-29% faster and construction got 11-20% slower, because node:sqlite rows have a null prototype, DictionaryTemplate hands you Object.prototype, and the SetPrototypeV2 per row costs more than the shared map saves. Discarded. A hypothesis dying on a measurement is the method working.

The whole method

  1. Fix the baseline and copy the binary out, so you’re not comparing a build against itself.
  2. Find the noise floor by benchmarking the baseline against itself. Mine was ±2%. Without it, every number here is an opinion.
  3. Profile before touching anything. Group by cost, discard the idle threads, and use the call tree to find out whose work the hot symbol is.
  4. Profile more than one shape, and make one small. The 8.1% I fixed was invisible in the 100-row profile.
  5. Read the source. The cache I “added” already existed.
  6. Benchmark to convict, and check the shape of the win against the mechanism you claimed.

The order matters: each step is there to keep the next one honest. And step 3 is the one I’d hand to anyone starting out: I could have read node_sqlite.cc for a week without suspecting those four strings, and run the benchmark suite a hundred times without it saying a word about them.

Thanks for reading!