Forem: Mohamed Hussain S

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.

PostgreSQL Backups and Point-in-Time Recovery with pgBackRest

Mohamed Hussain S — Fri, 13 Mar 2026 15:10:54 +0000

This article is part of a series on PostgreSQL WAL.

Part 1 - How PostgreSQL WAL Actually Works

Part 2 - How Debezium Uses PostgreSQL WAL for CDC

Part 3 - PostgreSQL Backups with pgBackRest and PITR

In the previous articles, we've explored how PostgreSQL records every change inside the database using Write-Ahead Logging (WAL).

We also saw how tools like Debezium can read WAL to stream database changes in real time.

But WAL has another very important role.

It enables database backups and recovery.

By combining a base backup with archived WAL segments, PostgreSQL can restore a database to any specific moment in time. This capability is known as Point-in-Time Recovery (PITR).

In this article, we’ll explore how this works and how to implement it using pgBackRest.

Why Backups Alone Are Not Enough

A common backup strategy is to take periodic full backups of a database.

For example:

02:00 → Full backup taken
14:00 → Accidental DELETE executed

If we restore the 02:00 backup, the database returns to that state.

But everything between 02:00 and 14:00 is lost.

                          restore backup
                                 ↓
                      database returns to 02:00
                                 ↓
                        12 hours of data lost

This is where WAL archiving becomes important.

Instead of relying only on periodic backups, PostgreSQL can archive WAL segments continuously.

This allows the database to replay changes until a specific moment in time.

How WAL Enables Point-in-Time Recovery

Point-in-Time Recovery works by combining two components:

                              Base Backup
                                   +
                        Continuous WAL Archiving
                                   =
                         Point-in-Time Recovery

The recovery flow looks like this:


                               Base Backup
                                    │
                                    ▼
                              Restore Backup
                                    │
                                    ▼
                           Replay WAL Segments
                                    │
                                    ▼
                            Reach Target Time

Because WAL contains every change in chronological order, PostgreSQL can replay those changes to reconstruct the database state at any moment.

Introducing pgBackRest

pgBackRest is a popular backup and restore solution designed specifically for PostgreSQL.

It provides several capabilities including:

managing full, differential, and incremental backups
archiving WAL segments
enforcing backup retention policies
restoring databases efficiently

pgBackRest integrates directly with PostgreSQL’s WAL archiving system and stores backups in a repository.

This repository can be:

local storage
remote servers
object storage

For testing in this article, we will use MinIO as an object storage backend.

Testing Architecture

For local testing, the setup consists of two containers.

                         PostgreSQL + pgBackRest
                                   │
                                   ▼
                                 MinIO

The PostgreSQL container runs both the database and pgBackRest.
MinIO acts as an object storage service where backups and WAL segments are stored.

This architecture makes it easy to test backup and restore workflows locally.

Enabling WAL Archiving

To enable WAL archiving in PostgreSQL, the following parameters must be configured.

archive_mode = on
archive_command = 'pgbackrest --stanza=demo archive-push %p'

Here is what happens internally:

                         WAL segment reaches 16MB
                                   ↓
                   PostgreSQL executes archive_command
                                   ↓
                      pgBackRest uploads WAL segment
                                   ↓
                      WAL stored in backup repository

This ensures WAL segments are continuously archived.

Running a Base Backup

Once pgBackRest is configured, a base backup can be taken using:

pgbackrest --stanza=demo backup --type=full

pgBackRest supports multiple backup types.

Type	Description
Full	Complete backup of the database
Differential	Backup of changes since last full backup
Incremental	Backup of changes since last backup

These options allow flexible backup strategies.

Simulating Data Loss

To verify the backup pipeline, a sample table was created.

CREATE TABLE users(
  id serial primary key,
  name text
);

Sample data was inserted:

INSERT INTO users(name)
VALUES ('Alice'), ('Bob'), ('Charlie');

After confirming the data, a full backup was taken.

Next, the table was intentionally removed to simulate a failure scenario.

DROP TABLE users;

At this point, the table and its data no longer existed.

Restoring the Database

To restore the database, PostgreSQL must first be stopped.

However, in this setup PostgreSQL and pgBackRest are running inside the same container.
If the PostgreSQL process stops, the container also stops, which means we can no longer access pgBackRest inside that container.

To work around this, a temporary container was started using the same data volume. This allowed us to run the restore operation while PostgreSQL remained stopped.

The restore process then involved the following steps:

                            Restore base backup
                                   ↓
                         Replay archived WAL segments
                                   ↓
                      Database reaches consistent state

Inside the temporary container, the restore command was executed:

pgbackrest --stanza=demo restore --type=immediate

After the restore completed, the original PostgreSQL container was started again.

At first glance, it seemed like the restore had worked.
However, the result was not exactly what we expected - something interesting was happening behind the scenes.

We'll see why in a moment.

A PITR Behaviour Observed During Testing

The following sequence occurred:

                             table created
                                   ↓
                              backup taken
                                   ↓
                             table deleted
                                   ↓
                           restore base backup
                                   ↓
                               WAL replay
                                   ↓
                           table still deleted

Even after restoring the backup, the table remained deleted. This happened because PostgreSQL continued replaying WAL segments during recovery. Since the delete operation was recorded in WAL, it was replayed again during restore.

Note: PostgreSQL recovery is "greedy" by default—it will try to replay every piece of WAL it can find in the archive to bring you to the most recent state possible. By using --type=immediate, we tell pgBackRest to tell PostgreSQL: "Stop as soon as you reach a consistent state," effectively ignoring any changes (like our accidental DELETE) that happened after the backup was finalized.

To prevent this, the restore command used:

# Inside the temporary container
pgbackrest --stanza=demo restore --type=immediate

Understanding how WAL replay works is important when performing PITR.

Automating Backups with Cron

Backups are usually automated using scheduled jobs.

In this setup, a full backup is taken weekly, and differential backups are taken on the remaining days.

For example:

0 2 * * 0 pgbackrest --stanza=demo backup --type=full
0 2 * * 1-6 pgbackrest --stanza=demo backup --type=diff

This configuration creates:

one full backup per week
daily differential backups

This strategy balances storage efficiency and recovery speed.

Final Thoughts

Throughout this series, we’ve seen how Write-Ahead Logging (WAL) forms the foundation for several critical PostgreSQL features.

The same WAL mechanism powers:

crash recovery
replication
change data capture
backups
point-in-time recovery

Understanding WAL makes it easier to understand how PostgreSQL maintains reliability and data safety in production environments.

Tools like pgBackRest build on top of this mechanism to provide robust backup and restore workflows.

How Debezium Uses PostgreSQL WAL for Change Data Capture

Mohamed Hussain S — Wed, 11 Mar 2026 15:10:41 +0000

This article is part of a series on PostgreSQL WAL.

Part 1 — How PostgreSQL WAL Actually Works  
Part 2 — How Debezium Uses PostgreSQL WAL for CDC  
Part 3 — PostgreSQL Backups with pgBackRest and PITR

In the previous article, we've explored how PostgreSQL uses Write-Ahead Logging (WAL) to ensure durability and recover from crashes.

If you haven't read it yet, you can check it out here:

How PostgreSQL WAL Actually Works (And Why Everything Depends on It)

But WAL is not just an internal recovery mechanism.

Because WAL records every change that happens inside the database, it also enables systems to stream those changes in real time.

This is exactly how Change Data Capture (CDC) systems work.

Instead of polling tables repeatedly, CDC tools read database changes directly from WAL and turn them into events.

One of the most widely used CDC platforms for PostgreSQL is Debezium.

In this article, we’ll explore how Debezium reads PostgreSQL WAL and converts database operations into a stream of change events.

What is Change Data Capture (CDC)

Applications often need to react to changes happening inside a database.

For example, imagine a simple update:

UPDATE users SET name='Alice Cooper' WHERE id = 1;

That change might need to trigger other systems.

For example:

updating a search index
syncing data into a data warehouse
updating analytics pipelines
triggering downstream workflows

A traditional approach would be to periodically query the database to detect changes.

For example:

poll database every few seconds
compare results
detect differences

But this approach has several problems:

inefficient
high database load
delayed updates
difficult to scale

Change Data Capture solves this by streaming database changes as they happen.

Why WAL Makes CDC Possible

PostgreSQL writes every change into WAL before modifying the actual table files.

Because of this, WAL effectively contains a chronological record of all database operations.

Instead of scanning tables, CDC systems can simply read this log.

The idea looks like this:

                            Application Query
                                    ↓
                                PostgreSQL
                                    ↓
                            WAL record created
                                    ↓
                            CDC tool reads WAL
                                    ↓
                             Event generated

This makes CDC both efficient and reliable.

The CDC system simply consumes changes in the order they occurred.

Debezium works exactly this way.

It does not read tables directly.
Instead, it reads PostgreSQL WAL.

Enabling Logical Decoding in PostgreSQL

For CDC systems to read WAL, PostgreSQL must provide enough information about row-level changes.

This is controlled by the wal_level configuration.

wal_level = logical

PostgreSQL supports three WAL levels:

wal_level	Purpose
minimal	crash recovery only
replica	physical replication
logical	logical decoding (CDC tools)

When wal_level is set to logical, PostgreSQL records additional metadata needed for decoding row-level changes.

This makes it possible for external systems to interpret WAL records and reconstruct database events.

Replication Slots

When Debezium connects to PostgreSQL, it creates something called a replication slot.

A replication slot is managed by PostgreSQL and tracks the progress of a consumer reading WAL.

Its job is simple:

                            Debezium reads WAL
                                    ↓
                   Postgres tracks last consumed position
                                    ↓
                        WAL not deleted until consumed

This prevents PostgreSQL from removing WAL segments that Debezium still needs.

The position in WAL is identified using something called an LSN (Log Sequence Number).

An LSN represents a specific position inside the WAL stream.

Because the replication slot tracks the last processed LSN, Debezium can resume streaming from the correct position even if it restarts.

Logical Decoding Plugins

WAL records are stored in a binary format that is not directly readable by external systems.

PostgreSQL solves this using logical decoding plugins.

These plugins translate WAL records into a logical representation of database changes.

Some commonly used plugins include:

pgoutput
wal2json
decoderbufs

The default plugin used by PostgreSQL is pgoutput.

It converts WAL records into logical change events that CDC tools like Debezium can consume.

How Debezium Streams Database Changes

When everything is configured, the end-to-end flow looks like this:

                          Application writes data
                                    ↓
                      PostgreSQL writes change to WAL
                                    ↓
                    Logical decoding plugin interprets WAL
                                    ↓
                Debezium reads changes using replication protocol
                                    ↓
                     Debezium publishes events to Kafka
                                    ↓
                    Downstream systems consume the events

This architecture allows applications to react to database changes in near real time.

A simplified architecture might look like this:

Consumers could include:

microservices
analytics pipelines
search indexing systems
data warehouses

Example Change Event

When Debezium captures a change from WAL, it converts it into a structured event.

A simplified example might look like this:

{
  "op": "c",
  "table": "users",
  "before": null,
  "after": {
    "id": 1,
    "name": "Alice"
  }
}

The op field indicates the type of operation:

Operation	Meaning
c	create
u	update
d	delete

These events can then be consumed by downstream systems to trigger further processing.

Why WAL Is So Powerful

What makes this architecture powerful is that multiple systems can reuse the same WAL stream.

The same WAL records that power CDC can also be used for:

replication
backups
point-in-time recovery

In other words, WAL acts as a universal source of truth for database changes.

Final Thoughts

Change Data Capture allows systems to react to database changes without constantly querying tables.

PostgreSQL makes this possible because every change is recorded in WAL.

Tools like Debezium simply read that stream of changes and convert them into events that other systems can consume.

This makes it possible to build event-driven architectures directly on top of a relational database.

Next Article in This Series

In the next article, we’ll explore how PostgreSQL WAL is used for database backups and point-in-time recovery using tools like pgBackRest.

We’ll walk through how WAL archiving enables restoring a PostgreSQL database to a specific moment in time.

How PostgreSQL WAL Actually Works (And Why Everything Depends On It)

Mohamed Hussain S — Mon, 09 Mar 2026 15:01:18 +0000

Every change in PostgreSQL is written twice.

First to WAL.
Then later to the actual table files.

This mechanism is called Write-Ahead Logging (WAL) and it’s one of the most important parts of PostgreSQL’s architecture.

Without WAL, PostgreSQL would not be able to safely recover from crashes, replicate data, or support tools like CDC pipelines and backup systems.

In this Post we’ll break down:

how PostgreSQL stores data internally
what WAL is and why it exists
what WAL files actually contain
how PostgreSQL recovers from crashes
why replication, CDC, and backups depend on WAL

How Data Is Stored in PostgreSQL (Quick Glance)

Before understanding WAL, it helps to know how PostgreSQL actually stores data on disk.

When you create a table in PostgreSQL:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT
);

PostgreSQL creates a table file on disk inside its data directory.

But rows are not stored randomly inside that file.

Instead, PostgreSQL organizes table storage into fixed-size pages.

                               Table
                                 │
                                 ▼
                          Pages (8 KB each)
                                 │
                                 ▼
                       Rows stored inside pages

Each page has a default size of 8 KB.

Inside a page PostgreSQL stores multiple rows (also called tuples).

Example simplified view:

Page (8 KB)

+---------------------+
| Row: (1, Alice)     |
| Row: (2, Bob)       |
| Row: (3, Charlie)   |
| Row: (4, David)     |
+---------------------+

When new data is inserted, PostgreSQL finds a page with enough free space and places the row there.

Why PostgreSQL Writes to WAL Before Writing Data

PostgreSQL does not directly write changes to the table files.

Instead it first records the change in WAL (Write-Ahead Log).

The rule PostgreSQL follows is simple:

                        Write change to WAL
                                 ↓
                         Flush WAL to disk
                                 ↓
                 Apply change to table pages later

This guarantees that if the database crashes, PostgreSQL still knows what changes were supposed to happen.

WAL acts as a durable record of database operations.

The Problem Databases Need to Solve

Consider a simple query:

INSERT INTO users(name) VALUES ('Alice');

For PostgreSQL to execute this, it must:

modify a page inside the table file
write the updated page to disk

But what happens if the database crashes mid-write?

For example:

                         write page to disk
                                 ↓
                           power failure
                                 ↓
                       page partially written
                                 ↓
                     database becomes corrupted

Databases must guarantee durability.

Once a transaction commits, the data should survive crashes.

This is exactly the problem Write-Ahead Logging solves.

What WAL Actually Contains

A common misconception is that WAL stores full rows.

It doesn’t.

Instead, WAL stores instructions describing what changed.

A simplified idea of a WAL record might look like:

page: 123
offset: 45
action: insert
tuple: ('Alice')

In other words:

WAL records describe how to reproduce the change, not the entire dataset.

This makes WAL much smaller and faster to write than copying full data pages.

Where WAL Is Stored

All WAL files are stored in the PostgreSQL directory:

pg_wal/

Inside this directory you’ll see files like:

000000010000000000000001
000000010000000000000002
000000010000000000000003

These files are called WAL segments.

Each segment has a fixed size.

Default size:

16 MB

When a segment fills up, PostgreSQL automatically creates a new one.

                           segment fills
                                 ↓
                       new WAL segment created
                                 ↓
                         logging continues

This process happens continuously while the database is running.

When Table Pages Are Actually Written

Once WAL is safely flushed to disk, PostgreSQL can update the actual table pages.

These writes happen through background processes such as:

background writer
checkpoints

This allows PostgreSQL to batch writes efficiently and reduce disk I/O.

Even if the database crashes before the page write finishes, WAL already contains enough information to recover.

Crash Recovery

When PostgreSQL starts after a crash, it performs WAL replay.

The process looks like this:

                         database crashes
                                 ↓
                       postgres starts again
                                 ↓
                          read WAL records
                                 ↓
                           replay changes
                                 ↓
                     database becomes consistent

This ensures that committed transactions are not lost.

WAL acts as the source of truth for rebuilding the database state.

Why WAL Is More Than Crash Recovery

Although WAL was originally designed for crash recovery, it powers several important PostgreSQL capabilities today.

Replication

Standby servers replicate data by replaying WAL records from the primary server.

This allows replicas to stay synchronized with the primary database.

Change Data Capture (CDC)

Tools like Debezium read WAL using logical decoding.

Instead of scanning database tables, they stream changes directly from WAL.

This enables architectures like:

Applications can react to database changes in near real time.

Backups and Point-in-Time Recovery

Backup tools such as pgBackRest continuously archive WAL segments.

This enables Point-in-Time Recovery (PITR).

For example:

                          base backup taken
                                 ↓
                      WAL archived continuously
                                 ↓
                          restore database
                                 ↓
                   replay WAL until target timestamp

Without WAL archiving, restoring a database to an exact moment in time would not be possible.

A Simple Mental Model

One way to think about PostgreSQL is:

Tables store current state
WAL stores history of changes

Whenever PostgreSQL needs to recover, replicate, or stream changes, it uses that history.

Final Thoughts

Write-Ahead Logging is one of the core mechanisms that makes PostgreSQL reliable.

It enables:

crash recovery
replication
change data capture
backup systems
point-in-time recovery

In many ways, WAL is the foundation that many PostgreSQL features are built on.

Understanding WAL makes it much easier to understand how the rest of the PostgreSQL ecosystem works.

Next Article in This Series

In the next article, we’ll look at how tools like Debezium read PostgreSQL WAL to stream database changes in real time using logical decoding.

Later in the series, we’ll also explore how WAL is used for database backups and point-in-time recovery with tools like pgBackRest.