DEV Community: Mohamed Hussain S

Disk Is Full, but Your Postgres Tables Are Small (The Replication Slot Trap)

Mohamed Hussain S — Thu, 05 Nov 2026 12:36:18 +0000

Here's a confusing way for a Postgres server to run out of disk. The alert fires, you check your biggest tables, and nothing looks wrong. The database is small. The disk is not.

The space is going to the pg_wal directory, and the usual cause is a replication slot that nothing is reading from.

I reproduced it locally on Postgres 17 so we can watch it happen and then see how to find it and stop it.

What a Replication Slot Does

A replication slot is a bookmark. It tells Postgres: "Keep every piece of WAL from this point on until the consumer confirms it has read it."

The catch is that Postgres keeps the promise even if the consumer never comes back. WAL keeps piling up, and nothing cleans it.

I used a physical slot for the demo, which is the kind a standby server uses. Logical slots, the kind CDC tools like Debezium use, hold back WAL in the same way, but I didn't test one here.

Step 1: A Baseline

First I lowered max_wal_size so the effect would be easy to see, then measured the WAL directory and the database:

ALTER SYSTEM SET max_wal_size = '256MB';
SELECT pg_reload_conf();

SELECT count(*) AS wal_files, pg_size_pretty(sum(size)) AS wal_size FROM pg_ls_waldir();
SELECT pg_size_pretty(pg_database_size('mydb')) AS db_size;

 wal_files | wal_size
-----------+----------
        11 | 176 MB

 db_size
---------
 119 MB

(A note on max_wal_size: my SHOW max_wal_size straight after the reload still printed the old 1GB, and I didn't re-check it. The slot status later in this post reads extended, which means the WAL had grown past max_wal_size, so the limit was in effect.)

Step 2: Create a Slot Nobody Reads

SELECT pg_create_physical_replication_slot('stuck_slot', true);

SELECT slot_name, slot_type, active, restart_lsn, wal_status
FROM pg_replication_slots;

 slot_name  | slot_type | active | restart_lsn | wal_status
------------+-----------+--------+-------------+------------
 stuck_slot | physical  | f      | 0/154F3AB8  | reserved

The true makes the slot reserve WAL from this point right away. active is f because no standby is connected. This is what a dead consumer looks like.

Step 3: Generate Some Writes

CREATE TABLE wal_demo (id serial PRIMARY KEY, payload text);

INSERT INTO wal_demo (payload)
SELECT md5(random()::text) FROM generate_series(1, 2000000);

UPDATE wal_demo SET payload = md5(payload);

CHECKPOINT;

The CHECKPOINT matters. Normally that's when Postgres cleans up old WAL, so it's the fair moment to measure.

SELECT count(*) AS wal_files, pg_size_pretty(sum(size)) AS wal_size FROM pg_ls_waldir();

SELECT slot_name, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

 wal_files | wal_size
-----------+----------
        61 | 976 MB

 slot_name  | active | wal_status | retained_wal
------------+--------+------------+--------------
 stuck_slot | f      | extended   | 954 MB

WAL went from 11 files (176 MB) to 61 files (976 MB). The slot alone is holding back 954 MB.

The wal_status column is worth knowing:

reserved: the WAL the slot needs is within max_wal_size
extended: max_wal_size has been exceeded, but the files are still kept for the slot
unreserved: the WAL is no longer guaranteed and will be removed at the next checkpoint
lost: WAL the slot needed has already been removed

Step 4: Make the Tables Small Again

DROP TABLE wal_demo;
CHECKPOINT;

SELECT pg_size_pretty(pg_database_size('mydb')) AS db_size;
SELECT count(*) AS wal_files, pg_size_pretty(sum(size)) AS wal_size FROM pg_ls_waldir();

 db_size
---------
 119 MB

 wal_files | wal_size
-----------+----------
        61 | 976 MB

This is the symptom from the title. The database is 119 MB. The WAL directory is 976 MB, about 8x bigger. Dropping the table freed nothing, because the slot still insists on keeping the WAL.

How to Find It

This is the query I'd keep around:

SELECT slot_name, slot_type, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

Look for a slot with active = f and a large retained_wal. That's your suspect. Then check what the slot belongs to before touching it: a standby that's down, or a CDC connector that stopped.

The Fix

If the consumer is really gone, drop the slot:

SELECT pg_drop_replication_slot('stuck_slot');
CHECKPOINT;

 wal_files | wal_size
-----------+----------
        15 | 240 MB

WAL fell from 976 MB to 240 MB. It didn't go all the way back to the 176 MB I started with after one checkpoint.

One warning: dropping a slot means the consumer can no longer resume from where it left off. For a standby, that can mean rebuilding it. For a CDC tool like Debezium, it can mean taking a fresh snapshot. Don't drop a slot just to free disk without checking who owns it.

How to Stop It Happening Again

Postgres has a safety limit, max_slot_wal_keep_size. It caps how much WAL a slot is allowed to hold back. I set it to 300MB and ran the same kind of write load with a fresh slot:

ALTER SYSTEM SET max_slot_wal_keep_size = '300MB';
SELECT pg_reload_conf();

SELECT pg_create_physical_replication_slot('limited_slot', true);

CREATE TABLE wal_demo (id serial PRIMARY KEY, payload text);
INSERT INTO wal_demo (payload) SELECT md5(random()::text) FROM generate_series(1, 2000000);
CHECKPOINT;

SELECT slot_name, wal_status, safe_wal_size FROM pg_replication_slots;

  slot_name   | wal_status | safe_wal_size
--------------+------------+---------------
 limited_slot | lost       |

The slot went to lost. Postgres removed WAL the slot still needed, so the disk was protected, but the consumer can no longer catch up.

That's the trade-off. Without the limit, a dead consumer can fill your disk and take the primary down. With the limit, the primary survives and the consumer has to be rebuilt or re-snapshotted. Most people prefer the second failure, but you should pick it on purpose.

Also alert on slot lag, so you hear about it long before disk usage does. The query above is a good start.

Clean Up Your Test

DROP TABLE wal_demo;
SELECT pg_drop_replication_slot('limited_slot');
ALTER SYSTEM RESET max_slot_wal_keep_size;
ALTER SYSTEM RESET max_wal_size;
SELECT pg_reload_conf();

Takeaways

A replication slot makes Postgres keep WAL until the consumer reads it, even if the consumer is dead.
If the disk is full but your tables are small, check pg_wal and pg_replication_slots first.
active = f with a large retained WAL value is the red flag.
Dropping a slot fixes the disk but can force the consumer to rebuild, so check who owns it.
max_slot_wal_keep_size protects the primary, at the cost of invalidating a slot that falls too far behind.

These numbers come from a single run in a local Docker container, so your sizes will differ. Run it yourself to see it on your own setup.

I Added an Index and Postgres Still Does a Seq Scan (Here's Why)

Mohamed Hussain S — Thu, 29 Oct 2026 11:52:35 +0000

You have a slow query. You add an index. You run the query again, check the plan, and there it is: Seq Scan.

The usual reaction is "Postgres is ignoring my index." Sometimes that's true, and something is wrong. But a lot of the time, Postgres made the right call, and the real problem is somewhere else.

I reproduced five cases locally on Postgres 17 so we can look at real plans instead of guessing.

The Setup

A table of orders with a million rows. About 95% are completed and 5% are pending:

CREATE TABLE orders (
    id          serial PRIMARY KEY,
    customer_id int,
    status      text,
    created_at  timestamptz DEFAULT now()
);

INSERT INTO orders (customer_id, status, created_at)
SELECT (random() * 10000)::int,
       CASE WHEN random() < 0.95 THEN 'completed' ELSE 'pending' END,
       now() - random() * interval '365 days'
FROM generate_series(1, 1000000);

ANALYZE orders;

CREATE INDEX idx_orders_status ON orders (status);

I run every query with EXPLAIN (ANALYZE, BUFFERS). That runs the query for real and shows what actually happened.

What to Look At in a Plan

Plans look intimidating, but for this post you only need five things:

The node type: Seq Scan, Index Scan, Bitmap Heap Scan
rows= in the first parentheses (the estimate) vs the second (the actual). A big gap means the planner guessed wrong.
cost=: the planner's estimate in arbitrary units. It picks the plan with the lowest cost.
Buffers: how many 8 kB pages the query touched. shared hit means found in cache, read means fetched from outside shared buffers.
Execution Time at the bottom

Case 1: The Index Exists, and Postgres Is Right to Skip It

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'completed';

Seq Scan on orders  (cost=0.00..19824.00 rows=950933 width=25)
                    (actual time=0.026..134.500 rows=949820 loops=1)
  Filter: (status = 'completed'::text)
  Rows Removed by Filter: 50180
  Buffers: shared hit=7324
Execution Time: 170.498 ms

There is an index on status, and Postgres didn't use it. Why?

Because this query returns 95% of the table. Going through an index to fetch almost every row is more work than just reading the table from start to finish. Look at the estimate: 950,933 rows predicted, 949,820 actual. The planner knew exactly what it was doing.

To check, I told Postgres to avoid seq scans and ran it again:

SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'completed';
RESET enable_seqscan;

Bitmap Heap Scan on orders  (cost=10654.16..29864.82 rows=950933 width=25)
                            (actual time=53.171..159.778 rows=949820 loops=1)
  ...
Execution Time: 207.462 ms

With the index forced, it took 207 ms instead of 170 ms. The planner's cost numbers pointed the same way: 19,824 for the seq scan against 29,864 for the bitmap scan.

(enable_seqscan = off doesn't forbid seq scans. It makes them very expensive in the planner's eyes. It's a diagnostic tool, not something to leave on.)

Takeaway: when a query returns a large share of the table, a Seq Scan is often the fastest plan.

Case 2: The Same Index, a Selective Query

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'pending';

Index Scan using idx_orders_status on orders
  (cost=0.42..6573.73 rows=49067 width=25)
  (actual time=0.127..15.953 rows=50180 loops=1)
  Index Cond: (status = 'pending'::text)
  Buffers: shared hit=7320 read=46
Execution Time: 18.441 ms

Same table, same index, different value. Now Postgres uses the index and finishes in 18 ms instead of 170 ms.

Look at the Buffers line, though. It touched 7,366 buffers, about the same as the 7,324 the seq scan read. The pending rows are scattered randomly through the table, so even though only 5% of rows match, the query still visits almost every page.

My reading is that it's faster mainly because it processes and returns far fewer rows, not because it reads fewer pages. "Few matching rows" doesn't automatically mean "few pages touched."

Case 3: The Index Is Fine, the Query Isn't

This is the one that causes real production slowdowns. I added an index on customer_id:

CREATE INDEX idx_orders_customer ON orders (customer_id);

Now two queries that look like they do the same thing:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id::text = '42';

The first one:

Bitmap Heap Scan on orders  (cost=5.20..371.40 rows=100 width=25)
                            (actual time=0.140..0.363 rows=104 loops=1)
  Buffers: shared hit=104 read=3
Execution Time: 0.402 ms

The second one:

Gather  (cost=1000.00..16115.67 rows=5000 width=25)
        (actual time=2.043..53.119 rows=104 loops=1)
  Workers Planned: 2
  ->  Parallel Seq Scan on orders
        Filter: ((customer_id)::text = '42'::text)
        Rows Removed by Filter: 333299
        Buffers: shared hit=7324
Execution Time: 53.164 ms

Same 104 rows. 0.4 ms against 53 ms, about 130x slower, and the second query had to bring in parallel workers just to scan the whole table.

The cast on the column is the reason. The index stores customer_id values as integers, and customer_id::text is a different expression that the index knows nothing about, so Postgres can't use it.

Also look at the estimates. For the second query, the planner expected 5,000 rows and got 104. When you wrap a column in a function, Postgres loses its statistics for it and starts guessing.

The fix here is the obvious one: compare customer_id = 42 and let the integer index do its job. If you genuinely need to query on an expression, Postgres supports indexes on expressions, but I didn't test that here.

The same trap shows up with WHERE lower(email) = ..., WHERE date(created_at) = ... and similar.

Case 4: A Partial Index for the Rare Value

If you only ever query for the rare value, you don't need to index all 1,000,000 rows. You can index only the ones you care about:

CREATE INDEX idx_orders_pending ON orders (id) WHERE status = 'pending';

SELECT pg_size_pretty(pg_relation_size('idx_orders_status'))  AS full_index,
       pg_size_pretty(pg_relation_size('idx_orders_pending')) AS partial_index;

 full_index | partial_index
------------+---------------
 6904 kB    | 1120 kB

The partial index is about 6x smaller, because it only contains the 5% of rows where status = 'pending'. A smaller index means less to store, less to cache, and less to update on every write.

What About Indexing `created_at` Instead?

A reader suggested a partial index on created_at, which fits better when you ask for something like the oldest pending orders. I tested it with a query that has an ORDER BY and a LIMIT:

CREATE INDEX idx_orders_pending_created ON orders (created_at) WHERE status = 'pending';

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 20;

Before the new index, this was the best the planner could do:

Limit  (cost=2952.95..2953.00 rows=20 width=25) (actual time=140.350..140.357 rows=20 loops=1)
  Buffers: shared hit=3 read=7459
  ->  Sort  (...)  Sort Method: top-N heapsort  Memory: 27kB
        ->  Index Scan using idx_orders_pending on orders
              (actual time=1.269..132.395 rows=50180 loops=1)
Execution Time: 140.530 ms

It read all 50,180 pending rows and sorted them to return 20. After creating the index:

Limit  (cost=0.29..12.33 rows=20 width=25) (actual time=0.112..0.163 rows=20 loops=1)
  Buffers: shared hit=20 read=2
  ->  Index Scan using idx_orders_pending_created on orders
        (cost=0.29..30617.39 rows=50867 width=25)
        (actual time=0.109..0.153 rows=20 loops=1)
Execution Time: 0.200 ms

No sort, and only 20 rows read. Buffers dropped from about 7,462 to 22. One caveat on timing: the "before" run was a cold-cache run (note read=7459), so 140 ms exaggerates the gap. The row counts and buffers are the fairer comparison. The new index is the same size as the one on id, 1120 kB.

Without the LIMIT, the planner still picked the id index:

Index Scan using idx_orders_pending on orders  (cost=0.29..1699.30 rows=50867 width=25)
Execution Time: 17.112 ms

The estimated cost of reading every pending row through the created_at index was 30,617, against 1,699 for the id index (see the cost on the Index Scan line in the plan above). So the same partial index is a great fit for "oldest 20 pending orders" and the wrong one for "give me all pending orders." Which index helps depends on the query.

Case 5: Does Lowering `random_page_cost` Change Anything?

The same reader suggested checking random_page_cost. The default is 4, and on SSD storage that can make the planner too reluctant to use indexes. I tried 1.1 on the same table:

SHOW random_page_cost;   -- 4

SET random_page_cost = 1.1;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'completed';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending';
RESET random_page_cost;

The plans didn't change:

completed: still a Seq Scan, with the same estimated cost (19824.00) at both settings. A sequential scan doesn't depend on random_page_cost.
pending: still the same Index Scan. Only its estimated cost moved, from 1699.30 to 1290.40.

The timings need a warning. The completed query took 191 ms at the default and 145 ms at 1.1, but it ran the identical plan both times, so that gap is run-to-run noise, not the setting. Don't read conclusions from a single run.

The other caveat is that this table fits entirely in cache (every Buffers line is hit), so this test says little about a real SSD system under load. On a production database, change random_page_cost only after comparing actual query times before and after.

A Checklist for the Next Seq Scan You See

Is the query returning a big share of the table? Then a Seq Scan may be correct.
Compare the estimated rows to the actual rows. A big gap points to stale statistics (try ANALYZE) or a function or cast on the column.
Is the column wrapped in a function or cast? Rewrite the query, or use an expression index.
Do you only query for a small, specific slice of rows? Consider a partial index.
Not sure the planner chose well? Test with SET enable_seqscan = off in a session, compare the times, then RESET it.

The Takeaways

A Seq Scan is not automatically a problem. For queries that return most of the table, it's often the best plan.
The estimated and actual row counts are the fastest way to see if the planner is confused.
A cast or function on an indexed column can make the index useless, and the damage here was about 130x.
Few matching rows doesn't always mean few pages read.

These numbers come from single runs on my laptop, mostly with a warm cache (the one exception is noted in Case 4), so your timings will differ. The relative differences are what matter, so run the examples yourself.

Why Your Postgres Table Keeps Growing Even Though You're Deleting Rows

Mohamed Hussain S — Wed, 21 Oct 2026 08:35:20 +0000

A while back I noticed something odd. A table with a modest number of rows was taking far more disk space than it should. SELECT count(*) said one thing, pg_relation_size said another.

I had deleted and updated plenty of rows, so where was the space going? The answer is MVCC, and once you see it, a lot of Postgres behavior starts to make sense.

Let's reproduce it locally and watch it happen.

The Short Version

In Postgres, an UPDATE does not change a row in place, and a DELETE does not remove it. Both just leave the old version of the row behind. Those leftover versions are called dead tuples, and they keep taking up space until something cleans them up.

That "something" is VACUUM. But even VACUUM doesn't do exactly what most people expect.

Why Postgres Works This Way

Postgres uses MVCC (Multi-Version Concurrency Control) so that readers never block writers and writers never block readers.

If a transaction is reading a row while another one updates it, the reader needs to keep seeing the old version. So Postgres keeps both versions: the old one for anyone who still needs it, and a new one for everyone else.

Every row version carries two hidden columns, xmin (the transaction that created it) and xmax (the transaction that deleted or replaced it). Postgres uses these to decide which version each transaction is allowed to see.

Let's Reproduce It

Start with a simple table:

CREATE TABLE bloat_demo (
    id  int PRIMARY KEY,
    val text
);

-- Turn off autovacuum for this table so we can see the raw behavior
ALTER TABLE bloat_demo SET (autovacuum_enabled = false);

INSERT INTO bloat_demo
SELECT g, md5(g::text)
FROM generate_series(1, 100000) AS g;

Check the size:

SELECT pg_size_pretty(pg_relation_size('bloat_demo')) AS size,
       count(*) AS live_rows
FROM bloat_demo;

On my machine (running Postgres 17 in docker) this showed 6672 kB for 100,000 rows. Your number may differ slightly by version and settings.

Step 1: Watch a row move

SELECT ctid, xmin, xmax, id FROM bloat_demo WHERE id = 1;

UPDATE bloat_demo SET val = 'changed' WHERE id = 1;

SELECT ctid, xmin, xmax, id FROM bloat_demo WHERE id = 1;

The ctid (the physical location of the row) changes after the update. The row didn't get modified in place. Postgres wrote a brand new version somewhere else and left the old one behind.

In my run, the ctid went from (0,1) to (833,41). The table was about 834 pages at that point, so the new version landed on the very last page. Postgres appended it at the end of the file instead of rewriting the row where it was. The xmin also changed (742 to 754), because a different transaction created this version.

Step 2: Update everything, a few times

UPDATE bloat_demo SET val = md5(val);
UPDATE bloat_demo SET val = md5(val);
UPDATE bloat_demo SET val = md5(val);

Now check again:

SELECT pg_size_pretty(pg_relation_size('bloat_demo')) AS size,
       count(*) AS live_rows
FROM bloat_demo;

Still 100,000 live rows, but the table went from 6672 kB to 26 MB, about 4x bigger. Every pass left a full copy of dead rows behind.

You can see them in the stats:

SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'bloat_demo';

(These stats can lag by a moment, so if n_dead_tup looks low, wait a few seconds and run it again.)

My run showed n_dead_tup = 299962 next to 100,000 live rows: roughly three full copies of the table, one per update pass. It isn't exactly 300,000 because these numbers are estimates.

What VACUUM Actually Does

VACUUM bloat_demo;

Run the size query again. Here is the part that surprises most people: the file usually does not shrink.

VACUUM marks the space taken by dead tuples as reusable inside the table. New inserts and updates will fill that space instead of extending the file. But the file on disk stays the same size, because Postgres only gives space back to the OS in one narrow case: when completely empty pages sit at the very end of the table.

So VACUUM stops the table from growing further. It does not make it smaller.

Here is the proof from my run. After VACUUM the size was still 26 MB. Then I updated every row once more:

UPDATE bloat_demo SET val = md5(val);
SELECT pg_size_pretty(pg_relation_size('bloat_demo'));

It stayed at 26 MB. The new row versions went into the space vacuum had freed, instead of extending the file. That is what VACUUM is for: it keeps the table from growing, it doesn't shrink it.

Getting the space back

If you really need to shrink the file, you have two main options:

VACUUM FULL rewrites the whole table into a new file. It works, but it takes an ACCESS EXCLUSIVE lock, which blocks reads and writes for the whole duration. Not something you want to run casually on a busy production table.
pg_repack does a similar rewrite with much shorter locks. It is an extension you have to install, but it is the usual choice for live systems.

I ran VACUUM FULL on the demo table and the size dropped from 26 MB back to 6672 kB, exactly where it started.

The Gotcha: Long-Running Transactions

Here is the one that causes real production pain. VACUUM can only remove a dead tuple if no transaction could still need to see it.

If someone leaves a transaction open for hours (a forgotten BEGIN in a psql session, or an app stuck in idle in transaction), vacuum has to keep every row version that transaction might still read. Dead tuples pile up and vacuum runs but cleans almost nothing.

Find the offenders:

SELECT pid, state, xact_start, now() - xact_start AS open_for, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

If the oldest transaction on the list has been open for hours, that is probably your bloat problem.

Here is what this looked like in my test. In a second psql session I opened a transaction and left it open:

BEGIN;
SELECT txid_current();   -- returned 761

Back in the first session I updated every row and ran a verbose vacuum:

UPDATE bloat_demo SET val = md5(val);
VACUUM VERBOSE bloat_demo;

The relevant lines of the output:

tuples: 0 removed, 200000 remain, 100000 are dead but not yet removable
removable cutoff: 761

Vacuum ran fine and removed nothing. The 100,000 old row versions have to stay, because transaction 761 might still need to see them. The cutoff of 761 is the ID of that open transaction. A second VACUUM VERBOSE gave the same result. Ending the transaction with COMMIT or ROLLBACK lifts the block.

After I ran ROLLBACK in the second session, the same command gave a different result:

tuples: 100000 removed, 100000 remain, 0 are dead but not yet removable
removable cutoff: 763

Same table, same VACUUM. This time it removed all 100,000 dead versions, and the cutoff moved from 761 to 763 now that the old transaction was gone. The only thing that changed was the open transaction.

One detail: a bare BEGIN with nothing inside it doesn't hold vacuum back. What does is the transaction's ID or snapshot. I ran txid_current() inside mine, which assigns it an ID.

It's Not Always a psql Session

In my test the blocker was a plain open transaction, and the cutoff in VACUUM VERBOSE (761) matched its txid_current(). But the same "dead but not yet removable" line can come from other places that never show up in your list of connections. I only tested the open-transaction case above. The rest are things to check on a real system.

Thanks to Arhan Canli for pointing these out in the comments.

Take the removable cutoff number from VACUUM VERBOSE and look for what is holding that ID:

1. Open transactions and snapshots

SELECT pid, state, backend_xid, backend_xmin, xact_start
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC NULLS LAST;

2. Replication slots that stopped consuming

SELECT slot_name, slot_type, active, xmin, catalog_xmin
FROM pg_replication_slots;

An inactive slot with an old xmin or catalog_xmin holds cleanup back.

3. Standbys with hot_standby_feedback = on

With this setting, the replica reports its oldest snapshot to the primary, so a long query on the standby can block vacuum on the primary.

SELECT application_name, state, backend_xmin
FROM pg_stat_replication;

4. Forgotten prepared transactions

SELECT gid, prepared, transaction, owner
FROM pg_prepared_xacts;

Whichever one has an ID matching your cutoff is the culprit.

Stopping the forgotten-session case

For app connections that get stuck open, set a timeout so Postgres terminates the session instead of letting bloat build up:

ALTER ROLE app_user SET idle_in_transaction_session_timeout = '5min';

After that time Postgres closes the session, so your app needs to handle the dropped connection and retry.

How to Check Your Own Database

A quick look at which tables have the most dead tuples:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

A high dead_pct together with an old last_autovacuum means autovacuum is not keeping up (or is being blocked, see above).

This is an estimate based on statistics, not an exact measurement. For precise numbers, the pgstattuple extension can scan a table and report real bloat.

What I Took Away From This

UPDATE and DELETE never free space immediately. They create dead tuples.
VACUUM makes that space reusable, but it doesn't shrink the file.
VACUUM FULL shrinks it, at the cost of blocking the table.
Long-running transactions silently stop vacuum from doing its job.
Tables with heavy update traffic are the ones to watch.

The next time a table looks bigger than its data should justify, start with n_dead_tup and pg_stat_activity before touching anything else.

Have you run into table bloat in production? I'd like to hear how you found it.

What Actually Happens When You Start a Goroutine?

Mohamed Hussain S — Tue, 06 Oct 2026 07:16:41 +0000

Here's a program. It starts a million goroutines, parks every one of them, and then asks the kernel how many threads the process has.

release := make(chan struct{})
var started sync.WaitGroup
started.Add(1_000_000)

for i := 0; i < 1_000_000; i++ {
    go func() {
        started.Done()
        <-release // park here, all million alive at once
    }()
}
started.Wait()

fmt.Println("goroutines:", runtime.NumGoroutine())
fmt.Println("OS threads:", threads.Count())

Take a guess before you scroll.

goroutines: 1000001
OS threads: 3

Three.

So what did go actually do a million times, if it wasn't starting threads?

This post answers that with six small programs. Every number below comes from running them. You can run them too.

Why any of this exists: most languages give you one OS thread per unit of work, and threads are expensive - megabytes of stack, real kernel scheduling overhead. So you either pay that cost per task, or restructure everything around callbacks/async-await to avoid it. Go's pitch is that you keep writing plain, blocking-looking code, and the runtime multiplexes thousands of these onto a handful of real threads for you. That claim is exactly what the experiment above just tested.

Go syntax used below, once: go f() starts a goroutine - a concurrently-running call to f. ch <- v sends on a channel, v := <-ch receives; both block until the other side is ready. wg.Add/.Done/.Wait is a counter that blocks until it hits zero. That's the entire vocabulary this post needs. If none of it is new, skip straight to Experiment 1.

The Setup

Go 1.27.1, released September 2026
Linux amd64, one vCPU, on purpose, so nobody gets to say "it just used more cores"
Thread counts come from the Threads: line of /proc/self/status. That's the kernel's number, not Go's.

package threads

var re = regexp.MustCompile(`Threads:\s+(\d+)`)

func Count() int {
    d, _ := os.ReadFile("/proc/self/status")
    n, _ := strconv.Atoi(string(re.FindSubmatch(d)[1]))
    return n
}

I ran everything on Go 1.22.2 first and then again on 1.27.1. The picture was the same in both. The only visible difference is one extra column in the scheduler trace.

The baseline thread count wobbles between 3 and 5 from run to run. Compare the change, not the absolute value.

All six programs, plus a script that runs them, are in one repo: https://github.com/mohhddhassan/go-goroutine-experiments

Experiment 1: How Many Threads Does a Goroutine Cost?

The program from the top, looped over N and run twice: once with GOMAXPROCS=1, once with GOMAXPROCS=8. It also measures heap plus stack before and after, and how long the spawn loop took.

go run ./01-million

goroutines alive	OS threads, `GOMAXPROCS=1`	OS threads, `GOMAXPROCS=8`	memory each	spawn cost
1,000	3	9	2.9 KB	1.6 µs
10,000	3	9	2.7 KB	2.3 µs
100,000	3	10	2.9 KB	3.2 µs
1,000,000	3	10	2.9 KB	3.4 µs

The thread count ignored the goroutine count completely. It followed GOMAXPROCS.

A goroutine costs about 2.9 KB and a couple of microseconds. The spawn cost creeps up as N grows.

Meet G, M and P

Three letters explain that table.

G: a goroutine. A small struct plus a stack that starts at 2 KB. The kernel has never heard of it.
M: an OS thread. The only thing the kernel schedules.
P: a scheduling context. There are exactly GOMAXPROCS of them, and an M needs one to run Go code.

go process()
  -> a G is created
  -> it goes on a P's run queue
  -> an M holding that P runs it
  -> the OS schedules the M like any other thread

Goroutines are unbounded. Ps are not. That's the gap the table fell into.

Now to check that story instead of just believing it.

Experiment 2: Where Does a New Goroutine Wait?

GODEBUG=schedtrace=N makes the runtime print its own state every N milliseconds.

The program starts 200 CPU-bound goroutines, all from main:

for i := 0; i < 200; i++ {
    wg.Add(1)
    go func() {
        defer wg.Done()
        x := 0
        for j := 0; j < 200_000_000; j++ {
            x += j
        }
        _ = x
    }()
}
wg.Wait()

GODEBUG=schedtrace=1 GOMAXPROCS=4 go run ./02-runqueues

SCHED 1ms: gomaxprocs=4 idleprocs=2 threads=5 runqueue=0 [ 0 0 0 74 ]
SCHED 5ms: gomaxprocs=4 idleprocs=0 threads=5 runqueue=0 [ 17 18 31 32 ]
SCHED 9ms: gomaxprocs=4 idleprocs=0 threads=5 runqueue=0 [ 17 18 31 130 ]

runqueue is the global queue. The bracket is one local run queue per P.

At 1 ms, main has queued 74 goroutines on the P it's running on. The other three Ps are empty.

At 5 ms, those three Ps hold 17, 18 and 31 goroutines. The global queue is still 0.

Nobody put those goroutines there. main only ever queued onto its own P. The other Ps took them from it. That's work stealing.

At 9 ms the numbers add up: 17 + 18 + 31 + 130 = 196, plus the 4 currently running, makes 200.

Now the same program with one P:

GODEBUG=schedtrace=100 GOMAXPROCS=1 go run ./02-runqueues

SCHED 100ms: gomaxprocs=1 runqueue=5  [ 194 ] schedticks=[ 9 ]
SCHED 201ms: gomaxprocs=1 runqueue=10 [ 189 ] schedticks=[ 14 ]
SCHED 302ms: gomaxprocs=1 runqueue=15 [ 184 ] schedticks=[ 19 ]

Every 100 ms, five goroutines move from the local queue to the global one. schedticks, the number of times this P picked something to run, goes up by five.

Five switches per 100 ms.

Something is interrupting these goroutines. Who?

Experiment 3: Who Decides When a Goroutine Stops?

Two goroutines spin on one P. Each one watches the clock, and whenever it notices it was off the CPU for more than a millisecond, it writes down for how long.

runtime.GOMAXPROCS(1)
// in each of the two goroutines:
last := time.Now()
for last.Before(end) {
    now := time.Now()
    if d := now.Sub(last); d > time.Millisecond {
        gaps = append(gaps, d) // we were descheduled for d
    }
    last = now
}

go run ./03-timeslice

98 descheduled gaps
min=15.3ms  median=20.2ms  p90=20.2ms  max=40.3ms

Each goroutine runs for about 20 ms and then the other one gets the P.

That matches the trace above. Five switches per 100 ms is 20 ms each. Two different measurements, one number.

The runtime's sysmon thread checks every ~10 ms and sets a preemption flag on any G running >10 ms - so a goroutine can run almost a full extra tick before sysmon catches it, which is why the observed number lands closer to 20 ms than 10.

Nothing in those loops asked to be interrupted. So what about a loop with no function calls at all, where the runtime has no natural place to step in?

runtime.GOMAXPROCS(1)
go func() {
    for {
    }
}()
t := time.Now()
time.Sleep(100 * time.Millisecond)
fmt.Printf("main resumed after %v\n", time.Since(t).Round(time.Millisecond))

go run ./04-tightloop
main resumed after 116ms

GODEBUG=asyncpreemptoff=1 timeout 5 go run ./04-tightloop
(nothing. killed by the timeout after 5 s)

Function calls use compiler-inserted stack checks for cooperative preemption; tight loops (for {}) require Go 1.14+ OS signals (SIGURG).

Experiment 4: What Happens When a Goroutine Blocks?

I expected "blocked" to cost about the same everywhere.

It doesn't.

One program, five ways to get stuck. 200 goroutines each, GOMAXPROCS=1, sampled while they're all stuck and again 1.5 seconds after they finish:

go build -o blocking ./05-blocking
GOMAXPROCS=1 ./blocking rawread 200

stuck on	threads before	threads during	1.5 s after
channel receive	5	5	5
`time.Sleep`	4	4	4
TCP socket `Read`	4	4	4
CPU-bound loop	4	4	4
raw `read(2)` on a pipe	4	203	203

Four rows cost nothing. One cost a thread per goroutine.

The goroutines in the last row do this:

syscall.Pipe(p[:])
go func(fd int) {
    b := make([]byte, 1)
    syscall.Read(fd, b) // blocks in the kernel
}(p[0])

Scaled up to 1,000 goroutines each:

TCP socket read     threads: 5 -> 5
raw read(2)         threads: 4 -> 1003 -> 1004

What's going on:

Channels and timers are runtime bookkeeping. The goroutine parks, and no thread is involved.
Network reads go through the netpoller. The socket is non-blocking, the goroutine parks, and epoll wakes it up. A thousand idle connections cost zero extra threads.
A raw syscall blocks the thread inside the kernel. That M is stuck, so the runtime hands its P to another M to keep everything else running.

That handoff is what stops one blocked goroutine from freezing the rest. The price is one OS thread per stuck goroutine.

And the threads don't leave. 203 was still 203 after 1.5 seconds. The runtime keeps them around for reuse.

Experiment 5: What Does `GOMAXPROCS` Control?

Eight CPU-bound goroutines, identical work, one core:

for p in 1 2 8; do GOMAXPROCS=$p go run ./06-gomaxprocs; done

GOMAXPROCS	wall time
1	1.140 s
2	1.139 s
8	1.138 s

Identical. GOMAXPROCS doesn't create CPUs.

It sets the number of Ps, meaning how many threads may run Go code at the same moment. On a machine with real cores that's your parallelism limit. Here there was one core, so raising the limit changed nothing.

What I Couldn't Test

Real parallelism. One vCPU. I can show what GOMAXPROCS sets, not the speedup it unlocks. On a multi-core box, run experiment 5 yourself and tell me what you get.
Individual steals. I inferred stealing from queue lengths. go tool trace would show each steal directly.
Cgo and disk reads. I measured a pipe read. I believe they take the same path, but I didn't test them.
Other platforms. Everything here is Linux. The thread counting reads /proc.

Where This Falls Short

Goroutines are cheap. They aren't free.

A million goroutines is about 2.9 GB. 2.9 KB each sounds like nothing until you multiply.
A thread per stuck goroutine adds up. The runtime caps threads at 10,000 by default. 1,000 raw reads already got to 1,003. I didn't push it to the cap.
"Cheap to start" says nothing about what it does next. Every thread in this post came from what the goroutines did after they started.

The Actual Answer

go process() doesn't start a thread. It starts a chain.

go process()
  -> a G is allocated                     (~2.9 KB, ~2-3 µs)
  -> queued on the P that ran the go statement
  -> idle Ps steal from it                (seen within ~5 ms)
  -> an M holding that P runs it          (threads follow GOMAXPROCS, not goroutines)
  -> interrupted about every 20 ms        (tight loops included)
  -> parks on a channel, timer or socket  (0 extra threads)
  -> or blocks in a raw syscall           (its P moves to another M: 1 thread each)

A goroutine isn't a lightweight thread. It's a job the runtime schedules onto a handful of threads, and it asks the OS for more only when something blocks in the kernel.

What costs a thread isn't go process().

It's what process() does once it's running.

Next in this series: what actually happens when a Go channel blocks.

When Should You Actually Worry About a Growing Replication Queue?

Mohamed Hussain S — Wed, 16 Sep 2026 05:51:04 +0000

Last time, the takeaway was simple: a non-zero replication queue isn't a problem statement, it's a starting point for one. It just means a replica has pending work - fetches, merges, mutations - that Keeper says it needs to do.

That still leaves the actual question unanswered though:

When does a queue go from "normal" to "something's actually wrong"?

This is where you stop looking at the queue as a single number and start treating it as a signal with context - trend, task type, retries, and the exception behind it.

Quick Refresher

Before diving in, the short version from last time:

Every replica has its own local replication queue
The queue is generated from Keeper's replication log
system.replicas gives you the summary (queue_size, absolute_delay, etc.)
system.replication_queue gives you the detail (task type, retries, last_exception)

With that in place, here's how to actually reason about a growing queue.

Rule #1: A Snapshot Means Almost Nothing

If you check queue_size once and see a number, you've learned almost nothing about whether the replica is healthy.

queue_size = 20

could mean:

a large batch of inserts just landed, and the replica is about to chew through all 20 tasks in seconds
the replica has been stuck on the same 20 tasks for the last hour

Same number. Completely different situations.

The only way to tell the difference is to sample it more than once.

SELECT
    database,
    table,
    replica_name,
    queue_size,
    absolute_delay,
    now() AS checked_at
FROM system.replicas
WHERE table = 'your_table';

Run this a few times, a few minutes apart, and look at the shape:

10:00 → queue_size = 20
10:05 → queue_size = 20
10:10 → queue_size = 20
10:15 → queue_size = 21

That's not "a queue of 20." That's a queue that isn't moving, and is now growing. That's the actual signal worth investigating - not the number itself.

If instead you see:

10:00 → queue_size = 20
10:05 → queue_size = 6
10:10 → queue_size = 0

that's a replica doing exactly what it's supposed to do. Nothing to chase there.

Rule #2: Queue Size and Replication Lag Are Not the Same Metric

It's easy to conflate these two, but they answer different questions.

Metric	What it tells you
`queue_size`	How many tasks are currently pending
`absolute_delay`	How far behind (in seconds) the replica actually is

A replica can have a nonzero queue and low delay - because it's processing fast enough that it never really falls behind in any meaningful sense.

A replica can also have a small queue and still be meaningfully behind, if the pending tasks are old and just haven't moved.

That's why system.replicas also exposes:

SELECT
    replica_name,
    queue_size,
    queue_oldest_time,
    absolute_delay
FROM system.replicas
WHERE table = 'your_table';

queue_oldest_time tells you when the oldest task in the queue was created. If that timestamp is recent, the queue is young - probably fine. If it's from an hour ago and the queue hasn't shrunk, you're looking at a genuinely stuck replica, not just a busy one.

Rule #3: `num_tries` Tells You If a Task Is Actually Failing

Every entry in system.replication_queue carries a num_tries column.

SELECT
    replica_name,
    type,
    create_time,
    num_tries,
    last_attempt_time,
    last_exception
FROM system.replication_queue
ORDER BY num_tries DESC
LIMIT 10;

A task with num_tries = 0 or 1 just hasn't been picked up yet - could be totally normal, especially right after a burst of inserts.

A task with num_tries = 40 and climbing is a task that keeps failing and retrying. That's a completely different problem than "the replica is just busy." Something is preventing that specific task from completing, and it will keep retrying until whatever's blocking it is resolved.

This is usually the fastest way to tell "backlog" apart from "broken."

Rule #4: `last_exception` Is the Real Diagnostic

If num_tries tells you that something is failing, last_exception tells you why.

Common categories you'll actually run into:

Network / connectivity issues - a replica can't reach the source replica to fetch a part
Disk space - not enough room to write the incoming part
Missing or corrupted parts - the part a task depends on isn't available where expected
Too many parts - ClickHouse throttling merges/inserts because part count exceeds configured limits
Keeper session issues - the replica lost its session with Keeper and can't coordinate properly

None of these show up in queue_size. They only show up when you actually read the exception text.

SELECT
    replica_name,
    type,
    num_tries,
    last_exception
FROM system.replication_queue
WHERE last_exception != ''
ORDER BY num_tries DESC;

If this query returns nothing, your queue is backed up but not actively failing - it's just working through volume. If it returns rows, you now know exactly what's blocking progress, and you can go fix that specific thing instead of guessing.

Rule #5: Don't Restart Blind

It's tempting, when a queue looks bad, to reach straight for:

SYSTEM RESTART REPLICA your_table;

Sometimes that's the right move - for example, if the replica's Keeper session got wedged and a restart lets it re-establish state cleanly.

But if the actual problem is disk space, a missing part on another replica, or a network issue between nodes, restarting doesn't fix any of that. It just resets the local state and lets the same tasks fail again, possibly after masking the original exception in the process.

The order that actually works:

Confirm the queue is genuinely stuck (not just busy) - check the trend
Look at num_tries to find which tasks are actually failing, not just pending
Read last_exception for those tasks
Fix the underlying cause
Then consider whether a restart or SYSTEM SYNC REPLICA is needed to help it recover

Skipping straight to step 5 is how you end up chasing the same issue again a few hours later.

The Checklist

When a queue looks concerning, this is the order I actually go through:

Is it growing, or just non-zero? - sample queue_size more than once
How old is the oldest task? - check queue_oldest_time
Is absolute_delay moving too? - queue size and lag should usually track loosely; if delay is flat while queue climbs, something's off
Are tasks retrying? - check num_tries per task, not just the total count
What does last_exception say? - this is almost always where the real answer is
Is it one bad task or the whole queue? - a single stuck GET_PART blocking everything behind it looks very different from broad, evenly-spread failures

Six questions, in that order, will get you to the actual root cause faster than staring at a single queue_size number ever will.

Final Takeaway

A replication queue growing isn't inherently bad - replicas fall behind and catch up constantly, especially under bursty write patterns. The number alone was never designed to tell you whether that's fine or not.

What actually tells you is:

Is the queue moving, and if it isn't, what does last_exception say about why?

That's the real question behind "should I worry about this." Everything else - queue_size, absolute_delay, num_tries - is just context that helps you answer it faster.

Understanding the Replication Queue in ClickHouse

Mohamed Hussain S — Mon, 07 Sep 2026 18:28:48 +0000

I was testing out CH-Ops - an admin GUI for self-hosted ClickHouse - on a simple setup: 1 shard, 2 replicas. Stumbled onto the replication queue almost by accident.

Here's what I did: I stopped one of the nodes (let's call it Node B), then inserted some data through the other one (Node A). Just wanted to see what would happen.

Then, while Node B was still down, I checked it in CH-Ops. It had stuff sitting in its replication queue.

My first assumption was: okay, this must be showing what's left to replicate across the cluster - the total pending replication work.

So I switched over and checked Node A, the one that was actually up and had just received the insert.

Its queue was empty.

That didn't match what I expected at all. If the queue was a cluster-wide "here's what still needs to replicate" view, Node A should've shown something too - it was the one that had the fresh data now waiting to reach Node B. Instead it was Node B, the down one, sitting there with pending tasks.

That mismatch is what sent me digging. Turns out the queue isn't cluster-wide at all - it's specific to each ClickHouse instance. Once I brought Node B back up, its queue drained in seconds and the data showed up.

That whole experiment is basically the entire post in miniature. Here's the mental model I ended up with.

A Queue Belongs to a Replica, Not to the Table

This is the first thing to get straight.

With a ReplicatedMergeTree table, you can have multiple replicas holding copies of the same data. It's tempting to think of replication as one shared pipe between them.

It isn't.

Each replica keeps its own local replication queue.

So if you see:

Replica 1 → queue_size = 0
Replica 2 → queue_size = 25

that doesn't mean 25 operations are waiting somewhere in the middle for both replicas to pick up.

It means Replica 2, specifically, has 25 tasks it hasn't finished yet.

Once that clicked for me, the rest of the system made a lot more sense.

So Where Do These Tasks Come From?

Replication in ClickHouse isn't a direct copy from one replica to another. There's a coordination layer in between.

ClickHouse Keeper holds the replication log - the record of operations that happened on the table. When something replicated happens (a part gets inserted, a merge runs, a mutation is applied), that operation gets logged in Keeper.

Each replica watches that log and figures out: what do I need to do to catch up to this?

That "what do I need to do" becomes a task, and the task lands in that replica's queue.

Depending on what happened, a task might be:

fetch a part
merge parts
apply a mutation
drop a range
run another replicated operation

So the queue is really just a to-do list, generated from the shared log, but executed locally.

Where You Actually See This: `system.replicas`

CH-Ops was showing me a friendlier view of exactly this, but under the hood, this is the table doing the work. It's usually the first place people look, and for good reason - it gives you a quick health snapshot per replica.

SELECT
    database,
    table,
    replica_name,
    is_leader,
    is_readonly,
    queue_size,
    inserts_in_queue,
    merges_in_queue,
    part_mutations_in_queue,
    absolute_delay
FROM system.replicas;

Example output:

┌─replica_name─┬─queue_size─┬─inserts_in_queue─┬─merges_in_queue─┬─absolute_delay─┐
│ replica_1    │ 0          │ 0                │ 0               │ 0              │
│ replica_2    │ 12         │ 3                │ 9               │ 4              │
└──────────────┴────────────┴──────────────────┴─────────────────┴────────────────┘

queue_size gives you the total. inserts_in_queue, merges_in_queue, and part_mutations_in_queue break that total down by task type, which already tells you more than the single number does.

But it still doesn't tell you why those 12 tasks are sitting there. For that, you need the next table.

The Detail View: `system.replication_queue`

If system.replicas tells you how much work is waiting, system.replication_queue tells you what that work actually is.

SELECT
    database,
    table,
    replica_name,
    type,
    create_time,
    num_tries,
    last_exception
FROM system.replication_queue
ORDER BY create_time;

Instead of a single number, you now get the actual list:

GET_PART
MERGE_PARTS
GET_PART
MUTATE_PART
GET_PART
...

You can see the task type, how long it's been sitting there (create_time), how many times it's been retried (num_tries), and - this is the important one - last_exception.

That single column is often the difference between "the queue is 12" and "I know exactly why the queue is 12."

Two Tables, Two Different Questions

Once I had both queries in my toolkit, it became clear they answer different questions:

Table	Question it answers
`system.replicas`	How much work is a replica behind on, right now?
`system.replication_queue`	What is that work, specifically, and is it stuck?

Neither one alone gives you the full picture. system.replicas is a summary. system.replication_queue is the detail behind that summary.

Queue Size Is Not the Same as Replication Lag

This one tripped me up early on, so it's worth calling out explicitly.

A replication queue measures pending work. Replication lag (absolute_delay) measures how far behind a replica is.

They're related, but they're not interchangeable.

A replica can have:

queue_size = 50

and be processing those 50 tasks fast enough that lag barely moves. A few minutes later, the queue is empty and nothing was ever "behind" in any meaningful sense.

A replica sitting at:

queue_size = 5

for hours, on the other hand, is a very different story - even though 5 sounds small next to 50.

The number by itself doesn't tell you which situation you're in. That's the whole point of this post: the queue is a data source, not a verdict.

The Mental Model, Put Simply

Here's the picture:

The replica is the participant - it holds data and does work.

The replication queue is that participant's pending work, generated from what Keeper says has happened on the table.

The queue exists because the replica has catching up to do. That's it. It's not a fault indicator on its own - it's a reflection of activity and progress.

What This Sets You Up For

Understanding the queue as "a to-do list, not a fault code" is step one.

Step two - the part where you actually decide when to worry - comes down to trend, task type, and last_exception, and that's a big enough topic to deserve its own post.

For now, the takeaway is simpler:

A non-zero replication queue isn't a problem statement. It's a starting point for one.

Know what the queue is before you try to diagnose what it means.

ClickHouse 26.8 LTS: 57 Breaking Changes Since 26.3

Mohamed Hussain S — Fri, 28 Aug 2026 09:33:52 +0000

If you run ClickHouse in production, you're probably on 26.3 LTS. And now 26.8 LTS is out, which means the LTS-to-LTS upgrade conversation starts again.

Here's the thing most release posts skip: this is not a one-release hop.

Going from 26.3 LTS to 26.8 LTS means crossing 26.4, 26.5, 26.6 and 26.7 as well. Every breaking change in those four releases applies to you, and some of the ones most likely to ruin your day aren't in 26.8 at all.

So instead of writing another "here are the 26.8 features" post, I wanted to write the thing I'd actually want before scheduling this upgrade: what breaks, what silently changes, what order to do things in, and what you get for the trouble.

Where 26.8 stands

26.8 LTS was announced on 27 August and first tagged on 30 August (v26.8.1.2041-lts). As of 29 September it's already at 26.8.14, so the usual wait for the first few patch releases has mostly played out. Target a recent patch, not .1. 26.3 LTS is still getting patches too (26.3.35 as of 28 September), so the timing is yours to choose.

The scale of what you're crossing

Release	Date	Breaking	New features	Perf improvements
26.4	2026-04-30	6	34	45
26.5	2026-05-21	10	31	48
26.6	2026-06-25	10	50	79
26.7	2026-07-22	11	35	117
26.8 LTS	2026-08-27	21	49	127
Total		58	199	416

A note on that 58. The upstream changelog lists 58 backward-incompatible entries across these five releases, but one is recorded twice - the http_max_fields reduction (PR #103285) appears in both the 26.4 and 26.5 sections, presumably from a backport. That leaves 57 unique breaking changes. The table shows raw changelog counts; two other sections carry the same backport duplicates (new features: 199 → 197 unique; performance: 416 → 415), and the prose uses the unique figures. Counted on 29 September 2026 against the finalised changelog.

On top of all that, roughly 100 settings changed their default value across the five releases. Those don't appear in any "backward incompatible" list, and they're where a lot of the surprises actually live.

I've grouped everything below by what it does to you, not by which release it landed in. That's the more useful ordering when you're planning an upgrade.

Part 1: Three things that can stop the upgrade dead

Start here. These aren't "test carefully" items - they're "the server won't start" or "you can't go back" items.

1. The x86 build now requires AVX2 (26.6)

The default x86 build moved from x86-64-v2 (SSE4.2) to x86-64-v3, which needs AVX2, BMI1, BMI2, F16C, FMA, LZCNT, MOVBE and XSAVE. In practice: Intel Haswell or newer, AMD Excavator or newer.

Virtually every x86 CPU after 2015 supports this. But if you have older hardware anywhere in the fleet, or you're running on a hypervisor that masks CPU flags, the binary simply won't run.

Check every node before you plan anything else:

grep -o 'avx2' /proc/cpuinfo | head -1

No output means you need the amd64compat build, which targets plain x86-64.

This one is easy to miss because it doesn't appear in the 26.8 notes at all.

2. `insert_deduplication_version` has a mandatory migration order (26.6 → 26.7)

If your config sets insert_deduplication_version to old_separate_hashes or compatible_double_hashes, a 26.7+ server refuses to start. Not a warning - a refusal.

The migration path has to happen before you upgrade:

Run a release that supports compatible_double_hashes (it writes both the legacy and unified hashes).
Keep it running for at least replicated_deduplication_window_seconds - one hour by default - for replicated tables. For non-replicated tables with non_replicated_deduplication_window > 0, the window is count-based, so run it for at least that many inserts.
Remove the setting, or set it to new_unified_hash.
Now upgrade.

If you never set this setting, you can ignore all of it. Worth a quick SELECT * FROM system.server_settings WHERE name = 'insert_deduplication_version' to be sure.

While we're here: 26.6 also changed what dedup means. It now works on the whole inserted block per insert rather than per part or partition. A retry of the same insert is still deduplicated, but two different inserts that happen to produce an identical part are no longer cross-deduplicated, and reordered inserts of the same rows are no longer deduplicated either. If you were relying on the old behaviour as a correctness guarantee, that's worth thinking about.

3. S3 stops using the server's own credentials for user queries (26.7)

This is the one I'd most expect to catch people out, because of how it fails.

S3 access originating from user SQL no longer resolves the server's own cloud credentials by default - not environment variables, not IMDS/IRSA, not instance profiles, not AWS config files, not role_arn-based STS, not GCP OAuth metadata. Such requests now need explicit credentials or NOSIGN. Named collections default use_environment_credentials to 0.

To restore the old behaviour you need both:

SET use_environment_credentials = 1;
SET s3_allow_server_credentials_in_user_queries = 1;

The nastier part is startup behaviour. A persistent S3 or S3Queue table, a dynamic S3 disk, or a DataLakeCatalog whose definition resolves such credentials now loads anonymously rather than aborting server startup. That's controlled by s3_load_table_anonymously_if_credentials_restricted, which is on by default.

So the server comes up clean, everything looks healthy, and then queries against those tables fail at read time. Test every S3 access path before this hop, not after.

Part 2: Changes that alter results without throwing an error

These are the ones that won't show up in your logs. Your queries keep running; the answers change.

Loose date strings now parse (26.5)

date_time_input_format and cast_string_to_date_time_mode both moved from basic to best_effort.

Strings like 2024 April 4 or Apr 15, 2020 10:30:00 used to be rejected. Now they parse. If you were relying on strict parsing to reject malformed input at the door, that door is now open - bad data lands and gets interpreted rather than bouncing.

`CAST` preserves the source time zone (26.5)

CAST to DateTime or DateTime64 without an explicit time zone now preserves the time zone of its source argument, matching what toDateTime and toDateTime64 already did.

More correct, but it changes displayed timestamps in existing queries. If you have dashboards where times shifted after an upgrade, this is a likely culprit.

`FINAL` on a JOIN stopped leaking to other tables (26.6)

This was a bug fix, but it changes results.

Previously, FINAL on the left-most table of a JOIN was also applied to the other joined tables. That was never intended. Now it isn't.

If you have queries that were quietly depending on the old behaviour, they'll start returning pre-merge duplicates from the right-hand side. There's a compatibility setting:

SET analyzer_compatibility_apply_final_to_all_joined_tables = 1;

It's registered in the settings history, so compatibility set below 26.6 restores it automatically.

Unquoted JSON numbers become timestamps (26.8)

In JSONEachRow and similar formats, an unquoted number for a DateTime/DateTime64 column is now read as a Unix timestamp with optional sub-second precision - consistent with Values, CAST and toDateTime64.

Concretely, before this change:

1703363853.035 was rejected outright
A bare 1703363853 was read as the raw scaled tick value, producing a 1970-... timestamp

Now both do what you'd expect. Quoted strings and ClickHouse's own JSON output are unaffected.

For most people this silently fixes things. But if your pipeline was compensating for the old raw-ticks behaviour - pre-scaling values, or writing quoted strings specifically to avoid it - you'll now get a double correction.

SET input_format_read_datetime_number_as_raw_value = 1;

`toDate32` reinterprets a whole numeric range (26.8)

Date32 extended its range from [1900-01-01, 2299-12-31] to [0000-01-01, 9999-12-31], matching DateTime64.

The subtle consequence: in toDate32(N), values in [120530, 2932896] are now interpreted as day numbers - dates from 2300-01-01 to 9999-12-31 - rather than Unix timestamps in early 1970. If you feed epoch seconds into toDate32, you get wildly different results.

Array function semantics fixed (26.8)

arrayIntersect and arraySymmetricDifference no longer count a value repeated within a single argument as if it appeared in several arguments:

SELECT arrayIntersect([1], [2], [1, 1]);       -- [] (was [1])
SELECT arrayIntersect([1, 2], [2], [1, 1, 2]); -- [2] (was [1, 2])
SELECT arraySymmetricDifference([1], [2, 2]);  -- [2, 1] (was [1])

A value now counts for an argument only if it was present in every argument before it, so the result contains exactly the values present in all arguments. arrayUnion is unaffected.

There's a nice side effect: arrayIntersect now builds its hash table from the smallest argument rather than all of them - up to 1.85x faster and about a third less memory when argument sizes differ a lot.

Trivial views over `Distributed` tables push down (26.8)

For a view whose body is a plain SELECT over a single Distributed table, the whole outer query now goes to the shards. This is optimize_trivial_view_pushdown_to_distributed, on by default.

Two observable changes: FINAL and SAMPLE written on the view reference now propagate to the shard-local table instead of being silently ignored, and extremes isn't reported on single-shard clusters.

If you had views where FINAL was quietly a no-op, your results change - more correct, probably slower.

Response format header overrides `FORMAT` (26.8)

The X-ClickHouse-Format header is now an alias for output_format, so it overrides both the query's FORMAT clause and the URL path extension. Before, it only set a default when nothing else specified a format. A client or proxy that sends it alongside a query with its own FORMAT now gets the header's format. It never changes how an INSERT body is parsed.

Part 3: Operational behaviour changes

`max_insert_threads` now defaults to auto (26.8)

Probably the biggest operational change in 26.8. INSERT SELECT is now parallelised across available CPU cores by default, and max_insert_threads can also fan out the write side of a plain INSERT where the destination path can safely do so.

Faster ingestion, but with consequences:

More data parts from the same query, so more merge pressure and a real risk of too_many_parts on tables with tight parts_to_throw_insert
Different row order within an insert, which matters for anything depending on insertion order - a non-deterministic ORDER BY tie-break, _part / _block_number assumptions, a ReplacingMergeTree without a proper version column

One detail worth knowing if you diff configs: the declared default is literally 0 in both 26.7 and 26.8. What changed is the setting's type, from UInt64 to MaxThreads. Under UInt64, both 0 and 1 meant single-threaded. Under MaxThreads, 0 resolves to auto. So system.settings reads 0 before and after while the behaviour flips underneath you.

Upstream records the change as 1 → 0 in its compatibility mapping, which looks contradictory until you realise 1 is what you now set to get the old behaviour - not what the declaration used to say.

SET max_insert_threads = 1;  -- restores the old behaviour

Lightweight update patch parts get a v2 format (26.8)

Patch parts produced by lightweight UPDATE now use a v2 on-disk format, sorted by (sorting_key..., _block_number, _block_offset) with a new merge algorithm. Peak memory during apply is bounded by the largest equal-sort-key run rather than the whole patch, and updates crossing merge boundaries no longer fall back to an in-memory Join apply. Old-format patch parts stay readable.

For replicated clusters, this needs a rolling-upgrade pin. Note that patch_parts_version is a MergeTree setting, not a session setting, so SET patch_parts_version = 'v1' will throw. Use the config:

<merge_tree>
    <patch_parts_version>v1</patch_parts_version>
</merge_tree>

Or per table:

ALTER TABLE my_table MODIFY SETTING patch_parts_version = 'v1';

Hold that until every replica is on 26.8, then remove it.

Upstream's own guidance also allows setting compatibility below 26.8 instead of the explicit pin, until every replica is upgraded.

The same logic applies to text indexes: text_index_serialization_version defaults to v2_with_positions in 26.8, which persists token positions for phrase search. Older servers can't read that format, so pin it to v1_with_codec during the rollout.

Hash joins now spill by default (26.5)

max_bytes_ratio_before_external_join moved from 0 to 0.5. Hash joins now spill to disk at 50% of the memory limit.

Generally a good thing - fewer OOM kills. But if you had joins comfortably fitting in memory, some of them will now start writing to disk and get slower. Watch join-heavy query latency after the upgrade.

`REPLACE PARTITION` from an empty source is now rejected (26.6)

Previously, ALTER TABLE ... REPLACE PARTITION ... FROM ... where the source table had no parts in that partition would silently drop the destination partition and write nothing. A genuine data-loss footgun.

It's now rejected with BAD_ARGUMENTS. If you have automation that relied on the old behaviour as a clearing mechanism, it will start failing - which is the correct outcome, but it will fail. Use DROP PARTITION explicitly, or set allow_replace_partition_from_empty_source = 1.

`AggregatingMergeTree` validates schemas at creation (26.7)

AggregatingMergeTree now rejects, at table creation time, schemas where a column is neither part of the sorting key nor an aggregate-state measure (AggregateFunction / SimpleAggregateFunction).

Those columns were silently collapsed to an arbitrary value during background merges, producing wrong results for anything grouping or filtering on them. Now you find out up front.

Existing tables keep working, but any automation that recreates such tables will start failing. allow_dimensions_outside_sorting_key = 1 restores the old permissiveness.

HTTP request limits tightened (26.4)

http_max_fields dropped from 1,000,000 to 1,000, and http_max_field_name_size from 128 KB to 4 KB, to limit pre-authentication memory usage. New http_max_request_header_size and http_headers_read_timeout settings arrived alongside.

If any client sends a lot of headers or URL parameters - some role-based auth proxies do - it'll start getting rejected.

Part 4: The tooling around ClickHouse

This is the part upgrade checklists usually miss. ClickHouse itself starts fine; the dashboards, exporters and scripts built around it don't.

Asynchronous metrics became maps (26.8)

Per-CPU-core and per-device metrics were consolidated into single key-value metrics. OSUserTimeCPU0, OSUserTimeCPU1, OSUserTimeCPU2 became a single OSUserTimeCPU metric holding a map from core number to value.

The same applies to the other OS*TimeCPU* metrics, plus CPUFrequencyMHz_*, Temperature*, EDAC*, Block*_*, Network(Receive|Send)*_*, Disk*_*, *BlobsQueueEstimate and AsyncLogging*QueueSize.

Downstream:

system.asynchronous_metrics gains key_values Map(LowCardinality(String), Float64). The plain value column is NaN for those metrics
system.asynchronous_metric_log logs one row per key via a new key column
Prometheus exports them as labels: ClickHouseAsyncMetrics_BlockReadBytes{device="sda"}
Graphite MetricsTransmitter sends <prefix>.<Metric>.<key>

Any Grafana panel keyed on the old metric names goes blank. Not errors - just empty charts, which is worse because nobody notices for a week.

`EXPLAIN PLAN` output format changed (26.7)

explain_query_plan_default moved from legacy to pretty, so EXPLAIN PLAN now defaults to actions=1, compact=1, pretty=1.

If you have anything that parses EXPLAIN output - query validators, cost estimators, index advisors, any "explain this query" feature in an internal tool - it breaks. This one is in 26.7, so a 26.8-only reading of the changelog misses it entirely.

SET explain_query_plan_default = 'legacy';

`EXPLAIN SYNTAX` returns one row (26.8)

EXPLAIN SYNTAX now returns the reformatted query as a single String record with embedded newlines, instead of one record per line. So SELECT count() FROM (EXPLAIN SYNTAX ...) returns 1.

Two ways to restore the old shape - the per-statement option and the session setting:

EXPLAIN SYNTAX single_record = 0 SELECT 1;
-- or session-wide:
SET explain_syntax_single_record = 0;

Other EXPLAIN kinds (PLAN, PIPELINE, AST) keep their per-line tree output.

System table changes

Change	Release	Impact
`system.users.valid_until` is now `Array(DateTime64(0))`, was `Array(DateTime)`	26.8	Deadlines past 2106 are exact. Tooling reading this column needs to handle the new type
`show_data_lake_catalogs_in_system_tables` renamed to `show_remote_databases_in_system_tables` and broadened	26.6	At the default `0`, MySQL and PostgreSQL databases are now hidden from `system.tables`, `system.columns` and `system.completions` - not just data lake catalogs. Schema browsers will show fewer databases
`system.instrumentation.parameters` renamed to `arguments`; `HANDLER PARAMETERS` syntax renamed to `HANDLER ARGUMENTS`	26.6	Old names are gone, not aliased
`system.histogram_metric_log` deprecated	26.5	Histograms are now a nested column on `system.metric_log`

`allow_feature_tier` numbers shifted (26.8)

A new private preview tier sits between experimental and beta, so the ordering is now experimental < private preview < beta < production.

The numeric levels moved with it: 0 allows all tiers, 1 excludes experimental, 2 additionally excludes private preview, 3 allows production only.

If you pinned allow_feature_tier = 2 to mean "production settings only", you now need 3. This is filed under New Feature in the changelog, but it's a silent behaviour change to an existing config value.

Part 5: Removals - the grep list

Things that no longer exist. Each of these is a cheap search through your codebase and configs.

Removed	Release	Replacement
`library` dictionary source (`SOURCE(LIBRARY(...))`)	26.8	Fails with `UNKNOWN_ELEMENT_IN_CONFIG`. `dictionaries_lib_path` is inert
`include_from` defaulting to `/etc/metrika.xml`	26.8	Add `<include_from>/etc/metrika.xml</include_from>` explicitly. Separately-loaded `users.xml` and XML dictionary configs each need their own
MySQL source `ssl_ca` / `ssl_cert` / `ssl_key` as file paths from SQL	26.8	Use `ssl_ca_pem` / `ssl_cert_pem` / `ssl_key_pem` with inline contents. Paths still work in the server config file
NATS `nats_credential_file` from SQL	26.8	Use inline `nats_credentials`, or a named collection in the server config
Apache Arrow-based `Arrow` / `ArrowStream` reader and writer	26.8	Native implementation only. The `*_use_native_reader` / `_writer` settings are accepted but ignored
Config-based workload scheduling (`resources`, `workload_classifiers` sections)	26.7	`CREATE RESOURCE` / `CREATE WORKLOAD`. Old sections ignored with a warning
`snowflakeToDateTime`, `snowflakeToDateTime64`, `dateTimeToSnowflake`, `dateTime64ToSnowflake`	26.7	The `snowflakeID*` variants. Deprecated since 24.6
`hasColumnInTable` remote form (hostname/username/password args)	26.7	Only `hasColumnInTable(database, table, column)`. The remote mode allowed arbitrary outbound connections and leaked credentials into query logs
`zip` / `zipx` backup archives on object storage	26.7	Use `tar.gz`. Zip needs seeking to read its central directory, which is very slow over object storage
Naive Bayes models as XML config + `.bin` files	26.7	Recreate as dictionaries with the `NAIVE_BAYES` layout. Upside: roughly 49x less memory and 11x faster on the cited model
`kql` table function	26.5	`SET dialect = 'kusto'`
Arrow-based Parquet reader and writer	26.5	Native implementation
KQL `array_sort_asc` / `array_sort_desc`	26.6	Now `UNKNOWN_FUNCTION`
`allow_experimental_query_deduplication`	26.6	Removed entirely
Arguments to `RANK` / `DENSE_RANK`	26.5	Per SQL standard these take zero arguments. `allow_rank_dense_rank_arguments = 1` restores leniency

Also worth checking: icebergHash and icebergBucket now reject 128/256-bit integers and Decimal256 (26.6) instead of silently truncating them into colliding hashes, and nested Dynamic/Variant are rejected in min/max aggregates and minmax indexes (26.6).

Part 6: What you actually get for it

It's worth saying: this is a lot of upgrade work, and the payoff is real. 415 performance improvements across the five releases, many of them enabled by default.

Aggregation and grouping. A new adaptive parallel GROUP BY algorithm where each thread aggregates into its own cache-resident hash table until it hits a threshold, then freezes it. Bounded-heap pruning for GROUP BY ... ORDER BY ... LIMIT. Smaller hash-table cells for single-String keys. Parallelised final merge of single-level tables. Aggregations without aggregate functions now use HashSet instead of HashMap, up to 1.8x faster.

Reads. Lazy materialization for Parquet on object storage - on a 200 MB S3 file, an ORDER BY ... LIMIT 10 read 3.3 MB instead of 171 MB. That's 51x less I/O and 8x faster. read_in_order_use_virtual_row on by default cuts peak memory when reading in primary key order over tables with many parts. Dictionary-page-based row group skipping in the Parquet V3 reader.

Joins. IEJoin, a sort-based algorithm for ON clauses with two inequality comparisons - previously those ran as a CROSS JOIN with a filter, INNER only. parallel_full_sorting_merge, which shards a merge join by join-key hash across threads, benchmarked around 2.4x faster and 3.3x less memory than parallel_hash. Constant-false JOIN conditions no longer read the non-contributing side.

Per-partition processing. DISTINCT, window functions and IN (subquery) set building can now keep each partition's rows in a single stream when the partition expression is a deterministic function of the relevant columns, skipping the hash scatter entirely.

On the feature side, the ones I'd actually reach for:

Pipe operators - FROM t |> WHERE ... |> AGGREGATE ... |> ORDER BY ..., GoogleSQL-style. In a query starting with FROM, SELECT is optional and defaults to SELECT *
run_query_in_background - the server accepts the query, returns immediately, and runs it to completion regardless of what happens to the connection. Built for long INSERT SELECT, CTAS and POPULATE
CREATE HANDLER / ALTER HANDLER / DROP HANDLER - custom HTTP handlers defined in SQL, persisted locally or in Keeper, with currentHandler(), currentRequestURL() and system.handlers. This could genuinely replace some small API services
Atomic POPULATE - CREATE MATERIALIZED VIEW ... POPULATE is now locally atomic, so rows inserted through the same server during population aren't missed or duplicated. Local insert path only, and it needs a snapshot-capable source. POPULATE also works with TO now
system.user_query_log - every user sees their own query log rows without needing access to system.query_log. Useful if you're building multi-tenant tooling
create_union_system_log_tables - auto-maintained all_... tables like system.all_query_log, unioning a log table, its rotated versions, and the same table across cluster replicas
Keeper on-disk storage via a custom LSM tree, with similar performance to the in-memory storage, and a Raft topology view in the Keeper dashboard
GROUPS window frames (SQL:2011), where boundaries count peer groups rather than physical rows or value distances
New text index tokenizers - Japanese (MeCab), Chinese (jieba-style), ICU locale-aware segmentation, and splitByRegexp, which keeps tokens like C++ and C# intact
Data lake expansion - BigQuery table function and engine, Snowflake Horizon (read and write Iceberg), S3 Tables catalog with working INSERT, and Puffin format support

The upgrade plan

Here's the order I'd actually do this in.

Step 0: Audit before you touch anything

-- Dictionaries using the removed library source
SELECT name, source FROM system.dictionaries WHERE source ILIKE '%library%';

-- AggregatingMergeTree schemas that 26.7 would now reject
SELECT database, table, name, type
FROM system.columns
WHERE (database, table) IN (
    SELECT database, name FROM system.tables
    WHERE engine LIKE '%AggregatingMergeTree%'
  )
  AND is_in_sorting_key = 0
  AND type NOT LIKE '%AggregateFunction%';

-- Non-default settings you're carrying
SELECT name, value, default FROM system.settings WHERE changed;
SELECT name, value FROM system.server_settings WHERE changed;

-- Removed functions still in use
SELECT DISTINCT query FROM system.query_log
WHERE event_date >= today() - 30
  AND type = 'QueryFinish'
  AND (query ILIKE '%snowflakeToDateTime%'
       OR query ILIKE '%dateTimeToSnowflake%'
       OR query ILIKE '%array_sort_asc%'
       OR query ILIKE '%array_sort_desc%'
       OR query ILIKE '%kql(%')
LIMIT 100;

Plus a manual pass over:

/proc/cpuinfo on every node for AVX2
insert_deduplication_version in your config
Every S3 access path - tables, disks, catalogs, named collections
/etc/metrika.xml reliance
MySQL ssl_* paths and NATS nats_credential_file in named collections and dictionaries
Backup scripts using zip to object storage
resources / workload_classifiers config sections
allow_feature_tier = 2 in config
Ingestion clients writing unquoted epoch numbers into DateTime64 via JSONEachRow

Step 1: Use `compatibility` as a shock absorber

SET compatibility = '26.3';

Set this globally, upgrade the binaries, confirm the cluster is healthy, then peel it back one release at a time - 26.4, 26.5, 26.6, 26.7 - with a validation pass between each.

This restores the great majority of the ~100 default flips in one move, which turns "57 breaking changes at once" into something you can bisect. It does not cover removals, renames or type changes. Those need actual code fixes, which is what Step 0 finds.

Step 2: Pin the on-disk formats during the rollout

While a mixed-version cluster exists:

patch_parts_version = 'v1' (MergeTree setting, config or ALTER TABLE)
text_index_serialization_version = 'v1_with_codec'

Remove both once every replica is on 26.8.

Two other on-disk changes are safe by design and don't need pinning: packed_skip_index_max_bytes (defaults to 1 MiB, affects only new parts, still readable by older servers) and compute_exact_num_defaults_for_sparse_columns (the flag in serialization.json is ignored by older versions, so parts survive a downgrade).

Step 3: Watch these after the upgrade

Part counts and merge queue depth in system.parts and system.merges - the max_insert_threads effect
system.errors for UNACCEPTABLE_URL, ILLEGAL_COLUMN, UNKNOWN_ELEMENT_IN_CONFIG and BAD_ARGUMENTS, which cover most of the new rejections
Monitoring gaps where per-CPU and per-device async metrics used to be
Join memory profiles - max_bytes_ratio_before_external_join = 0.5 means spilling starts where it previously didn't
Anything that parses EXPLAIN output

Final thoughts

The pattern I'd take away from this: the changes most likely to hurt you aren't in the release you're upgrading to. AVX2, the S3 credential change, the dedup migration ordering and the EXPLAIN output format all landed in 26.6 and 26.7. If you read only the 26.8 notes - which is what most people do for an LTS upgrade - you miss all four.

The second pattern: a meaningful share of the risk sits around ClickHouse rather than inside it. Dashboards keyed on metric names, scripts parsing EXPLAIN output, schema browsers reading system.tables, tooling reading system.users. The server will start fine and those will quietly break.

None of which is an argument against upgrading. 415 performance improvements, many of them on by default, is a genuinely good deal - and 26.3 won't be the current LTS forever.

Just budget for it as a five-release jump, because that's what it is.

Verified against the upstream CHANGELOG.md, SettingsChangesHistory.cpp and the 26.8 release branch source as of 29 September 2026. If you spot something that's drifted since, let me know in the comments.

Understanding the ORC File Format: Why Is It So Fast

Mohamed Hussain S — Fri, 21 Aug 2026 10:15:32 +0000

Every article about ORC says the same thing.

"Columnar storage, built-in indexes, predicate pushdown - so it's fast."

That's not an explanation. That's a slogan.

So I wrote a small ORC parser from scratch, generated 5 million rows, and opened the file with my hands.

The question I actually wanted answered:

What happens inside an ORC file when a query asks for 2 columns out of 100?

What a Columnar File Actually Does

Strip away the marketing, and it comes down to three things.

Store each column's values together, so you can read one column without touching the others.
Encode those values in ways that only work when neighbours are similar.
Keep a small map of what's inside, so a query can skip most of the file without opening it.

Everything else - compression codecs, bloom filters, ACID support - is built around that core job.

The Setup

An orders table. Deliberately boring.

order_id     int64    sequential
customer_id  int64    250k distinct
country      string   10 distinct
product      string   2,000 distinct
quantity     int32    1-19
price        double   40k distinct
order_date   date     730 days
status       string   5 distinct

5,000,000 rows, written with PyArrow:

orc.write_table(
    tbl, "orders.orc",
    compression="zlib",
    stripe_size=8 * 1024 * 1024,      # small on purpose
    row_index_stride=10_000,
    dictionary_key_size_threshold=0.8,
)

Production ORC uses 64-256 MB stripes. My whole file is 53 MB, so a default stripe would swallow the entire dataset and there'd be nothing to skip. Shrinking it is how you see multi-stripe behaviour on a laptop.

Same data also went out as a 310 MB CSV, for scale.

ORC Is Read Backwards

The last byte of the file is a single number: the length of the PostScript.

Here are the final 26 bytes of my file:

08 c0 03 10 01 18 80 80 04 22 02 00 0c 28 97 05 30 06 82 f4 03 03 4f 52 43 19

That's a Protocol Buffers message. Decoded:

field	value	meaning
1	448	footer length
2	1	codec = ZLIB
3	65536	compression block size
5	663	metadata length

So a query engine does three reads: last byte, PostScript, then footer + metadata. A few hundred bytes on a 53 MB file.

After that it knows the schema, the row count, every stripe's byte offset, and min/max stats for every column.

It hasn't touched a single row of data yet.

Note the ordering: the codec lives in the PostScript, and the PostScript is never compressed. It can't be - you'd need the codec to decompress the thing that tells you the codec.

Stripes

A stripe is a self-contained slab of rows. Its own index, its own data, its own footer.

 #       offset     index         data  footer       rows
 0            3     8,412    8,544,070     159    687,104
 1    8,552,644     8,487    8,556,939     158    688,128
 ...
 7    59,917,580     2,796    2,315,836    152    186,176

8,412 bytes of index for 8.5 MB of data. 0.1% overhead for the ability to skip.

That ratio is the entire trade.

Because each stripe carries its own footer, you can hand a Spark executor one byte range and it can decode that stripe with zero knowledge of the rest of the file. That's the basis of parallel scans.

Where a Column Physically Lives

Inside a stripe, a column isn't "a column." It's a set of streams.

col name       stream                   bytes
  3 country    DICTIONARY_DATA             23
  3 country    LENGTH                       5
  3 country    DATA                   310,404
  6 price      DATA                 2,374,052

All streams for one column sit contiguously. "Read country from stripe 3" is one range read, not 687,104 scattered seeks.

There's also a PRESENT stream for nulls - and ORC omits it entirely when a column has none. My columns are all non-null, so null tracking cost zero bytes. A row format pays for nullability on every row whether you use it or not.

Encodings Do More Than Compression Does

People credit ORC's size to compression. Mostly wrong.

Bytes per column, whole file:

column	bytes	bytes/row
order_id	11,541	0.002
price	17,022,257	3.404
customer_id	13,293,357	2.659

Five million 64-bit integers in 11 KB.

That's ORC's RLE v2 picking its DELTA sub-encoding: for a monotonic sequence it stores a base, a delta width, and a run length. zlib never saw 40 MB of integers, because the encoder never produced them.

Same effect on sorted dates:

order_date, 5M values:
  random order : 7,390,184 bytes
  sorted       :     4,898 bytes    <- 1,500x smaller

The PyArrow gotcha

dictionary_key_size_threshold defaults to 0.0, which disables dictionary encoding entirely.

	DIRECT (default)	DICTIONARY
status	796,196 B/stripe	271,047 B/stripe
whole file	62.24 MB	53.32 MB

14% of the file, from one keyword argument.

Compression Sits On Top

Only after encoding does the codec run - in independent 64 KB chunks, each with a 3-byte header. Bit 0 says "stored raw", because if compressing a chunk made it bigger, ORC just doesn't.

Chunking is what makes seeking possible. One big compressed blob would mean inflating 8 MB to read the last value.

codec	file MB	vs CSV	write s	full scan s
uncompressed	86.14	3.6x	2.15	1.73
snappy	69.49	4.5x	2.35	0.51
zlib	53.32	5.8x	4.45	1.02
zstd	51.72	6.0x	2.34	0.53

zstd wins on all three axes. zlib is only still the default because ORC is old.

Also worth noting: uncompressed ORC is still 3.6x smaller than CSV. That gap is pure layout and encoding.

I Actually Measured the Skipping

ORC keeps min/max statistics at three zoom levels: file (448 B), stripe (663 B), and row group of 10,000 rows (8.4 KB per stripe).

Query:

SELECT price FROM orders
WHERE order_date BETWEEN '2024-06-01' AND '2024-06-30'

One month out of 24. About 4% of rows. Same query, same data, two files - one in insertion order, one sorted by order_date.

Unsorted:

stripe 0   2023-01-01 .. 2024-12-30   READ
stripe 1   2023-01-01 .. 2024-12-30   READ
...
stripes to read: 8/8
bytes read: 24,430,256

Every stripe spans the whole date range, so every min/max says "maybe". Zero pruning.

Sorted by order_date:

stripe 0   2023-01-01 .. 2023-04-10   SKIP
stripe 4   2024-02-02 .. 2024-05-11   SKIP
stripe 5   2024-05-11 .. 2024-08-19   READ
stripe 6   2024-08-19 .. 2024-11-26   SKIP
...
stripes to read: 1/8
bytes read: 2,335,435          <- 13.7% of the unsorted read

And inside that one surviving stripe, the row-group index eliminated another 68%: 22 of 69 row groups matched. The engine ends up decoding roughly 220,000 of 5,000,000 rows.

The uncomfortable conclusion:

ORC's statistics are a summary, not an index. Their usefulness is entirely determined by how you wrote the data.

Sorting by your dominant filter column isn't a tuning detail. It's the difference between skipping 87% of the file and skipping nothing.

A predicate stats can't help with

country = 'IN'
stripe ranges: ('AU','US'), ('AU','US'), ('AU','US'), ...
stripes that could contain 'IN': 8/8

Scattered values mean every range straddles the target. That's the gap bloom filters fill - they cost 1.4% of file size on product, and are a waste on country.

Does Column Projection Actually Pay Off?

I wrapped the file handle in a counter so I could see every byte the reader actually pulled off disk.

8 columns, 53 MB:

query	bytes read	% of file
`SELECT *`	53,279,130	99.93%
`SELECT country, price`	19,265,356	36.13%
`SELECT order_id`	29,356	0.06%

100 columns, 1M rows, 153 MB - the original question:

query	bytes read	% of file	time
`SELECT *`	153,188,271	99.92%	3.03s
`SELECT order_id, price`	3,424,697	2.23%	0.04s

2 columns out of 100 = 2.23% of the bytes. Not "less I/O" in a hand-wavy sense. 45x less, measured.

Reading 2 columns from the 310 MB CSV still requires pulling all 310 MB off disk and parsing every field to find the commas.

How Big Should a Stripe Be?

Same sorted data, same June predicate:

stripe target	stripes	rows scanned	% rows
2 MB	29	350,208	7.0%
8 MB	8	680,960	13.6%
32 MB	2	2,294,592	45.9%
64 MB	1	5,000,000	100.0%

Halve the stripe, halve the rows you're forced to scan - until pruning bottoms out at the real selectivity. Total file size moved 0.3% across that whole range, because more stripes cost more metadata, not more data.

So why not 2 MB stripes everywhere? Because a stripe is also the unit of parallelism, and on object storage each one is a separate GET. The row-group index already gives you 10,000-row granularity inside a big stripe - that's the layer meant to do fine pruning. Keep stripes big.

What I Couldn't Test

A real cluster. Everything here is one machine, local NVMe. On S3, request count matters more than byte count, and my numbers say nothing about that.
lz4 in this build. It produced 86.02 MB - essentially identical to uncompressed. Either the PyArrow build isn't wiring it up or something else is wrong. I'm reporting it rather than quietly dropping the row, but don't trust that number.
Bloom filter effectiveness. I measured what they cost (1.4% of file size), not what they save, because PyArrow's reader doesn't expose filter pushdown for ORC. You'd need Hive or Trino for that.

Where This Doesn't Help

Columnar layout is a bet, and it loses on some workloads.

Not for OLTP. Inserting one row means rewriting a stripe. Fetching one full record means touching every column's streams separately.
Small files kill it. All this metadata machinery amortizes over large files. A thousand 2 MB ORC files is the classic way to make a fast format slow.

One Thing a Different Columnar Format Does Differently

ORC isn't the only format built on these ideas - Parquet uses the same stripe/row-group/statistics playbook. The clearest place they diverge is encoding scope.

Same data, same codec, same row grouping:

order_id (sequential)    ORC 7.8 KB    vs   Parquet 7.2 MB     (912x)
price (40k distinct)     ORC 16.3 MB   vs   Parquet 10.2 MB    (0.62x)

ORC's dictionary encoding applies only to string columns - a DOUBLE always goes out DIRECT, full width, and only the compressor gets a shot at it. Parquet dictionary-encodes any physical type, so a double with modest cardinality compresses to narrow indices where ORC can't.

That's a real, measured trade-off, not a verdict. It's also its own rabbit hole - I measured it properly in a follow-up post.

So, Why Is It Fast?

Not one reason. A chain, where each link multiplies the next.

columnar layout      -> read 2 columns, not 100          (45x fewer bytes)
  x encodings        -> delta/dictionary before codec    (up to 3,467x on one column)
  x compression      -> chunked, seekable                (~2x on top)
  x statistics       -> skip stripes that can't match    (8x when sorted)
  x row-group index  -> skip 10k-row blocks inside those (3x more)
  ---------------------------------------------------------------
  = read 2.2% of the file, decode 4% of the rows you touched

Every layer is doing the same thing from a different angle: making it possible to not read something.

ORC isn't fast because it's clever with the bytes it reads. It's fast because it spends 0.1% of the file on a map detailed enough to avoid reading almost all of them.

And the part that's actually in your control: sorting by your filter column moved my query from 24 MB to 2.3 MB. No format setting in this entire post came close to that.

Why My ClickHouse Server Ran Out of Disk (and It Wasn't the Data I Expected)

Mohamed Hussain S — Wed, 22 Jul 2026 10:57:03 +0000

A deployment failed with an error that had nothing to do with ClickHouse on the surface.

usermod: /etc/passwd... No space left on device

At first, it looked like a Linux problem. Maybe a Docker problem.

It wasn't either.

The root filesystem was completely full.

Checking Disk Usage

The first step was the obvious one.

df -h

/dev/sda1   96G   100% Used

100% used. No surprise there, given the error. But that's a symptom, not a cause.

Ruling Out Inode Exhaustion

Before assuming it was actual data filling the disk, I checked whether it was inode exhaustion instead - a different problem that looks similar from the outside.

df -i

3% inode usage

Not an inode problem. Something was genuinely consuming space.

Finding What Was Actually Consuming Disk

Instead of guessing, I started at the root and walked down.

sudo du -sh /* | sort -hr

/var was consuming almost everything.

sudo du -sh /var/* | sort -hr

/var/lib.

sudo du -sh /var/lib/* | sort -hr

/var/lib/clickhouse.

sudo du -sh /var/lib/clickhouse/* | sort -hr

store.

Five commands, one directory deeper each time. No assumptions, just narrowing the search space until there was nowhere left to hide.

Finding Which Table Actually Owned the Space

store is where ClickHouse keeps its actual data parts, so this didn't tell me what was large, only where. I connected to ClickHouse to ask it directly.

SELECT
    database,
    table,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
GROUP BY database, table
ORDER BY sum(bytes_on_disk) DESC;

system.text_log   76 GiB

Not application data. Not the tables I actually cared about.

system.text_log - ClickHouse's own internal server log, stored as a table - had quietly grown to 76 gigabytes.

Nobody had configured a retention policy on it. It had just been logging, forever, since the server started.

Deciding Not to Preserve It

This was a development server, so I didn't need to preserve anything. Stop the service, clear the data, move on.

sudo systemctl stop clickhouse-server

sudo rm -rf /var/lib/clickhouse/*

Checked the disk.

df -h

Still full.

sudo du -sh /var/lib/clickhouse

87G

The wildcard delete hadn't actually cleared everything. I listed the directory directly instead of trusting the glob.

sudo ls -lah /var/lib/clickhouse
sudo bash -c 'du -sh /var/lib/clickhouse/* | sort -hr'

87G   store

store was still there. I never fully pinned down why the first wildcard delete skipped it - possibly the server hadn't finished releasing file handles the moment systemctl stop returned. Either way, trusting the first command's exit code without re-checking the actual directory size cost me a step.

Removed it directly.

sudo rm -rf /var/lib/clickhouse/store

df -h

84G free

Problem solved, for now. But "for now" wasn't good enough, because the same 96GB disk was still the only place ClickHouse could write to.

The Real Fix: Get ClickHouse Off the Root Disk Entirely

The server had a second, 256GB disk. Unmounted. Unused. The whole time.

lsblk -f

sda   root filesystem
sdb   ext4   UUID=b16098f7-bd6a-496d-a2f5-86ad19913c7c

Mounted it temporarily to check what was on it.

sudo mkdir -p /mnt/chdisk
sudo mount /dev/sdb /mnt/chdisk
sudo ls -la /mnt/chdisk

lost+found

Empty. Unmounted it again and made the arrangement permanent instead of temporary.

sudo umount /mnt/chdisk

Configuring the Permanent Mount

Edited /etc/fstab:

UUID=b16098f7-bd6a-496d-a2f5-86ad19913c7c  /var/lib/clickhouse  ext4  defaults  0  2

Moved the old (now-empty) directory out of the way, created a fresh mount point, and mounted it.

sudo mv /var/lib/clickhouse /var/lib/clickhouse.old
sudo mkdir /var/lib/clickhouse
sudo mount -a
sudo systemctl daemon-reload

Verified the mount actually took:

findmnt /var/lib/clickhouse

TARGET                SOURCE
/var/lib/clickhouse   /dev/sdb

Every read and write to /var/lib/clickhouse now transparently goes to /dev/sdb instead of the root disk. ClickHouse doesn't need to know or care - as far as its own config is concerned, it's still just writing to /var/lib/clickhouse.

Fixed ownership and permissions, since a fresh mount point doesn't inherit them:

sudo chown -R clickhouse:clickhouse /var/lib/clickhouse
sudo chmod 750 /var/lib/clickhouse

Restarted and confirmed:

df -h

/dev/sda1   84G free
/dev/sdb    mounted on /var/lib/clickhouse

What I'm Changing Going Forward

Clearing text_log fixed the immediate problem. It didn't fix the actual cause - nothing stops it from growing back to 76GB again.

The real fix is giving ClickHouse's system logs a retention policy, the same way you'd expect any log to expire eventually.

<clickhouse>
    <text_log>
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl>
    </text_log>

    <trace_log>
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl>
    </trace_log>

    <metric_log>
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl>
    </metric_log>

    <part_log>
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl>
    </part_log>

    <query_log>
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl>
    </query_log>

    <query_thread_log>
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl>
    </query_thread_log>

    <processors_profile_log>
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl>
    </processors_profile_log>

    <asynchronous_metric_log>
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl>
    </asynchronous_metric_log>
</clickhouse>

Dropped in as /etc/clickhouse-server/config.d/system_log_retention.xml, this keeps every system log table capped at 7 days of history, deleted automatically, no manual cleanup required again.

Before and After

The Bigger Lesson

I went in assuming a full disk meant the actual data had grown too fast.

It hadn't. The application data was fine. What had grown out of control was ClickHouse's own internal logging - a system table nobody had put a limit on, quietly consuming more space than the data it was supposed to be observing.

And underneath that, a second problem had been sitting there the whole time: a 256GB disk that was never mounted, on a server that only ever had 96GB to work with.

Neither problem was visible from df -h alone. Getting to the real cause meant walking the filesystem one directory at a time, then asking ClickHouse directly which table actually owned the space.

Isolating database storage onto its own disk, separate from the OS, isn't just a performance habit. It's what stops a logging table from being able to take your entire root filesystem down with it.

Can ClickHouse Replace a Vector Database?

Mohamed Hussain S — Tue, 21 Jul 2026 12:07:17 +0000

Vector databases like Pinecone, Weaviate, and Milvus exist for one job: store embeddings and find the nearest ones to a query vector, fast.

ClickHouse wasn't built for that.

It was built for analytics - scanning huge tables of structured data quickly.

But ClickHouse can also store vectors and search them, using plain SQL.

So the real question isn't "does ClickHouse support vector search."

It's "is that support good enough to skip a dedicated vector database."

What a Vector Database Actually Does

Strip away the marketing, and it comes down to three things.

Store embeddings (arrays of floats).
Index them so similarity search doesn't require scanning everything.
Return the nearest neighbors to a query vector.

Everything else - hybrid search, metadata filtering, embedding generation - is built around that core job.

Storing Vectors in ClickHouse

Vectors are just arrays.

CREATE TABLE articles (
    id UInt32,
    title String,
    embedding Array(Float32)
) ENGINE = MergeTree ORDER BY id

No extension needed. No plugin. This is a normal column type.

Brute Force Search

The simplest way to find similar vectors is a distance function, ordered and limited.

SELECT title, L2Distance(embedding, {target_vector}) AS score
FROM articles
ORDER BY score ASC
LIMIT 5

This works correctly out of the box, and gives exact results.

The catch: every query scans every row's vector. Fine at a few thousand rows, painfully slow at a few million.

This is exactly the problem specialized vector databases were built to avoid.

I Actually Benchmarked This

Instead of guessing, I ran the brute-force query above against real ClickHouse (via chdb, the embeddable version of the same engine), on synthetic normalized embeddings.

How search time scales with row count (128-dim vectors):

Rows	Avg query time
50,000	0.025s
100,000	0.046s
250,000	0.155s
500,000	0.220s
1,000,000	0.413s

It's close to linear, which is exactly what you'd expect from brute force: no shortcuts, every row gets a distance calculation.

How search time scales with vector dimension (200,000 rows):

Dimension	Avg query time
128	0.086s
384	0.251s
768	0.449s

Dimension hurts roughly as much as row count. A 768-dim embedding (common for larger sentence-transformer models) costs about 5x what a 128-dim one does, at the same row count. If you're using something like OpenAI's 1536-dim embeddings, expect that cost to roughly double again - I couldn't get a clean number at 1536 dims in my test environment (it ran out of memory before finishing), but the trend makes the direction obvious.

Does SQL-native filtering actually help? I tested WHERE category = 'engineering' against an unfiltered query, on the same 500k-row table, filtering out about 75% of rows:

Query	Avg time
Unfiltered	0.245s
Filtered (~25% of rows match)	0.225s

Honestly, smaller improvement than I expected - about 8%, not a proportional 75% drop. The vector column still gets read before the filter meaningfully prunes work, because the table isn't ordered by category. If you want filtering to actually pay off, you'd need to design your ORDER BY / partitioning around your common filter columns, the same way you'd tune any ClickHouse table - vectors don't get a free pass on data modeling.

What I couldn't test: ClickHouse's HNSW-based vector_similarity index. The chdb build I used doesn't ship with it compiled in, so I can't give you a real indexed-vs-brute-force number here - only the brute-force baseline above. If you want that comparison, you'll need to run it against a real ClickHouse server (Docker or ClickHouse Cloud) with allow_experimental_vector_similarity_index enabled. Better to tell you that than make up a "10x faster" number I didn't actually measure.

Indexed (Approximate) Search

ClickHouse supports an HNSW-based vector similarity index - the same graph-based approach most dedicated vector databases use internally.

The idea: once an index is built on the vector column, queries should run faster than brute force, at the cost of exact accuracy - trading a bit of recall for a lot of speed. That tradeoff is the entire premise behind nearest-neighbor search in every vector database, not just ClickHouse. I just can't hand you a verified number for it from this test run - see above.

What ClickHouse Adds That Vector Databases Usually Don't

The interesting part isn't the distance calculation. It's what you can do around it in the same query.

SELECT title, category, L2Distance(embedding, {target_vector}) AS score
FROM articles
WHERE category = 'engineering'
  AND published_at > now() - INTERVAL 30 DAY
ORDER BY score ASC
LIMIT 5

Filtering by metadata, joining against other tables, aggregating - all native SQL, all in the same pass as the similarity search.

In many dedicated vector databases, metadata filtering is a secondary feature bolted onto the vector index. In ClickHouse, it's the same engine that's been handling filters, joins, and aggregations for years - the advantage is in query flexibility and not having a second system to keep in sync, not necessarily raw speed (my benchmark below shows filtering alone isn't a big speed win unless your table is actually modeled around it).

Where ClickHouse Falls Short

It isn't a drop-in replacement for every vector database use case.

It's not built for high-frequency, single-row upserts the way an OLTP-oriented vector store is.
It doesn't generate embeddings for you - you bring your own vectors, computed elsewhere.
Its ANN indexing is newer than systems that have done nothing but similarity search for years, so tooling and edge-case handling are less mature.

If the workload is millions of small writes per second with constant re-indexing, a purpose-built vector database is still the safer choice.

The Actual Answer

ClickHouse is an analytical database that added vector search.

A vector database is a system built only around vector search.

If vectors are one part of a larger analytical dataset - filtered, joined, aggregated alongside structured columns - ClickHouse can genuinely replace a dedicated vector database and remove a piece of infrastructure.

If vectors are the whole workload, and writes are constant and high-frequency, a dedicated vector database still earns its place.

There isn't a single right answer. There's a right answer for a given workload.

Why Localhost Worked but the Application Couldn't Connect

Mohamed Hussain S — Wed, 08 Jul 2026 14:48:10 +0000

One of the most frustrating debugging sessions I've had wasn't caused by a broken application.

It wasn't caused by a firewall.

It wasn't even caused by the network.

The application worked perfectly.

At least, that's what I thought.

From the server itself, everything looked healthy.

curl http://127.0.0.1:8080

The application responded immediately.

The API worked.

Health checks passed.

Logs looked normal.

Yet every request coming from outside the server failed.

At first, it felt like the application was refusing connections.

The real problem was much simpler.

I misunderstood what localhost actually meant.

The Initial Assumption

When an application starts successfully, it's easy to assume it's ready for everyone to access.

That was my assumption too.

The service was running.

The process was alive.

The port existed.

So naturally, I expected clients on the network to connect without any issues.

Instead, every external request timed out.

Everything Looked Healthy

The first thing I checked was whether the application was actually running.

ps aux | grep sidecar

The process was there.

Next, I checked whether it was listening.

ss -tulpn

Again, everything seemed fine.

The application was listening on port 8080.

So why couldn't another machine connect?

The Important Detail I Missed

The answer was hidden in a single column of the output.

Instead of listening on:

0.0.0.0:8080

the application was listening on:

127.0.0.1:8080

At first glance, they both look like valid addresses.

Operationally, they mean completely different things.

What Localhost Really Means

127.0.0.1 is the loopback interface.

Traffic sent to this address never leaves the machine.

It doesn't travel through the network.

It never reaches another computer.

Only processes running on the same host can connect to it.

So this works:

curl http://127.0.0.1:8080

But from another machine:

curl http://server-ip:8080

the connection simply fails.

Nothing was wrong with the application.

It was doing exactly what it had been told to do.

Why 0.0.0.0 Is Different

When an application binds to:

0.0.0.0

it listens on all available network interfaces.

That means the service becomes reachable through:

the server's IP address
internal network interfaces
external interfaces (assuming routing and firewall rules allow it)

The application hasn't changed.

Only the interface it listens on has.

That tiny configuration difference completely changes who can communicate with it.

My Wrong Mental Model

This debugging session taught me an important lesson.

I had been thinking:

If the application starts successfully, networking must also be working.

Those are two completely different things.

An application can:

start correctly
bind successfully
respond to localhost

and still be completely unreachable from anywhere else.

Application health and network accessibility are separate problems.

Debugging It Systematically

Instead of assuming the application was broken, I started validating each layer independently.

First, verify the process exists.

ps aux | grep sidecar

Next, verify what interface it's actually listening on.

ss -tulpn

Then test locally.

curl http://127.0.0.1:8080

Finally, test remotely.

curl http://server-ip:8080

That simple progression immediately tells you where communication stops.

Instead of debugging everything at once, you're narrowing the search space one layer at a time.

Why This Happens So Often

This isn't unique to one application.

Many frameworks default to binding only to localhost during development.

That's perfectly reasonable.

It prevents accidentally exposing services to an entire network.

The problem appears when that same configuration moves into production.

The application still starts.

Health checks still succeed.

Logs still look clean.

Only remote clients fail.

Without checking the listening interface, it's easy to spend hours investigating the wrong thing.

The Bigger Lesson

Looking back, nothing in the deployment was actually broken.

The operating system behaved exactly as expected.

The application behaved exactly as expected.

The network behaved exactly as expected.

The only thing that was wrong was my assumption.

I treated "the application is running" as proof that "the application is reachable."

Those aren't the same statement.

One describes a running process.

The other describes network accessibility.

Confusing the two can send you down completely the wrong debugging path.

Final Thoughts

Production debugging often isn't about finding a broken component.

It's about understanding how different layers interact.

An application can be healthy while remaining inaccessible.

A network can be perfectly functional while a service listens on the wrong interface.

The more I debug distributed systems, the more I realize that successful deployments depend less on memorizing commands and more on building accurate mental models of how systems communicate.

This experience reinforced one lesson I'll carry into every future deployment:

Just because localhost works doesn't mean your application is actually reachable.

Understanding ACME: What I Learned While Debugging HTTPS Certificate Failures

Mohamed Hussain S — Tue, 07 Jul 2026 14:26:48 +0000

Deploying an application with HTTPS feels straightforward.

Point your domain to the server, configure a reverse proxy like Caddy, and let Let's Encrypt automatically issue a certificate.

That was exactly what I expected.

Instead, certificate issuance kept failing, even though everything looked correct.

At first, I assumed something was wrong with Caddy.

It wasn't.

The real lesson wasn't about Caddy at all-it was about understanding how ACME actually validates a domain.

The Problem

I had a server running behind Caddy, with DNS records pointing to the correct IP address.

The application was reachable, the firewall allowed HTTP and HTTPS traffic, and Caddy was listening on the expected ports.

Yet every attempt to obtain a certificate failed.

Initially, I started checking the obvious things:

Was DNS configured correctly?
Were ports 80 and 443 open?
Was Caddy configured correctly?
Was the firewall blocking traffic?

Everything appeared to be fine.

So why wasn't Let's Encrypt issuing a certificate?

My Wrong Assumption

At first, I thought HTTPS certificate issuance was simply a conversation between my server and Let's Encrypt.

If my server could reach Let's Encrypt, surely the certificate should be issued.

That assumption was completely wrong.

The important connection isn't just your server reaching Let's Encrypt.

It's Let's Encrypt successfully reaching your server.

That single realization changed how I approached the entire problem.

Understanding ACME

Let's Encrypt uses the ACME (Automatic Certificate Management Environment) protocol to verify that you actually own the domain you're requesting a certificate for.

The process is roughly:

Your ACME client requests a certificate.
Let's Encrypt sends a validation challenge.
Your server proves ownership by responding correctly.
Only after successful validation is the certificate issued.

In other words, certificate issuance isn't automatic trust.

It's a verification process.

Looking at the Logs

Once I stopped guessing and started reading the logs, things became much clearer.

Instead of generic "certificate failed" messages, the ACME logs explained which validation step was failing.

That immediately narrowed the investigation.

Rather than assuming TLS was broken, I started asking more useful questions:

Can Let's Encrypt reach my server?
Is the expected challenge actually being served?
Is DNS resolving to the correct address?
Is traffic arriving where I think it is?

Those questions were far more valuable than repeatedly changing configuration files.

The Bigger Lesson

One thing I learned during this debugging session is that HTTPS failures are often not TLS failures at all.

They can be:

DNS issues
Routing problems
Network interface problems
Reverse proxy configuration mistakes
Port forwarding issues

The certificate request simply exposes those underlying networking problems.

In my case, the root cause wasn't ACME itself-it was a networking issue preventing successful validation.

Understanding how ACME worked helped me stop treating the symptoms and start investigating the actual cause.

A Better Way to Debug ACME Failures

Instead of randomly changing configuration files, I found it much more effective to work through a simple checklist.

First, verify that DNS resolves to the expected IP address.

Next, confirm that ports 80 and 443 are reachable from outside the network.

Then inspect the ACME client logs to determine which validation step is failing.

Finally, validate that your server is actually serving the expected challenge response.

Each step eliminates an entire class of possible problems.

What Changed My Thinking

Before this experience, I viewed HTTPS certificate issuance as a feature provided by the reverse proxy.

Now I see it differently.

ACME is fundamentally a validation protocol.

TLS certificates are simply the end result of successfully proving domain ownership.

Once I understood that, the debugging process became much more systematic.

Instead of asking:

"Why won't Let's Encrypt issue my certificate?"

I started asking:

"What is the ACME server trying to verify, and why is that verification failing?"

That shift in thinking made all the difference.

Final Thoughts

Debugging production systems often comes down to replacing assumptions with understanding.

At first, I thought the problem was Caddy.

Then I suspected DNS.

Then I questioned my firewall.

In reality, none of those were the underlying issue.

The real lesson was understanding how ACME validates a deployment before a certificate is ever issued.

Once that mental model clicked, the logs became easier to interpret, the investigation became more focused, and the actual root cause was much easier to identify.

Sometimes the hardest part of debugging isn't fixing the system.

It's understanding how the system is supposed to work in the first place.

DEV Community: Mohamed Hussain S

Disk Is Full, but Your Postgres Tables Are Small (The Replication Slot Trap)

What a Replication Slot Does

Step 1: A Baseline

Step 2: Create a Slot Nobody Reads

Step 3: Generate Some Writes

Step 4: Make the Tables Small Again

How to Find It

The Fix

How to Stop It Happening Again

Clean Up Your Test

Takeaways

I Added an Index and Postgres Still Does a Seq Scan (Here's Why)

The Setup

What to Look At in a Plan

Case 1: The Index Exists, and Postgres Is Right to Skip It

Case 2: The Same Index, a Selective Query

Case 3: The Index Is Fine, the Query Isn't

Case 4: A Partial Index for the Rare Value

What About Indexing created_at Instead?

Case 5: Does Lowering random_page_cost Change Anything?

A Checklist for the Next Seq Scan You See

The Takeaways

Why Your Postgres Table Keeps Growing Even Though You're Deleting Rows

The Short Version

Why Postgres Works This Way

Let's Reproduce It

Step 1: Watch a row move

Step 2: Update everything, a few times

What VACUUM Actually Does

Getting the space back

The Gotcha: Long-Running Transactions

It's Not Always a psql Session

How to Check Your Own Database

What I Took Away From This

What Actually Happens When You Start a Goroutine?

The Setup

Experiment 1: How Many Threads Does a Goroutine Cost?

Meet G, M and P

Experiment 2: Where Does a New Goroutine Wait?

Experiment 3: Who Decides When a Goroutine Stops?

Experiment 4: What Happens When a Goroutine Blocks?

Experiment 5: What Does GOMAXPROCS Control?

What I Couldn't Test

Where This Falls Short

The Actual Answer

When Should You Actually Worry About a Growing Replication Queue?

Quick Refresher

Rule #1: A Snapshot Means Almost Nothing

Rule #2: Queue Size and Replication Lag Are Not the Same Metric

Rule #3: num_tries Tells You If a Task Is Actually Failing

Rule #4: last_exception Is the Real Diagnostic

Rule #5: Don't Restart Blind

The Checklist

Final Takeaway

Understanding the Replication Queue in ClickHouse

A Queue Belongs to a Replica, Not to the Table

So Where Do These Tasks Come From?

Where You Actually See This: system.replicas

The Detail View: system.replication_queue

Two Tables, Two Different Questions

Queue Size Is Not the Same as Replication Lag

The Mental Model, Put Simply

What This Sets You Up For

ClickHouse 26.8 LTS: 57 Breaking Changes Since 26.3

Where 26.8 stands

The scale of what you're crossing

Part 1: Three things that can stop the upgrade dead

1. The x86 build now requires AVX2 (26.6)

2. insert_deduplication_version has a mandatory migration order (26.6 → 26.7)

3. S3 stops using the server's own credentials for user queries (26.7)

Part 2: Changes that alter results without throwing an error

Loose date strings now parse (26.5)

CAST preserves the source time zone (26.5)

FINAL on a JOIN stopped leaking to other tables (26.6)

Unquoted JSON numbers become timestamps (26.8)

toDate32 reinterprets a whole numeric range (26.8)

Array function semantics fixed (26.8)

Trivial views over Distributed tables push down (26.8)

Response format header overrides FORMAT (26.8)

What About Indexing `created_at` Instead?

Case 5: Does Lowering `random_page_cost` Change Anything?

Experiment 5: What Does `GOMAXPROCS` Control?

Rule #3: `num_tries` Tells You If a Task Is Actually Failing

Rule #4: `last_exception` Is the Real Diagnostic

Where You Actually See This: `system.replicas`

The Detail View: `system.replication_queue`

2. `insert_deduplication_version` has a mandatory migration order (26.6 → 26.7)

`CAST` preserves the source time zone (26.5)

`FINAL` on a JOIN stopped leaking to other tables (26.6)

`toDate32` reinterprets a whole numeric range (26.8)

Trivial views over `Distributed` tables push down (26.8)

Response format header overrides `FORMAT` (26.8)

`max_insert_threads` now defaults to auto (26.8)

`REPLACE PARTITION` from an empty source is now rejected (26.6)

`AggregatingMergeTree` validates schemas at creation (26.7)

`EXPLAIN PLAN` output format changed (26.7)

`EXPLAIN SYNTAX` returns one row (26.8)

`allow_feature_tier` numbers shifted (26.8)

Step 1: Use `compatibility` as a shock absorber