Alright, let’s talk about sequences. Think of a sequence as a number dispenser. You walk up to it, press the big red button, and out pops a number, one higher than the last. It’s a simple, brilliant, and utterly essential concept for generating unique identifiers. The SERIAL pseudo-type you might have used is just PostgreSQL being friendly and wrapping this whole process into a one-liner. But when you use CREATE SEQUENCE directly, you get the keys to the machine. You can tweak its gears, and with that power comes the responsibility not to shoot yourself in the foot.

The basic incantation is straightforward:

CREATE SEQUENCE my_sequence_name;

This gives you a dispenser that starts at 1 and counts up by 1 forever. Useful, but boring. The real magic is in the parameters you can tack onto that statement.

The Core Knobs to Twist

Let’s break down the key options you have. This is where you stop being a user and start being a plumber.

INCREMENT: This does exactly what it says on the tin. Want to count by 5s? INCREMENT 5. Want to count backwards for some reason? INCREMENT -1. It’s not just for positive integers. The default is, sensibly, 1.

MINVALUE / MAXVALUE: These define the hard boundaries of your sequence. The defaults are usually what you want (1 and the max value for a bigint, 2^63-1, which is a number so large you will die of old age before your app hits it). But you might change them if, for instance, you’re using a negative INCREMENT and want to set a lower bound.

CYCLE / NO CYCLE: This is a fantasticly terrible idea. When a ascending sequence hits its MAXVALUE, what should it do? NO CYCLE (the default) will throw an error. This is the safe, responsible choice. CYCLE will wrap around back to MINVALUE and start all over again. Why is this terrifying? Uniqueness. If you’re using this sequence as a primary key, cycling will absolutely, guaranteed, eventually cause a duplicate key violation. Only use CYCLE for things that are genuinely cyclical and where uniqueness is not a concern, like generating a repeating pattern of numbers for some external report. If you use it for a PK, I will know, and I will be disappointed.

CACHE: Ah, CACHE. The ultimate performance trap. To avoid hitting the disk for every single nextval() call, PostgreSQL can grab a block of numbers at once and hand them out from memory. So, CACHE 20 means it pre-allocates 20 values and only updates the on-disk sequence value when that block is used up. This is great for performance. The pitfall? If your database crashes, any cached but unused sequence values are lost forever. This creates gaps in your sequence. This is normal and by design. The trade-off is performance for absolute continuity. If you cannot tolerate gaps under any circumstances (e.g., legal requirements for invoice numbers), you must set CACHE 1. For 99.9% of applications, gaps are meaningless and the performance boost from a higher cache (like 20, 50, or even 100) is well worth it.

A Realistic Example

Let’s create a sequence that’s optimized for a high-throughput application, acknowledges it will have gaps, and knows its limits.

CREATE SEQUENCE order_id_seq
    INCREMENT 1
    MINVALUE 1
    MAXVALUE 9223372036854775807  -- that's the bigint max, you don't have to type it
    START 1000                    -- start at 1000, because why not?
    CACHE 50
    NO CYCLE;

Now, to get a value, you use nextval():

SELECT nextval('order_id_seq');
-- nextval
-----------
--    1000

SELECT nextval('order_id_seq');
-- nextval
-----------
--    1001

To see where you’re at without incrementing, use currval(), but only after you’ve called nextval() at least once in your session. Otherwise, it has no idea what you’re talking about.

SELECT currval('order_id_seq');
-- currval
-----------
--    1001

The “What If I Messed Up?” Department

Changed your mind? No problem. ALTER SEQUENCE is your friend. Maybe you started with CACHE 1 and your traffic increased.

ALTER SEQUENCE order_id_seq CACHE 50;

Or perhaps you need to restart the sequence for a test database (be incredibly careful doing this anywhere else).

ALTER SEQUENCE order_id_seq RESTART WITH 1000;

A crucial note on ALTER SEQUENCE: It doesn’t magically re-number existing values in tables. It only changes the numbers that will be handed out next. It’s about the future, not the past.

The direct control CREATE SEQUENCE gives you is a hallmark of PostgreSQL’s power. You’re not just using a feature; you’re tuning an engine. Use the CACHE, understand the trade-off of CYCLE, and you’ll have a robust system for generating identifiers that can scale with your application.