DEV Community: Muhammad Hadeed Tariq

Understanding Durability in PostgreSQL The "D" in ACID

Muhammad Hadeed Tariq — Fri, 18 Jul 2025 07:10:18 +0000

In our previous deep-dive articles on ACID, we explored Isolation in PostgreSQL, understanding how isolation levels work under the hood. You can revisit that discussion here.

It's been a few months since that article (life happens - juggling a job, projects, and everything in between), but I'm now back and committed to writing consistently. I craft these articles not only for you but also as a resource for my future self, which is why I delve deeply into specific subtopics and include practical prototypes for better clarity. Today, we're going to break down the final, crucial pillar of the ACID model: Durability.

What Durability Means
How PostgreSQL Manages Durability Under the Hood
Write-Ahead Logging (WAL): Durability’s Foundation
Dirty Pages and Deferred Disk Writes
Crash Recovery: Why WAL Exists in the First Place
Practical Implications: A Real-World Banking Scenario
Distributed Transactions and Durability
Summary: Why Durability Is Non-Negotiable
Further Reading

1. What durability means?

Durability means that once a transaction is committed, its changes must persist no matter what. Even if the database server crashes or there's a power loss, the committed changes must still be there when the system comes back up.

Sounds simple? The internal mechanisms aren't.

2. How PostgreSQL manages durability under the hood

A common misconception especially among developers new to PostgreSQL is that when a write query is executed (like INSERT, UPDATE, or DELETE), the changes are immediately written to disk. Contrary to what many assume, PostgreSQL does not immediately write changes to disk when a query is executed. Instead, it follows a carefully layered process that balances speed with reliability.

Let’s examine that process in structured steps.

2.1 Modifications Begin in Memory: The Buffer Cache

All write operations in PostgreSQL first modify data in memory, not on disk. This memory region is known as the buffer cache.

PostgreSQL stores all data in fixed-size blocks called data pages (typically 8KB).
When a row is updated or inserted, the relevant data page is loaded into memory (if not already present).
The modification is applied directly to the in-memory copy of the page.

This design greatly enhances performance by minimizing disk I/O. However, it introduces a serious risk: memory is volatile. If the system crashes now, those changes are gone.

So how does PostgreSQL protect those changes? That’s where the next mechanism comes in.

3. Write-Ahead Logging (WAL): Durability’s Foundation

To guarantee durability, PostgreSQL uses the Write-Ahead Log (WAL) a journal-like structure that records what changes are about to happen before they’re applied to disk.

3.1 What the WAL Does

For every data modification, PostgreSQL generates a WAL record describing the change.
This WAL record is written and flushed to disk before the transaction is considered committed.
Only after this flush does PostgreSQL report the transaction as "successfully committed."

This ensures that even if a crash occurs, PostgreSQL can use the WAL to replay and reapply changes upon recovery.

You can dive deeper into this concept in my article on Atomicity, where I also provide a prototype WAL simulation in Go to illustrate how the logging and recovery process works.

4. Dirty Pages and Deferred Disk Writes

Even after WAL is safely persisted, the actual data pages in memory are not immediately written to disk. These modified pages are known as dirty pages.

4.1 When Are Dirty Pages Flushed?

Checkpoints: Periodic events where PostgreSQL flushes dirty pages to disk to reduce recovery time.
Background Writer: A dedicated process that writes dirty pages incrementally, smoothing out I/O spikes.

This design allows PostgreSQL to batch writes and avoid frequent disk operations, which boosts throughput without compromising durability since WAL is already safely stored.

To understand the interaction between buffer cache, background writer, and checkpointing in more depth, refer to this analysis of PostgreSQL's memory components.

5. Crash Recovery: Why WAL Exists in the First Place

Let’s briefly imagine a system without WAL. If all changes live only in memory, and the server crashes, those changes vanish even if users were told the transaction succeeded.

That’s a catastrophic failure of durability.

WAL ensures that PostgreSQL can recover by:

Scanning the WAL log on startup
Replaying committed changes to reconstruct the latest consistent state

This recovery process is automatic and forms the backbone of PostgreSQL's fault tolerance.

6. Practical Implications: A Real-World Banking Scenario

Consider this use case: a user initiates a bank transfer. The application confirms success, but the server crashes milliseconds later.

If durability is not guaranteed, that transaction might never make it to the ledger:

The sender’s balance was reduced in memory but not written to disk.
The recipient never receives the money.

Such a failure damages trust and introduces serious compliance issues. Durability mechanisms like WAL ensure that confirmed transactions remain valid, no matter what.

7. Distributed Transactions and Durability

Durability becomes even more challenging in distributed systems, where data spans multiple nodes or databases.

To ensure consistency across all participants, protocols like:

Two-Phase Commit (2PC)
Three-Phase Commit (3PC)
Consensus algorithms (Raft, Paxos)

...are used to make sure either:

All nodes commit the transaction
Or all abort ensuring atomicity and durability even in partial failures

While PostgreSQL itself is a single-node system, extensions like Citus or external systems like CockroachDB apply these principles in distributed settings.

8. Summary: Why Durability Is Non-Negotiable

Durability in PostgreSQL is not an optional feature it is integral to correctness. Here's a recap of how it is achieved:

Changes are made to memory (buffer cache)
A WAL record is generated and flushed to disk before commit
Dirty pages remain in memory and are flushed later via checkpoints or background writer
WAL enables crash recovery by replaying committed changes

Without these safeguards, even successful transactions can silently vanish, leading to irreparable data loss.

Understanding Durability in PostgreSQL The "D" in ACID

Muhammad Hadeed Tariq — Fri, 18 Jul 2025 07:10:18 +0000

In our previous deep-dive articles on ACID, we explored Isolation in PostgreSQL, understanding how isolation levels work under the hood. You can revisit that discussion here.

What Durability Means
How PostgreSQL Manages Durability Under the Hood
Write-Ahead Logging (WAL): Durability’s Foundation
Dirty Pages and Deferred Disk Writes
Crash Recovery: Why WAL Exists in the First Place
Practical Implications: A Real-World Banking Scenario
Distributed Transactions and Durability
Summary: Why Durability Is Non-Negotiable
Further Reading

1. What durability means?

Sounds simple? The internal mechanisms aren't.

2. How PostgreSQL manages durability under the hood

Let’s examine that process in structured steps.

2.1 Modifications Begin in Memory: The Buffer Cache

All write operations in PostgreSQL first modify data in memory, not on disk. This memory region is known as the buffer cache.

PostgreSQL stores all data in fixed-size blocks called data pages (typically 8KB).
When a row is updated or inserted, the relevant data page is loaded into memory (if not already present).
The modification is applied directly to the in-memory copy of the page.

This design greatly enhances performance by minimizing disk I/O. However, it introduces a serious risk: memory is volatile. If the system crashes now, those changes are gone.

So how does PostgreSQL protect those changes? That’s where the next mechanism comes in.

3. Write-Ahead Logging (WAL): Durability’s Foundation

To guarantee durability, PostgreSQL uses the Write-Ahead Log (WAL) a journal-like structure that records what changes are about to happen before they’re applied to disk.

3.1 What the WAL Does

For every data modification, PostgreSQL generates a WAL record describing the change.
This WAL record is written and flushed to disk before the transaction is considered committed.
Only after this flush does PostgreSQL report the transaction as "successfully committed."

This ensures that even if a crash occurs, PostgreSQL can use the WAL to replay and reapply changes upon recovery.

You can dive deeper into this concept in my article on Atomicity, where I also provide a prototype WAL simulation in Go to illustrate how the logging and recovery process works.

4. Dirty Pages and Deferred Disk Writes

Even after WAL is safely persisted, the actual data pages in memory are not immediately written to disk. These modified pages are known as dirty pages.

4.1 When Are Dirty Pages Flushed?

Checkpoints: Periodic events where PostgreSQL flushes dirty pages to disk to reduce recovery time.
Background Writer: A dedicated process that writes dirty pages incrementally, smoothing out I/O spikes.

This design allows PostgreSQL to batch writes and avoid frequent disk operations, which boosts throughput without compromising durability since WAL is already safely stored.

To understand the interaction between buffer cache, background writer, and checkpointing in more depth, refer to this analysis of PostgreSQL's memory components.

5. Crash Recovery: Why WAL Exists in the First Place

Let’s briefly imagine a system without WAL. If all changes live only in memory, and the server crashes, those changes vanish even if users were told the transaction succeeded.

That’s a catastrophic failure of durability.

WAL ensures that PostgreSQL can recover by:

Scanning the WAL log on startup
Replaying committed changes to reconstruct the latest consistent state

This recovery process is automatic and forms the backbone of PostgreSQL's fault tolerance.

6. Practical Implications: A Real-World Banking Scenario

Consider this use case: a user initiates a bank transfer. The application confirms success, but the server crashes milliseconds later.

If durability is not guaranteed, that transaction might never make it to the ledger:

The sender’s balance was reduced in memory but not written to disk.
The recipient never receives the money.

Such a failure damages trust and introduces serious compliance issues. Durability mechanisms like WAL ensure that confirmed transactions remain valid, no matter what.

7. Distributed Transactions and Durability

Durability becomes even more challenging in distributed systems, where data spans multiple nodes or databases.

To ensure consistency across all participants, protocols like:

Two-Phase Commit (2PC)
Three-Phase Commit (3PC)
Consensus algorithms (Raft, Paxos)

...are used to make sure either:

All nodes commit the transaction
Or all abort ensuring atomicity and durability even in partial failures

While PostgreSQL itself is a single-node system, extensions like Citus or external systems like CockroachDB apply these principles in distributed settings.

8. Summary: Why Durability Is Non-Negotiable

Durability in PostgreSQL is not an optional feature it is integral to correctness. Here's a recap of how it is achieved:

Changes are made to memory (buffer cache)
A WAL record is generated and flushed to disk before commit
Dirty pages remain in memory and are flushed later via checkpoints or background writer
WAL enables crash recovery by replaying committed changes

Without these safeguards, even successful transactions can silently vanish, leading to irreparable data loss.

Hands-On with P2P Networks: Building a Messaging System

Muhammad Hadeed Tariq — Mon, 12 May 2025 04:46:46 +0000

Imagine a world where communication happens directly between devices no central server, no middleman, just peer-to-peer. That’s the essence of pure peer-to-peer (P2P) networks. These networks are resilient, decentralized, and increasingly relevant in systems like IoT Device Communication, file sharing, and secure messaging.

In this article, we’ll break down how a pure peer-to-peer system works by building a simple messaging system from scratch using Golang. We'll explore core P2P concepts like node identification, message routing, and decentralized lookup, and implement them step by step in a way that’s easy to follow even if you’re not a Go expert.

By the end, you’ll not only understand the theory behind P2P communication but also have a working messaging network where nodes can discover and message each other without relying on any central server.

Let’s dive into the mechanics behind this powerful network design

My Journey: From BitTorrent to DHTs

While writing my own mini BitTorrent client to download Linux distributions, I stumbled upon the term pure peer-to-peer network, and more specifically, Kademlia. At first, these terms felt overwhelming, but I kept exploring and things started to click.

That’s when I came across something fundamental the concept of a Distributed Hash Table (DHT). So first, I’ll break down the core terms used in DHTs to build a solid foundation. Then, we’ll move on to the prototype project I built, which will make everything much clearer through practical demonstration.

What is a DHT?

A Distributed Hash Table (DHT) is basically like a hash table, but spread across many peers in a network. A regular hash table stores (key, value) pairs and offers fast lookups, often in O(1) time complexity, because the data is kept in one place usually in memory.

But a DHT works differently. Here, the key-value pairs aren’t stored in one table they’re distributed across multiple nodes in the network. For example, each node (often identified by its IP address and a unique Node ID) holds a portion of the data. So, when you want to find a key, you don’t search a local table you need to find the right node that holds it.

That’s where routing algorithms come in they help direct the query to the correct node. Among several DHT routing algorithms, Kademlia stands out as one of the most efficient, because it uses a clever XOR-based distance metric for routing, and it can locate a key in around O(log N) time, where N is the number of nodes in the network.

What is Kademlia?

Now you might be wondering, what is Kademlia?

Kademlia is a routing algorithm used in DHT-based peer-to-peer systems. It’s built on a clever idea: instead of using geographical or IP-based distances, it uses something called the XOR distance metric.

Here’s how it works:

Each peer (or node) has a Node ID, typically a 160-bit (20-byte) value.
When you want to find a node or a key, you XOR the target ID with your own Id.
The result gives you a distance, and the smaller the value (closer the XOR is to 0), the closer the node is to you in the DHT.
The most significant bit (from the left) where two IDs differ determines the "distance" in the network this forms the basis of the routing.

I get that all of this may sound a bit maze-like right now, but don’t worry once I show you an example, it’ll start making a lot more sense.

Right now, I’m just laying the foundation explaining the basic concepts you’ll need to understand Kademlia and how it helps structure a pure peer-to-peer network.

A guide to routing table

As we dive deeper into Kademlia, another important concept comes up is routing table. So, let’s break that down.

A routing table is a data structure each node maintains to keep track of other nodes in the network. It is made up of K-buckets now you might be wondering, what are K-buckets?

Think of K-buckets as containers that store information about the closest nodes to a given node, based on XOR distance. Each K-bucket typically holds information about up to K nodes in many implementations, K is 20.

Now, the number of K-buckets a node has depends on the bit-length of the Node ID. For example, if the Node ID is 160 bits long (as in many real-world systems like BitTorrent), then the routing table will have 160 K-buckets one for each possible bit prefix length.

Messaging System

So as you get familiar with the terms used in the pure peer to peer network now we looked in to our example which is a pure peer to peer messaging system

Let’s start with the NodeID. In our example, each peer is assigned a unique NodeID, which for simplicity is just 16 bits (2 bytes). This keeps things easy to follow, though in real systems like Kademlia, the IDs are usually 160 bits. The NodeID is simply an array of bytes and can be represented as a hexadecimal string for readability.

const IdLength = 8
const IdBits = 16

type NodeID [IdBits / IdLength]byte

func (id NodeID) String() string {
    return hex.EncodeToString(id[:])
}

To measure how "far apart" two nodes are in the network, we use the XOR distance metric a fundamental concept in DHT (Distributed Hash Table) systems. The XOR of two node IDs gives a new value that represents how different they are bit-by-bit. A smaller XOR result means the nodes are closer in the ID space.

func (id NodeID) XOR(otherId NodeID) *big.Int {
    var result NodeID
    for i := 0; i < len(id); i++ {
        result[i] = id[i] ^ otherId[i]
    }
    return new(big.Int).SetBytes(result[:])
}

Once we can uniquely identify and compare distances between peers, the next step is to maintain a list of known peers. This is where K-Buckets come into play. A K-Bucket is simply a container that stores a small list of peer contacts typically capped at a fixed number like 20 in real systems, but we’ll use 2 here for demonstration purposes.

Each contact holds essential info about a peer: its NodeID, network address (e.g., IP and port), and the last time it was seen online.

const contactSize = 2

type Contacts struct {
    Id           NodeID
    Address      string
    last_seen_at time.Time
}

type KBucket struct {
    contacts []Contacts
}

We can search the bucket for a specific peer by ID using the Find method, which returns the address if the node is found.

func (kb *KBucket) Find(id NodeID) string {
    for _, c := range kb.contacts {
        if c.Id == id {
            return c.Address
        }
    }
    return ""
}

To maintain the K-Bucket, we use the Add method. It checks if the node already exists. If there's space, it adds the new contact. If the bucket is full, it tries to evict the least recently seen contact using the Evict method.

func (kb *KBucket) Add(contact Contacts) bool {
    for _, c := range kb.contacts {
        if c.Id == contact.Id {
            return true
        }
    }

    if len(kb.contacts) < contactSize {
        kb.contacts = append(kb.contacts, contact)
        return true
    } else {
        evicted := kb.Evict()
        if evicted {
            kb.contacts = append(kb.contacts, contact)
            return true
        } else {
            fmt.Println("There is no space in this bucket")
            return false
        }
    }
}

Eviction is a key part of a robust peer list. When the bucket is full, we sort the contacts by their last_seen_at timestamp (oldest first) and attempt to ping the oldest contact. If it's offline, we safely remove it and make room for a new one.

func (kb *KBucket) Evict() bool {
    sort.Slice(kb.contacts, func(i, j int) bool {
        return kb.contacts[i].last_seen_at.Before(kb.contacts[j].last_seen_at)
    })

    contact := kb.contacts[0]
    _, err := net.Dial("tcp", contact.Address)
    if err != nil {
        kb.contacts = append(kb.contacts[:0], kb.contacts[1:]...)
        return true
    }
    return false
}

This strategy aligns with Kademlia's Least Recently Seen (LRS) eviction policy ensuring that more active and reliable peers remain in the routing table while stale or unreachable peers are replaced. This is essential for maintaining a responsive and scalable peer-to-peer network.

Once the structure of the K-Buckets is in place, we move on to the core component of peer discovery: the Routing Table. The routing table acts as a high-level container for all buckets and is responsible for managing peer lookups and updates across the network. It consists of a slice of K-Buckets and the current node's ID.

type RoutingTable struct {
    buckets []KBucket
    selfId  NodeID
}

Each peer maintains its own routing table, which contains buckets representing various distance ranges from the current node. When a new contact is introduced, the appropriate bucket is determined based on the Most Significant Bit (MSB) index of the XOR distance between the current node ID and the contact's ID. This is handled by the GetMSBIndex utility function:

func GetMSBIndex(distance *big.Int) int {
    if distance.Sign() == 0 {
        return -1
    }
    return distance.BitLen() - 1
}

This function ensures that a contact is placed in the correct bucket based on its logical distance from the current node. If the calculated distance is zero (i.e., the node is attempting to add itself), the function returns -1, and the contact is ignored.

The Add method of the routing table adds a given contact to the correct bucket, if possible:

func (rt *RoutingTable) Add(contact Contacts) {
    distance := rt.selfId.XOR(contact.Id)
    index := GetMSBIndex(distance)

    if index >= 0 {
        isAdded := rt.buckets[index].Add(contact)
        if isAdded {
            fmt.Printf("The contact is added to bucket: %d \n", index)
        } else {
            fmt.Println("Not added")
        }
    } else {
        fmt.Println("Same peer can't connect with each other")
    }
}

Another critical feature is the ability to find the closest peers to a target node ID. This is particularly useful during message forwarding and when bootstrapping the network. The FindClosestContacts method performs a global scan across all buckets, sorts the contacts by XOR distance to the target ID, and returns the closest k contacts:

func (rt *RoutingTable) FindClosestContacts(targetID NodeID, k int) []Contacts {
    var allContacts []Contacts

    for _, bucket := range rt.buckets {
        allContacts = append(allContacts, bucket.contacts...)
    }

    sort.Slice(allContacts, func(i, j int) bool {
        d1 := targetID.XOR(allContacts[i].Id)
        d2 := targetID.XOR(allContacts[j].Id)
        return d1.Cmp(d2) == -1
    })

    if len(allContacts) > k {
        return allContacts[:k]
    }
    return allContacts
}

To support the initialization of nodes and routing tables, we define utility constructors. NewNodeId generates a hashed node ID using SHA-1, and NewRoutingTable initializes the routing table with empty K-Buckets.

func NewNodeId(data []byte) NodeID {
    hash := sha1.Sum(data[:])
    var newId NodeID
    copy(newId[:], hash[:len(newId)])
    return newId
}

func NewRoutingTable(selfID NodeID) *RoutingTable {
    buckets := make([]KBucket, IdBits)
    for i := 0; i < len(buckets); i++ {
        buckets[i] = KBucket{contacts: make([]Contacts, 0, contactSize)}
    }
    return &RoutingTable{buckets: buckets, selfId: selfID}
}

Adding Messaging Capability to Peers

With the routing infrastructure in place, we can now add real messaging capabilities between nodes in the network. This is crucial for simulating peer-to-peer interactions like sending commands, exchanging data, or simulating real-world communication in a distributed system.

We introduce a MessagingPeer structure, which encapsulates the logic to start a server, send and receive messages, and use the routing table to locate other peers in the network.

1. Message Structure

We define a Messages struct to encapsulate message-related data:

type Messages struct {
    SenderId       NodeID
    ReceiverId     NodeID
    MessageContent string
    MessageId      string
}

SenderId and ReceiverId identify the message endpoints.
MessageContent holds the actual message text.
MessageId can be used for tracking, deduplication, or threading.

2. MessagingPeer Struct

We now define a MessagingPeer to wrap the node's messaging functionality:

type MessagingPeer struct {
    ID           NodeID
    Messages     []Messages
    Port         int
    Address      string
    RoutingTable *RoutingTable
}

Messages keeps a record of received messages.
RoutingTable is used to find other nodes (reusing our earlier routing logic).
Each peer listens on its own Port.

3. Starting the Server

Each peer runs a TCP server to receive messages:

func (mp *MessagingPeer) StartServer() {
    ln, err := net.Listen("tcp", fmt.Sprintf(":%d", mp.Port))
    if err != nil {
        log.Fatalf("Failed to start server: %v", err)
    }
    defer ln.Close()

    for {
        conn, err := ln.Accept()
        if err != nil {
            continue
        }
        go mp.handleConnection(conn)
    }
}

The server listens on its configured port.
Incoming connections are handled concurrently.

4. Receiving and Storing Messages

When a peer receives a message, it is deserialized and stored:

func (mp *MessagingPeer) handleConnection(conn net.Conn) {
    defer conn.Close()
    buf := make([]byte, 1024)
    n, err := conn.Read(buf)
    if err != nil {
        return
    }

    var msg Messages
    err = json.Unmarshal(buf[:n], &msg)
    if err != nil {
        return
    }

    mp.Messages = append(mp.Messages, msg)
}

This allows each peer to act as a receiver in the network.

5. Local Node Lookup

We add a helper function that wraps our existing routing logic to find nodes by ID:

func (mp *MessagingPeer) FindNode(targetID NodeID) []Contacts {
    return mp.RoutingTable.FindClosestContacts(targetID, contactSize)
}

This uses our bucket-based routing table to get nearby contacts.

6. Iterative Lookup Across Peers

If a node is not found locally, we query other known peers:

func (mp *MessagingPeer) IterativeFindNode(targetId NodeID, knownPeers map[string]*MessagingPeer) []Contacts {
    visited := make(map[string]bool)
    shortList := mp.RoutingTable.FindClosestContacts(targetId, contactSize)
    closest := shortList

    for {
        newShortList := []Contacts{}
        for _, contact := range shortList {
            idStr := contact.Id.String()
            if visited[idStr] {
                continue
            }
            visited[idStr] = true
            peer := knownPeers[idStr]
            if peer == nil {
                continue
            }
            closerContacts := peer.FindNode(targetId)
            for _, c := range closerContacts {
                if !visited[c.Id.String()] {
                    newShortList = append(newShortList, c)
                }
            }
        }
        all := append(closest, newShortList...)
        sort.Slice(all, func(i, j int) bool {
            return targetId.XOR(all[i].Id).Cmp(targetId.XOR(all[j].Id)) < 0
        })
        if len(all) > contactSize {
            all = all[:contactSize]
        }
        if EqualContacts(closest, all) {
            break
        }
        closest = all
        shortList = newShortList
    }
    return closest
}

Works similarly to Kademlia’s node lookup.
Stops when no closer peers are found.

7. Sending a Message to Another Peer

We now allow one peer to send a message to another, using either the routing table or iterative search:

func (mp *MessagingPeer) SendMessage(content string, peerId NodeID, network map[string]*MessagingPeer) (string, error) {
    if mp.ID == peerId {
        return "", fmt.Errorf("same peer can't send message to itself")
    }

    distance := mp.ID.XOR(peerId)
    index := GetMSBIndex(distance)
    peerAddress := mp.RoutingTable.buckets[index].Find(peerId)

    if peerAddress == "" {
        closest := mp.IterativeFindNode(peerId, network)
        for _, con := range closest {
            if con.Id == peerId {
                peerAddress = con.Address
                break
            }
        }
    }

    if peerAddress == "" {
        return "", fmt.Errorf("could not find peer address")
    }

    conn, err := net.Dial("tcp", peerAddress)
    if err != nil {
        return "", fmt.Errorf("failed to connect to peer: %w", err)
    }
    defer conn.Close()

    msg := &Messages{
        SenderId:       mp.ID,
        ReceiverId:     peerId,
        MessageContent: content,
        MessageId:      "Random",
    }

    msgBytes, err := json.Marshal(msg)
    if err != nil {
        return "", fmt.Errorf("failed to serialize message: %w", err)
    }

    _, err = conn.Write(msgBytes)
    if err != nil {
        return "", fmt.Errorf("failed to send message: %w", err)
    }

    return "message sent successfully", nil
}

Attempts direct routing first.
Falls back to iterative peer discovery if needed.
Message is serialized as JSON and sent via TCP.

8. Peer Initialization

Finally, we define a constructor for MessagingPeer:

func NewMessagingPeer(port int, address string) *MessagingPeer {
    add := fmt.Sprintf("%s:%d", address, port)
    selfID := NewNodeId([]byte(add))
    return &MessagingPeer{
        ID:           selfID,
        Messages:     make([]Messages, 0),
        Port:         port,
        Address:      add,
        RoutingTable: NewRoutingTable(selfID),
    }
}

This sets the NodeID using the address + port as a seed, ensuring uniqueness in the network.

Main Function: Bringing It All Together

As all the components are now in place for our simple peer-to-peer messaging system, we move on to our main function, where we use everything we’ve built so far peer creation, server start-up, routing table population, and the actual message sending mechanism.

package main

import (
    "fmt"
    "time"
    "github.com/HadeedTariq/p-to-p-msg-system-kademlia/algo"
)

We import the necessary packages including our algo package where all the peer and routing logic lives.

Creating and Starting Peers

peerA := algo.NewMessagingPeer(8000, "localhost")
go peerA.StartServer()
peerB := algo.NewMessagingPeer(8001, "localhost")
go peerB.StartServer()
peerC := algo.NewMessagingPeer(8002, "localhost")
go peerC.StartServer()
peerD := algo.NewMessagingPeer(8003, "localhost")
go peerD.StartServer()
peerE := algo.NewMessagingPeer(8004, "localhost")
go peerE.StartServer()
time.Sleep(1 * time.Second)

We create five peers (A through E), each listening on a unique port.

Each MessagingPeer starts its own server in a goroutine to handle incoming messages concurrently.

We add a small delay using time.Sleep to ensure all servers are up before proceeding.

Manually Populating Routing Tables

peerB.RoutingTable.Add(algo.Contacts{
    Id: peerA.ID,
    Address: peerA.Address,
})
peerB.RoutingTable.Add(algo.Contacts{
    Id: peerC.ID,
    Address: peerC.Address,
})
peerC.RoutingTable.Add(algo.Contacts{
    Id: peerD.ID,
    Address: peerD.Address,
})
peerD.RoutingTable.Add(algo.Contacts{
    Id: peerE.ID,
    Address: peerE.Address,
})

In a real distributed network, peers discover each other via ping or bootstrap mechanisms.

Here, we manually add entries to some routing tables to simulate an imperfect but realistic partial network view.

Peer B knows A and C.
C knows D.
D knows E.

This mimics limited knowledge in a real-world scenario.

Simulating the Network Map

network := map[string]*algo.MessagingPeer{}
network[peerA.ID.String()] = peerA
network[peerB.ID.String()] = peerB
network[peerC.ID.String()] = peerC
network[peerD.ID.String()] = peerD
network[peerE.ID.String()] = peerE

We construct a network map to simulate our distributed environment.

This acts as a directory the sending peer can use during IterativeFindNode().

Note: This is only for simulation. In a real P2P system, peers wouldn’t have access to this map directly.

Sending the Message

msg, err := peerB.SendMessage("Hello from B to E", peerE.ID, network)
if err != nil {
    fmt.Println(err)
}
fmt.Println(msg)

Here’s where the actual communication happens:

Peer B tries to send a message to Peer E.
Since B doesn't know E directly, it uses the iterative lookup we implemented earlier to find E via the known path B → C → D → E.
Once found, it establishes a TCP connection and sends the message.

If successful, we see "message sent successfully" printed.

Keeping the Program Alive

select {}

We block the main goroutine using select {} so all servers stay alive and continue listening for incoming messages.

Conclusion

So as you can see, we’ve built our own small peer-to-peer messaging system from scratch! I’ve tried to explain each concept in a simple and easy-to-understand way so you don’t need to copy and paste everything blindlyjust understand the flow and play around with the code.
You can find the complete source code here: GitHub Repository
I also write about database-related topics and other backend concepts. Feel free to check out more of my work here: @HadeedTariq on Dev.to

Before I end this post, I want to take a moment to express my deep love and unwavering support for our Palestinian Muslim brothers and sisters 🇵🇸. Their lives, their struggles, and their voices matter. In a world that too often turns a blind eye, we must continue to speak up and stand with the oppressed. May justice rise, may peace prevail, and may the people of Palestine live with dignity, freedom, and hope. You are not forgotten your life matters.The recent surge in terrorist activities and the brutal actions by Israel are unequivocally condemned.

Understanding Isolation in PostgreSQL: The “I” in ACID

Muhammad Hadeed Tariq — Sun, 04 May 2025 11:39:43 +0000

In the previous article, we explored the Consistency aspect of ACID — the fundamental set of properties that define reliable database transactions. As part of this deep dive series on ACID, I’m using PostgreSQL as the reference system, but the principles largely apply to most relational databases with only minor differences in implementation.

Today’s topic is Isolation, the “I” in ACID. Isolation becomes critical when multiple transactions run concurrently. It ensures that the outcome of executing transactions in parallel is the same as if they were executed one after another — avoiding weird side effects, data corruption, or misleading results.

Imagine two transactions running at the same time: one updating a balance and another reading it. Without proper isolation, the reader might get a value that never actually existed, leading to inconsistencies or even security issues.

Each isolation level represents a tradeoff between performance and consistency. The right choice depends on your application’s needs. Some systems prioritize consistency (e.g., banking apps), while others may prefer availability and speed (e.g., analytics dashboards). We’ll explore all major isolation levels with examples and caveats so you know when and why to use each.

Read Uncommitted
Read Committed
Repeatable Read
Serializable Read
Conclusion

Read Uncommitted

The Read Uncommitted isolation level allows a transaction to read data that has been modified by another transaction but not yet committed. While this may appear beneficial for performance, especially in highly concurrent systems, it introduces a significant risk: the possibility of dirty reads. A dirty read occurs when a transaction reads data that is later rolled back, meaning the data never officially existed in the database.

At this level, no locks are acquired on the data being read, so transactions are free to read rows that might still be in the process of being changed. This non-blocking behavior can improve speed, but it does so at the cost of consistency and reliability. In practice, systems operating under Read Uncommitted may make decisions based on temporary or invalid data, which can be especially dangerous in environments where correctness matters—like financial systems or inventory tracking. Let's take a look with example.

Example

Here’s how a dirty read can happen:

Transaction A starts and updates a user's balance from 100 to 200. However, the change is not yet committed.
Transaction B runs while Transaction A is still open and reads the new balance of 200.
Believing the balance is updated, Transaction B takes some action—like approving a purchase or initiating a transfer.
Later, Transaction A fails or is rolled back, which means the update to 200 is discarded and the balance remains 100.
Now, Transaction B has already acted on the assumption that the balance was 200, even though that value was never officially stored.

This is a dirty read: Transaction B relied on data that was never committed and ultimately did not exist. As a result, the system may end up in an incorrect state—something that is especially dangerous when financial or business-critical logic is involved.

Because of such risks, Read Uncommitted provides no isolation guarantees and is rarely suitable for real-world applications. It might be acceptable in certain reporting or analytics tasks where minor inconsistencies are tolerable, but in most cases, more reliable isolation levels like Read Committed are preferred.

Read Committed

The Read Committed isolation level ensures that a transaction can only read data that has already been committed by other transactions. This provides a significant improvement over Read Uncommitted, as it prevents dirty reads. A transaction will never see partial or rollback-prone changes made by others.

However, while this level avoids dirty reads, it doesn't completely eliminate concurrency issues. One key problem that can still occur under Read Committed is a write-write conflict, often referred to as a lost update. This happens when two transactions read the same data, modify it independently, and then overwrite each other’s results, unintentionally discarding one set of changes.

Example

Here’s how a lost update can occur under Read Committed:

Transaction A reads a value x = 0 from the database.
Transaction B also reads the same value x = 0 shortly after.
Both transactions independently increment x to 1.
Transaction A writes x = 1 back to the database.
Transaction B then also writes x = 1, overwriting A’s result.

The final value in the database is 1, even though both transactions performed an increment, and logically, the value should have been 2.

This type of issue happens because Read Committed does not prevent two transactions from reading the same data at the same time, nor does it coordinate their updates. Without additional mechanisms like explicit locking, optimistic concurrency control, or application-level safeguards, such conflicts can go undetected.

Read Committed is the default isolation level in PostgreSQL, offering a reasonable balance between performance and safety for many use cases. It’s a safe starting point for most applications, but developers must still be aware of its limitations under concurrent write scenarios.

Repeatable Read

The Repeatable Read isolation level ensures that once a transaction reads data, that data stays the same throughout the life of the transaction. Even if other transactions update or insert rows in the background, your transaction continues to operate on a consistent snapshot of the data. This prevents anomalies like non-repeatable reads and in PostgreSQL, even phantom reads.

This level is useful when you need strong consistency within a transaction — like generating reports, doing multi-step calculations, or reading the same data multiple times while expecting it not to change.

Example

Here’s what Repeatable Read helps prevent:

Non-Repeatable Reads

Transaction A starts and reads a row where x = 1.
Meanwhile, Transaction B updates that same row to x = 3 and commits.
If Transaction A reads x again, it still sees x = 1, not the updated value.
This guarantees that any value you read once stays the same, regardless of external changes.

Phantom Reads

Transaction A runs a query like SELECT * FROM users WHERE age > 30 and sees 5 users.
At the same time, Transaction B inserts a new user with { name: "John", age: 35 } and commits.
If Transaction A reruns the same query, it still sees only the original 5 users.
The new user (“John”) doesn’t appear because the snapshot taken at the start of the transaction excludes any changes made afterward.

Behind the scenes, PostgreSQL uses MVCC (Multi-Version Concurrency Control) to maintain this behavior. It captures a snapshot of the database when the transaction starts, and all reads are based on that version. If a conflicting update is attempted, PostgreSQL may throw a serialization error, requiring the transaction to retry.

While PostgreSQL offers strong protection under Repeatable Read, behavior varies across databases. For example, in MySQL, phantom reads can still occur unless you use extra locking. So even though the name is the same, the guarantees may differ — always check how your specific database implements it.

Sure! Here's the Serializable Reads section rewritten in a medium-length, article-friendly format with a clear explanation and example under a single subheading.

Serializable Read

The Serializable isolation level is the strictest isolation guarantee offered by PostgreSQL. It ensures that all concurrent transactions behave as if they were executed one after another, in some serial order — even if they’re actually running in parallel.

PostgreSQL doesn’t use traditional locking to achieve this. Instead, it uses a smarter, more efficient technique called Serializable Snapshot Isolation (SSI). This approach avoids blocking other transactions during reads or writes. Instead of holding locks, PostgreSQL watches for patterns that could lead to inconsistencies and rolls back one of the transactions if a conflict is detected.

This ensures consistency but introduces the possibility of serialization failures, meaning your transaction might be rolled back and you’ll need to retry it.

Example — Preventing Subtle Conflicts

Transaction A reads from a table of available coupons where active = true.
Transaction B simultaneously marks one of those coupons as active = false and commits.
Transaction A, unaware of the change, proceeds to assign the now-inactive coupon to a user.
PostgreSQL detects that the sequence of actions would not be valid if executed in order and rolls back Transaction A.

This rollback ensures the system remains consistent, as if Transaction B had completed before Transaction A even started.

Key point: In PostgreSQL, Serializable doesn’t block others like traditional locking — it lets transactions run in parallel and only intervenes if the outcome would be invalid. Other databases like MySQL or SQL Server often rely on row or table locking to achieve similar guarantees, which can reduce performance or cause deadlocks.

Conclusion

Isolation is a crucial piece of the puzzle when it comes to building reliable, concurrent applications. Picking the right isolation level is all about understanding your use case — whether you're building a banking system that demands full consistency or a high-throughput analytics app that prioritizes availability.

PostgreSQL gives you excellent control and guarantees, especially through MVCC and SSI. But remember — with stronger isolation often comes increased complexity and performance trade-offs.

Before I end this post, I want to take a moment to express my deep love and unwavering support for our Palestinian Muslim brothers and sisters 🇵🇸. Their lives, their struggles, and their voices matter. In a world that too often turns a blind eye, we must continue to speak up and stand with the oppressed. May justice rise, may peace prevail, and may the people of Palestine live with dignity, freedom, and hope. You are not forgotten—your life matters.The recent surge in terrorist activities and the brutal actions by Israel are unequivocally condemned.

Mastering Database Consistency: Unlocking the 'C' in ACID with PostgreSQL

Muhammad Hadeed Tariq — Sun, 20 Apr 2025 13:31:17 +0000

Introduction

In the last article, I talked about atomicity in databases. As you know, I’m doing a deep-dive series on ACID in databases, and now it's time to tackle the C—Consistency.

As always, I’ll use PostgreSQL for demonstration, but with minor changes, this applies to almost all traditional relational databases.

Consistency means that when a transaction runs—which is just a group of operations—the state of the database remains valid both before and after the transaction. This includes ensuring that constraints like UNIQUE, NOT NULL, CHECK, and foreign key rules are upheld. If a transaction would violate these rules, it’s rolled back automatically.

Let's deep dive into:

Real-World Consistency: The E-commerce Platform
Consistency Through Relationships (Cascade Operations)
How PostgreSQL Enforces Consistency Internally
Consistency Difference from CAP Theorem
Conclusion

Real-World Consistency: The E-commerce Platform

Let’s say you’re building an e-commerce platform. In a typical order transaction, several operations happen together:

Reducing stock
Creating the order
Sending notifications

These operations must respect all business rules and constraints before being committed. If, for example, the stock level drops below zero or the order cannot be created due to a constraint violation, the entire transaction is rolled back. This is where consistency from the ACID principles ensures that your database remains in a valid state — no partial or inconsistent data gets committed.
For instance, stock should never go below zero. To enforce that, you might add a constraint:

ALTER TABLE products
ADD CONSTRAINT stock_positive CHECK (stock >= 0);

Now imagine during a transaction, a customer places an order for more items than are available. If the constraint fails but the order still gets created — your database is not consistent, and thus not ACID compliant.

Consistency ensures that all business rules, constraints, and dependencies are respected before a transaction is committed. It keeps your data clean and trustworthy.

Consistency Through Relationships (Cascade Operations)

But consistency doesn’t stop at just field-level checks. It also involves maintaining referential integrity across related tables.

Imagine your database has a users table and an orders table, where each order belongs to a user:

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

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);

If a user is deleted, all of their orders should be deleted too. PostgreSQL handles this via cascading operations, which are processed synchronously as part of the same transaction — helping maintain consistency across tables.

To visually understand this process, take a look at the diagram below. It illustrates the path of a cascade delete, showing how PostgreSQL ensures that both the user and their associated orders are handled within the same transaction, enforcing referential integrity.

However, some large-scale systems offload these cascading deletions to background jobs or asynchronous triggers. While this may improve performance, it introduces risk: if the job fails or runs late, and another transaction reads the data that should have been deleted, your system may act on stale or invalid data.

This leads to a consistency problem — not because of the database engine itself, but due to how the application is designed.

How PostgreSQL Enforces Consistency Internally

To enforce this kind of consistency — both in constraints and in relational integrity — PostgreSQL relies on a mechanism called the WAL (Write-Ahead Log), which I discussed in detail in the previous article on Atomicity.

Whenever a transaction begins, every operation — whether it’s a new insert, a cascading delete from a foreign key relationship, or a column update — is first written to the WAL before it modifies the actual data in your tables.

This ensures that no partial or invalid state ever reaches the persistent database. If something fails (like a constraint violation or a cascading issue), PostgreSQL can safely roll back using the WAL, guaranteeing that the data remains consistent and valid throughout the system.

Here’s a high-level look:

BEGIN TRANSACTION
  → Write operation to WAL (INSERT, DELETE, UPDATE)
  → Validate constraints (CHECK, UNIQUE, FK, NOT NULL)
  → If everything is valid: COMMIT
  → Else: ROLLBACK (discard uncommitted WAL entries)

So even in complex operations like deleting a user and all their orders via ON DELETE CASCADE, all changes are recorded in WAL first. If anything fails, the entire chain — including the dependent deletions — is rolled back, and your data remains untouched and valid.

This is how PostgreSQL ensures relational consistency — even deep across table relationships — at the transaction level.

Consistency Difference from CAP Theorem

Many people confuse CAP Theorem consistency with ACID consistency, but they refer to different things.

CAP consistency applies to distributed systems where data is replicated across multiple nodes. It means that every node must reflect the most recent write, or return an error if it cannot.
For example, if a user writes data to Node A, and another user reads that data immediately from Node B, CAP consistency guarantees that Node B either returns the updated value or fails to respond if the update hasn't reached it yet.
This differs from eventual consistency, where Node B may return outdated (stale) data while it catches up — but eventually, all nodes will synchronize.
Each model has trade-offs: CAP consistency favors correctness, while eventual consistency favors availability and scalability.

So CAP consistency is about all replicas agreeing on the same value while ACID consistency is about maintaining data integrity within a single database instance.

Conclusion

Consistency is not just about applying rules—it’s about ensuring your business logic, data integrity, and user trust remain intact, even when the system is under load.

From constraint checking to WAL logging to concurrency control, PostgreSQL has your back. And if you’re building critical apps—like an e-commerce platform—you should care deeply about this stuff.

Before I end this post, I want to take a moment to express my deep love and unwavering support for our Palestinian Muslim brothers and sisters 🇵🇸. Their lives, their struggles, and their voices matter. In a world that too often turns a blind eye, we must continue to speak up and stand with the oppressed. May justice rise, may peace prevail, and may the people of Palestine live with dignity, freedom, and hope. You are not forgotten—your life matters.The recent surge in terrorist activities and the brutal actions by Israel are unequivocally condemned.

Understanding Atomicity: The First Pillar of ACID Transactions

Muhammad Hadeed Tariq — Sun, 13 Apr 2025 06:33:48 +0000

Introduction

If you've worked with relational databases, you've likely come across the term ACID—an acronym that ensures reliable database transactions. Each letter in ACID holds deep meaning and importance: Atomicity, Consistency, Isolation, and Durability.

In this article, we're going to focus on the first pillar: Atomicity. You’ll understand what it means, why it matters, and how databases implement it internally. This post includes a real-world analogy, a SQL implementation, and even a hands-on Go simulation of a Write-Ahead Log (WAL)—a key mechanism databases like PostgreSQL use to guarantee atomicity.

If you're interested in diving into the rest of ACID properties, you can explore:

Let's deep dive in to:

What is Atomicity in Databases?
Atomicity in Action: A Collaborative Recipe Platform
How Databases Ensure Atomicity (Under the Hood)
Write Ahead Logging Explained
WAL Simulation in Go
Conclusion

What is Atomicity in Databases?

For demonstration purposes, we'll assume a PostgreSQL database. However, with only minor changes, the same principles and techniques apply to almost all traditional SQL databases like MySQL, MariaDB, and SQL Server.

Atomicity means that a group of operations within a transaction are treated as a single, indivisible unit. Either all operations succeed, or none do. There’s no halfway state.

Think of a transaction like a switch—it either flips entirely or not at all. This prevents data corruption, especially in systems where multiple operations are tightly coupled.

Atomicity in Action: A Collaborative Recipe Platform

Let’s skip the classic "bank transfer" story. Here’s a fresh real-world scenario that shows why atomicity is critical:

Scenario: Collaborative Recipe Creation Platform

A platform where users can collaboratively create and edit recipes.

Atomic Transaction Example:

When a user adds a new ingredient to a recipe, three things must happen:

The ingredient is added to the recipe's ingredient list.
The recipe’s "last modified" timestamp is updated.
The contributor’s "contribution count" is incremented.

The Problem Without Atomicity:

The ingredient is added, but the recipe no longer exists.
The contributor's count updates, but the ingredient is never stored.
One part succeeds while the others fail—leading to data inconsistency.

As you can see, without atomicity, there's a real risk of data inconsistency—where some parts of a transaction are saved while others are not. In a simple recipe app, it might just mean a missing ingredient or an incorrect contributor count. But in critical business applications—like banking, e-commerce, or healthcare—even small inconsistencies can lead to serious consequences. It can damage user trust, trigger system errors, and ultimately cause financial or reputational loss. That's why atomicity isn’t just a nice-to-have—it’s essential.
So, how do databases actually guarantee atomicity during operations? Let’s see how we can ensure atomicity using SQL transactions in a practical scenario.

Practical SQL Example

Here’s how you'd structure this in SQL to ensure atomicity:

BEGIN;  -- Start the transaction

-- Step 1: Check if the recipe exists and lock it
SELECT recipe_id FROM recipes 
WHERE recipe_id = 1 
FOR UPDATE;

-- Step 2: Add a new ingredient
INSERT INTO ingredients (recipe_id, name, added_by) 
VALUES (1, 'Garlic', 2);

-- Step 3: Update the recipe's timestamp
UPDATE recipes 
SET last_modified = NOW() 
WHERE recipe_id = 1;

-- Step 4: Increment user's contribution count
UPDATE users 
SET contribution_count = contribution_count + 1
WHERE user_id = 2;

COMMIT;  -- Commit only if all operations succeed

If any step fails, the database rolls everything back to the state before BEGIN.

How Databases Ensure Atomicity (Under the Hood)

Now you may wonder, how does the database make sure this all-or-nothing behavior works, especially if the system crashes in between?

The answer lies in something called a Write-Ahead Log (WAL).

Write Ahead Logging Explained

In PostgreSQL, Write-Ahead Logging (WAL) ensures that every transaction is all or nothing—the core of atomicity. Before the database makes any real changes, it first writes them to a special log file (the WAL). Think of it like drafting a message in your notes app before sending it—if your phone crashes, you still have the draft.

If the system crashes in the middle of a transaction, PostgreSQL uses that log to figure out what was safely finished and what wasn’t. It only applies the changes that were fully completed, and rolls back anything else. This way, the database never ends up in a broken or half-updated state.

WAL also adds a touch of durability, because even if the power goes out, the log is still there to bring everything back as it was before.

WAL Simulation in Go

package main

import (
    "bufio"
    "fmt"
    "os"
)

type WriteAheadLog struct {
    logFile string
}

func NewWriteAheadLog(filename string) *WriteAheadLog {
    return &WriteAheadLog{logFile: filename}
}

func (wal *WriteAheadLog) LogWrite(transaction string) {
    file, err := os.OpenFile(wal.logFile, os.O_APPEND|os.O_CREATE|os.O_WRONLY, 0644)
    if err != nil {
        fmt.Println("Error writing log:", err)
        return
    }
    defer file.Close()

    _, err = file.WriteString(transaction + "\n")
    if err == nil {
        fmt.Println("Logged:", transaction)
    }
}

func (wal *WriteAheadLog) LogRecover() {
    file, err := os.Open(wal.logFile)
    if err != nil {
        fmt.Println("Error reading log:", err)
        return
    }
    defer file.Close()

    fmt.Println("Recovering transactions...")
    scanner := bufio.NewScanner(file)
    for scanner.Scan() {
        fmt.Println("Recovered:", scanner.Text())
    }
}

func (wal *WriteAheadLog) Commit() {
    err := os.WriteFile(wal.logFile, []byte{}, 0644)
    if err != nil {
        fmt.Println("Error committing log:", err)
    } else {
        fmt.Println("Committed transactions.")
    }
}

func main() {
    wal := NewWriteAheadLog("wal_log.txt")

    wal.LogWrite("INSERT INTO users VALUES(1, 'Alice')")
    wal.LogWrite("UPDATE users SET name = 'Bob' WHERE id = 1")

    fmt.Println("\nSimulating system restart...\n")
    wal.LogRecover()

    wal.Commit()
}

Conclusion

Atomicity ensures that your data operations don't leave the database in a broken or inconsistent state. Whether you’re building a social app, a recipe platform, or a financial system—atomic transactions preserve trust and data integrity.

Modern databases, especially PostgreSQL, use mechanisms like WAL to guarantee atomicity even in the face of unexpected crashes. By understanding these internals, you become better equipped to design reliable, scalable systems.

In future articles, we’ll dive into the remaining pillars of ACID—Consistency, Isolation, and Durability—each critical in its own right.

Before I end this post, I want to take a moment to express my deep love and unwavering support for our Palestinian Muslim brothers and sisters 🇵🇸. Their lives, their struggles, and their voices matter. In a world that too often turns a blind eye, we must continue to speak up and stand with the oppressed. May justice rise, may peace prevail, and may the people of Palestine live with dignity, freedom, and hope. You are not forgotten—your life matters.The recent surge in terrorist activities and the brutal actions by Israel are unequivocally condemned.

From Slow Queries to Fast Responses: The Power of Connection Pooling

Muhammad Hadeed Tariq — Mon, 17 Mar 2025 02:23:34 +0000

Introduction

So, what is connection pooling? If you’re a backend developer working with databases, you've likely heard this term. You might have also come across advice on the internet suggesting that instead of opening and closing a connection to the database every time, you should use connection pooling in your application. But why? What are the benefits?

Let’s dive deep into:

Normal Database Interaction
- Downsides
- Diagram for Normal Database Interaction
- Code for Normal Database Interaction
Connection Pooling
- Benefits of Connection Pooling
- Diagram for Connection Pooling
- Code for Connection Pooling
Things to Consider When Using Connection Pooling
Conclusion

Normal Database Interaction

Normally, in an application, database interaction follows this process:

A request comes to the server.
The server establishes a connection to the database.
The request is processed, and the response is returned.
The connection is closed.

To make a request and get a response, a protocol is required. This is usually TCP (Transmission Control Protocol) or gRPC (gRPC Remote Procedure Calls). Let’s assume TCP is being used. You may already know that TCP has a cost due to the three-way handshake process, which adds latency.

With this native approach, every request opens a new connection to the database and then closes it after processing.

Downsides

High Resource Consumption: Each request creates a new TCP connection, leading to excessive resource usage.
Scalability Issues: If site traffic is high, maintaining a separate connection for each user can cause the database to hit its connection limit.
Increased Costs: Running many connections increases database expenses significantly.

Diagram for Normal Database Interaction

Code for Normal Database Interaction

For demonstration purposes, we’ll use PostgreSQL with Node.js and the pg driver. However, you can use other drivers for different languages:

1. Python

psycopg2 → Most widely used PostgreSQL adapter
asyncpg → High-performance async driver for PostgreSQL
SQLAlchemy → ORM that uses psycopg2 by default

2. Java

PostgreSQL JDBC Driver → org.postgresql.Driver
Hibernate → ORM that works with PostgreSQL via JDBC
Spring Data JPA → Uses Hibernate under the hood

3. C# (.NET)

Npgsql → Official PostgreSQL driver for .NET
Entity Framework Core → Uses Npgsql for PostgreSQL

4. PHP

PDO_PGSQL → PHP Data Object (PDO) driver for PostgreSQL
pg_connect() → Native PostgreSQL function in PHP

5. Ruby

pg gem → Default PostgreSQL adapter for Ruby
ActiveRecord → Uses pg for PostgreSQL in Rails

6. Go

pgx → High-performance PostgreSQL driver
database/sql with lib/pq → Go's standard DB driver with PostgreSQL support

1. Install the `pg` package

npm install pg

2. Create a database connection file (`db.js`)

const { Client } = require("pg");

// PostgreSQL connection configuration
const client = new Client({
  user: "your_username",
  host: "localhost",
  database: "your_database",
  password: "your_password",
  port: 5432,
});

// Connect to the database
client.connect()
  .then(() => console.log("Connected to PostgreSQL!"))
  .catch(err => console.error("Connection error", err.stack));

module.exports = client;

3. Query the Database (`index.js`)

const client = require("./db");

async function fetchUsers() {
  try {
    const result = await client.query("SELECT * FROM users");
    console.log(result.rows);
  } catch (error) {
    console.error("Error executing query", error);
  } finally {
    client.end(); // Close the connection
  }
}

fetchUsers();

4. Run the script

node index.js

In this setup, every request establishes a new database connection, processes the query, and then immediately closes the connection. While this may seem straightforward, it introduces significant overhead, consuming unnecessary resources and increasing latency. This approach becomes even more problematic as traffic scales, leading to performance bottlenecks.

To address these inefficiencies, let’s explore connection pooling—a smarter way to manage database connections efficiently.

Connection Pooling

Now, let’s explore connection pooling and how it optimizes database interactions.

Instead of establishing a new connection for every request, a pool of persistent TCP connections is maintained within the application’s memory, allowing connections to be reused efficiently. This eliminates the overhead of repeatedly opening and closing connections, reducing latency and resource consumption.

For more advanced scenarios, isolated connection pool environments (such as managed database connection pools) are available, but for simplicity, we'll focus on the in-memory approach.

Here’s how it works when a request is made:

The application retrieves an available connection from the pool.
The query is executed.
The connection is returned to the pool, making it ready for reuse.

Benefits of Connection Pooling

Reduces Latency: No need to establish a fresh TCP connection each time.
Saves Authentication Time: The database does not need to authenticate every request separately.
Scalability: The number of connections in the pool can be dynamically increased or decreased based on load.

Diagram for Connection Pooling

Code for Connection Pooling

We will modify the previous code to use connection pooling instead of individual connections.

1. Create a connection pool (`db.js`)

const { Pool } = require("pg");

// PostgreSQL connection pool configuration
const pool = new Pool({
  user: "your_username",
  host: "localhost",
  database: "your_database",
  password: "your_password",
  port: 5432,
  max: 10,                        // Maximum number of clients in the pool
  idleTimeoutMillis: 30000,        // Close idle clients after 30 seconds
  connectionTimeoutMillis: 2000,   // Return error if connection takes longer than 2 sec
  allowExitOnIdle: false,         // Prevents process exit if idle clients exist
});

// Event listeners for debugging
pool.on("connect", () => {
  console.log("Connected to PostgreSQL!");
});

pool.on("acquire", () => {
  console.log("Client acquired from the pool");
});

pool.on("release", () => {
  console.log("Client released back to the pool");
});

pool.on("error", (err) => {
  console.error("Unexpected error on idle client", err);
  process.exit(-1);
});

module.exports = pool;

*2. Query the Database (`index.js`)**

const pool = require("./db");

async function fetchUsers() {
  let client;
  try {
    client = await pool.connect(); // Get a client from the pool
    console.log("Connected to database");

    const result = await client.query("SELECT * FROM users");
    console.log("Users:", result.rows);
  } catch (error) {
    console.error("Error executing query", error);
  } finally {
    if (client) client.release(); // Release the client back to the pool
  }
}

fetchUsers();

3. Run the script

node index.js

Things to Consider When Using Connection Pooling

Always return connections to the pool after execution. Otherwise, you might run out of connections, leading to application downtime.
Avoid too few connections in the pool, as this can introduce latency.
Avoid too many connections, as excessive connections may consume unnecessary resources and slow down the application.
Set an idle timeout to release connections that remain unused for too long.
Set a query timeout to ensure connections are not held indefinitely due to slow queries.

Conclusion

Efficient database interaction is crucial for building scalable applications. As we've seen, directly opening and closing connections for every request is inefficient, resource-intensive, and limits scalability.

By implementing connection pooling, we significantly reduce latency, optimize resource usage, and improve overall performance. Instead of repeatedly establishing new connections, a pool of persistent connections is maintained, allowing queries to be executed faster and more efficiently.

DEV Community: Muhammad Hadeed Tariq

Understanding Durability in PostgreSQL The "D" in ACID

Table of Contents

1. What durability means?

2. How PostgreSQL manages durability under the hood

2.1 Modifications Begin in Memory: The Buffer Cache

3. Write-Ahead Logging (WAL): Durability’s Foundation

3.1 What the WAL Does

4. Dirty Pages and Deferred Disk Writes

4.1 When Are Dirty Pages Flushed?

5. Crash Recovery: Why WAL Exists in the First Place

6. Practical Implications: A Real-World Banking Scenario

7. Distributed Transactions and Durability

8. Summary: Why Durability Is Non-Negotiable

Further Reading

Understanding Durability in PostgreSQL The "D" in ACID

Table of Contents

1. What durability means?

2. How PostgreSQL manages durability under the hood

2.1 Modifications Begin in Memory: The Buffer Cache

3. Write-Ahead Logging (WAL): Durability’s Foundation

3.1 What the WAL Does

4. Dirty Pages and Deferred Disk Writes

4.1 When Are Dirty Pages Flushed?

5. Crash Recovery: Why WAL Exists in the First Place

6. Practical Implications: A Real-World Banking Scenario

7. Distributed Transactions and Durability

8. Summary: Why Durability Is Non-Negotiable

Further Reading

Hands-On with P2P Networks: Building a Messaging System

My Journey: From BitTorrent to DHTs

What is a DHT?

What is Kademlia?

A guide to routing table

Messaging System

Adding Messaging Capability to Peers

1. Message Structure

2. MessagingPeer Struct

3. Starting the Server

4. Receiving and Storing Messages

5. Local Node Lookup

6. Iterative Lookup Across Peers

7. Sending a Message to Another Peer

8. Peer Initialization

Main Function: Bringing It All Together

Creating and Starting Peers

Manually Populating Routing Tables

Simulating the Network Map

Sending the Message

Keeping the Program Alive

Conclusion

Understanding Isolation in PostgreSQL: The “I” in ACID

Table of Contents

Read Uncommitted

Example

Read Committed

Example

Repeatable Read

Example

Non-Repeatable Reads

Phantom Reads

Serializable Read

Example — Preventing Subtle Conflicts

Conclusion

Mastering Database Consistency: Unlocking the 'C' in ACID with PostgreSQL

Introduction

Real-World Consistency: The E-commerce Platform

Consistency Through Relationships (Cascade Operations)

How PostgreSQL Enforces Consistency Internally

Consistency Difference from CAP Theorem

Conclusion

Understanding Atomicity: The First Pillar of ACID Transactions

Introduction

What is Atomicity in Databases?

Atomicity in Action: A Collaborative Recipe Platform

Scenario: Collaborative Recipe Creation Platform

Atomic Transaction Example:

The Problem Without Atomicity:

Practical SQL Example

How Databases Ensure Atomicity (Under the Hood)

1. Install the `pg` package

2. Create a database connection file (`db.js`)

3. Query the Database (`index.js`)

1. Create a connection pool (`db.js`)

*2. Query the Database (`index.js`)**