The Non-Transactional Reality of PostgreSQL Sequences

The Expectation of Monotonicity in Order Systems

When building an order management pipeline, the primary objective is to capture, validate, and permanently store transactional records such as customer purchases, financial ledgers, or invoices. This system represents a comprehensive infrastructure architecture rather than a simple database configuration because it operates as a multi-layered distributed pipeline. In a typical production environment, this architecture encompasses web servers ingesting thousands of concurrent requests, connection pools regulating database lifecycles, and downstream services like fulfillment, inventory, and accounting that ingest this data via asynchronous message queues.

To ensure absolute tracking, auditing, and predictability across these decoupled architectural boundaries, a common engineering assumption is that these transaction records will possess sequentially ordered identifiers, moving uniformly from one integer to the next without omission. In my implementation, I utilized PostgreSQL with a primary key defined as a big integer generated by default as an identity. This approach is widely recognized for its enterprise stability and seamless integration within robust backend data ecosystems.

During routine disaster recovery drills, however, the monitoring logs revealed an unexpected pattern in the primary key sequence. Instead of a continuous, gapless progression, the identifiers exhibited distinct omissions, appearing as a broken sequence with missing elements. A manual audit verified that no records were lost; every transaction was accounted for, yet the identifiers contained significant gaps. This discovery prompted a detailed investigation into the core mechanics of sequence manipulation within the PostgreSQL engine.

Simulating the Anomalies: Forward and Backward Jumps

To isolate the root causes of these numerical omissions, I constructed a controlled replication environment using Kotlin and the Exposed framework to simulate various infrastructure failure states.

The Forward Jump and the Mechanics of the Write-Ahead Log

The first scenario reproduces a sudden infrastructure termination. The configuration initializes a sequence and captures the initial increment within a standard database transaction block.

transaction {
    exec(”CREATE SEQUENCE seq;”)
    val firstVal = exec(”SELECT nextval(’seq’);”) { rs ->
        rs.next()
        rs.getLong(1)
    }

    println(”Initial value: $firstVal”)
}

Once the initial value is confirmed as one, the application executes an ungraceful process termination at the operating system level, targeted directly at the backend process identifier associated with the current database session.

fun triggerDatabaseProcessCrash() {
    transaction {
        val pid = exec(”SELECT pg_backend_pid();”) { rs ->
            rs.next()
            rs.getInt(1)
        }

        Runtime.getRuntime().exec(”kill -9 $pid”)
    }
}

Following this abrupt termination, the Kotlin application encounters a communication exception or a transient connection exception as the underlying connection pool loses its link to the server. Upon the re-establishment of a stable connection to the database instance, a subsequent call to the sequence reveals a substantial forward leap rather than the expected single increment.

transaction {
    val nextVal = exec(”SELECT nextval(’seq’);”) { rs ->
        rs.next()
        rs.getLong(1)
    }
    println(”Value after crash recovery: $nextVal”)
}

The resulting output yields a value of 34. This behavior is directly attributable to an internal optimization parameter within the PostgreSQL source code, governed by a pre-allocation macro.

To minimize persistent disk write frequency and maximize concurrent scalability, the engine pre-allocates a block of 32 sequence values by default and logs this allocation to the Write-Ahead Log. When an ungraceful shutdown occurs, the remaining unassigned values within that cached block are permanently lost, causing the sequence to resume from the boundary of the subsequent pre-allocated block during recovery.

The Backward Jump and Uncommitted States

An even more perplexing anomaly occurs when a sequence appears to move backward following a critical system failure. This state can be demonstrated by advancing a sequence multiple times within a single transaction block without executing a formal commit statement, followed by an immediate hard process termination.

import org.jetbrains.exposed.sql.transactions.transaction
import org.jetbrains.exposed.sql.exec

fun demonstrateBackwardsJump() {
    transaction { exec(”CREATE SEQUENCE seq;”) }

    transaction {
        val v1 = exec(”SELECT nextval(’seq’);”) { it.next(); it.getLong(1) }
        val v2 = exec(”SELECT nextval(’seq’);”) { it.next(); it.getLong(1) }
        val v3 = exec(”SELECT nextval(’seq’);”) { it.next(); it.getLong(1) }
        println(”Sequence values in-transaction: $v1, $v2, $v3”)
        val pid = exec(”SELECT pg_backend_pid();”) { it.next(); it.getInt(1) }
        Runtime.getRuntime().exec(”kill -9 $pid”)
    }
}

Upon reconnecting to the database and invoking the next value, the system returns a value of one. This behavior emphasizes that engine sequences operate entirely outside standard transactional boundaries. While individual sessions track these increments locally during an active transaction, the underlying values are discarded during a hard crash because they were never permanently etched into the Write-Ahead Log as a committed state.

Evaluating Alternatives: The Flawed Custom Counter Workaround

In an attempt to bypass the inherent gaps associated with standard database sequences, a developer might consider implementing a custom identity counter utilizing standard transactional tables and functions. The implementation typically involves creating an explicit sequence tracking table and an atomic update function.

CREATE TABLE MY_SEQ (ID BIGINT NOT NULL);

INSERT INTO MY_SEQ (ID) VALUES (0);

CREATE FUNCTION NEXT_VAL() RETURNS BIGINT
    LANGUAGE SQL AS
‘UPDATE MY_SEQ SET ID = ID + 1 RETURNING ID’;

While this mechanism successfully eliminates numerical gaps by utilizing the standard transactional engine, it introduces a massive performance penalty that makes it unviable for high-throughput applications. When the custom function executes the update statement, PostgreSQL applies an exclusive row-level lock to that single row within the tracking table. Consequently, every concurrent transaction across the entire application ecosystem must wait in a strict, single-file queue to obtain a new identifier.

If a single transaction requires 100 milliseconds to process its internal business logic prior to committing, all other concurrent threads are completely blocked for that duration. In a high-concurrency production environment, this structural bottleneck rapidly triggers database connection timeouts, thread starvation, and severe application latency. The trade-off between absolute numerical continuity and system throughput represents a deliberate choice where performance must be prioritized.

Architectural Best Practices for Kotlin Applications

Managing non-transactional sequence behavior within Kotlin services requires a deliberate approach to application architecture and data flow design.

Essential Insight: Database sequences must be treated as internal, transient optimization helpers rather than durable, externally accurate identifiers.

When developing services that interface with PostgreSQL sequences, specific architectural practices must guide the implementation to prevent data degradation across the broader system. It is vital to never expose or distribute an identifier generated by a sequence to external systems, such as asynchronous message brokers or user-facing REST responses, until the enclosing database transaction has been successfully committed. If a system failure or network interruption occurs prior to the final commit, the sequence value is permanently discarded, leading to data inconsistencies or dangling references within external architectures.

Furthermore, structuring database transaction boundaries to be as narrow and short-lived as possible limits the window of vulnerability for process crashes and mitigates the risk of unexpected numerical anomalies. If the core business domain dictates a strict requirement for guaranteed, immutable, and gapless identifiers that must survive catastrophic infrastructure failures, database-driven sequences must be abandoned entirely. In such scenarios, transitioning to high-resolution Universally Unique Identifiers or deploying a dedicated identity reservation ledger specifically engineered to preserve state integrity across volatile failure scenarios provides the necessary durability.

---