SWE-0002

Insufficient Integer Size for Growth

RelationalIoT, ApplicationType:SocialMediaReliabilityDataTypeMisuse
Cantorian Technical Debt Magnitude: ℵ₀ (Countable)

Description

Using 32-bit integers for auto-incrementing primary keys or counters that will exceed 2.14 billion, causing complete write failures when the limit is reached. This weakness stems from underestimating data scale or using database defaults without consideration. Modern applications can reach billions of records faster than anticipated, especially with IoT devices, user-generated content, or audit logs.

Illustrative Cantor Point

The Cantor Point occurs during initial table creation when choosing INT over BIGINT. The developer thinks "2 billion is practically infinite" without calculating actual growth rates or considering the system's intended lifespan. This 4-byte savings becomes a time bomb that threatens the entire system's ability to create new records.

Categories: CP-Schema, CP-API, CP-Process

Real-World Examples / Observed In

  • Heroku (2023): Integer overflow in authorization table caused platform-wide outage, preventing all deployments when the ID exceeded 32-bit limits
  • Etsy: Preemptively shut down the site when discovering an ID approaching 32-bit limit to prevent data corruption
  • Common Pattern: Many startups hit this limit 3-5 years after launch when initial "we'll never have that many records" assumptions prove false

Common Consequences & Impacts

Technical Impacts

  • - Complete write failures on affected tables
  • - Foreign key constraint violations in related tables
  • - Emergency migrations requiring table locks
  • - Potential data corruption if overflow isn't caught

Human/Ethical Impacts

  • - Users unable to access services
  • - Developers working through nights on emergency fixes
  • - Business operations halted
  • - Potential data loss affecting user history

Business Impacts

  • - Total service outage (no new users, orders, posts)
  • - Emergency maintenance windows
  • - Customer trust erosion
  • - Revenue loss during downtime

Recovery Difficulty & Escalation

8.5
4

ADI Principles & Axioms Violated

  • Principle of Fragile Stability: The system works perfectly until the exact moment it catastrophically fails
  • Principle of Invisible Decay: The approaching limit is invisible until it's too late
  • Principle of Emergent Transformation: Growth patterns change as systems scale

Detection / 60-Second Audit

-- PostgreSQL: Check INT columns approaching overflow
WITH int_columns AS (
    SELECT 
        schemaname,
        tablename,
        attname as column_name,
        atttypid
    FROM pg_stats 
    WHERE schemaname = 'public' 
    AND (atttypid = 'int4'::regtype OR atttypid = 'int2'::regtype)
)
SELECT 
    ic.*,
    'SELECT MAX(' || ic.column_name || ') FROM ' || ic.tablename || ';' as check_query,
    CASE 
        WHEN ic.atttypid = 'int4'::regtype THEN '2,147,483,647'
        WHEN ic.atttypid = 'int2'::regtype THEN '32,767'
    END as max_value
FROM int_columns ic
JOIN pg_stats ps ON ic.tablename = ps.tablename AND ic.column_name = ps.attname
WHERE ps.n_distinct > 1000000  -- Focus on high-cardinality columns
OR ic.column_name LIKE '%_id'
ORDER BY ps.n_distinct DESC;

-- Check actual usage percentage
SELECT 
    nspname || '.' || relname as table_name,
    attname as column_name,
    CASE 
        WHEN atttypid = 'int4'::regtype THEN 
            ROUND((pg_sequence_last_value(pg_get_serial_sequence(nspname||'.'||relname, attname))::numeric / 2147483647) * 100, 2)
        WHEN atttypid = 'int2'::regtype THEN 
            ROUND((pg_sequence_last_value(pg_get_serial_sequence(nspname||'.'||relname, attname))::numeric / 32767) * 100, 2)
    END as percent_used
FROM pg_attribute a
JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE a.attnum > 0 
AND NOT a.attisdropped
AND (a.atttypid = 'int4'::regtype OR a.atttypid = 'int2'::regtype)
AND pg_get_serial_sequence(n.nspname||'.'||c.relname, a.attname) IS NOT NULL
ORDER BY percent_used DESC NULLS LAST;
-- MySQL: Find INT columns that may overflow
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    COLUMN_TYPE,
    CASE 
        WHEN DATA_TYPE = 'int' AND COLUMN_TYPE LIKE '%unsigned%' THEN '4,294,967,295'
        WHEN DATA_TYPE = 'int' THEN '2,147,483,647'
        WHEN DATA_TYPE = 'mediumint' AND COLUMN_TYPE LIKE '%unsigned%' THEN '16,777,215'
        WHEN DATA_TYPE = 'mediumint' THEN '8,388,607'
        WHEN DATA_TYPE = 'smallint' AND COLUMN_TYPE LIKE '%unsigned%' THEN '65,535'
        WHEN DATA_TYPE = 'smallint' THEN '32,767'
    END as max_value,
    CONCAT('SELECT MAX(', COLUMN_NAME, ') FROM ', TABLE_NAME) as check_query
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND DATA_TYPE IN ('int', 'smallint', 'mediumint')
AND (COLUMN_KEY = 'PRI' OR COLUMN_NAME LIKE '%_id' OR EXTRA LIKE '%auto_increment%');

-- Check AUTO_INCREMENT usage
SELECT 
    TABLE_NAME,
    AUTO_INCREMENT as current_value,
    CASE 
        WHEN DATA_TYPE = 'int' AND COLUMN_TYPE LIKE '%unsigned%' THEN 
            ROUND((AUTO_INCREMENT / 4294967295) * 100, 2)
        WHEN DATA_TYPE = 'int' THEN 
            ROUND((AUTO_INCREMENT / 2147483647) * 100, 2)
    END as percent_used
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_SCHEMA = DATABASE()
AND t.AUTO_INCREMENT IS NOT NULL
AND c.EXTRA LIKE '%auto_increment%'
ORDER BY percent_used DESC;
-- SQL Server: Check for integer columns near limits
SELECT 
    SCHEMA_NAME(t.schema_id) + '.' + t.name as table_name,
    c.name as column_name,
    ty.name as data_type,
    CASE 
        WHEN ty.name = 'int' THEN '2,147,483,647'
        WHEN ty.name = 'smallint' THEN '32,767'
        WHEN ty.name = 'tinyint' THEN '255'
    END as max_value,
    'SELECT MAX(' + c.name + ') FROM ' + SCHEMA_NAME(t.schema_id) + '.' + t.name as check_query
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE ty.name IN ('int', 'smallint', 'tinyint')
AND (c.is_identity = 1 OR c.name LIKE '%_id' OR c.name LIKE '%ID')
ORDER BY t.name, c.name;

-- Check identity column usage
SELECT 
    SCHEMA_NAME(t.schema_id) + '.' + t.name as table_name,
    c.name as column_name,
    IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) as current_value,
    CASE 
        WHEN ty.name = 'int' THEN 
            CAST(IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) / 2147483647.0 * 100 AS DECIMAL(5,2))
        WHEN ty.name = 'smallint' THEN 
            CAST(IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) / 32767.0 * 100 AS DECIMAL(5,2))
    END as percent_used
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE c.is_identity = 1
AND ty.name IN ('int', 'smallint')
ORDER BY percent_used DESC;

Prevention & Mitigation Best Practices

  1. Default to BIGINT: The 4-byte savings is negligible compared to the risk
  2. Consistent Types: Ensure foreign keys match primary key types exactly
  3. Growth Projection: Calculate worst-case scenarios - if ANY chance of exceeding 2B, use BIGINT
  4. Monitoring Thresholds: Alert at 50% capacity (1 billion for INT) to allow migration time
  5. UUID Alternative: Consider UUIDs for distributed systems or where sequential IDs pose security risks
  6. Migration Strategy: Plan for online schema changes before they're needed

Real World Examples

-- Heroku authorization table hit INT limit
CREATE TABLE authorizations (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 32-bit signed: max 2,147,483,647
    app_id INT NOT NULL,
    user_id INT NOT NULL,
    created_at TIMESTAMP
);
-- After 9 years, ID approached 2.1 billion
-- Platform-wide outage: no new deployments possible
-- IoT sensor data overwhelms counter
CREATE TABLE sensor_readings (
    reading_id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(50),
    temperature DECIMAL(5,2),
    recorded_at TIMESTAMP
);
-- 10,000 devices × 1 reading/minute = 14.4M/day
-- Hits INT limit in just 149 days!
-- Always use BIGINT for auto-incrementing IDs
CREATE TABLE authorizations (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,  -- 64-bit: 9,223,372,036,854,775,807
    app_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Would take 292 billion years at 1M inserts/second

-- Monitor approaching limits
SELECT 
    TABLE_NAME,
    AUTO_INCREMENT,
    CASE DATA_TYPE
        WHEN 'int' THEN 2147483647
        WHEN 'bigint' THEN 9223372036854775807
    END as max_value,
    (AUTO_INCREMENT / CASE DATA_TYPE
        WHEN 'int' THEN 2147483647
        WHEN 'bigint' THEN 9223372036854775807
    END) * 100 as percent_used
FROM information_schema.TABLES t
JOIN information_schema.COLUMNS c 
    ON t.TABLE_NAME = c.TABLE_NAME 
    AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
WHERE t.TABLE_SCHEMA = DATABASE()
AND c.COLUMN_KEY = 'PRI'
AND c.EXTRA = 'auto_increment'
AND AUTO_INCREMENT > 1000000;

AI Coding Guidance/Prompt

Prompt: "When creating database tables with ID columns:"
Rules:
  - Always use BIGINT for auto-incrementing IDs unless explicitly justified
  - Calculate maximum rows over 10-year period before choosing INT
  - Flag any INT primary keys in code review
  
Example:
  # Bad: Using INT by default
  CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT
  );
  
  # Good: Future-proof with BIGINT
  CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT
  );
  
  # Also Good: UUID for distributed systems
  CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid()
  );

Relevant Keywords

insufficient integer size growth Symptoms: slow queries, data inconsistency, constraint violations Preventive: schema validation, constraint enforcement, proper typing Tech stack: PostgreSQL, MySQL, SQL Server, Oracle Industry: all industries, enterprise, SaaS

Related Patterns

The Cantorian Technical Debt Magnitude scale gives developers an intuitive sense of magnitude beyond simple hour counts - some debts aren't just larger in scale, but qualitatively different in their complexity.

Cantor Points are critical decision junctures—or even moments of non-decision—where seemingly small choices can create drastically divergent futures for a system's integrity, security, and evolvability. These are the "forks in the road" where one path might lead to manageable complexity, while another veers towards systemic entanglement or even chaos. They often appear trivial at the time but can set in motion irreversible or costly-to-reverse consequences.

Applied Data Integrity (ADI) is a framework to understanding the far-reaching consequences of schema and data decisions that impact security and reliability, and accumulate into ethical debt that affects real human lives. Built on research from real-world incidents, ADI uncovered 7 Principles to identify when these decisions are being made, and how to make them better, to avoid future technical debt and potentially catastrophic "butterfly effects" of small decisions that ripple into chaotic technical and ethical debt.