Warning Index Issues

Integer Primary Key

A table uses an integer (4-byte) primary key that may exhaust available IDs. For high-volume tables, this can cause application failures when IDs run out.

What is this issue?

PostgreSQL's integer type is a 4-byte signed integer with a maximum value of 2,147,483,647
(about 2.1 billion). While this seems large, high-volume applications can exhaust this range:

- 1 million inserts/day = exhausted in ~6 years
- 10 million inserts/day = exhausted in ~7 months
- 100 million inserts/day = exhausted in ~3 weeks

Once exhausted, INSERTs will fail with an error, causing application outages.

Common problematic types:
- integer / int / int4
- serial (creates an integer sequence)
- smallint / int2 (max 32,767 - even more dangerous)

Why it matters

Application Failure

When IDs exhaust, all inserts fail with sequence overflow errors

Difficult Migration

Migrating from integer to bigint on large tables is complex and time-consuming

Downtime Risk

Emergency migrations may require extended downtime

Cascade Effects

Foreign keys referencing the table also need updates

How PG Pilot detects it

```sql
SELECT
  c.table_schema,
  c.table_name,
  c.column_name,
  c.data_type,
  pg_size_pretty(pg_relation_size(c.table_schema || '.' || c.table_name)) AS table_size
FROM information_schema.columns c
JOIN information_schema.table_constraints tc
  ON c.table_schema = tc.table_schema
  AND c.table_name = tc.table_name
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
  AND c.column_name = kcu.column_name
WHERE tc.constraint_type = 'PRIMARY KEY'
  AND c.data_type IN ('integer', 'smallint');
```

How to fix it

1

Assess current usage

Check how much of the integer range is used:

SELECT
  max(id) AS current_max,
  2147483647 AS max_possible,
  round(100.0 * max(id) / 2147483647, 2) AS percent_used
FROM your_table;

Also check the sequence:

SELECT last_value, max_value
FROM your_table_id_seq;
2

Plan the migration window

For large tables, migration requires careful planning:
- Estimate migration time based on table size
- Plan for foreign key updates
- Schedule during low-traffic periods

3

Alter the column type

For PostgreSQL 11+, this can be done with minimal locking:

ALTER TABLE your_table
  ALTER COLUMN id TYPE bigint;

For older versions or very large tables, consider using
pg_repack or a blue-green deployment strategy.

4

Update the sequence

Change the sequence to bigint as well:

ALTER SEQUENCE your_table_id_seq AS bigint;

Prevention

  • Always use `bigint` or `bigserial` for primary keys in new tables
  • Use UUIDs for distributed systems
  • Monitor sequence usage with PG Pilot
  • Plan migrations before reaching 50% capacity

Related Issues

Sequence Near Exhaustion

Related Features

Index Health

PostgreSQL Documentation