Definition
- Sequence – a standalone object created with
CREATE SEQUENCE, drawn withnextval; independent of any table, shareable across multiple tables, explicitly controllable. - Identity/auto-increment – a column-level feature that binds a generator to one table's column and fills it automatically on insert.
- Independence – sequences are reusable and multi-table; identity is table-bound and automatic.
- Gaps are normal – values are cached per session and rolled-back transactions consume values; sequences guarantee uniqueness and order, not contiguity.
- Gapless alternative – for legally gapless series like invoice numbers, use a counter row updated inside the transaction and accept the contention.
A sequence is like a central ticket machine in National Bank of India's lobby: anyone from any counter can pull the next ticket number. An identity column is like a ticket roll bolted to one counter. Gaps happen because a customer who pulls ticket 51 and then leaves without being served still spent ticket 51. The machine never reissues it: unique rising numbers, not a perfectly unbroken run.
Understanding the Concepts
What is a database sequence?
A sequence is created with CREATE SEQUENCE and you draw values with nextval.
-- Create a shared sequence at National Bank of India
CREATE SEQUENCE global_ref_seq
START WITH 1000
INCREMENT BY 1
CACHE 50;
-- Draw the next value
SELECT nextval('global_ref_seq'); -- 1000
SELECT nextval('global_ref_seq'); -- 1001
-- Inspect the current value in this session
SELECT currval('global_ref_seq'); -- 1001
-- Jump the sequence
SELECT setval('global_ref_seq', 5000);
What is an identity or auto-increment column?
An identity column binds a generator to exactly one column of one table.
-- PostgreSQL: GENERATED ALWAYS AS IDENTITY
CREATE TABLE transactions (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
amount NUMERIC(18,2)
);
-- MySQL: AUTO_INCREMENT
CREATE TABLE transactions (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
amount DECIMAL(18,2)
);
-- Insert without specifying id
INSERT INTO transactions (amount) VALUES (50000.00);
-- id is automatically assigned
In PostgreSQL, serial and identity are both backed by a sequence under the hood.
What are the key differences?
| Feature | Sequence | Identity/Auto-Increment |
|---|---|---|
| Independence | Standalone object | Bound to one table column |
| Multi-table sharing | Yes (multiple tables use one sequence) | No |
| Explicit control | nextval, setval, currval | Automatic on insert |
| Pre-insert access | Yes (know the id before inserting) | Not easily |
| Configuration | Start, increment, min, max, cache, cycle | Limited |
-- Shared sequence across two tables
CREATE TABLE transfers (
id BIGINT DEFAULT nextval('global_ref_seq') PRIMARY KEY,
amount NUMERIC(18,2)
);
CREATE TABLE loans (
id BIGINT DEFAULT nextval('global_ref_seq') PRIMARY KEY,
principal NUMERIC(18,2)
);
-- Both draw from the same pool: globally unique IDs
Why do sequences have gaps?
Gaps come from two sources: caching and rollbacks.
Caching:
Session A grabs values 1001-1050 from the sequence cache
Session A uses only 1001-1010 and disconnects
Values 1011-1050 are discarded: gap!
Rollbacks:
Transaction X draws nextval = 1051
Transaction X fails and rolls back
Value 1051 is consumed but no row has it: gap!
-- Demonstration of gaps
BEGIN;
INSERT INTO transactions (amount) VALUES (10000.00); -- gets id 1001
ROLLBACK; -- transaction rolled back
INSERT INTO transactions (amount) VALUES (20000.00); -- gets id 1002
-- id 1001 is permanently consumed but no row exists for it
This is intentional. Making a sequence gapless would require serializing every insert through a single lock, destroying concurrency.
How do you implement a gapless counter when required?
For legally required gapless series like invoice numbers, use a counter row inside the transaction.
-- Gapless counter table
CREATE TABLE counters (
name VARCHAR(50) PRIMARY KEY,
value BIGINT NOT NULL DEFAULT 0
);
INSERT INTO counters (name) VALUES ('invoice_number');
-- Draw next gapless number inside the transaction
BEGIN;
UPDATE counters SET value = value + 1 WHERE name = 'invoice_number'
RETURNING value;
-- Returns the next invoice number
-- If the transaction rolls back, the counter is also rolled back: no gap
COMMIT;
The trade-off is contention: every concurrent invoice insert serializes on this one row.
How do you use sequences in Java?
Rajesh uses the sequence to get the id before inserting at National Bank of India.
package com.bank.repository;
import java.math.BigDecimal;
import java.sql.*;
public class TransferRepository {
public long createTransfer(Connection conn, BigDecimal amount)
throws SQLException {
// Get the id first from the shared sequence
long id;
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(
"SELECT nextval('global_ref_seq')")) {
rs.next();
id = rs.getLong(1);
}
// Insert with the known id
String sql = "INSERT INTO transfers (id, amount) VALUES (?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setLong(1, id);
ps.setBigDecimal(2, amount);
ps.executeUpdate();
}
return id;
}
}
What are common mistakes with sequences?
- Relying on contiguity: never use
MAX(id)as a row count. - Assuming
id + 1is the next row: gaps exist. - Not setting an appropriate cache size: too small hurts throughput, too large wastes values on restart.
- Forgetting to grant USAGE on the sequence to the application role.
-- Common mistake: using MAX(id) as row count
SELECT MAX(id) FROM transactions; -- 50000
-- Actual row count might be 48500 due to gaps
-- Correct: SELECT COUNT(*) FROM transactions;
What should a senior engineer know?
- Never rely on id contiguity for any business logic.
- Choose a shared sequence when multiple tables need ids from one pool.
- Choose identity for the common case of one table filling its own key.
- Set cache size appropriate to throughput (higher for high-insert tables).
- For gapless legal numbering, use a transactional counter and accept the serialization cost.
Visual Overview
Sequence (standalone): Identity (table-bound):
┌────────────────────┐ ┌────────────────────┐
│ global_ref_seq │ │ transactions.id │
│ nextval() ──▶ 1001 │ │ AUTO_INCREMENT │
│ nextval() ──▶ 1002 │ │ 1, 2, 3, ... │
└────────┬───────────┘ └────────────────────┘
│
┌─────┴─────┐
▼ ▼
transfers loans
(id=1001) (id=1002)
Both share one sequence
Gaps from caching:
Cache block: [1003-1050]
Session uses: 1003, 1004, 1005
Session ends: 1006-1050 discarded ──▶ GAP
Key Takeaway
A database sequence is a standalone object that generates a monotonically increasing series of numbers on demand. It is independent of any table, so it can be shared across multiple tables and called before inserting to know the id in advance. An identity or auto-increment column is a column-level convenience that binds a generator to one table's column and fills it automatically.
The difference is independence and control. A sequence is reusable, multi-table, and explicitly controllable. Identity is table-bound and automatic. Choose a shared sequence when several tables need ids from one pool or when you need the id before the insert. Choose identity for the ordinary case of one table filling its own primary key.
Both develop gaps, and this is normal. Caching causes gaps because a session grabs a block of values and discards any unused ones when it ends. Rollbacks cause gaps because a rolled-back transaction still consumed its value. Making sequences gapless would serialize every insert through one lock and kill concurrency.
At the National Bank of India, Rajesh shares one sequence across transfer and loan reference numbers for global uniqueness, and he accepts gaps as normal. For legally required gapless invoice numbers, he uses a transactional counter row and accepts the serialization cost.
Remember one sentence: a sequence is a standalone, shareable number generator while identity binds one to a single table, and both gap on caching and rollbacks because they guarantee uniqueness and order, not contiguity.
---