Forem: Mohamed Hussain S

PostgreSQL Restore Failures: It Wasn’t pgBackRest, It Was My Recovery Logic

Mohamed Hussain S — Wed, 06 May 2026 12:24:28 +0000

I was building and testing a PostgreSQL backup and restore workflow using pgBackRest.

The idea was simple:

take backups
restore them automatically
validate the database
make recovery predictable

Instead, I ended up repeatedly breaking PostgreSQL recovery itself.

At one point, PostgreSQL refused to start entirely, the application depending on it failed to start, and I started seeing errors like:

invalid checkpoint record
could not locate a valid checkpoint record at 0/DEAD

Later, I also hit timeline mismatch errors like:

ERROR: [058]: target timeline 3 forked from backup timeline 2

At first, I thought:

pgBackRest restores were corrupting PostgreSQL.

That assumption turned out to be completely wrong.

The real problem was the way I was handling recovery.

What I Was Building

I was testing a PostgreSQL backup/restore flow locally after repeated restore failures elsewhere.

To isolate the issue properly, I moved PostgreSQL onto my local machine and started testing the restore logic independently through API-triggered workflows.

The restore flow looked roughly like this:

Download backup repo
Stop PostgreSQL
Restore backup
Start PostgreSQL
Validate database

Sounds straightforward.

It wasn't.

The First Major Failure

After a restore attempt, PostgreSQL refused to start.

The logs looked like this:

LOG: database system was interrupted
LOG: invalid checkpoint record
PANIC: could not locate a valid checkpoint record at 0/DEAD

At that point:

PostgreSQL was down
the application couldn't start
authentication-related functionality stopped working
and repeated restore attempts made things even worse

What confused me initially was this:

The restore itself appeared to complete.

But PostgreSQL would immediately enter recovery problems afterward.

My Wrong Assumption

This was the real issue.

Every time recovery failed, I kept seeing files like:

backup_label
recovery.signal
standby.signal

So I assumed they were leftover artifacts from failed restores.

My restore automation started aggressively cleaning them up.

Something like this:

rm -f recovery.signal standby.signal backup_label

I genuinely believed this was helping PostgreSQL start cleanly.

In reality:

I was deleting the exact recovery metadata PostgreSQL needed.

That misunderstanding caused almost every major issue afterward.

What PostgreSQL Was Actually Trying To Do

This was the turning point.

pgBackRest wasn't randomly writing junk files into the data directory.

Those files exist for a reason.

During restore:

backup_label tells PostgreSQL where recovery should begin
recovery.signal tells PostgreSQL to enter recovery mode
WAL replay reconstructs a consistent database state

PostgreSQL was actually trying to perform a valid recovery process.

My automation kept interrupting or invalidating it.

Once I understood that, the entire problem started making sense.

The Recovery Loop Problem

Because my cleanup logic removed recovery metadata prematurely, PostgreSQL ended up in inconsistent states repeatedly.

Sometimes it would:

enter recovery mode
fail WAL replay
lose checkpoint continuity
refuse startup entirely

Other times it would partially start, but remain stuck in recovery mode.

That led to additional logic being added just to stabilize startup behavior.

For example:

SELECT pg_is_in_recovery();

and when required:

SELECT pg_promote();

The goal wasn't to "force PostgreSQL to work".

The goal was:

let PostgreSQL finish recovery properly, then promote only when necessary.

That distinction mattered a lot.

The Timeline Mismatch Error

At one stage, I also hit this:

ERROR: [058]: target timeline 3 forked from backup timeline 2

This one was especially confusing at first.

The issue was not just corrupted startup state anymore.

Now PostgreSQL was rejecting WAL history itself.

This happened because earlier restore attempts had already created inconsistent recovery timelines.

I had essentially created multiple broken recovery histories while repeatedly testing and modifying the restore process.

That was another important lesson:

PostgreSQL backups are not just data files.
They are tightly connected to WAL history and recovery timelines.

At this point, I realized I was no longer debugging a simple restore failure. I was debugging recovery history itself.

The Real Problem In My Restore Flow

Initially, my restore logic tried to "fix" PostgreSQL after restore.

That approach was fundamentally flawed.

The older flow looked roughly like this:

Old Approach	Problem
Delta restore	Mixed old/new recovery state
Delete `backup_label`	Broke recovery metadata
Delete `recovery.signal`	Interrupted recovery
Force archive changes	Caused WAL continuity issues
Hope PostgreSQL starts	No validation or recovery awareness

I was treating recovery artifacts like corruption.

They weren't corruption.

They were part of PostgreSQL recovery itself.

The Change That Finally Fixed It

The biggest realization was this:

Stop fighting PostgreSQL recovery.

Instead of trying to manually "clean up" PostgreSQL after restore, I changed the restore flow completely.

The corrected restore flow became:

Stop PostgreSQL cleanly
Completely empty the data directory
Run pgBackRest restore properly
Let PostgreSQL recover normally
Wait for readiness
Promote only if recovery mode persists
Validate using pgBackRest check

The critical change was this:

self._run_pgbackrest("restore", "--type=immediate")

And equally important:

self._empty_directory(self.pg_data_dir)

Instead of attempting partial or delta-style recovery cleanup, the restore process now starts from a completely clean data directory.

That eliminated a huge amount of inconsistent state.

Why `--type=immediate` Helped

This turned out to be extremely important.

--type=immediate tells pgBackRest:

restore to the latest immediately consistent point available.

That meant:

PostgreSQL could perform proper WAL-based recovery
recovery metadata stayed intact
WAL replay remained valid
timeline handling became predictable

Most importantly:

PostgreSQL itself was finally allowed to control recovery correctly.

The Mistake That Increased the Blast Radius

One thing I learned the hard way:

Never test restore automation against a database actively used by an application.

Even though this was a testing workflow, the PostgreSQL instance was still tied to application startup behavior.

So whenever PostgreSQL failed:

application startup failed too
user-related functionality broke
debugging became much harder under pressure

After repeated failures, I moved the restore testing flow entirely onto my local machine and isolated PostgreSQL from the rest of the application stack.

That made debugging significantly easier.

Another Subtle Issue: Backup Failures After Restore

I also ran into another confusing problem after some restore attempts.

In certain cases, subsequent backups started failing unexpectedly after a restore.

Part of the issue came from mixing:

restore operations
delta-style restore assumptions
and archive/WAL state inconsistencies

At one stage, I was also toggling archive-related behavior incorrectly during recovery experiments, which further complicated WAL continuity.

This reinforced another important realization:

PostgreSQL backups are tightly coupled with WAL history and recovery timelines.

Even when the database appears to start correctly, inconsistent recovery state can break future backup behavior in subtle ways.

What I Learned From This

This experience completely changed how I think about PostgreSQL recovery.

Some major lessons:

backup_label and recovery.signal are not garbage files
PostgreSQL recovery is heavily WAL-dependent
Timelines matter more than most people realize
Partial cleanup creates inconsistent recovery states
A clean restore is often safer than trying to "repair" recovery manually
pgBackRest already knows how to orchestrate PostgreSQL recovery properly
Restore validation matters as much as backup creation
Backup testing should happen in isolated environments

Most importantly:

PostgreSQL recovery is not something you should "fight".

Once I stopped trying to override recovery behavior manually and instead allowed PostgreSQL + pgBackRest to handle recovery the way they were designed to, the restore flow finally became stable.

The Final Restore Flow That Actually Worked

After multiple failed recovery attempts, timeline mismatches, and broken startup states, I stopped trying to manually "fix" PostgreSQL recovery and instead simplified the restore process completely.

The final stable flow looked roughly like this:

# simplified restore flow

stop_postgres()

empty_data_directory()

pgbackrest_restore("--type=immediate")

start_postgres()

wait_for_connection()

if postgres_is_in_recovery():
    promote_postgres()

pgbackrest_check()

The important part here is not the code itself.

It's the recovery philosophy behind it.

The earlier versions of my restore logic tried to:

partially clean recovery state
remove recovery metadata
force PostgreSQL out of recovery
preserve old data directory state

That approach kept creating inconsistent recovery conditions.

The corrected flow instead does three important things:

starts from a completely clean data directory
lets pgBackRest manage recovery metadata properly
allows PostgreSQL to perform WAL recovery the way it was designed to

The biggest change was no longer treating files like backup_label or recovery.signal as corruption artifacts.

They were part of the recovery process itself.

Final Thought

At the beginning, I thought PostgreSQL restores were failing because the database was corrupted.

In reality, the corruption was coming from my own recovery assumptions.

The system wasn't broken.

My mental model of PostgreSQL recovery was.

arrayJoin in ClickHouse: Why Your Rows Are Duplicating (and How to Control It)

Mohamed Hussain S — Tue, 28 Apr 2026 10:20:11 +0000

When working with arrays in ClickHouse, arrayJoin feels straightforward.

Until your query suddenly returns far more rows than expected.

The Use Case

Let’s say you have a table like this:

CREATE TABLE events (
    user_id UInt32,
    actions Array(String)
) ENGINE = MergeTree
ORDER BY user_id;

Example row:

user_id: 1
actions: ['click', 'scroll', 'purchase']

Now you want each action as a separate row.

The Tool: `arrayJoin`

SELECT user_id, arrayJoin(actions) AS action
FROM events;

Output:

1   click
1   scroll
1   purchase

So far, everything looks correct.

Where Things Go Wrong

Now let’s say you write:

SELECT user_id,
       arrayJoin(actions) AS action,
       arrayJoin(actions) AS action2
FROM events;

You might expect:

3 rows

But you actually get:

9 rows

Why This Happens

arrayJoin doesn’t just flatten arrays.

It expands rows.

Each element in the array creates a new row.

So when you use it multiple times:

First arrayJoin → expands rows
Second arrayJoin → expands again

Result:

3 elements → 3 × 3 = 9 rows

This is effectively a cartesian multiplication of rows.

The Hidden Impact

This becomes a real problem when:

Arrays are large
Multiple arrayJoins are used
You don’t expect row multiplication

Result:

Incorrect output
Sudden increase in row count
Slower queries

The Better Approach

1. Use a single `arrayJoin` when possible

SELECT user_id,
       arrayJoin(actions) AS action
FROM events;

2. Use `ARRAY JOIN` syntax (cleaner and explicit)

SELECT user_id, action
FROM events
ARRAY JOIN actions AS action;

3. Use `arrayZip` to avoid unintended multiplication

If you’re working with multiple arrays:

SELECT user_id,
       arrayJoin(arrayZip(actions, actions)) AS zipped
FROM events;

This ensures elements are paired instead of multiplied.

Why This Matters

arrayJoin is powerful-but easy to misuse.

If used without understanding:

Row count can explode
Queries become expensive
Results can be misleading

Real-World Use Cases

Event tracking pipelines
Flattening nested JSON
Working with semi-structured logs
Exploding arrays into rows for analysis

One Important Gotcha

Every arrayJoin multiplies rows.

If your result size looks unexpectedly large, this is one of the first things to check.

Final Thoughts

arrayJoin is one of the most useful tools in ClickHouse.

But its behavior is not always intuitive.

In many cases, the issue is not the data itself-but how the query expands it.

Understanding this early can save a lot of debugging time.

greatCircleDistance in ClickHouse: Avoiding Full Table Scans

Mohamed Hussain S — Mon, 20 Apr 2026 16:18:23 +0000

When working with location data, one problem shows up almost immediately:

“How do I calculate the distance between two coordinates stored in my database?”

At first, it seems like something you’d have to handle outside the database.

But if you're using ClickHouse, there’s a built-in function for this.

The Right Tool: `greatCircleDistance`

greatCircleDistance(lat1, lon1, lat2, lon2)

It calculates the shortest distance between two points on Earth.

Example

SELECT greatCircleDistance(13.0827, 80.2707, 12.9716, 77.5946) AS distance_meters;

This gives you the distance between Chennai and Bangalore - in meters.

Looks Simple… But There’s a Catch

Now let’s say you write a query like this:

SELECT city
FROM locations
WHERE greatCircleDistance(lat, lon, 13.0827, 80.2707) < 5000;

At first glance, this looks perfectly fine.

But this can quietly turn into a full table scan - especially on large datasets.

Why This Happens

In ClickHouse, indexes don’t work like traditional B-tree indexes.

They are:

Sparse
Designed for range pruning

They work well for queries like:

WHERE lat BETWEEN x AND y

But not for:

WHERE greatCircleDistance(lat, lon, x, y) < 5000

Because:

The function is applied on the columns, so ClickHouse cannot use the index to skip data efficiently.

The Better Approach (What You Should Actually Do)

Instead of directly applying the function, reduce the dataset first.

Bounding Box Filter

SELECT city
FROM locations
WHERE lat BETWEEN (13.0827 - 0.05) AND (13.0827 + 0.05)
  AND lon BETWEEN (80.2707 - 0.05) AND (80.2707 + 0.05)
  AND greatCircleDistance(lat, lon, 13.0827, 80.2707) < 5000;

(The bounding box is an approximation to reduce the search space before exact filtering.)

Why This Works

lat BETWEEN → uses index
lon BETWEEN → reduces rows further
greatCircleDistance → applied only on filtered data

So instead of scanning the entire table:
=> You narrow it down first, then compute accurately

Real-World Use Cases

This pattern is useful in:

Delivery radius filtering
Finding nearby users
Geo-based analytics
Ride-sharing systems

One Important Gotcha

Make sure:

Coordinates are in degrees (not radians)
Order is always (lat, lon)

Swapping them will give incorrect results.

Final Thoughts

greatCircleDistance is powerful - but if used blindly, it can hurt performance.

In ClickHouse, performance often depends more on how you query than what you query.

Sometimes, the right approach isn’t just using a function - but knowing when and how to use it efficiently.

Why My S3 Backup Setup Broke: Buckets, “Folders”, and Scheduling Misconceptions

Mohamed Hussain S — Thu, 16 Apr 2026 07:04:20 +0000

Another lesson in building reliable systems - not just configuring them.

I thought I had everything set up correctly.

Backups configured
S3-compatible storage connected
Backup triggered via cron jobs during testing

And yet nothing showed up where I expected.

What looked like a simple configuration issue turned out to be a wrong mental model of how S3 actually works.

This post breaks down what went wrong and what fixed it.

The Setup

I was working with:

An S3-compatible object storage (not AWS directly)
A system that allows:
- Configuring a bucket
- Setting a backup path
- Defining backup frequency

Everything seemed straightforward.

But the problem started with one assumption:

Buckets can behave like folders.

The First Mistake: Treating Buckets Like Folders

In a traditional file system, you think like this:

backups/
  app1/
    db.sql

So it felt natural to assume:

Create a “folder” in object storage
Then create buckets inside it for different use cases

In my case, I had something like a folder already created in the object storage UI, and I assumed:

That is my base, and I can create buckets under it

So I tried:

Connecting to that “folder” as a bucket
Then creating another bucket inside it (for vector DB backups)

This kept failing.

At first, I thought:

Maybe it is a permission issue
Maybe my user does not have enough access

But that was not the real problem.

What Was Actually Going Wrong

I was effectively trying to:

Treat a bucket like a parent directory
And create another bucket inside it

That is not how S3 works.

In S3:

Buckets are top-level containers
You cannot nest buckets inside other buckets

So when I tried to:

Connect to an existing bucket
And then create another bucket under it

It failed because the concept itself is invalid.

The Correct Mental Model

This is how S3 actually works:

bucket: backups
object key: app1/2026-04-15/db.sql

There are only two things:

Bucket (top-level)
Object key (full path as a string)

There is no real folder hierarchy.

Organizing Data the Right Way

The fix was not about creating folders.

It was about changing how I name objects.

Instead of trying to structure things at the bucket level, I moved that structure into the object key.

For example:

object_name = f"qdrant/{collection_name}/{snapshot_name}"

This gives a structure like:

bucket: backups

qdrant/
  collection_1/
    snapshot_001
  collection_2/
    snapshot_002

Even though S3 is flat internally, most UIs render this as a folder-like structure.

This is the correct way to organize data.

The Second Mistake: Mixing Bucket and Path

Another issue was passing paths as part of the bucket name.

For example:

bucket = backups/qdrant

This is invalid.

Correct approach:

bucket = backups
object key = qdrant/collection_name/snapshot

S3 APIs expect a valid bucket name, not a path.

What Finally Clicked

The breakthrough was realizing:

I was not dealing with folders at all
I was dealing with string prefixes inside object keys

Once I stopped trying to create hierarchy at the bucket level and moved everything into object naming, the entire setup started working as expected.

Putting It All Together

Correct configuration:

Bucket:

  backups

Object naming:

  f"qdrant/{collection_name}/{snapshot_name}"

This alone was enough to:

Organize backups cleanly
Avoid bucket-related errors
Make the storage layout intuitive in the UI

Key Takeaways

Buckets are not folders
You cannot create a bucket inside another bucket
S3 is a flat object store
Folder-like structures come from object key prefixes
Always keep bucket and path separate

Final Thought

The issue was not with permissions or configuration.

It was a mismatch between how I expected storage to behave and how it actually works.

Once the mental model changed, the implementation became simple.

If something feels unnecessarily complicated in S3, it is often a sign that the model being used is incorrect.

Debugging a Broken Metrics Pipeline: What Actually Went Wrong

Mohamed Hussain S — Thu, 16 Apr 2026 03:29:50 +0000

Part 4 of a series on building a metrics pipeline into ClickHouse
Read Part 3: Understanding Vector Transforms

When Things Still Don’t Work

At this point, the pipeline looked correct.

Sources were defined
Transforms were working
Data structure matched expectations

And yet, something was still off.

Data wasn’t behaving the way it should.

This is where debugging became the main task.

The Only Way Forward: Logs

When dealing with ingestion issues in ClickHouse, logs become your best source of truth.

I started monitoring the error logs directly:

sudo tail -f /var/log/clickhouse-server/clickhouse-server.err.log

This immediately surfaced issues that were not visible from the pipeline configuration.

An Error That Didn’t Make Sense

At one point, I started seeing this error repeatedly:

There exists no table monitoring.cpu in database monitoring

This was confusing.

I hadn’t created a table named cpu
It wasn’t part of my current setup
My Vector configuration didn’t reference it

So where was it coming from?

What Was Actually Happening

After digging deeper, the issue had nothing to do with my current pipeline.

It turned out that a previously used Telegraf process was still running in the background.

Even though I had:

Removed configurations
Switched tools
Rebuilt the pipeline

The old process was still active and sending data using an outdated setup.

That’s why ClickHouse was reporting errors for a table I never intended to use.

The Real Problem

This wasn’t a configuration issue.

It was a runtime issue.

The system I was debugging was not the only system running.

That realization changed how I approached debugging.

Fixing It

The solution was simple - but easy to miss.

First, I checked for any running Telegraf processes:

ps aux | grep telegraf

Then stopped them explicitly:

sudo systemctl stop telegraf

Once the old process was stopped, the errors disappeared.

What This Teaches

This led to an important lesson:

Always validate the runtime environment - not just the configuration.

When working with pipelines:

Old processes may still be running
Multiple agents may write to the same destination
Previous setups can interfere with new ones

If you don’t account for this, you may end up debugging the wrong problem.

The Debugging Loop

Most of the pipeline development ended up looking like this:

Write → Run → Fail → Check logs → Fix → Repeat

Each iteration helped refine:

Transform logic
Data structure
Schema alignment

This loop is where real progress happens.

What Finally Worked

Once:

Transforms were correct
Timestamps were fixed
Old processes were stopped

The pipeline stabilized.

Data started flowing consistently into ClickHouse, and queries returned expected results.

Series Recap

This series covered:

Part 1: Why the Telegraf approach didn’t work
Part 2: Understanding Vector pipelines
Part 3: Writing transforms and handling data
Part 4: Debugging and making the pipeline reliable (this post)

Final Thought

Building data pipelines is rarely about getting things right on the first try.

It’s about:

Observing how the system behaves
Identifying where it breaks
Iterating until it stabilizes

Debugging is not a side task - it is the process.

From Pipelines to Transforms: Making Vector Work with ClickHouse

Mohamed Hussain S — Thu, 16 Apr 2026 01:13:59 +0000

Part 3 of a series on building a metrics pipeline into ClickHouse
Read Part 2: Understanding Vector Pipelines

Where Things Got Real

By this point, the pipeline structure made sense.

I understood:

Sources
Transforms
Sinks

But the pipeline still wasn’t working reliably.

That’s when it became clear:

The hardest part wasn’t collecting data.
It was transforming it correctly.

Why Transforms Matter

Raw metrics are rarely usable as-is.

When sending data into ClickHouse, even small inconsistencies can break ingestion.

Some common issues encountered were:

Wrong data types
Unexpected field structures
Missing values
Incorrect timestamp formats

Even if everything else is correct, these issues cause inserts to fail.

Enter VRL

In Vector, transformations are written using Vector Remap Language.

At first, VRL feels simple.

But in practice, it’s strict.

Types must be explicit
Fields must be handled carefully
Errors are not ignored

That strictness is what makes pipelines reliable - but also harder to get right.

The Timestamp Problem

One of the biggest issues I faced was timestamp handling.

ClickHouse expects timestamps in a specific format.

The raw data didn’t match that format.

Even when everything else was correct, inserts would fail silently because of this.

The fix looked like this:

.timestamp = to_unix_timestamp!(parse_timestamp!(.timestamp, "%+"))

This line did three things:

Parsed the incoming timestamp
Converted it into a Unix format
Made it compatible with ClickHouse

It seems simple - but this was a major blocker.

Normalizing Metrics

Another challenge was aligning the data structure with what ClickHouse expects.

For both host and GPU metrics, this required:

Converting values into numeric types
Standardizing field names
Adding metadata like host and source
Ensuring consistent structure across all metrics

Without this step, ingestion would fail even if the pipeline looked correct.

From Raw Data to Queryable Format

One important transformation was changing how metrics were structured.

Instead of storing multiple values in a single record:

cpu, memory, disk

The data was reshaped into a row-based format:

metric_name = "cpu", value = ...
metric_name = "memory", value = ...

This made it easier to:

Query data in ClickHouse
Aggregate metrics
Maintain a consistent schema

Why This Was Hard

Most of the time spent on this pipeline wasn’t on setup.

It was here:

Write transform → Run → Fail → Fix → Repeat

Each iteration revealed:

A type mismatch
A missing field
A formatting issue

This is where the pipeline actually gets built.

What Changed After This

Once the transforms were correct:

Data started flowing reliably
Inserts into ClickHouse succeeded
Queries started returning meaningful results

At that point, the pipeline finally felt stable.

What’s Next

Even after fixing transformations, one major challenge remained:

Debugging unexpected failures.

In the next part, I’ll walk through:

How I debugged pipeline issues
What ClickHouse logs revealed
And a mistake that cost me time

Series Overview

Part 1: Why the Telegraf approach didn’t work
Part 2: Understanding Vector Pipelines
Part 3: Writing transforms and handling data correctly (this post)
Part 4: Debugging and making the pipeline reliable

Final Thought

Transforms are where pipelines either succeed or fail.

Understanding how data needs to be shaped is more important than the tool itself.

Once that becomes clear, everything else starts to fall into place.

Understanding Vector Pipelines: From Config Files to Data Flow

Mohamed Hussain S — Thu, 09 Apr 2026 19:29:55 +0000

Part 2 of a series on building a metrics pipeline into ClickHouse
Read Part 1: Why my metrics pipeline with Telegraf didn’t work

Picking Up Where Things Broke

In the previous part, I talked about trying to build a metrics pipeline using Telegraf - and why that approach didn’t work for my use case.

The biggest issue wasn’t just tooling.

It was this:

I didn’t have enough control over how data moved through the system.

That’s what led me to explore a different approach.

Why Vector

I came across Vector while looking for something more flexible.

At a glance, it felt different.

Instead of thinking in terms of plugins and configs, Vector is built around a pipeline model.

And that changes everything.

The Core Idea: Pipelines

At the center of Vector is a simple concept:

Sources → Transforms → Sinks

That’s it.

But this model makes the flow of data explicit.

Sources → where data comes from
Transforms → how data is modified
Sinks → where data is sent

Compared to my earlier approach, this immediately felt clearer.

What This Actually Means

Instead of writing a config and hoping everything connects correctly, you define:

What data you are collecting
How that data should be shaped
Where that data should go

That shift sounds small - but it changes how you think about the system.

From Config Files to Data Flow

With Telegraf, my thinking looked like this:

Write config → Run → Debug errors

With Vector, it started becoming:

Collect → Transform → Route → Store

The focus moved from:

“What config do I write?”

to:

“How does data move through each stage?”

The New Learning Curve

Of course, switching tools didn’t magically solve everything.

There were new challenges.

Vector uses YAML for configuration, which was different from the TOML I was used to.

And more importantly:

The pipeline only works if every stage is defined correctly.

Some of the early issues I ran into:

Incorrect source definitions
Misconfigured sinks
Data not flowing as expected
Silent failures when something didn’t connect properly

At times, it felt like nothing was happening-even though everything looked “correct.”

First Realization: Everything Is Connected

One important thing I learned quickly:

If one stage breaks, the entire pipeline breaks.

Unlike simpler setups, you can’t treat components independently.

A bad transform can stop data entirely
A misconfigured sink can drop everything silently
A source that doesn’t emit correctly makes debugging harder

This forced me to start thinking in terms of end-to-end flow, not individual pieces.

What Improved Immediately

Despite the challenges, a few things became better compared to before:

Clear visibility into how data moves
Better control over transformations
More flexibility in shaping data before sending it to ClickHouse

Even though things weren’t fully working yet, I finally felt like I was closer to solving the actual problem.

What Was Still Missing

At this stage, the pipeline structure made sense.

But one part was still unclear-and turned out to be the hardest:

How to correctly transform the data so that ClickHouse would accept it.

This is where most of the complexity showed up.

What’s Next

In the next part, I’ll dive into the most challenging part of this setup:

Writing transforms using Vector Remap Language (VRL)
Handling strict data types
Fixing timestamp issues
And shaping metrics into a format that ClickHouse can actually ingest

Series Overview

This post is part of a series:

Part 1: Why the Telegraf approach didn’t work
Part 2: Understanding Vector pipelines (this post)
Part 3: Writing transforms and handling data correctly
Part 4: Debugging and making the pipeline reliable

Final Thought

Switching tools didn’t solve the problem immediately.

But it did something more important:

It made the system visible.

Once I could see how data moved through each stage, debugging stopped being guesswork-and started becoming structured.

Why My Metrics Pipeline with Telegraf Didn’t Work (and What I Learned)

Mohamed Hussain S — Tue, 07 Apr 2026 10:18:51 +0000

Part 1 of a series on building a metrics pipeline into ClickHouse

Collecting metrics is easy.

Shipping them to an analytical database without losing your mind is the hard part.

The Goal

At one point, the task seemed straightforward:

Collect system metrics (CPU, memory, GPU) and store them in ClickHouse for analysis.

This is a common observability use case.
You collect metrics, send them somewhere, and run queries on top.

Simple enough.

But in practice, it didn’t go as planned.

The Initial Approach: Telegraf

I started with Telegraf.

It’s widely used for collecting system metrics and has a plugin-based architecture, which makes it a natural first choice.

This was also where I first came across TOML.

At first, it felt like I just needed to “write a config and run it.”
But very quickly, I realized:

Configuration isn’t just syntax-it defines how your system behaves.

What I Was Trying to Build

The idea was simple:

Collect host-level metrics (CPU, memory, etc.)
Collect GPU metrics
Push everything into ClickHouse
Run analytical queries on top

Essentially, a basic observability pipeline.

Where Things Started Breaking

On paper, Telegraf looked like it should work.

In reality, I ran into a few issues:

No straightforward way to push data into ClickHouse
Lack of a native ClickHouse output plugin
Debugging wasn’t very intuitive
Configurations became rigid as complexity increased

At some point, I was spending more time trying to make the tool fit the use case than actually solving the problem.

A Shift in Perspective

This is where something important clicked.

Up until this point, I was thinking in terms of:

Write config → Run tool → Expect output

But that approach wasn’t working.

What I needed instead was a clearer understanding of how data actually flows:

Data source → Transformation → Destination

The problem wasn’t just the tool-it was the lack of control over how data moved through the system.

Why I Decided to Move Away

At this stage, it became clear that I needed:

More control over data transformations
Better visibility into how data flows
A system that is easier to debug

Telegraf, while powerful, didn’t give me that level of flexibility for this use case.

What’s Next

That’s when I decided to try a different approach using Vector.

Instead of treating configuration as static setup, Vector treats it as a pipeline.

In the next part, I’ll walk through:

How Vector pipelines work
Why the sources → transforms → sinks model made a difference
And what changed when I adopted that approach

Series Overview

This post is part of a series:

Part 1: Why the Telegraf approach didn’t work (this post)
Part 2: Understanding Vector pipelines
Part 3: Writing transforms and handling data
Part 4: Debugging and making the pipeline reliable

Final Thought

What started as a simple setup turned into a deeper lesson:

Tools don’t solve problems-understanding systems does.

Once that became clear, the direction forward was much easier.

Full Text Search in ClickHouse: What Works in 2026

Mohamed Hussain S — Wed, 01 Apr 2026 06:46:21 +0000

ClickHouse is the undisputed heavyweight champion of analytics famed for fast aggregations, massive columnar storage, and processing trillions of rows. Historically, however, if you wanted "real" full-text search, the engineering consensus was clear: Don't use ClickHouse. You had to pay the "architectural tax" of syncing your data to a dedicated engine like Elasticsearch or OpenSearch.

But as of 2026, that consensus has shifted. With the General Availability of Inverted Indices and native ranking functions, the question is no longer if ClickHouse can do search, but how much of your infrastructure you can now simplify by moving it all into one place.

What Do We Mean by "Full-Text Search"?

Full-text search is fundamentally different from simple string filtering. In a dedicated search ecosystem, it typically requires:

Tokenization: Breaking sentences into individual, searchable words.
Inverted Indexing: A specialized data structure that maps tokens to row IDs so the engine doesn't have to scan the entire table.
Relevance Scoring: Ranking results using algorithms like BM25 so the best matches appear first.

In the past, ClickHouse only handled basic filtering. Today, it handles the entire stack.

What Actually Works: The 2026 Reality

ClickHouse now provides a tiered approach to text search. Depending on your performance needs, you have three primary tools:

1. The Heavyweight: Native Inverted Indices

This is the single biggest update to the ClickHouse ecosystem. You no longer need to rely on brute-force LIKE patterns that scan every byte of data. By defining an Inverted Index, ClickHouse creates a mapping that allows it to jump directly to the relevant data blocks.

-- Creating a high-performance inverted index
ALTER TABLE logs ADD INDEX inv_idx message TYPE inverted(0) GRANULARITY 1;

The performance impact is massive. For datasets in the billions of rows, an indexed search can be 10x to 100x faster than a standard query because it narrows the search space to a few "granules" of data.

2. Industry-Standard Ranking (BM25)

A search engine is only as good as its sorting. ClickHouse now supports BM25 scoring natively. This allows you to find "connection errors" and ensure the rows where those terms appear most prominently are at the top of your result set.

3. Precision Tokenization

Using functions like hasToken(), ClickHouse understands word boundaries. It knows that a search for the word "log" should not return results for "logger" or "biological." This brings a level of precision previously reserved for dedicated search engines.

Where ClickHouse Excels

In the current landscape, ClickHouse is the "sweet spot" for several specific high-growth use cases:

Log Analytics & Observability: This is the primary "Elasticsearch killer." You can search billions of logs for a specific error message and, in the same query, calculate the average latency or error rate.
Architectural Simplicity: Managing a ClickHouse cluster and a search cluster is an operational nightmare. Moving both workloads to ClickHouse reduces your infrastructure footprint, simplifies your ingestion pipelines, and slashes your cloud bill.
Hybrid Queries: ClickHouse allows you to join search results with structured metadata (like user IDs or pricing tables) instantly - something that is notoriously difficult in traditional search engines.

What Still "Doesn't Work"

Despite these massive strides, ClickHouse is not a magic bullet for every search problem. There are still areas where dedicated engines hold the lead:

Complex Linguistics: If you need deep morphological analysis (e.g., matching "mice" to "mouse" or handling complex compounding in German), dedicated engines still have more mature language plugins.
Fuzzy Matching & Auto-Correct: While ClickHouse can calculate levenshteinDistance(), it isn't yet optimized for high-concurrency "did you mean?" style suggestions found on major e-commerce sites.
Multi-tenant Search Products: If you are building a consumer-facing product where search is the entire product, the fine-grained tuning of a search-first engine is still superior.

ClickHouse vs. Search Engines: The 2026 Comparison

Feature	ClickHouse (2026)	Elasticsearch / OpenSearch
Primary Strength	Analytics + Search	High-Relevance Search
Storage Cost	Very Low (Columnar)	High (Index Overhead)
Aggregation Speed	Best-in-class	Moderate
Relevance (BM25)	Fully Supported	Industry Standard
Operational Effort	Low (Single System)	High (Multiple Systems)

Final Thoughts

The boundary between "Analytics" and "Search" has officially blurred.

If you are analyzing logs, building internal observability tools, or need to search across massive datasets where cost and aggregation speed matter most, ClickHouse is now a full-text search engine.

Choosing ClickHouse in 2026 means opting for a simpler architecture and better performance without sacrificing the core search capabilities your team needs.

When Synthetic Data Lies: A Hidden Correlation Problem I Didn’t Expect

Mohamed Hussain S — Thu, 26 Mar 2026 10:06:38 +0000

While working on a small analytics setup using ClickHouse and Superset, I generated some synthetic data to test queries and dashboards.

Initially, everything looked fine. The distributions seemed reasonable, and the dashboards behaved as expected.

But as I increased the dataset size, a few patterns started to look off.

Revenue seemed to concentrate in a single country.
In some cases, certain countries had no purchases at all.

At first, it looked like a simple distribution issue. But the patterns were too consistent to ignore.

Checking the Usual Suspects

The first assumption was that something was wrong with the queries or aggregations.

So I checked:

query logic
filters
materialized views
dashboard configurations

Everything seemed correct.

Which pointed to a different possibility:

The issue wasn’t in how the data was queried - it was in how the data was generated.

Looking at the Data More Closely

Instead of relying on dashboards, I went back to the raw data.

A simple aggregation made things clearer:

one country dominating purchases
another missing entirely

Overall event distribution looks reasonable - the issue isn’t obvious here

At this point, it was clear that the data itself wasn’t behaving as expected.

The First Issue: Randomness That Wasn’t Quite Random

The initial data generation logic used rand() like this:

multiIf(
    rand() % 100 < 40, 'India',
    rand() % 100 < 65, 'US',
    rand() % 100 < 80, 'UK',
    rand() % 100 < 90, 'Germany',
    'UAE'
)

At a glance, this looks reasonable.

But each rand() call is evaluated independently.

So instead of generating a single random value and assigning a category, the logic evaluates a new random value at each step.

This leads to unintended distributions and subtle bias in the data.

Fixing That… and Introducing Another Problem

To make the data more stable, I switched to a deterministic approach using number:

(number * 17) % 100 AS event_rand
(number * 29) % 100 AS country_rand

This made the distributions predictable and easier to reason about.

After fixing randomness - a different issue appears (some countries have zero purchases)

But it introduced a different issue.

Both event_type and country were now derived from the same base value: number.

The Real Issue: Hidden Correlation

Even with different multipliers, these values were not independent.

They were mathematically related.

This meant:

certain rows could only produce certain combinations
some combinations would never occur

In this case, the rows that produced "purchase" never aligned with the rows that produced "UAE".

Which resulted in:

UAE having zero purchases
other countries showing skewed distributions

What Was Actually Wrong

The issue wasn’t randomness.

It was lack of independence.

The variables in the synthetic dataset were not independent of each other.

And that’s enough to produce misleading analytics.

Fixing the Data Generation

To resolve this, I changed how the values were generated:

used different transformations
added offsets
ensured each variable had its own distribution

For example:

(number * 17) % 100 AS event_rand
(number * 31 + 13) % 100 AS country_rand
(number * 47 + 23) % 100 AS device_rand

This breaks the alignment between variables and restores independence.

After this change, the distributions behaved as expected.

Why This Matters

At smaller scales, the issue wasn’t obvious.

The data looked fine, and the dashboards seemed reasonable.

But as the dataset grew:

patterns became more consistent
biases became more visible
incorrect assumptions started to look like real insights

Key Takeaway

Synthetic data can look correct while still producing misleading results.

The problem wasn’t query performance.

It was data correctness.

Scaling the data didn’t create the issue.

It revealed it.

Final Thoughts

This was a good reminder that:

data generation deserves as much attention as querying
small assumptions can lead to large inconsistencies
and “reasonable-looking” data isn’t always reliable

When working with analytics systems, it’s easy to trust what the data shows.

But sometimes, it’s worth questioning how that data was created in the first place.

This came up while building an analytics setup using ClickHouse and Superset, where I was comparing raw tables and materialized views.

If you're interested in that setup, you can read about it here:
👉 link to Blog 1

How ClickHouse + Superset Work Together for Analytics (And What Actually Matters)

Mohamed Hussain S — Wed, 25 Mar 2026 00:59:24 +0000

Modern analytics systems require more than just fast databases - they need a complete workflow from data storage to visualization.

I set up a small analytics pipeline using ClickHouse and Apache Superset to understand how dashboards are built end to end.

The setup itself was straightforward, but while testing it, one question kept coming up:

Does query optimization actually matter at smaller scales?

To explore this, I compared queries on a raw table with queries on a materialized view. The difference wasn’t huge - but it was enough to reveal how things behave as data grows.

Why I Built This

The goal wasn’t to simulate a production system, but to:

understand how ClickHouse works in an analytics workflow
explore how Superset interacts with a database
observe how query performance changes with different data models

This was more of a hands-on exploration than a benchmark.

Why a BI Tool?

Running SQL queries directly is sufficient for basic analysis. However, as requirements grow, teams need:

reusable datasets
interactive dashboards
faster exploration

A BI tool provides a structured way to bridge raw data and decision-making.

Why Apache Superset Instead of Grafana

Both tools serve different purposes:

Apache Superset

SQL-first analytics workflow
rich visualization capabilities
designed for OLAP use cases

Grafana

strong in monitoring and observability
optimized for time-series metrics
less flexible for ad-hoc analytics

For analytics workloads on ClickHouse, Superset provides greater flexibility and control.

Why ClickHouse + Superset?

ClickHouse and Superset complement each other in a typical analytics stack:

ClickHouse handles large-scale aggregations efficiently
Superset enables exploration and visualization on top of SQL

ClickHouse performs the computation, while Superset exposes it for analysis.

Architecture

The overall architecture follows a simple flow:

Data → ClickHouse → Materialized View → Superset → Dashboard

This separation makes it easier to control performance - heavy computation stays in ClickHouse, while Superset focuses on visualization.

Dataset Design

A simple events table was created in ClickHouse using synthetic data.

The goal was not to simulate production-scale workloads, but to:

validate the integration
build dashboards
observe query behavior

Dashboard Creation in Superset

After establishing the connection:

datasets were defined on ClickHouse tables
charts were built using SQL queries
dashboards were assembled with filters for interaction

Superset acts as a visualization layer while still relying heavily on SQL for data definition.

Explore View

Final Dashboard

Raw Table vs Materialized View

To understand performance behavior, queries were executed on:

the raw table
a materialized view with pre-aggregated data

Results

Raw table → ~281 ms
Materialized view → ~222 ms

Raw Table

MV Table

Why Materialized Views Improve Performance

Materialized views:

reduce the volume of data scanned
pre-compute aggregations
simplify query logic

Even though the dataset is small, the improvement is measurable.

At this scale, the difference is minor - but it highlights something important:

As data grows, these small optimizations compound significantly.

Key Insight

The performance difference is small at low scale, but the pattern is clear.

As datasets grow, query performance becomes less about the BI tool and more about how the data is modeled.

Materialized views, pre-aggregation, and query design matter far more than visualization tooling.

Challenges Faced

Driver Not Detected by Superset

Error:

Could not load database driver: ClickHouseConnectEngineSpec

Root Cause

Superset runs inside its own internal virtual environment:

/app/.venv

The package was installed using system pip instead of the venv pip, making it invisible to Superset.

Fix

/app/.venv/bin/python -m ensurepip
/app/.venv/bin/python -m pip install clickhouse-connect

ClickHouse Not Visible in UI

ClickHouse did not appear in the database dropdown.

Fix

Use manual connection string:

clickhousedb://default:password@clickhouse:8123/default

Authentication Issues

Authentication failures occurred due to existing volumes storing old credentials.

Fix

Reset the ClickHouse volume and restart containers.

SQLite Migration Errors

Error:

table ab_permission already exists

Fix

Rebuild containers and allow Superset to handle initialization automatically.

Key Learnings

Data modeling plays a critical role in analytics performance
Materialized views are essential for scalable query performance
Superset relies on a properly optimized backend
Docker environment isolation can introduce subtle issues
Understanding internal environments (like virtualenvs) is crucial

A Note on Synthetic Data

One interesting issue I ran into during this process was with synthetic data generation.

At first, everything looked correct - but as the dataset grew, some unexpected patterns started to appear in the results.

It turned out to be a subtle problem related to how the data was being generated, not queried.

I’ll cover that in a follow-up post.

Conclusion

This setup was a good way to understand how modern analytics systems are put together - combining storage, computation, and visualization.

Even with a small dataset, experimenting with different query strategies shows how systems behave as they scale.

The tools themselves are powerful, but performance ultimately depends on how the data is structured and queried.

References

Apache Superset Documentation
Superset to ClickHouse

Managing Large PostgreSQL Tables with Native Partitioning and pg_partman

Mohamed Hussain S — Mon, 16 Mar 2026 08:31:49 +0000

As databases grow, tables that store large volumes of time-based data can quickly become difficult to manage.

Over time, this leads to several issues:

queries become slower
indexes grow larger
maintenance operations like VACUUM take longer
managing old data becomes complicated

PostgreSQL provides native table partitioning to help address these problems.

However, while partitioning improves performance and data management, operating partitioned tables manually can introduce operational complexity.

In this article, we’ll explore:

how native PostgreSQL partitioning works
the operational challenges of managing partitions manually
how pg_partman automates partition management

Native PostgreSQL Partitioning

PostgreSQL supports table partitioning, allowing a large logical table to be split into multiple smaller physical tables called partitions.

PostgreSQL currently supports three partitioning methods:

Range
List
Hash

For time-based data, range partitioning is the most common approach.

For example, imagine storing application events with a timestamp.

CREATE TABLE events (
    id BIGSERIAL,
    created_at TIMESTAMP,
    data JSONB
) PARTITION BY RANGE (created_at);

Here, events becomes the parent table.

Actual data is stored in child tables called partitions.

Example partitions:

CREATE TABLE events_2026_03_20
PARTITION OF events
FOR VALUES FROM ('2026-03-20') TO ('2026-03-21');

CREATE TABLE events_2026_03_21
PARTITION OF events
FOR VALUES FROM ('2026-03-21') TO ('2026-03-22');

Each partition is a physical table inside PostgreSQL.

How Data Is Inserted into Partitions

Applications still insert data into the parent table.

INSERT INTO events (created_at, data)
VALUES ('2026-03-21 10:15:00', '{"event":"login"}');

PostgreSQL automatically routes the row to the correct partition based on the partition key.

In this example, the row would be stored inside:

events_2026_03_21

Queries still run against the parent table.

SELECT * FROM events
WHERE created_at >= now() - interval '7 days';

PostgreSQL internally performs partition pruning, meaning only the relevant partitions are scanned.

Why Partitioning Improves Performance

Partitioning helps in several ways.

Instead of scanning a very large table, PostgreSQL only scans the partitions that match the query conditions.

Example:

                          Query for last 7 days
                                   ↓
                 PostgreSQL scans only recent partitions
                                   ↓
                        Older partitions are skipped

Partitioning also simplifies operations like:

archiving historical data
dropping old data quickly
managing index sizes

For example, dropping old data can be done instantly by dropping a partition.

DROP TABLE events_2024_03_01;

This is far faster than deleting millions of rows from a single large table.

The Operational Challenge with Native Partitioning

While native partitioning is powerful, managing partitions manually introduces operational challenges.

For example:

new partitions must be created ahead of time
old partitions must be removed manually
retention policies must be implemented manually
missing partitions can cause insert failures

Example scenario:

                  application inserts event at midnight
                                   ↓
                        new partition does not exist
                                   ↓
                              insert fails

As systems scale and tables grow, manually managing partitions becomes increasingly difficult.

This is where automation becomes valuable.

Introducing pg_partman

pg_partman is a PostgreSQL extension designed to automate partition management.

It builds on top of PostgreSQL’s native partitioning and helps manage partitioned tables more efficiently.

pg_partman can automatically handle:

creation of future partitions
retention and removal of old partitions
partition maintenance operations

This reduces the operational overhead of managing partitioned tables manually.

How pg_partman Works

pg_partman works by managing a parent partitioned table and automatically maintaining its partitions.

A simplified workflow looks like this:

                              Parent table
                                   ↓
                         pg_partman configuration
                                   ↓
                   Automatic creation of future partitions
                                   ↓
               Optional retention policies for old partitions

For example, if a table is partitioned by day, pg_partman can automatically create upcoming partitions.

events_p2026_03_22
events_p2026_03_23
events_p2026_03_24

This ensures that new inserts always have a valid partition.

Benefits of Using pg_partman

Compared to managing partitions manually, pg_partman provides several advantages.

                    Automatic partition creation
                                   ↓
                    Reduced operational overhead
                                   ↓
                   Safer data retention management
                                   ↓
                   Less risk of missing partitions

Instead of maintaining partition logic manually in application code or scripts, pg_partman handles this inside the database.

When pg_partman Is Useful

pg_partman is particularly useful for workloads involving large append-only datasets, such as:

event logs
analytics data
application activity tracking
time-series data

In these scenarios, new data continuously arrives while older data eventually becomes less important.

Partition automation helps manage this lifecycle efficiently.

Final Thoughts

PostgreSQL’s native partitioning provides powerful capabilities for managing large datasets.

However, operating partitioned tables manually can introduce additional operational complexity.

Extensions like pg_partman simplify this process by automating partition creation and maintenance.

By combining PostgreSQL’s native partitioning features with pg_partman’s automation, teams can manage large time-based datasets more reliably and with less manual intervention.

Forem: Mohamed Hussain S

PostgreSQL Restore Failures: It Wasn’t pgBackRest, It Was My Recovery Logic

What I Was Building

The First Major Failure

My Wrong Assumption

What PostgreSQL Was Actually Trying To Do

The Recovery Loop Problem

The Timeline Mismatch Error

The Real Problem In My Restore Flow

The Change That Finally Fixed It

Why --type=immediate Helped

The Mistake That Increased the Blast Radius

Another Subtle Issue: Backup Failures After Restore

What I Learned From This

The Final Restore Flow That Actually Worked

Final Thought

arrayJoin in ClickHouse: Why Your Rows Are Duplicating (and How to Control It)

The Use Case

The Tool: arrayJoin

Where Things Go Wrong

Why This Happens

The Hidden Impact

The Better Approach

1. Use a single arrayJoin when possible

2. Use ARRAY JOIN syntax (cleaner and explicit)

3. Use arrayZip to avoid unintended multiplication

Why This Matters

Real-World Use Cases

One Important Gotcha

Final Thoughts

greatCircleDistance in ClickHouse: Avoiding Full Table Scans

The Right Tool: greatCircleDistance

Example

Looks Simple… But There’s a Catch

Why This Happens

The Better Approach (What You Should Actually Do)

Bounding Box Filter

Why This Works

Real-World Use Cases

One Important Gotcha

Final Thoughts

Why My S3 Backup Setup Broke: Buckets, “Folders”, and Scheduling Misconceptions

The Setup

The First Mistake: Treating Buckets Like Folders

What Was Actually Going Wrong

The Correct Mental Model

Organizing Data the Right Way

The Second Mistake: Mixing Bucket and Path

What Finally Clicked

Putting It All Together

Key Takeaways

Final Thought

Debugging a Broken Metrics Pipeline: What Actually Went Wrong

When Things Still Don’t Work

The Only Way Forward: Logs

An Error That Didn’t Make Sense

What Was Actually Happening

The Real Problem

Fixing It

What This Teaches

The Debugging Loop

What Finally Worked

Series Recap

Final Thought

From Pipelines to Transforms: Making Vector Work with ClickHouse

Where Things Got Real

Why Transforms Matter

Enter VRL

The Timestamp Problem

Normalizing Metrics

From Raw Data to Queryable Format

Why This Was Hard

What Changed After This

What’s Next

Series Overview

Final Thought

Understanding Vector Pipelines: From Config Files to Data Flow

Picking Up Where Things Broke

Why Vector

The Core Idea: Pipelines

Why `--type=immediate` Helped

The Tool: `arrayJoin`

1. Use a single `arrayJoin` when possible

2. Use `ARRAY JOIN` syntax (cleaner and explicit)

3. Use `arrayZip` to avoid unintended multiplication

The Right Tool: `greatCircleDistance`