3x Faster TID Range Scans - Postgres 19

If you've ever had to scrub significantly large tables—whether updating older records or deleting expired ones—you know the pain of trying to do it efficiently without locking the entire table. One common trick is to iterate over the ctid (the physical location of the tuple) to process the table in chunks. Until now, this was strictly a single-threaded affair. But with the latest commit to Postgres 19, TID Range Scans can now be parallelized, allowing you to harness multiple cores to rip through these maintenance tasks significantly faster.

In my tests, upto 3x Faster!!

The Problem with Linear Scanning

When you perform a batched operation using ctid (e.g., WHERE ctid >= '(0,0)' AND ctid < '(1000,0)'), Postgres uses a Tid Range Scan. Previously, this scan type didn't support parallelism. If you had a 1 TB table and wanted to process ranges of it, a single CPU core would have to do all the heavy lifting for that range, fetching blocks one by one. Even if you had 64 cores gathering dust, they couldn't help with that specific scan node.

The Solution: Parallel TID Range Scan

Commit 0ca3b169, developed by Cary Huang and committed by David Rowley, introduces infrastructure to allow Tid Range Scan to participate in parallel query plans. The logic effectively splits the block range among the available parallel workers. Instead of one process sweeping from block 0 to N, workers can grab chunks of blocks concurrently.

I was following the discussion on the mailing list, where Cary and David collaborated closely to refine the parallel safety logic, ensuring workers handle scan limits correctly—leading to the robust implementation that landed in the master branch.

NOTE

This feature is currently in the master branch (Postgres 19devel). As with all unreleased features, it could theoretically be rolled back or modified before the final release.

Benchmark: Before vs. After

To see this in action, I spun up two Postgres instances: one built just before the commit (Postgres 18) and one after (Postgres 19). I created a table bench_tid_range with 10 million rows and ran a count(*) query over the first 50% of the table using a ctid range condition.

Test Setup:

• Table Size: 10 Million rows
• Query: SELECT count(*) FROM bench_tid_range WHERE ctid >= '(0,0)' AND ctid < '(41667,0)'

Environment	Workers	Execution Time (Median)	Speedup
Before (Pg 18)	0	448 ms	1.00x
After (Pg 19)	0	435 ms	1.03x
After (Pg 19)	1	238 ms	1.88x
After (Pg 19)	2	174 ms	2.58x
After (Pg 19)	3	151 ms	2.97x
After (Pg 19)	4	150 ms	2.98x
After (Pg 19)	5	147 ms	3.05x
After (Pg 19)	6	143 ms	3.14x
After (Pg 19)	7	147 ms	3.04x
After (Pg 19)	8	147 ms	3.04x

*(Each execution time is a Median of 9 runs)*

The chart above illustrates the performance scaling. Note that Postgres 19 with 0 workers (i.e. serial) is almost identical in speed to Postgres 18; the efficiency gains are visible only when parallelism is enabled.

We see a massive drop in execution time just by enabling 1 worker (which effectively gives us 2 processes scanning: the leader + 1 worker). The gains continue robustly up to 3 workers, where execution time settles around 150ms. Beyond that, we hit diminishing returns as the overhead of managing parallel workers and aggregating results begins to dominate the raw scanning speed. The "sweet spot" for this specific workload appears to be around 2-3 workers.

Under the Hood: Deep Dive

Let's look at the actual EXPLAIN (ANALYZE, BUFFERS) output to see *why* it's faster.

Before (Postgres 18)

The legacy behavior forces a serial scan. The single process reads all 41,667 pages.

 Aggregate  (cost=104171.87..104171.88 rows=1 width=8) (actual time=427.723..427.724 rows=1.00 loops=1)
   Buffers: shared read=41667
   ->  Tid Range Scan on bench_tid_range  (cost=0.01..91671.70 rows=5000069 width=0) (actual time=0.130..243.502 rows=5000040.00 loops=1)
         TID Cond: ((ctid >= '(0,0)'::tid) AND (ctid < '(41667,0)'::tid))
         Buffers: shared read=41667

After (Postgres 19)

The new Parallel Tid Range Scan node appears. Notice the Gather node launching 2 workers.

 Finalize Aggregate  (cost=68713.25..68713.26 rows=1 width=8) (actual time=166.873..169.987 rows=1.00 loops=1)
   Buffers: shared read=41667
   ->  Gather  (cost=68713.03..68713.24 rows=2 width=8) (actual time=166.238..169.978 rows=3.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared read=41667
         ->  Partial Aggregate  (cost=67713.03..67713.04 rows=1 width=8) (actual time=164.518..164.519 rows=1.00 loops=3)
               Buffers: shared read=41667
               ->  Parallel Tid Range Scan on bench_tid_range  (cost=0.01..62504.63 rows=2083362 width=0)
                     (actual time=0.095..97.492 rows=1666680.00 loops=3)
                     TID Cond: ((ctid >= '(0,0)'::tid) AND (ctid < '(41667,0)'::tid))
                     Buffers: shared read=41667

Key Improvements:

1. Distributed Cost: The cost estimate dropped from 104k to 68k, indicating the planner correctly anticipates the benefit of parallelism.

2. Shared Load: While the total buffers read (41667) is the same, the work is shared across 3 processes (1 leader + 2 workers), reducing the wall-clock time for the scan itself from ~243ms to ~97ms per worker.

Counter-Intuitive Alternative: Forced Parallel Seq Scan?

You might ask: *"Why didn't Postgres v18 just choose a Parallel Sequential Scan? Wouldn't scanning the whole table with 4 workers be faster than scanning half the table with 1?"*

I tested exactly that on the v18 instance by forcing enable_tidscan = off with 4 workers.

• Execution Time: ~230 ms.

 Finalize Aggregate  (cost=124959.76..124959.77 rows=1 width=8) (actual time=227.917..229.476 rows=1.00 loops=1)
   Buffers: shared hit=15791 read=67543
   ->  Gather  (cost=124959.34..124959.75 rows=4 width=8) (actual time=227.660..229.469 rows=5.00 loops=1)
         Workers Planned: 4
         Workers Launched: 4
         Buffers: shared hit=15791 read=67543
         ->  Partial Aggregate  (cost=123959.34..123959.35 rows=1 width=8) (actual time=224.154..224.155 rows=1.00 loops=5)
               Buffers: shared hit=15791 read=67543
               ->  Parallel Seq Scan on bench_tid_range  (cost=0.00..120834.30 rows=1250017 width=0) 
                     (actual time=0.150..169.772 rows=1000008.00 loops=5)
                     Filter: ((ctid >= '(0,0)'::tid) AND (ctid < '(41667,0)'::tid))
                     Rows Removed by Filter: 999992
                     Buffers: shared hit=15791 read=67543
 Planning:
   Buffers: shared hit=25
 Planning Time: 0.217 ms
 Execution Time: 229.584 ms

It was faster than the serial TID scan (~448 ms)! However, it had to visit all ~83k pages (15,791 hit + 67,543 read) instead of just the ~41k pages we cared about.

The new Parallel TID Range Scan (~150 ms) is still 35% faster than the brute-force/forced Parallel Seq Scan *and* it generates half the I/O load. It is the best of both worlds: fast execution time (Parallelism) and efficient resource usage (Index-like scoping).

Use Case: The Hotel Analogy

To understand why this matters, let's step away from databases and imagine a massive hotel with 1 million rooms. You need to verify all guests whose names start with a letter before 'S'.

The Logical Approach (Offset Problem)

You tell the front desk: *"Give me a list of the first 100 guests with names A-R."* matches 100 people named 'Aaron', 'Abby', etc.

Then you ask: *"Give me the next 100 guests."*

To answer this, the clerk has to skip through the 'A's again just to know where to start counting the 'B's. As you get deeper into the alphabet, the clerk spends more and more time skipping known names just to find the new ones. By the time you are asking for names starting with 'R', they are re-reading 90% of the guest list just to find your batch.

The Physical Approach (TID Scan)

This is the TID Range Scan. You stop caring about the names (the logical value) and organize by Room Number (the physical location).

• Worker 1: "Go check Rooms 1 to 100."
• Worker 2: "Go check Rooms 101 to 200."

Worker 2 doesn't care who is in Room 1. They go straight to Room 101. If the person inside is named "Zach", they skip them. If it's "Alice", they verify them.

• Zero Rescanning: No one re-reads the list of 'A's.
• Parallelism: Because the rooms are physically distinct, you can send 8 workers to different floors simultaneously without them needing to coordinate or skip over each other's results.

This approach transforms an O(N^2) maintainence nightmare into a linear, parallelizable task.

Reproduce this Test

Want to try it yourself on Postgres 19devel? Here is the SQL to set up the test table and run the benchmark.

-- 1. Create the table
DROP TABLE IF EXISTS bench_tid_range;
CREATE TABLE bench_tid_range (id int, payload text);

-- 2. Insert 10M rows to generate ~41k pages
INSERT INTO bench_tid_range 
SELECT x, 'payload_' || x 
FROM generate_series(1, 10000000) x;

-- 3. Vacuum to set visibility map and freeze (important for stable benchmarks)
VACUUM (ANALYZE, FREEZE) bench_tid_range;

-- 4. Enable Parallelism for the Session
SET max_parallel_workers_per_gather = 4; -- Try 2, 4, 8
SET min_parallel_table_scan_size = 0;    -- Force parallel scan even for smaller tables

-- 5. Run the Query
EXPLAIN (ANALYZE, BUFFERS) 
SELECT count(*) 
FROM bench_tid_range 
WHERE ctid >= '(0,0)' AND ctid < '(41667,0)';

Conclusion

This is a welcome "plumbing" improvement. It might not change your daily ad-hoc queries, but for DBAs and developers building custom data maintenance scripts, the ability to parallelize TID based scans is a powerful new tool in the optimization toolkit.

References:

• Commit 0ca3b169
• Discussion Thread

Speed up JOIN Planning - upto 16x Faster!

The hidden cost of knowing too much. That's one way to describe what happens when your data is skewed, Postgres statistics targets are set high, and the planner tries to estimate a join.

For over 20 years, Postgres used a simple O(N^2) loop to compare (equi-join) Most Common Values (MCVs) during join estimation. It worked fine when statistics targets are small (default_statistics_target defaults to 100). But in the modern era - we often see Postgres best-practices recommend cranking that up. Customers are known to be using higher values (1000 and sometimes even higher) to handle complex data distributions + throw a 10 JOIN query to the mix - and this "dumb loop" can easily become a silent performance killer during planning. 

That changes in Postgres 19.

The Problem: It's Quadratic!

When you join two tables, the planner needs to estimate how many rows will match. If both columns have MCV lists (lists of the most frequent values), eqjoinsel() tries to match them up to get a precise estimate.

Historically, it did this by comparing every item in list A with every item in list B.

• If you have 100 MCVs, that's 10,000 comparisons. Fast.
• If you have 10,000 MCVs (max stats target), that's 100,000,000 comparisons. Not so fast.

This meant that simply *planning* a query could take significantly longer than executing it, especially for simple OLTP queries.

The Solution: Hash It Out

The fix is elegant and effective.

Instead of a nested loop, the planner now:

1. Checks if the total number of MCVs is greater than 200 (100 each side).

2. If so, it builds a Hash Table of the MCVs from one (smaller) side.

3. It then probes this hash table with the MCVs from the other side.

The threshold of 200 was chosen because hashing has a startup cost (allocating memory, computing hashes). For smaller lists, the simple loop is actually faster. But once you cross that threshold, the hash table wins.

This transforms the complexity from O(N^2) to O(N), making the estimation step virtually instantaneous even with the largest statistics targets.

Let's Benchmark

I wanted to verify this myself, so I set up a worst-case scenario: two tables with 100,000 rows, but only 10,000 distinct values, and I cranked the statistics target to the maximum (10,000) to force a massive MCV list.

Setup

CREATE TABLE t1 (x int);
CREATE TABLE t2 (x int);

-- Scenario A: 3M rows, Stats 10000
INSERT INTO t1 SELECT x % 10000 FROM generate_series(1, 3000000) x;
INSERT INTO t2 SELECT x % 10000 FROM generate_series(1, 3000000) x;

-- Maximize statistics target
ALTER TABLE t1 ALTER COLUMN x SET STATISTICS 10000;
ALTER TABLE t2 ALTER COLUMN x SET STATISTICS 10000;

ANALYZE t1;
ANALYZE t2;

Results

I ran the following query 10 times on both versions, interleaved to ensure fairness and used median for the results:

EXPLAIN (ANALYZE, TIMING OFF) SELECT count(*) FROM t1 JOIN t2 ON t1.x = t2.x;

Scenario A: High Stats (10k MCVs)

• Rows: 3 Million
• Statistics Target: 10,000
• MCV Count: 10,000

• Before (Postgres 18): ~27.8 ms
• After (Postgres 19): ~1.75 ms
• Speedup: ~16x

Scenario B: Medium Stats (1k MCVs)

• Rows: 300,000
• Statistics Target: 1,000
• MCV Count: 1,000

• Before (Postgres 18): ~0.85 ms
• After (Postgres 19): ~0.60 ms
• Speedup: ~1.4x (40% speed-up)

Scenario C: Default Stats (100 MCVs)

• Rows: 30,000
• Statistics Target: 100 (Default)
• MCV Count: 100

• Before (Postgres 18): ~0.40 ms
• After (Postgres 19): ~0.43 ms
• Speedup: None (Optimization correctly skipped)

Since the total MCV count in Scenario C (100 + 100 = 200) did not exceed the 200 threshold, Postgres 19 correctly chose the simpler loop, avoiding the overhead of building a hash table. This confirms that the patch is smart enough to only kick in when it matters.

The "Quadratic Curve" in Action

To visualize the O(N^2) vs O(N) difference, I ran a benchmark across a wide range of statistics targets (from 10 to 10,000). In each test, the number of rows was set to 300x the statistics target to ensure the MCV list was fully populated and relevant.

Stats Target	PG 18 (ms)	PG 19 (ms)	Speedup
10	0.40	0.38	-
100	0.40	0.45	-
200	0.45	0.43	-
500	0.53	0.52	-
1000	0.85	0.63	1.3x
2000	1.73	0.68	2.5x
5000	7.54	1.14	6.6x
8000	17.97	1.57	11.4x
10000	27.56	1.92	14.3x

As you can see, up to a target of 500, the difference is negligible (and the optimization might not even kick in). But as the target grows, the quadratic cost of the old method explodes, while the new hash-based method scales linearly and remains extremely fast.

This patch is a classic example of modernizing legacy assumptions. The code written 20 years ago assumed MCV lists would be short (in all fairness the default was 100 for a long time). Today's hardware and data requirements have pushed those boundaries, and Postgres is evolving to meet them.

Thanks to Ilia Evdokimov for the patch, David Geier for co-authoring, and Tom Lane for the review.

Note: This feature is currently in the master branch for Postgres 19. As with all development features, it is subject to change before the final release.

References

• Commit: Speed up eqjoinsel() with lots of MCV entries
• Discussion: Mailing List Thread

Teaching Query Planner to See Inside C Functions

The Postgres planner just got a new superpower: X-ray vision for your black-box functions.

For years, Postgres has been able to "inline" simple SQL functions. If you wrote CREATE FUNCTION ... LANGUAGE SQL AS 'SELECT ...', the planner would often rip out the function call and paste the raw SQL directly into the main query. This was magic. It meant that WHERE clauses could be pushed down, indexes could be used, and performance was excellent.

But if you stepped outside the safety of simple SQL functions—say, into the world of C extensions or complex dynamic SQL—you were often stuck with a "black box." The planner saw a function, executed it blindly, and only *then* applied your filters.

That changes in Postgres 19.

The "Black Box" Problem

Imagine you have a function that returns a large dataset, but you only want a few rows:

SELECT * FROM my_complex_function() WHERE id = 42;

Before Postgres 19, if my_complex_function wasn't a simple SQL function, the database would:

1. Run my_complex_function() to completion (generating, say, 1 million rows).

2. Filter the result to find id = 42.

This is painfully inefficient. You wanted an Index Scan, but you got a full execution followed by a filter.

The Solution: SupportRequestInlineInFrom

With this feature, Postgres now allows function authors (specifically in C extensions) to provide a "support function" that tells the planner: *"Hey, if anyone calls me in a FROM clause, here is the raw SQL query tree that represents what I'm doing."*

The planner can then take that query tree and inline it, just like it does for standard SQL functions.

The Impact

This is a game-changer for:

• Extensions: Many extensions provide table-like interfaces (e.g., foreign data wrappers, set-returning functions). They can now expose their internal logic to the planner.
• Dynamic SQL: Functions that generate SQL strings and execute them can now potentially participate in optimization.

Before vs. After

Scenario: A PL/pgSQL function foo_from_bar that executes a dynamic query on text_tbl (containing 1 million rows).

CREATE TABLE text_tbl (f1 text);
INSERT INTO text_tbl SELECT 'row_' || generate_series(1, 1000000);
INSERT INTO text_tbl SELECT 'common_row' FROM generate_series(1, 100);
CREATE INDEX text_tbl_idx ON text_tbl(f1);
ANALYZE text_tbl;

Here is the PL/pgSQL function I used for testing (designed to return 100 rows). Note that it uses dynamic SQL, which is normally opaque to the planner, and then linked it to the C support function test_inline_in_from_support_func using ALTER FUNCTION.

CREATE OR REPLACE FUNCTION foo_from_bar(colname TEXT, tablename TEXT, filter TEXT)
RETURNS SETOF TEXT
LANGUAGE plpgsql
AS $function$
DECLARE
  sql TEXT;
BEGIN
  sql := format('SELECT %I::text FROM %I', colname, tablename);
  IF filter IS NOT NULL THEN
    sql := CONCAT(sql, format(' WHERE %I::text = $1', colname));
  END IF;
  RETURN QUERY EXECUTE sql USING filter;
END;
$function$ STABLE;

ALTER FUNCTION foo_from_bar(TEXT, TEXT, TEXT)
  SUPPORT test_inline_in_from_support_func;

And here is the query I ran:

SELECT * FROM foo_from_bar('f1', 'text_tbl', 'common_row');

Before (PG 18 and earlier)

->  Function Scan on public.foo_from_bar  (cost=0.25..10.25 rows=1000 width=32)
      Output: foo_from_bar
      Function Call: foo_from_bar('f1'::text, 'text_tbl'::text, 'common_row'::text)
      Buffers: shared hit=32 read=4
      Execution Time: 0.353 ms

The function is treated as a black box. The planner estimates 1000 rows (default) and scans the function output.

After (PG 19)

->  Index Only Scan using text_tbl_idx on public.text_tbl  (cost=0.42..8.44 rows=1 width=10)
      Output: text_tbl.f1
      Index Cond: (text_tbl.f1 = 'common_row'::text)
      Buffers: shared hit=1 read=3
      Execution Time: 0.064 ms

The planner "sees through" the function, realizes it's just a query on text_tbl, and uses the index directly. The execution time drops significantly (5.5x faster in this case).

I also ran a pgbench test (1000 iterations) to measure the end-to-end latency improvement:

• Before: 0.107 ms average latency
• After: 0.087 ms average latency
• Improvement: ~19% faster end-to-end (including network overhead)

Bonus: Outer WHERE Pushdown - Orders of magnitude faster!

Next, I ran a test where NULL was passed to the function (returning all 1 million rows) and applied an external WHERE clause:

SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL) WHERE foo_from_bar = 'common_row';

Before (PG 18):

->  Function Scan on public.foo_from_bar  (cost=0.25..12.75 rows=5 width=32)
      Output: foo_from_bar
      Function Call: foo_from_bar('f1'::text, 'text_tbl'::text, NULL::text)
      Filter: (foo_from_bar.foo_from_bar = 'common_row'::text)
      Rows Removed by Filter: 1000000
      Execution Time: 136.267 ms

The database scanned all 1 million rows from the function, then filtered them down to 100.

After (PG 19):

->  Index Only Scan using text_tbl_idx on public.text_tbl  (cost=0.42..8.44 rows=1 width=10)
      Output: text_tbl.f1
      Index Cond: (text_tbl.f1 = 'common_row'::text)
      Execution Time: 0.055 ms

The planner pushed the WHERE clause *inside* the function logic, triggering an index lookup (~2400x speedup).

Similarly, I tested adding a LIMIT 5 clause to the query and saw a ~20x speedup!!

• Before: The database scanned the function output until it got 5 rows (~1.091 ms).
• After: The planner pushed the limit down into the index scan, stopping immediately after finding 5 rows (~0.051 ms).

Testing Methodology

The benchmarks above were conducted using pgbench with the following parameters:

• Iterations: 1000 transactions per client.
• Concurrency: Single client (1 thread).
• Data Volume: The target table text_tbl contained 1,000,100 rows.
• Return Size: The query returned 100 rows per execution.
• Metric: Average latency reported by pgbench (excluding connection overhead).

• Commits tested: c0bc9af (before) and b140c8d (after).

The C support function (test_inline_in_from_support_func) used in this example is defined in src/test/regress/regress.c that is a part of the regression tests. To know more about support functions, see PostgreSQL Documentation on Function Optimization Information.

Discussion & Credits

Coincidentally a Linkedin thread mentioned that Set-Returning Functions (SRF) began their journey in Postgres 7.3 - that was 17 years ago! With this patch, the planner should be able to see through such a function and develop much better execution plans around them! Thanks to Paul A. Jungwirth (for initating the idea with a working patch) and Tom Lane (for in-depth review).

Note: This feature is currently in the master branch for Postgres 19. As with all development features, it is subject to change before the final release.

Settling COUNT(*) vs COUNT(1) debate in Postgres 19

A recent commit to the PostgreSQL master branch brings a nice quality-of-life optimization for a very common SQL pattern - improving performance by up to 64% for SELECT COUNT(h) where h is a NOT NULL column.

If you've ever wondered whether you should use COUNT(*) or COUNT(1), or if you've been dutifully using COUNT(id) on a non-null column, this change is for you.

Note: This feature is currently on the PostgreSQL master branch (committed in November 2025). As with any commit on master, it is subject to change or even rollback before the final release, although this is relatively rare for committed features. If all goes well, this change will be part of the PostgreSQL 19 major version release.

The Optimization

Historically in PostgreSQL, COUNT(*) has been faster than COUNT(1) or COUNT(column).

• COUNT(*): The planner knows it just needs to count rows. It doesn't need to check if any specific column is null.
• COUNT(column): The executor has to fetch the value of the column for each row and check if it is NULL before counting it. This involves "deforming" the tuple (extracting the data), which adds overhead.
• COUNT(1): Often used as a "faster" alternative by users, but treated similarly to COUNT(column) where the "column" is the constant 1.

The new commit, Have the planner replace COUNT(ANY) with COUNT(*), when possible, changes this behavior.

The planner can now detect when you are counting a generic expression (COUNT(ANY)) that:

1. Cannot be NULL: For example, a constant like 1 or a column defined as NOT NULL.

2. Has no extra clauses: No DISTINCT or ORDER BY inside the aggregate.

In these cases, the planner will automatically transform the query to use COUNT(*).

Performance Impact

This optimization eliminates the overhead of tuple deforming and null checking for these cases.

I ran a benchmark on a local PostgreSQL instance to verify the impact, comparing COUNT(h) (not null column), COUNT(1) (constant), and COUNT(*) (rows) on a table with 10 million rows.

Before Patch:

• SELECT count(h) FROM t: ~195 ms
• SELECT count(1) FROM t: ~126 ms
• SELECT count(*) FROM t: ~119 ms
• *Observation:* COUNT(h) is significantly slower (~64% overhead vs count(*)). COUNT(1) is faster than COUNT(h) but still lags behind COUNT(*).

After Patch:

• SELECT count(h) FROM t: ~117 ms
• SELECT count(1) FROM t: ~116 ms
• SELECT count(*) FROM t: ~114 ms
• *Observation:* COUNT(h) sees the major win, improving dramatically to match COUNT(*). COUNT(1) sees a minor speedup, also converging to the optimal COUNT(*) performance.

This means users who prefer COUNT(1) for stylistic reasons, or who count specific non-null columns, will now get the optimal performance of COUNT(*) automatically.

(The patch author, David Rowley, demonstrated similar speedups in the initial discussion).

What about Nullable Columns?

I also tested COUNT(nullable_col) to see if the optimization applies. As expected, it does not.

• Before Patch: COUNT(nullable_col): ~162 ms
• After Patch: COUNT(nullable_col): ~158 ms (No significant change)

Since the planner cannot guarantee the column is free of NULLs, it must still scan and check every value, so the performance remains unchanged. The optimization strictly targets columns where the planner *knows* the value cannot be NULL.

What about DISTINCT or ORDER BY?

I also tested COUNT(DISTINCT h) and COUNT(h ORDER BY h) to see if the optimization applies. As expected, it does not.

• Before Patch: COUNT(DISTINCT h): ~2663 ms (Median of 10 runs)
• After Patch: COUNT(DISTINCT h): ~2708 ms (Median of 10 runs)

Similarly for ORDER BY, the executor still needs to process the values to sort them, so no optimization is possible.

Test Methodology

• Versions:

* Before: Tested on commit dbdc717ac6743074c3a55fc5c380638c91d24afd.

* After: Tested on commit 42473b3b31238b15cc3c030b4416b2ee79508d8c.

• Data: Table t with 10 million rows, populated via INSERT INTO t (h) SELECT 1 FROM generate_Series(1,10000000);.
• Procedure: Each query was executed 10 times in an interleaved pattern (A, B, C, A, B, C...) to ensure fairness and mitigate caching bias. The reported time is the median of those 10 runs.
• System: Intel Core i7-4770 CPU @ 3.40GHz (4 cores/8 threads), 16GB RAM.

Detailed Analysis

To understand *why* the performance improved, we can look at the EXPLAIN plans and table statistics.

• Table Size: The table t (10 million rows) is approximately 346 MB.
• Disk vs. Memory: The benchmark results (median of 10 runs) indicate mostly in-memory / hot-cache performance (~3 GB/s throughput). However, even with some disk I/O involved (as seen in single-run EXPLAIN (ANALYZE, BUFFERS) tests showing ~28k buffer reads), the CPU overhead of deforming the tuple was the dominant differentiator.

The Key Difference:

In the Before scenario, COUNT(h) required the scanner to fetch the column data (width=4), whereas COUNT(*) did not (width=0).

-- Before Patch: COUNT(h)
->  Parallel Seq Scan on t  (... rows=4166657 width=4) ...

In the After scenario, the planner transforms COUNT(h) into COUNT(*), allowing it to skip fetching the column data entirely (width=0).

-- After Patch: COUNT(h)
->  Parallel Seq Scan on t  (... rows=4166657 width=0) ...

This reduction in "width" means the executor doesn't need to extract the integer value from the row, saving significant CPU cycles.

Community Discussion & Trivia

Thanks to David Rowley for working on the patch and Corey Huinker / Matheus Alcantara for reviewing this. The discussions debated on trade-offs between what is fastest and which of these is an "anti-pattern", but it is great to see that now all three are now equivalently optimized.

Future Possibilities:

The infrastructure added (SupportRequestSimplifyAggref) opens the door for further optimizations. For instance, COUNT(NULL) could theoretically be optimized to instantly return 0 without scanning any rows - although that specific change wasn't included in this commit to keep the scope focused.

References

• Commit Message: Have the planner replace COUNT(ANY) with COUNT(*), when possible
• Discussion Thread: Have the planner convert COUNT(1) / COUNT(not_null_col) to COUNT(*)

PostgreSQL Buildfarm Members: A status update

The PostgreSQL Buildfarm is a global network of machines that continuously test PostgreSQL across a wide range of operating systems, architectures, compilers, and branches. Over the past few years, I have created and maintained several buildfarm members, each with its own quirks and strengths. In this post, I’ll share a status-update working on the following animals: alligator, dodo, woodpecker, leafhopper, massasauga, parula, and snakefly.

What is the Buildfarm?

The Buildfarm is essential for PostgreSQL development. It helps catch platform-specific bugs early, ensures code quality, and provides confidence that new changes work everywhere. Each member reports results for multiple branches (like master, REL_18_STABLE, etc.), using different OSes, compilers, and hardware.

About the Architectures

The Open Hardware Frontier: RISC-V

RISC-V is an open standard instruction set architecture (ISA) and unlike most other ISAs, RISC-V is provided under open source licenses that do not require fees to use.

• ovenbird is my first foray into this architecture, running on a VisionFive 2 board.
• It (hopefully) represents the future of open hardware, and ensuring PostgreSQL compiles and runs correctly on it is a long-term investment in the open-source ecosystem.

Bridging Windows and Linux: WSL2

Windows Subsystem for Linux (WSL) lets developers run a GNU/Linux environment -- including most command-line tools, utilities, and applications -- directly on Windows, unmodified, without the overhead of a traditional virtual machine or dualboot setup.

• woodpecker runs inside a Debian container on WSL2.
• This setup is crucial for verifying that PostgreSQL behaves correctly in this increasingly popular development environment, which bridges the gap between Windows and Linux.

Small Scale, Big Impact: Raspberry Pi

Raspberry Pi revolutionized low-cost computing and is a fantastic platform for edge cases (pun intended) :)

• dodo runs on a Raspberry Pi 4 Model B.
• It helps identify performance regressions and race conditions that might be masked by faster hardware. It also ensures PostgreSQL remains viable for low-powered, IoT and edge computing use cases.

The Rise of ARM in the Cloud: Graviton

Several of the buildfarm animals I’ve created run on the Graviton processors. Graviton is Amazon’s custom ARM-based CPU family, designed for high performance and energy efficiency in AWS cloud environments.

• Graviton1 (first generation) was introduced in 2018, bringing ARM64 to AWS EC2.
• Graviton2 (second generation) launched in 2020, offering major improvements in performance and scalability.
• Graviton3 (third generation) arrived in 2022, further boosting compute, memory bandwidth, and energy efficiency—making it ideal for demanding workloads like database regression testing.
• Graviton4 (fourth generation) is the latest, offering even greater performance and efficiency for cloud-native workloads. The buildfarm animal 'leafhopper' is one of the first to test PostgreSQL on Graviton4.

Testing PostgreSQL on these platforms helps ensure the database runs smoothly on modern cloud hardware and takes advantage of ARM’s growing ecosystem.

Disclosure: The Graviton machines are provided by my employer. All other machines (including the WSL2, RISC-V, and Raspberry Pi instances) are my personal machines.

Meet the Buildfarm Animals

Here’s a quick overview of the machines I have created and recently worked on:

alligator

• OS: Ubuntu 24.04 LTS
• Arch: x86_64
• Compiler: gcc experimental (nightly build)
• Branches: master, REL_18_STABLE, REL_17_STABLE, REL_16_STABLE, REL_15_STABLE, REL_14_STABLE, REL_13_STABLE
• Notes: Tracks the latest GCC changes, often finds compiler regressions before anyone else.

dodo

• OS: Raspbian GNU/Linux 10
• Arch: armv7l
• Compiler: gcc experimental (nightly build)
• Branches: master, REL_18_STABLE, REL_17_STABLE, REL_16_STABLE, REL_15_STABLE, REL_14_STABLE, REL_13_STABLE
• Notes: ARM platform, useful for catching issues on lower-powered hardware.

woodpecker

• OS: Debian/WSL2@win11 12 (bookworm)
• Arch: x86_64
• Compiler: gcc 12.2.0
• Branches: master, REL_18_STABLE, REL_17_STABLE, REL_16_STABLE, REL_15_STABLE, REL_14_STABLE, REL_13_STABLE
• Notes: Runs inside WSL2 on Windows 11, great for testing integration with Windows environments.

leafhopper

• OS: Amazon Linux 2023
• Arch: aarch64/graviton4/r8g.2xl
• Compiler: gcc experimental (hourly build)
• Branches: master, REL_18_STABLE, REL_17_STABLE, REL_16_STABLE, REL_15_STABLE, REL_14_STABLE, REL_13_STABLE
• Notes: Created and managed in a work-based environment; leafhopper is one of the first buildfarm animals testing PostgreSQL on Graviton4 hardware.

massasauga

• OS: Amazon Linux 2
• Arch: aarch64 (Graviton1)
• Compiler: gcc experimental (nightly build)
• Branches: master, REL_18_STABLE, REL_17_STABLE, REL_16_STABLE, REL_15_STABLE, REL_14_STABLE, REL_13_STABLE
• Notes: Created and managed in a work-based environment; Graviton1 machine—one of the earliest ARM64 regression testers in the buildfarm, still running reliably after several years.

parula

• OS: Amazon Linux 2 (AL2) / Graviton3
• Arch: aarch64/Graviton3/c7g.2xl
• Compiler: gcc experimental (nightly build)
• Branches: master, REL_18_STABLE, REL_17_STABLE, REL_16_STABLE, REL_15_STABLE, REL_14_STABLE, REL_13_STABLE
• Notes: Created and managed in a work-based environment; focuses on the third generation of AWS Graviton hardware, useful for performance and compatibility.

snakefly

• OS: AmazonLinux@Graviton2 AL2
• Arch: aarch64 (Graviton2)
• Compiler: gcc experimental (nightly build)
• Branches: master, REL_18_STABLE, REL_17_STABLE, REL_16_STABLE, REL_15_STABLE, REL_14_STABLE, REL_13_STABLE
• Notes: Created and managed in a work-based environment; Graviton2-based member, helps ensure ARM64 stability across AWS generations.

ovenbird (newest member)

• OS: Ubuntu 24.04.3 LTS
• Arch: riscv64
• Compiler: gcc 13.3.0
• Branches: master, REL_18_STABLE, REL_17_STABLE, REL_16_STABLE, REL_15_STABLE, REL_14_STABLE, REL_13_STABLE
• Notes: The newest addition to the family, ovenbird brings riscv64 architecture to the buildfarm, helping ensure PostgreSQL is tested on cutting-edge open hardware.

Challenges and Rewards

Managing these buildfarm animals means keeping up with OS upgrades, compiler changes, hardware failures, and PostgreSQL branch updates. Some of these machines are especially aggressive about GCC: they check for updates from the GCC git repository every few hours, recompile a fresh GCC, and use it for the next buildfarm run. This helps catch compiler regressions and compatibility issues very early.

If you want to read more about how these GCC compiles work and see the open source repository, check out my blog post: Compiling Latest GCC to Test More.

Some of these machines have been running for 3-4 years, and their logs are a treasure trove for debugging tricky platform-specific issues. The diversity of hardware and software helps the PostgreSQL community maintain its reputation for reliability and portability.

Testing with the latest GCC is especially rewarding: it ensures that upstream GCC changes are in tandem with the expectations of the PostgreSQL community, and that PostgreSQL continues to compile and pass tests without surprises. A good example is an upstream GCC bug that was found, reported, and fixed—making sure that no GCC changes adversely affect PostgreSQL in the long run. Read more about this incident here: PostgreSQL mailing list discussion of a GCC bug.

Here's another email thread that exemplifies why testing gcc experimental is helpful in ensuring that PostgreSQL compiles and tests stay green: PostgreSQL mailing list - GCC experimental thread.

However, it is also important to note that aggressive testing of GCC HEAD needs to be balanced against the time of PostgreSQL developers. The current buildfarm system does not explicitly distinguish between "production" and "bleeding edge" machines, meaning failures on experimental setups can sometimes be distracting. As discussed in this mailing list thread, there is an ongoing conversation about how to best handle these "platform not believed stable" scenarios to ensure that transient failures on experimental toolchains don't unnecessarily burden the community.

Speaking of new architectures, a few months back I wrote about [Testing PostgreSQL on Debian/Hurd](https://www.thatguyfromdelhi.com/2025/08/testing-postgresql-on-debianhurd.html) and planned to add a Hurd machine to the buildfarm. It looks like I've been beaten to the punch! A new member, [fruitcrow](https://buildfarm.postgresql.org/cgi-bin/show_history.pl?nm=fruitcrow&br=master), is already up and running to test PostgreSQL on GNU/Hurd. This is fantastic news—having "competition" in adding diverse buildfarm members is exactly what we want. It shows that more people recognize that a wide array of test environments leads to a more stable PostgreSQL.

Final Thoughts

If you’re interested in contributing to PostgreSQL, running a buildfarm animal is a great way to help. It’s a hands-on way to learn about PostgreSQL internals, compilers, and operating systems, and it’s rewarding to see your machine’s name in the global test results.

Testing PostgreSQL on Debian/Hurd: A Windows + QEMU Adventure

Curiosity often leads to the most interesting technical adventures. This time, I decided to explore something off the beaten path: running Debian GNU/Hurd inside a virtual machine on my Windows 11 host and compiling PostgreSQL from source.

This post is part 1 of a multi-part series documenting the process, challenges, and discoveries along the way. Future parts will dive deeper into advanced topics, automation, and ongoing compatibility work—so if you're interested in PostgreSQL, alternative operating systems, or open source testing, stay tuned!

What is Debian?
Debian is one of the oldest and most respected Linux distributions, known for its stability, vast software repositories, and commitment to free software principles. While most people associate Debian with the Linux kernel, it’s actually a complete operating system that can run on different kernels.

What is GNU/Hurd?
GNU/Hurd is an alternative kernel developed by the GNU Project. Unlike Linux, GNU/Hurd is built on a microkernel architecture (specifically GNU Mach), aiming for greater modularity and flexibility. While GNU/Hurd is still experimental and not as mature or widely used as Linux, it represents a fascinating approach to operating system design.

Debian GNU/Hurd combines the familiar Debian userland (tools, package management, etc.) with the GNU/Hurd kernel, offering a unique environment for open source enthusiasts and OS tinkerers.

My goal for this experiment was to see how far I could get with a modern database stack—specifically, compiling and running PostgreSQL—on this unusual platform.

Setting Up the VM

Instead of the CD image, I used the pre-built disk image available here. After downloading and extracting the .img file, I launched the VM with QEMU using the following command:

qemu-system-x86_64.exe -machine type=pc,accel=whpx,kernel-irqchip=off -boot d -m 4096 -usb -display default,show-cursor=on -drive file=".\debian-hurd-i386-20250807.img",cache=writeback

Explanation of the command:

• qemu-system-x86_64.exe: Runs QEMU for 64-bit x86 systems (works for 32-bit guests too).
• -machine type=pc,accel=whpx,kernel-irqchip=off: Specifies a PC-type machine, enables Windows Hypervisor Platform acceleration (WHPX), and disables kernel IRQ chip emulation for compatibility.
• -boot d: Boots from the first hard disk.
• -m 4096: Allocates 4GB of RAM to the VM.
• -usb: Enables USB support.
• -display default,show-cursor=on: Uses the default display and ensures the mouse cursor is visible.
• -drive file=".\debian-hurd-i386-20250807.img",cache=writeback: Uses the extracted Hurd disk image as the hard drive and enables writeback caching for better disk performance.

This boots directly into the installed Debian/Hurd system with improved performance and usability on a Windows 11 host.

Preparing to Build PostgreSQL

Debian/hurd is minimal out of the box, so the first step was to install all the build tools and libraries required for compiling PostgreSQL:

sudo apt-get update
sudo apt-get install build-essential git libxml2-dev libxslt-dev autotools-dev automake libreadline-dev zlib1g-dev bison flex libssl-dev libpq-dev ccache

This command installs the compiler, linker, version control tools, XML and SSL libraries, autotools, and all other dependencies PostgreSQL may need for a successful build and test cycle.

Downloading and Compiling PostgreSQL

Instead of downloading a release tarball, I cloned the official PostgreSQL git repository and compiled the master branch:

git clone https://github.com/postgres/postgres.git
cd postgres
./configure --prefix=~/proj/localpg
make
make install

This approach ensures you're building the latest development version of PostgreSQL directly from source, and installs it locally to your user's ~/proj/localpg directory.

Setting Up the Database Cluster

PostgreSQL needs a data directory (cluster) to store its databases. Since the installation was local to my user, I simply initialized the cluster and started the server using the full path to the binaries (since they're not in my PATH):

~/proj/localpg/bin/initdb -D ~/proj/localpg/pgdata
~/proj/localpg/bin/pg_ctl -D ~/proj/localpg/pgdata -l logfile start

Connecting and Creating a Table

With the server running, I connected to the database and created a sample table:

~/proj/localpg/bin/psql -d postgres

Inside psql:

CREATE TABLE test_table (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO test_table (name) VALUES ('Hello from Debian/Hurd!');
SELECT * FROM test_table;

Example output:

CREATE TABLE
INSERT 0 1
 id |         name         
----+----------------------
  1 | Hello from Debian/Hurd!
(1 row)

Running the Test Suite

To ensure the build was solid, I went back to the source directory and ran:

cd ~/postgres
make check

This runs PostgreSQL's regression tests, verifying that the core features work as expected—even on Hurd. This ran mostly fine (except for a few tests that failed - more to be researched on that failure).

Quick QEMU Tip

When working with QEMU, remember that Ctrl-Alt-G is your friend—it releases the mouse and keyboard from the VM window, making it much easier to switch back to your host system.

Adding a Separate Volume for More Disk Space

The base Debian/Hurd image is quite small and can easily run out of space, especially when compiling large projects or running make check. I frequently hit disk full errors during testing.

Solution:

1. Shutdown the VM.

2. Resize the disk image:

   qemu-img resize debian-hurd-i386-20250807.img +10G
   

   This adds 10GB to the existing disk image.

3. Restart the VM.

4. Create a new partition:

   • Use fdisk /dev/hd0 (or the appropriate device) to create a new partition in the extra space.

5. Format the new partition:

   mkfs.ext4 /dev/hd0s3
   

   (Note: On my setup, the original root partition was /dev/hd0s2, so the new partition created for extra space was /dev/hd0s3. Adjust the device name as needed for your configuration.)

   Although the root volume is of ext2 type (!!!), Debian/Hurd works fine with ext4—so feel free to use ext4 for the new partition.

6. Mount the new volume:

   mkdir -p /mnt/newvol
   mount /dev/hd0s3 /mnt/newvol
   

7. Grant non-root user access:

   • As root, change ownership:

     chown robins:robins /mnt/newvol
     

   • Now your non-root user (e.g., robins) can use /mnt/newvol for compiling PostgreSQL and running make check without running out of disk space.

Why use a non-root user for PostgreSQL? PostgreSQL is designed to run as a non-root user for security reasons. Running the database server or its tests as root can expose your system to unnecessary risks and may even cause certain operations to fail. Always use a dedicated non-root user for installation, testing, and day-to-day database operations.

This approach made it possible to complete the build and test cycle without disk space issues.

Final Thoughts

Running Debian/Hurd in a VM on Windows 11 was surprisingly smooth, though some packages and features are less mature than on Linux. Compiling PostgreSQL from scratch was a great way to explore the system's capabilities and compatibility. If you're looking for a fun, geeky weekend project, give Debian/Hurd a try!

Next Steps & What's Still Pending

This is only part 1 of a multi-part series. In future installments, I'll cover:

• Setting up the PostgreSQL buildfarm for automated testing on Debian/Hurd
• Deeper investigation into SMP/multi-core support (currently not working)
• More QEMU optimization and compatibility testing
• Additional performance tuning and disk management strategies
• Troubleshooting Perl module installation issues (e.g., LWP::Protocol::https, LWP::Simple, Net::SSLeay), which currently fail to install—more research is needed to understand and resolve these problems.
• Investigating why make check did not complete successfully (failed on a few tests)—this requires further research.

Some features, like multi-core support, full buildfarm integration, reliable Perl module installation, and passing all PostgreSQL regression tests, are not yet working or fully tested. These will be explored in detail in future posts. Stay tuned!

Pi-hole Part 2: Going Fully Independent with Unbound

After my positive first impressions with Pi-hole, I decided to take the next logical step: eliminating my dependence on external DNS resolvers entirely. While Quad9 served me well, there's something unsettling about routing all my DNS queries through a third party, no matter how trustworthy they appear.

The solution? Unbound - a validating, recursive, caching DNS resolver that can operate completely independently, resolving domain names directly from authoritative sources without relying on upstream DNS providers.

Why Make the Switch?

My motivation goes beyond just privacy paranoia (though that's certainly part of it):

Privacy First: Every DNS query reveals your browsing patterns. Even with Quad9's privacy promises, I prefer keeping that data entirely within my network.

Security Enhancement: Unbound performs DNSSEC validation by default, ensuring the authenticity of DNS responses and protecting against DNS spoofing attacks.

Reduced Latency: While counterintuitive, eliminating the round-trip to external resolvers can actually improve response times for frequently accessed domains through better local caching.

True Independence: No more wondering about logging policies, data retention, or potential government requests to DNS providers.

The Downsides to Consider

Before diving in, it's worth acknowledging the trade-offs:

Increased Complexity: You're now responsible for maintaining and troubleshooting your own DNS infrastructure. When things break, you can't just blame your ISP's DNS servers.

Initial Query Delays: Cold cache scenarios will be slower as Unbound has to walk the entire DNS hierarchy from root servers. First visits to new domains will take noticeably longer.

Resource Usage: While minimal, you're now running an additional service that consumes memory and CPU cycles on your Pi.

Potential Connectivity Issues: If your Pi goes down, your entire network loses DNS resolution. External resolvers provide redundancy that you're giving up.

CDN Sub-optimization: Content delivery networks may not route you to the geographically closest servers, potentially affecting streaming and download performance. Many large DNS providers like Cloudflare and Google use Anycast networks, where the same IP address is announced from multiple geographic locations, automatically routing you to the nearest server. When you run your own recursive resolver, you lose this geographic optimization and might connect to CDN endpoints that are further away.

False Sense of Security: While your DNS queries are now private, it's important to remember that all your actual web traffic (HTTP/HTTPS requests) still flows through your ISP and is visible in their logs. You've privatized the "phone book lookup" but not the actual "conversation" - your ISP can still see which IP addresses you're connecting to, just not the domain names that resolved to them.

Maintenance Overhead: The root hints file should be updated periodically (though it changes infrequently), and you're responsible for keeping your Unbound configuration current and secure.

The Verdict: Benefits Outweigh the Costs

After weighing these trade-offs, the privacy and security benefits still made a compelling case for proceeding. The complexity is manageable for anyone comfortable with basic Linux administration, and the performance impacts are largely theoretical for typical home usage. Most importantly, the peace of mind from knowing exactly where my DNS queries go and how they're handled proved worth the additional overhead.

The Setup Process

Installing and configuring Unbound alongside Pi-hole turned out to be surprisingly straightforward.

Step 1: Install Unbound

sudo apt update
sudo apt install unbound -y

Step 2: Configure Unbound for Security and Performance

I created a custom configuration at /etc/unbound/unbound.conf.d/pi-hole.conf:

sudo nano /etc/unbound/unbound.conf.d/pi-hole.conf

Here's my security-focused configuration:

server:
    # Basic settings
    port: 5335
    do-ip4: yes
    do-ip6: yes
    do-udp: yes
    do-tcp: yes
    
    # Security settings
    trust-anchor-file: "/var/lib/unbound/root.key"
    auto-trust-anchor-file: "/var/lib/unbound/root.key"
    val-clean-additional: yes
    val-permissive-mode: no
    val-log-level: 1
    
    # Privacy settings
    hide-identity: yes
    hide-version: yes
    harden-glue: yes
    harden-dnssec-stripped: yes
    harden-below-nxdomain: yes
    harden-referral-path: yes
    use-caps-for-id: yes
    
    # Performance settings (security-first approach)
    cache-min-ttl: 300
    cache-max-ttl: 86400
    prefetch: yes
    prefetch-key: yes
    
    # Interface settings
    interface: 127.0.0.1
    access-control: 127.0.0.1/32 allow
    access-control: ::1 allow
    
    # Logging
    verbosity: 1
    log-queries: no
    log-replies: no
    
    # Root hints
    root-hints: "/var/lib/unbound/root.hints"

Step 3: Root Hints Management

Root hints are essential files that tell Unbound where to find the DNS root servers - the starting point for all DNS resolution. When installing Unbound via package manager, root hints are included and should be updated automatically through regular system updates.

Note: The root hints file changes infrequently (typically a few times per year when root servers are added, removed, or have IP changes). Rather than manual updates, it's best to keep your system updated through your package manager, which will handle root hints updates appropriately - roughly every 6 months is more than sufficient for most users.

Step 4: Initialize DNSSEC Root Key (Usually Optional)

Modern Unbound packages typically handle DNSSEC root key initialization automatically. However, if you encounter DNSSEC validation errors or want to ensure the key is properly configured, you can initialize it manually:

sudo unbound-anchor -a "/var/lib/unbound/root.key"
sudo chown unbound:unbound /var/lib/unbound/root.key

Note: If this step fails or the file already exists, it's likely already configured correctly by the package installation.

Step 5: Start and Enable Unbound

sudo systemctl enable unbound
sudo systemctl start unbound

Step 6: Test Unbound

Before integrating with Pi-hole, I verified Unbound was working with both valid and invalid domain lookups:

Testing with an existing domain:

robins@pi4:~ $ dig @127.0.0.1 -p 5335 google.com | egrep -w 'status|^google'
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 58713
google.com.             269     IN      A       142.250.70.206

Testing with a non-existent domain:

dig @127.0.0.1 -p 5335 asdfasdsfasfdfsafasd | egrep -w 'status|flags'

robins@pi4:~ $ dig @127.0.0.1 -p 5335 asdfasdsfasfdfsafasd | egrep -w 'status|flags'
;; ->>HEADER<<- opcode: QUERY, status: NXDOMAIN, id: 19691
;; flags: qr rd ra ad; QUERY: 1, ANSWER: 0, AUTHORITY: 1, ADDITIONAL: 1
; EDNS: version: 0, flags:; udp: 1232

Both tests confirm Unbound is working correctly. The existing domain test shows successful resolution with NOERROR status, while the non-existent domain test returns NXDOMAIN (Non-eXistent DOMAIN), which is the proper response when a domain doesn't exist. Note the ad flag in the non-existent domain response, indicating that even failed lookups are DNSSEC-validated - Unbound cryptographically verified that this domain truly doesn't exist rather than just accepting an unvalidated negative response.

Step 7: Configure Pi-hole to Use Unbound

In the Pi-hole admin interface:

1. Navigate to Settings → DNS
2. Uncheck all upstream DNS servers
3. Add 127.0.0.1#5335 as a custom DNS server
4. Enable DNSSEC validation
5. Save settings

The Results: Worth the Effort

After 24 hours of operation with the new setup, the benefits are clear:

Complete Privacy: All DNS queries now resolve independently without touching external servers (except for the initial root server queries, which reveal no specific domain information).

DNSSEC Validation: Every response is cryptographically verified, providing protection against DNS manipulation.

Improved Cache Efficiency: Unbound's more sophisticated caching algorithm seems to provide better hit rates for our household's browsing patterns.

Performance Impact: Negligible. The Pi 4 handles both Pi-hole and Unbound without breaking a sweat:

robins@pi4:~ $ uptime
 21:22:25 up 2 days,  9:47,  3 users,  load average: 0.00, 0.00, 0.00

Security Considerations

My configuration prioritizes security over raw performance:

• Conservative TTL settings: Shorter cache times mean more frequent validation
• Strict DNSSEC validation: No permissive mode that might accept invalid responses
• Minimal logging: No query logging to preserve privacy even locally
• Restricted access: Only localhost can query Unbound directly

Final Thoughts

Setting up Unbound with Pi-hole represents the logical conclusion of taking control over your DNS infrastructure. While the privacy and security benefits are the primary motivators, the technical satisfaction of running a completely self-contained DNS resolution system is considerable.

The setup process is more involved than simply pointing Pi-hole at an external resolver, but the configuration is straightforward and well-documented. For anyone concerned about DNS privacy or wanting to reduce external dependencies, this combination provides an excellent solution.

The Pi 4 continues to prove itself as the perfect platform for this type of network infrastructure project - handling both Pi-hole and Unbound with resources to spare. As I mentioned in part 1, even a Pi 2 would likely suffice for most households, making this an accessible upgrade for anyone looking to enhance their home network's privacy and security posture.