Right, so you want to build a system where things need unique IDs across different tables. Maybe you’re stitching together events from a user action log and an admin audit log into a single timeline. Or perhaps you’re building a distributed system and you need to guarantee that an ID generated in your orders table in one database will never, ever clash with an ID generated in your invoices table in another.

Your first thought might be, “I’ll just use a UUID.” And for many cases, that’s a perfectly cromulent choice. But UUIDs are big, they’re not sequential (which can be murder on your indexes if you’re not careful), and sometimes you just want a nice, tidy, globally unique integer. This is where the humble sequence can flex its muscles. A sequence is just a database object that spits out a series of ever-increasing numbers, completely independent of any single table. That independence is the key to our global uniqueness scheme.

Let’s create one. It’s dead simple.

CREATE SEQUENCE global_id_sequence;

Boom. You now have a counter that will dutifully hand out the next number every time you ask. To get a value from it, you use the nextval() function.

SELECT nextval('global_id_sequence');
-- Returns: 1

SELECT nextval('global_id_sequence');
-- Returns: 2

Notice that it just keeps going, oblivious to any tables you might have. It exists in its own little world of incremental bliss. This is what makes it “global.”

Hooking It Into Your Tables

The real magic happens when you tell your tables to use this shared sequence as a default value for their primary keys. Let’s say you have an events table and an audit_log table. You want every ID in both to be unique across the entire system.

CREATE TABLE events (
    id BIGINT PRIMARY KEY DEFAULT nextval('global_id_sequence'),
    event_name TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE audit_log (
    id BIGINT PRIMARY KEY DEFAULT nextval('global_id_sequence'),
    action TEXT NOT NULL,
    performed_at TIMESTAMPTZ DEFAULT NOW()
);

Now, whenever you insert a row into either table and don’t specify an id, the database will politely go ask our global_id_sequence for the next number.

INSERT INTO events (event_name) VALUES ('user_login');
INSERT INTO audit_log (action) VALUES ('purge_old_data');

SELECT * FROM events WHERE id = 3; -- Might be an event
SELECT * FROM audit_log WHERE id = 4; -- Might be an audit entry

See? The ID 3 is used in one table, 4 in the other. There will never, ever be a duplicate id between these two tables. It’s mathematically impossible as long as you let the sequence do its job.

The Glaring, Obvious Pitfall (You Knew This Was Coming)

I can hear you thinking, “Wait, what if I manually specify an id?” And you’ve just put your finger on the single biggest way to blow this whole scheme to smithereens. The sequence only provides a default value. It can’t stop some well-meaning but disastrous code from doing this:

INSERT INTO events (id, event_name) VALUES (100, 'manual_insert');

If 100 hasn’t been handed out by the sequence yet, you’ve just created a time bomb. The sequence will eventually hand out 100, and when it does, any insert that relies on the default will try to use that ID, resulting in a primary key violation. It’s a spectacular way to crash your application at 3 AM.

The solution is discipline. Never, ever manually insert a value into an id column that’s fed by a sequence. Treat those columns as read-only from your application’s perspective. If you need to reference an existing ID, that’s a SELECT operation, not an INSERT.

Best Practices for the Paranoid

  1. Use a dedicated schema: Don’t just leave your global sequences lying around in the public schema. Create a sequences or meta schema to house them. It keeps things organized and lets you set specific permissions.
  2. Plan for ranges: In a complex, multi-database system (e.g., using logical replication), you can pre-allocate blocks of IDs to different databases to prevent conflicts. One database’s sequence starts at 1, the next at 1,000,000, the next at 2,000,000. It’s a bit old-school, but it works.
  3. BIGINT. Always.: Just use BIGINT. Don’t try to be clever with INT. The ceiling of a regular INT is about 2.1 billion, which sounds like a lot until your event-sourcing system hits it in two years. A BIGINT ceiling is a number so large it’s basically a theoretical concept. It’s cheap insurance.
  4. Monitor the sequence: Keep an eye on how fast it’s advancing. A query like SELECT last_value FROM global_id_sequence; will tell you the last value used. If you see it jumping by millions a day, you know something is generating data at a ferocious rate.

So, is this better than UUIDs? It depends. It gives you smaller, sequential, human-friendly integers at the cost of requiring a tiny bit of central coordination (the sequence itself). For a single database, it’s often a fantastic and underused pattern. Just for the love of all that is holy, don’t manually insert IDs.