The Core Challenge: Why Sequences Break at Scale

The Core Challenge: Why Sequences Break at Scale

Database sequences are a foundational mechanism for generating unique, monotonically increasing identifiers. In PostgreSQL, a sequence is a lightweight object that maintains state—typically a 64-bit counter—and guarantees uniqueness through atomic increments. At small scale, this works flawlessly. But when you operate 100+ services consuming the same sequence, you encounter cascading failure modes:

• Contention bottleneck: Every ID generation becomes a serialized operation on a single database resource, creating lock waits and throughput ceilings.
• Coupling risk: All services depend on a single sequence's availability; sequence exhaustion or corruption affects the entire ecosystem.
• Migration complexity: Replacing the sequence requires coordinating ID generation logic across dozens of services simultaneously, risking ID collisions or gaps.

This lesson explores the architectural patterns and data flow strategies that allow you to migrate away from centralized sequences without breaking dependent systems.

Understanding the Current State: Sequence-Based ID Generation

In a typical PostgreSQL setup, services call nextval('sequence_name') to obtain the next ID:

-- Service A inserts a record
INSERT INTO orders (id, customer_id, amount) 
VALUES (nextval('order_id_seq'), 123, 99.99);

-- Service B inserts a record
INSERT INTO orders (id, customer_id, amount) 
VALUES (nextval('order_id_seq'), 456, 149.99);

Each call acquires a lightweight lock, increments the sequence, and returns the value. PostgreSQL's sequence implementation is highly optimized—it uses a cache to batch increments and reduce lock contention. However, the fundamental issue remains: all ID generation funnels through a single resource.

For a data analyst, this manifests as:

• Predictable ID ranges tied to insertion order (useful for time-series analysis, but brittle).
• Gaps in sequences when transactions roll back (expected behavior, but complicates auditing).
• Difficulty correlating IDs across systems that generate their own sequences independently.

Migration Strategy 1: Distributed ID Allocation with Ranges

The first pattern allocates ranges of IDs to each service, eliminating per-ID lock contention. Instead of calling nextval() for every record, a service requests a range once and exhausts it locally.

How it works:

1. A centralized ID allocator (a microservice or a dedicated table) maintains a high-water mark.
2. Each consuming service requests a range (e.g., IDs 1,000,000–1,999,999).
3. The service caches this range and generates IDs locally without further database calls.
4. When exhausted, it requests the next range.

Implementation in PostgreSQL:

-- Allocator table
CREATE TABLE id_allocations (
  service_name TEXT PRIMARY KEY,
  current_high_water BIGINT NOT NULL,
  range_size BIGINT NOT NULL DEFAULT 1000000,
  last_allocated_at TIMESTAMP DEFAULT NOW()
);

-- Allocator function (atomic)
CREATE OR REPLACE FUNCTION allocate_id_range(p_service TEXT, p_range_size BIGINT)
RETURNS TABLE(range_start BIGINT, range_end BIGINT) AS $$
DECLARE
  v_start BIGINT;
  v_end BIGINT;
BEGIN
  UPDATE id_allocations
  SET current_high_water = current_high_water + p_range_size,
      last_allocated_at = NOW()
  WHERE service_name = p_service
  RETURNING current_high_water - p_range_size, current_high_water
  INTO v_start, v_end;
  
  RETURN QUERY SELECT v_start, v_end;
END;
$$ LANGUAGE plpgsql;

A consuming service (in Python with psycopg2) would cache and use this range:

class IDGenerator:
    def __init__(self, service_name, db_conn, range_size=1000000):
        self.service_name = service_name
        self.db_conn = db_conn
        self.range_size = range_size
        self.current_id = None
        self.range_end = None
    
    def next_id(self):
        if self.current_id is None or self.current_id >= self.range_end:
            self._allocate_new_range()
        self.current_id += 1
        return self.current_id
    
    def _allocate_new_range(self):
        cursor = self.db_conn.cursor()
        cursor.execute(
            "SELECT * FROM allocate_id_range(%s, %s)",
            (self.service_name, self.range_size)
        )
        range_start, range_end = cursor.fetchone()
        self.current_id = range_start
        self.range_end = range_end
        cursor.close()

# Usage
gen = IDGenerator('order_service', db_conn)
for _ in range(5):
    print(gen.next_id())  # 1, 2, 3, 4, 5 (all local, no DB calls)

Advantages: Eliminates per-ID lock contention; services are decoupled from the allocator after range acquisition.

Trade-offs: IDs are no longer strictly monotonic across services (Service A might use 1–1M, Service B uses 1M–2M, but they insert concurrently). For analytics, this requires careful handling of ID ordering assumptions.

Migration Strategy 2: Snowflake-Style Distributed IDs

A more sophisticated approach uses a composite ID structure inspired by Twitter's Snowflake algorithm. Each ID encodes metadata: timestamp, service ID, and a local sequence.

Structure (64-bit):

• Bits 63–22 (42 bits): Millisecond timestamp (supports ~139 years from epoch).
• Bits 21–12 (10 bits): Service/worker ID (supports 1,024 services).
• Bits 11–0 (12 bits): Local sequence counter (supports 4,096 IDs per millisecond per service).

Each service generates IDs independently without any central allocator:

import time

class SnowflakeIDGenerator:
    def __init__(self, service_id):
        self.service_id = service_id & 0x3FF  # 10 bits
        self.sequence = 0
        self.last_timestamp = 0
    
    def next_id(self):
        current_timestamp = int(time.time() * 1000)
        
        if current_timestamp == self.last_timestamp:
            self.sequence = (self.sequence + 1) & 0xFFF  # 12 bits
            if self.sequence == 0:
                # Sequence overflow, wait for next millisecond
                while time.time() * 1000 <= current_timestamp:
                    time.sleep(0.001)
                current_timestamp = int(time.time() * 1000)
        else:
            self.sequence = 0
        
        self.last_timestamp = current_timestamp
        
        # Compose ID
        id_value = (current_timestamp << 22) | (self.service_id << 12) | self.sequence
        return id_value

# Usage
gen = SnowflakeIDGenerator(service_id=5)
for _ in range(3):
    print(bin(gen.next_id()))  # Fully distributed, no DB calls

Advantages: Zero database dependency; IDs are roughly sortable by timestamp; service ID is embedded for traceability.

Trade-offs: IDs are no longer globally unique if service IDs collide (requires careful allocation); timestamp-based ordering can be misleading if clocks drift; requires all services to upgrade simultaneously.

The Migration Process: Dual-Write and Validation

Migrating from sequences to a new ID scheme requires a careful choreography to avoid collisions and data loss. The standard pattern is dual-write with validation:

1. Phase 1 (Preparation): Deploy the new ID generator alongside the old sequence logic. Services write both the old sequence ID and the new distributed ID to a temporary column.
2. Phase 2 (Validation): Run batch jobs to verify that new IDs are unique and don't collide with existing sequence IDs. For a data analyst, this means querying for duplicates and gaps.
3. Phase 3 (Cutover): Gradually shift traffic from the old sequence to the new generator. Monitor for collisions in real-time.
4. Phase 4 (Cleanup): Once all services are migrated and validated, drop the old sequence and temporary columns.

In SQL, the validation step might look like:

-- Check for collisions between old and new IDs
SELECT COUNT(*) as collision_count
FROM orders
WHERE old_sequence_id IS NOT NULL
  AND new_distributed_id IS NOT NULL
  AND old_sequence_id = new_distributed_id;

-- Check for gaps in new ID ranges (per service)
WITH id_ranges AS (
  SELECT 
    service_id,
    MIN(new_distributed_id) as min_id,
    MAX(new_distributed_id) as max_id,
    COUNT(DISTINCT new_distributed_id) as unique_count
  FROM orders
  GROUP BY service_id
)
SELECT *
FROM id_ranges
WHERE unique_count < (max_id - min_id + 1);

Data Modeling Implications for Analytics

As a data analyst, you must adapt your data models and ETL pipelines to the new ID scheme:

• Sorting assumptions: If you relied on sequence IDs for temporal ordering, switch to explicit timestamp columns or event ordering fields.
• Range queries: Queries like WHERE id BETWEEN 1000000 AND 2000000 no longer map to a service or time period. Use service_id or timestamp columns instead.
• Incremental loads: ETL jobs that track progress via MAX(id) will break. Migrate to watermarking based on timestamps or sequence numbers in a separate tracking table.
• Deduplication: If your ETL uses ID-based deduplication, ensure the new ID scheme is truly unique before relying on it.

Example ETL adjustment in PySpark:

from pyspark.sql import SparkSession
from pyspark.sql.functions import col, max as spark_max

spark = SparkSession.builder.appName("IDMigrationETL").getOrCreate()

# Old approach (breaks with distributed IDs)
# last_id = spark.sql("SELECT MAX(id) FROM orders").collect()[0][0]

# New approach (uses timestamp watermark)
last_watermark = spark.sql(
    "SELECT MAX(created_at) FROM etl_checkpoint WHERE table_name = 'orders'"
).collect()[0][0]

df = spark.read.jdbc(
    url="jdbc:postgresql://localhost/mydb",
    table="orders",
    predicates=[f"created_at > '{last_watermark}'"],
    properties={"user": "analyst", "password": "***"}
)

# Process and deduplicate by (service_id, new_distributed_id)
df_deduplicated = df.dropDuplicates(["service_id", "new_distributed_id"])

# Update checkpoint
spark.sql(f"""
    INSERT INTO etl_checkpoint (table_name, last_watermark, updated_at)
    VALUES ('orders', NOW(), NOW())
""")

Monitoring and Rollback Considerations

During migration, instrument your systems to detect ID collisions and generation failures:

• Collision detection: Log all ID generation attempts and cross-check against the database. A spike in duplicates signals a problem.
• Latency tracking: Compare latency of the old sequence-based approach vs. the new approach. If the new generator is slower, services will timeout.
• Rollback plan: If collisions occur, revert to the old sequence immediately. This requires keeping the old sequence active during the entire migration window.

A simple monitoring query:

-- Monitor ID generation rate per service
SELECT 
  service_id,
  DATE_TRUNC('minute', created_at) as minute,
  COUNT(*) as id_count,
  COUNT(DISTINCT new_distributed_id) as unique_ids
FROM orders
WHERE created_at > NOW() - INTERVAL '1 hour'
GROUP BY service_id, DATE_TRUNC('minute', created_at)
HAVING COUNT(*) != COUNT(DISTINCT new_distributed_id)  -- Collision alert
ORDER BY minute DESC;

Key Takeaways

• Centralized database sequences become a bottleneck at scale; distributed ID allocation (ranges or Snowflake-style) eliminates per-ID contention.
• Migration requires dual-write validation and careful choreography across 100+ services; collisions and gaps must be detected and resolved before cutover.
• Data models and ETL pipelines must adapt: replace ID-based ordering with timestamp-based watermarks, and update deduplication logic to account for the new ID structure.
• Monitoring and rollback plans are critical; keep the old sequence active during migration to enable rapid recovery if collisions occur.