How to Control SEQUENCE Cache Behavior in PostgreSQL

How do I change SEQUENCE cache size in PostgreSQL?

CACHE in CREATE/ALTER SEQUENCE defines how many sequence values PostgreSQL pre-allocates in memory, reducing disk I/O and speeding inserts.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does the CACHE option do?

CACHE tells PostgreSQL to reserve a block of sequence numbers in shared memory. When a session calls nextval, values are served from memory until the cache is exhausted, then the next block is written to disk. Larger caches reduce disk writes but risk value loss after crashes.

When should I increase sequence cache?

High-insert tables like Orders often benefit. If you batch-insert thousands of rows per second, a cache of 100–1000 can remove sequence bottlenecks. Measure WAL and disk I/O before and after.

How to set cache on a new sequence?

Use CREATE SEQUENCE with the CACHE clause. Choose a size that balances speed and durability. Example below creates a 100-value cache for order IDs.

How to change cache on an existing sequence?

ALTER SEQUENCE lets you modify cache without recreating. The change is instantaneous and applies to the next write-back.

Does cache affect uniqueness?

No. PostgreSQL ensures each value is handed out once. Crash can skip cached but unissued numbers, yet gaps are already expected in surrogate keys.

Best practices for sequence caching

Start with CACHE 1 for low-traffic tables. Raise gradually while monitoring WAL volume and autovacuum. Avoid very large caches on sequences that matter for audit trails, as bigger gaps appear after restart.

Example: speeding bulk order inserts

The example query shows creating an Orders table with a high-cache sequence and demonstrates the performance gain.

Can I disable caching?

Yes—set CACHE 1. This forces every increment to hit disk, maximizing durability at the cost of speed.

Why How to Control SEQUENCE Cache Behavior in PostgreSQL is important

How to Control SEQUENCE Cache Behavior in PostgreSQL Example Usage


-- Fast bulk insert using a cached sequence
INSERT INTO Orders (customer_id, order_date, total_amount)
SELECT id, NOW(), 0
FROM Customers
LIMIT 10000;

How to Control SEQUENCE Cache Behavior in PostgreSQL Syntax


-- Create a sequence with custom cache
CREATE SEQUENCE order_id_seq
    INCREMENT BY 1
    START WITH 1
    MINVALUE 1
    CACHE 100;

-- Attach it to Orders.id
ALTER TABLE Orders ALTER COLUMN id SET DEFAULT nextval('order_id_seq');

-- Change cache size later
ALTER SEQUENCE order_id_seq CACHE 20;

Common Mistakes

Setting CACHE to thousands without testing. Large caches widen gaps after restart and bloat WAL. Fix: increment gradually, benchmark, and monitor WAL size.
Forgetting to ALTER SEQUENCE after copying Oracle DDL. Oracle defaults to CACHE 20; PostgreSQL defaults to 1. Fix: explicitly define the desired CACHE value during migration.