<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Safnas  on Medium]]></title>
        <description><![CDATA[Stories by Safnas  on Medium]]></description>
        <link>https://medium.com/@safnas.20?source=rss-bebcf8560484------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*Pcibq0iaXz9DgQQNvtiakg.jpeg</url>
            <title>Stories by Safnas  on Medium</title>
            <link>https://medium.com/@safnas.20?source=rss-bebcf8560484------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 12:41:25 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@safnas.20/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Understanding SSO, Authentication, Authorization, RBAC, and Audit Trails Through a Real-World…]]></title>
            <link>https://medium.com/@safnas.20/understanding-sso-authentication-authorization-rbac-and-audit-trails-through-a-real-world-16e64187a490?source=rss-bebcf8560484------2</link>
            <guid isPermaLink="false">https://medium.com/p/16e64187a490</guid>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[oauth]]></category>
            <category><![CDATA[cognito]]></category>
            <category><![CDATA[sso]]></category>
            <dc:creator><![CDATA[Safnas ]]></dc:creator>
            <pubDate>Wed, 24 Jun 2026 05:18:09 GMT</pubDate>
            <atom:updated>2026-06-24T05:18:09.873Z</atom:updated>
            <content:encoded><![CDATA[<h3>Understanding SSO, Authentication, Authorization, RBAC, and Audit Trails Through a Real-World Cognito + Snowflake Integration</h3><h3>Introduction</h3><p>Modern software systems rarely operate in isolation. Most enterprise applications today are composed of multiple services that need to trust each other in a secure and consistent way. This is where concepts like Single Sign-On (SSO), authentication, authorization, RBAC, and audit trails become essential.</p><p>At first glance, these terms often feel overlapping or abstract. However, when understood through a real-world system, their boundaries become much clearer.</p><p>In this article, I’ll break down these concepts in a practical way and connect them to an actual implementation involving AWS Cognito, Snowflake, and a custom application dashboard.</p><h3>What SSO Actually Means</h3><p>Single Sign-On (SSO) is a mechanism that allows a user to authenticate once and gain access to multiple systems without logging in again.</p><p>In traditional systems, each application maintains its own login flow. This leads to repeated authentication, fragmented sessions, and poor user experience.</p><p>SSO solves this by introducing a central identity provider.</p><p>In simple terms:</p><p>SSO means the user proves their identity once, and every connected system trusts that proof.</p><p>A common everyday example is Google’s ecosystem. When you log into Gmail, you are automatically authenticated across YouTube, Drive, and other Google services without signing in again.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wRfLg5U_2ZbioT8Vq8YBfQ.png" /><figcaption>Overview of SSO</figcaption></figure><h3>Authentication vs Authorization</h3><p>One of the most important distinctions in identity systems is understanding the difference between authentication and authorization.</p><p>Authentication is the process of verifying identity. It answers the question, &quot;Who are you?”</p><p>This typically involves login credentials such as a username and password, or a federated login through providers like AWS Cognito, Google, or Microsoft.</p><p>Once authentication succeeds, the system issues a token representing the user’s identity.</p><p>Authorization, on the other hand, determines what that user is allowed to do. It answers: “What permissions do you have?”</p><p>Even if the system knows who you are, it does not automatically mean you can access everything.</p><p>A simple analogy is entering a building. Authentication is showing your ID at the entrance. Authorization is whether you are allowed into specific rooms inside the building.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dxQzD5gL_fGcaSrSYJhDGw.png" /></figure><p>In most modern systems:</p><ul><li>Cognito handles authentication</li><li>Database handles authorization</li></ul><h3>Understanding Tokens in Modern Systems</h3><p>When a user logs in successfully, identity providers do not keep the session alive on the server side in a traditional way. Instead, they issue a token, typically a JSON Web Token (JWT).</p><p>A JWT is a self-contained object that carries information about the user, such as identity, expiration time, and sometimes custom attributes.</p><p>Once issued, the token does not change. This immutability is a core design principle of modern identity systems.</p><p>This also means that any change in user attributes (such as roles) will not be reflected until a new token is issued.</p><p>This behavior becomes very important when designing authorization systems.</p><h3>What RBAC Means in Practice</h3><p>Role-Based Access Control (RBAC) is a method of managing permissions using roles instead of assigning permissions directly to users.</p><p>Instead of defining permissions per user, systems define roles such as:</p><ul><li>Admin</li><li>Analyst</li><li>Viewer</li></ul><p>Each role carries a set of permissions, and users are assigned to roles.</p><p>This makes large-scale systems significantly easier to manage.</p><p>For example, instead of updating 100 users individually, you only update the role definition once.</p><p>Snowflake is heavily built around RBAC. Every action inside Snowflake is governed by roles, and access is granted based on role hierarchy.</p><h3>Audit Trails and Why They Matter</h3><p>An audit trail is a record of everything that happens inside a system. It provides visibility into user actions, system changes, and access patterns.</p><p>In data platforms like Snowflake, audit trails are critical for:</p><ul><li>Security monitoring</li><li>Compliance requirements</li><li>Debugging unexpected behavior</li><li>Tracking sensitive data access</li></ul><p>A typical audit log records:</p><ul><li>Who performed an action</li><li>When it was performed</li><li>What data was accessed</li><li>Which role was used</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xVuhZDpEr3mAIdqiQ_VDPw.png" /></figure><h3>Real-World Implementation: Cognito + Snowflake SSO</h3><p>Now let’s connect these concepts to a real implementation.</p><p>The system consisted of three main components:</p><ul><li>A custom application dashboard</li><li>AWS Cognito as the identity provider</li><li>Snowflake as the data warehouse</li></ul><p>The goal was to enable users to log in once and execute queries on Snowflake through the application securely.</p><h3>Authentication Flow</h3><p>When a user logs into the application, AWS Cognito handles authentication. After successful login, Cognito issues an ID token that represents the user.</p><p>This token contains identity information such as name, email, and unique user identifiers. It can also contain custom claims, which play an important role in authorization.</p><p>The application then uses this token to initiate a session with Snowflake through a connection pooling mechanism.</p><h3>The Key Challenge</h3><p>During implementation, everything worked correctly on the authentication side. Users were successfully logging in, and tokens were being generated as expected.</p><p>However, Snowflake consistently rejected the token with an “Invalid Token” error.</p><p>At first, the assumption was that role handling could be managed externally at the application layer. Since Cognito roles can change over time, it seemed reasonable to avoid embedding role information directly inside the token to prevent stale authorization states.</p><p>The idea was to pass role information dynamically during Snowflake connection and let Snowflake handle enforcement independently.</p><p>However, this design did not work as expected.</p><h3>Root Cause</h3><p>After extensive investigation through documentation and system behavior analysis, the root cause became clear.</p><p>Snowflake requires that authorization context must be included inside the signed identity token itself. External role injection during session creation is not considered trustworthy because it is not part of the signed identity assertion.</p><p>This is a fundamental principle of secure distributed systems: the identity provider is the only trusted source of both identity and authorization context.</p><h3>Final Solution</h3><p>The final implementation involved updating the Cognito configuration to include a custom role claim inside the ID token.</p><p>This allowed roles to be dynamically assigned in Cognito while still being securely embedded within the token itself.</p><p>Snowflake could then extract the role from the token and apply its internal RBAC system accordingly.</p><p>This resolved the authentication issue and enabled a fully functional end-to-end SSO flow.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rpB8vd3V4RDxDI2uqdmzyA.png" /></figure><h3>Key Learnings</h3><p>This implementation reinforced several important principles in identity and access management systems.</p><p>Authentication and authorization are separate concepts, but authorization must still be anchored in a trusted identity assertion.</p><p>Tokens are immutable, meaning system design must account for delayed propagation of changes such as role updates.</p><p>RBAC remains the most scalable approach for managing permissions in enterprise systems.</p><p>Finally, auditability is not optional in data systems; it is a core requirement for trust, compliance, and operational visibility.</p><h3>Conclusion</h3><p>SSO systems are not just about simplifying login flows. They are about establishing trust boundaries between systems and ensuring that identity and authorization are consistently enforced across distributed components.</p><p>In this implementation, the key realization was that Snowflake does not trust externally injected roles. It only trusts what is embedded within a signed token issued by the identity provider.</p><p>Once this principle was understood, the system naturally aligned into a secure and scalable architecture.</p><p>Understanding these concepts is essential for building modern cloud-native systems that involve identity, security, and data access at scale.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=16e64187a490" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[WAL and CDC Explained: From Crash Recovery to Real-Time Pipelines, Simply]]></title>
            <link>https://medium.com/@safnas.20/wal-and-cdc-explained-from-crash-recovery-to-real-time-pipelines-simply-fd2a4c5ae924?source=rss-bebcf8560484------2</link>
            <guid isPermaLink="false">https://medium.com/p/fd2a4c5ae924</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[backend-development]]></category>
            <dc:creator><![CDATA[Safnas ]]></dc:creator>
            <pubDate>Thu, 12 Mar 2026 07:27:55 GMT</pubDate>
            <atom:updated>2026-03-12T08:33:22.776Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rojHW289mRSKxD3iEI-zbQ.png" /></figure><p><em>A practical guide to Write-Ahead Logging and Change Data Capture — for engineers and the people who work with them</em></p><p>Imagine your database is a restaurant kitchen whiteboard. Orders go up, get erased, get updated. At any moment it shows you the current state. But ask “what happened to Table 7’s order an hour ago?” and nobody knows — the whiteboard only shows <em>now</em>, not <em>how we got here</em>.</p><p>Most teams deal with this by setting up a job that checks the database every few minutes: <em>“Anything new since last time?”</em> It works — until you notice deleted records still showing up in your dashboard, or a race condition silently drops updates, or your production database starts struggling under the query load.</p><p>Here’s the thing: your database has already been solving this problem since day one. It keeps a hidden, append-only log of every single change — every insert, update, and delete — in exact order. It uses this log to survive crashes. The same log can power your real-time pipelines.</p><p>That log is called the <strong>Write-Ahead Log (WAL)</strong>. Reading it systematically is called <strong>Change Data Capture (CDC)</strong>. Together, they replace fragile polling with a reliable event stream — with near-zero overhead on your database.</p><h3>Part 1: The Write-Ahead Log — How Databases Never Lose Data</h3><h3>The core idea</h3><p>WAL is built on one rule: <strong>write the log entry before changing the actual data.</strong></p><p>Here’s why that matters. Say you’re transferring $500 between two bank accounts. The database must subtract from one account and add to the other — two separate operations. If the server crashes between them, without a log you’d lose the $500 entirely. With WAL, the database first writes <em>“intend to move $500 from account A to account B”</em> to the log. On restart, it checks: was this committed? If yes, finish it. If no, roll it back. Either way, the data stays consistent.</p><p>This is what COMMIT actually guarantees. When your application gets a success response, it means the WAL entry is safely on disk — not necessarily that the data pages are written yet. The data pages come later, written by background processes. The log is the source of truth.</p><figure><img alt="Write Ahead Logs writing process in Postgres" src="https://cdn-images-1.medium.com/max/1024/1*LVlVk4Ysogct9Dek-fEf2Q.png" /></figure><h3>What WAL looks like in practice</h3><p>When you run:</p><pre>BEGIN;<br>UPDATE orders SET status = &#39;shipped&#39; WHERE id = 123;<br>COMMIT;</pre><p>PostgreSQL does this, in order:</p><ol><li>Modifies the row <strong>in memory</strong> (not on disk yet)</li><li>Writes a WAL record describing the change — also in memory first</li><li>On COMMIT, <strong>flushes WAL to disk</strong> (this is the fsync call)</li><li>Returns COMMIT OK to your application</li><li>Later, background processes write the actual data pages to disk</li></ol><p>If a crash happens between steps 4 and 5, the WAL replay on restart re-applies the change. Nothing is lost. This is also why you’ll see latency spikes under heavy write load — every commit waits for a disk flush.</p><h3>Replication: the same log, a second consumer</h3><p>Physical replication in PostgreSQL works by streaming WAL records from the primary server to standby servers. The standby just replays the same entries — same block-level changes, same order. This is how you get a hot standby that can take over within seconds if the primary fails.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Syco9jcK4Se9Y1_Qj2Kg7g.png" /></figure><h3>Physical Replication Analogy</h3><p><strong>Think of WAL as a security camera recording every safe crack in exact detail — timestamp, angle, tool marks.</strong> Physical replication is a second TV in the vault manager’s office playing that same raw footage. If the main safe (primary DB) breaks, the backup manager (standby) watches from the last frame and continues exactly — same cracks, same order. Perfect for failover, but tells you nothing about “gold bars moved” vs “cash stolen.”</p><p>The limitation: physical replication sends raw storage blocks. The standby knows <em>which bytes changed on which page</em> — not <em>which row in which table changed and what the values were</em>. That’s fine for failover. But it’s useless if you want to notify your email service when an order ships.</p><p>For that, you need the next layer.</p><h3>Part 2: Change Data Capture — Turning the Log into Events</h3><h3>Why polling breaks at scale</h3><p>The naive approach to syncing data looks like this:</p><pre>// Runs every 5 minutes<br>const lastRun = getLastRunTimestamp();<br>const changes = await db.query(<br>  `SELECT * FROM orders WHERE updated_at &gt; $1`,<br>  [lastRun]<br>);<br>for (const row of changes) {<br>  await syncToWarehouse(row);<br>}<br>await saveTimestamp(new Date());</pre><p>This has four structural problems:</p><ul><li><strong>Deletes are invisible.</strong> A deleted row has no updated_at. It vanishes silently. Your downstream system never finds out.</li><li><strong>Race conditions.</strong> A row updated at the exact millisecond your query runs may be caught or missed depending on timing. These bugs are nearly impossible to reproduce.</li><li><strong>Lag.</strong> You’re always at least one polling interval behind. On a 5-minute cycle, your “real-time” dashboard is actually 5 minutes stale.</li><li><strong>Database load.</strong> Every polling query scans your production tables. On high-traffic systems this becomes significant.</li></ul><h3>Log-based CDC: subscribe instead of poll</h3><p>CDC tools read the WAL directly — the same log already being written for crash recovery. Instead of querying tables, they tail the log and emit a structured event for every committed change:</p><pre>// CDC consumer — no polling, no timestamps, no missed deletes<br>cdc.on(&#39;orders&#39;, async (event) =&gt; {<br>  if (event.op === &#39;c&#39;) { /* INSERT */ }<br>  if (event.op === &#39;u&#39;) { /* UPDATE — has both before and after state */ }<br>  if (event.op === &#39;d&#39;) { /* DELETE — has the row that was deleted */ }<br>});</pre><p>Every change type is captured. Inserts, updates, <em>and</em> deletes. The ordering is guaranteed — you see changes in the exact sequence they were committed. End-to-end latency is typically under a second. And because you’re reading a log that already exists, the overhead on your primary database is near zero.</p><h3>Logical decoding: from raw bytes to row-level events</h3><p>WAL in its raw form is binary block data — meaningful to PostgreSQL internals, opaque to everything else. <strong>Logical decoding</strong> is the process that interprets those raw WAL records and reconstructs row-level changes.</p><p>Raw WAL (physical):</p><pre>Page write: relation 16384, block 42, offset 328<br>Raw bytes: [binary data]</pre><p>After logical decoding (CDC event):</p><pre>{<br>  &quot;table&quot;: &quot;orders&quot;,<br>  &quot;op&quot;: &quot;u&quot;,<br>  &quot;before&quot;: { &quot;id&quot;: 123, &quot;status&quot;: &quot;pending&quot; },<br>  &quot;after&quot;:  { &quot;id&quot;: 123, &quot;status&quot;: &quot;shipped&quot; },<br>  &quot;ts_ms&quot;: 1731409200000<br>}</pre><h3>Polling vs CDC Analogy</h3><p><strong>Polling is a night watchman circling the vault every 5 minutes, hoping to spot changes by flashlight.</strong> Misses silent deletions (empty safe?), races past fast updates, lags behind, wears out the floor (DB load).</p><p><strong>CDC is subscribing to the camera’s motion alerts — no circling needed.</strong> Every vault door open/close/lock triggers an instant text: “Gold added (INSERT),” “Cash removed (DELETE),” “Serial changed (UPDATE).” Sub-second, complete, zero extra work.</p><p>PostgreSQL exposes logical decoding through <strong>replication slots</strong> and <strong>output plugins</strong>. A replication slot is essentially a bookmark — it tracks how far a consumer has read the WAL, and tells PostgreSQL not to delete log entries the consumer hasn’t seen yet. The pgoutput plugin (built into PostgreSQL 10+) handles the translation from binary WAL to structured row events.</p><h3>Part 3: Debezium in Practice — A Real Pipeline</h3><h3>The setup</h3><p>Debezium is the most widely used open-source CDC tool. It runs as a Kafka Connect connector, reads PostgreSQL’s WAL via a replication slot, and publishes row-level change events to Kafka topics. Downstream systems — analytics, search, microservices — consume from those topics independently, at their own pace.</p><pre>PostgreSQL (WAL)<br>    → Replication Slot (logical decoding)<br>        → Debezium Connector<br>            → Kafka Topic: orders.public.orders<br>                → Analytics pipeline<br>                → Search index updater<br>                → Email notification service<br>                → Audit log</pre><p>You configure Debezium with a JSON connector definition:</p><pre>{<br>  &quot;name&quot;: &quot;orders-postgres-connector&quot;,<br>  &quot;config&quot;: {<br>    &quot;connector.class&quot;: &quot;io.debezium.connector.postgresql.PostgresConnector&quot;,<br>    &quot;database.hostname&quot;: &quot;postgres&quot;,<br>    &quot;database.port&quot;: &quot;5432&quot;,<br>    &quot;database.user&quot;: &quot;cdc_user&quot;,<br>    &quot;database.dbname&quot;: &quot;app_db&quot;,<br>    &quot;database.server.name&quot;: &quot;appdb&quot;,<br>    &quot;plugin.name&quot;: &quot;pgoutput&quot;,<br>    &quot;slot.name&quot;: &quot;debezium_orders_slot&quot;,<br>    &quot;publication.name&quot;: &quot;debezium_orders_pub&quot;,<br>    &quot;table.include.list&quot;: &quot;public.orders&quot;,<br>    &quot;snapshot.mode&quot;: &quot;initial&quot;<br>  }<br>}</pre><p>Key fields to understand:</p><p>Field What it does plugin.name: pgoutput Uses PostgreSQL&#39;s built-in logical decoding plugin — no extra extensions needed slot.name Creates a replication slot to track read position. This is what can cause disk issues if Debezium goes offline (more below) snapshot.mode: initial On first start, snapshots all existing rows, then switches to streaming WAL changes table.include.list Only watch specific tables — reduces noise and WAL processing overhead</p><h3>What the event looks like</h3><p>When UPDATE orders SET status=&#39;shipped&#39; WHERE id=123 commits, Debezium emits:</p><pre>{<br>  &quot;payload&quot;: {<br>    &quot;before&quot;: { &quot;id&quot;: 123, &quot;status&quot;: &quot;pending&quot; },<br>    &quot;after&quot;:  { &quot;id&quot;: 123, &quot;status&quot;: &quot;shipped&quot; },<br>    &quot;op&quot;: &quot;u&quot;,<br>    &quot;ts_ms&quot;: 1731409200000,<br>    &quot;source&quot;: {<br>      &quot;table&quot;: &quot;orders&quot;,<br>      &quot;lsn&quot;: 12345678<br>    }<br>  }<br>}</pre><ul><li>before / after — the full row state before and after the change</li><li>op — c (insert), u (update), d (delete), r (read, during snapshot)</li><li>lsn — Log Sequence Number, the exact position in the WAL. Useful for deduplication and ordering guarantees</li></ul><p>Your email service reads op: u + after.status: shipped and fires the notification. Your analytics system increments a counter. Your search index re-indexes the document. All from one event. All decoupled. No extra database queries.</p><h3>Snapshot modes — picking the right one</h3><p>When Debezium first connects to an existing database, it needs to decide what to do with data that already exists:</p><p>Mode What it does When to use initial Full table snapshot, then stream Default — when you need all historical data incremental Chunked snapshot, runs in background Large tables where locking is a concern schema_only Captures schema, no existing rows When you only care about future changes never Skip snapshot entirely When downstream already has the data</p><p>For large tables, initial mode can lock tables briefly and take significant time. incremental mode snapshots in configurable chunks and runs alongside live streaming — less disruptive for production systems.</p><h3>Part 4: Production Concerns — Where Teams Get Burned</h3><h3>Replication slots and disk growth</h3><p>This is the most common production failure mode for CDC deployments.</p><p>PostgreSQL’s replication slots keep a bookmark of how far each consumer has read. The database <strong>will not delete WAL segments</strong> until all slots have consumed them. If Debezium goes offline — even for a few hours on a write-heavy system — WAL accumulates. On busy databases this can be gigabytes per hour.</p><p>Teams have discovered this the hard way: Debezium connector restarts, nobody notices the slot falling behind, disk fills over 48 hours, database stops accepting writes, production is down.</p><p><strong>Two settings to configure immediately:</strong></p><pre>-- Check current slot lag<br>SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag<br>FROM pg_replication_slots;</pre><pre># In postgresql.conf<br>max_slot_wal_keep_size = 10GB  # PostgreSQL will invalidate the slot rather than fill the disk</pre><p>max_slot_wal_keep_size is your safety net. If a slot falls too far behind, PostgreSQL marks it invalid rather than letting WAL grow unbounded. Debezium will need to re-snapshot, but your database stays alive. Set this. Monitor slot lag. Alert when it crosses a threshold.</p><h3>Schema evolution</h3><p>What happens when you add a column, rename one, or change a type? CDC events have a schema — the structure of before and after fields is defined at event creation time. Without handling this, a schema change in PostgreSQL can break every downstream consumer simultaneously.</p><p>Debezium integrates with <strong>Schema Registry</strong> (Confluent or compatible) to version event schemas. When a column is added, a new schema version is registered. Consumers that support forward compatibility continue working. Consumers that need to handle the new column can upgrade independently.</p><p>Treat database schema changes as API changes. Your downstream systems have a contract with your event schema. Break it carelessly and you break your entire pipeline.</p><h3>WAL configuration tradeoffs</h3><p>A few PostgreSQL settings directly affect both crash recovery safety and CDC behavior:</p><p>Setting Effect CDC impact synchronous_commit = off Commits return before WAL is flushed — faster, but risks losing last few transactions on crash Slightly faster, small data loss window wal_level = logical Required for logical decoding / CDC Must be set for Debezium to work max_wal_senders Max concurrent WAL streaming connections Must accommodate both physical replicas and CDC slots checkpoint_completion_target How aggressively checkpoints spread I/O Affects write amplification under heavy load</p><p>wal_level = logical is the minimum requirement for CDC. It produces slightly more WAL than replica level but enables the row-level information logical decoding needs.</p><h3>Part 5: When to Use What</h3><p>Need Right tool Hot standby for failover Physical replication (WAL streaming) Sync to data warehouse / analytics Log-based CDC (Debezium → Kafka) Keep search index in sync with DB Log-based CDC Event-driven microservices reacting to DB changes Log-based CDC Cache invalidation on data changes Log-based CDC Audit log of every change Log-based CDC Quick prototype, lag acceptable Polling (but migrate when you scale)</p><p>Physical replication and CDC are not mutually exclusive. Most production setups run both: a physical standby for HA failover, and Debezium for event streaming — two independent consumers of the same WAL, configured separately, running without conflict.</p><p>The deciding question: <strong>are you keeping a database copy in sync, or broadcasting changes to non-database systems?</strong> The former is replication. The latter is CDC.</p><h3>The Big Picture</h3><p>Your database has always maintained a complete, ordered, durable record of every change. WAL exists because databases need to survive crashes — but that same log is the most reliable change stream you’ll ever have access to.</p><p>Polling with updated_at timestamps gives you an approximation of changes — incomplete, delayed, and blind to deletes. Reading the WAL gives you the actual changes — complete, ordered, and immediate.</p><p>The infrastructure shift: instead of every downstream system querying your database on a schedule, they subscribe to a Kafka topic and react to events as they arrive. Your database does less work. Your systems are more decoupled. Your data is more consistent.</p><p>The operational cost: you now have replication slots to monitor, WAL retention to manage, and schema evolution to think about carefully. These are manageable problems with the right alerting and configuration. They’re much smaller problems than silently wrong data in your warehouse or missed deletes in your dashboard.</p><h3>Key Takeaways</h3><ul><li><strong>WAL</strong> guarantees durability by writing log entries before data pages. COMMIT returns only after WAL is flushed to disk.</li><li><strong>Physical replication</strong> streams raw WAL blocks to standbys — great for HA, opaque to application logic.</li><li><strong>Logical decoding</strong> interprets WAL into row-level events — inserts, updates, deletes with before/after values.</li><li><strong>CDC</strong> reads those decoded events and broadcasts them to downstream systems via Kafka or similar.</li><li><strong>Debezium</strong> connects PostgreSQL’s replication slot to Kafka. Requires wal_level = logical. Configure snapshot mode based on your table sizes and locking tolerance.</li><li><strong>Replication slots retain WAL</strong> until consumed. Always set max_slot_wal_keep_size and monitor slot lag. This is the #1 production failure mode.</li><li><strong>Schema changes</strong> are breaking changes to your event consumers. Integrate Schema Registry from the start.</li></ul><h3>Further Reading</h3><ul><li><a href="https://www.postgresql.org/docs/current/wal-intro.html">PostgreSQL WAL Documentation</a> — official source on WAL guarantees and configuration</li><li><a href="https://debezium.io/documentation/reference/connectors/postgresql.html">Debezium PostgreSQL Connector Docs</a> — full connector reference, snapshot modes, slot configuration</li><li><a href="https://www.lotharschulz.info/2025/10/15/postgresql-cdc-best-practices-managing-wal-growth-and-replication-slots/">Managing WAL Growth and Replication Slots</a> — production operational best practices</li><li><a href="https://airbyte.com/blog/a-guide-to-logical-replication-and-cdc-in-postgresql">PostgreSQL Replication Guide — Airbyte</a> — logical replication and CDC deep dive</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fd2a4c5ae924" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>