PostgreSQL: How to share a sequence between tables

Introduction

In PostgreSQL, sequences are special objects used to generate unique numbers, often for primary key values. This tutorial will guide you on how to create and share a sequence across multiple PostgreSQL tables, ensuring consistent and unique identifiers throughout your database.

Understanding Sequences

Before diving into sharing sequences, it’s essential to understand what sequences are and how they function in PostgreSQL. A sequence is a database object that generates a serially incrementing number sequence, commonly used for unique identification purposes. Unlike AUTO_INCREMENT in some SQL databases, sequences in PostgreSQL can be created independently of tables and are often used when finer control over the identifiers is necessary.

CREATE SEQUENCE my_sequence;

This code creates a new sequence called ‘my_sequence’ with default increment settings.

Creating a Shared Sequence

To share a sequence between tables, first create one sequence that all desired tables will use.

CREATE SEQUENCE shared_sequence START WITH 1 INCREMENT BY 1;

With ‘shared_sequence’ in place, we can now reference it in multiple table definitions.

Using the Shared Sequence

To use the shared sequence when defining a new table, specify the ‘DEFAULT’ keyword with the ‘nextval’ function, passing the sequence name to retrieve the next value from the sequence for each insert command.

CREATE TABLE first_table (
 id INTEGER NOT NULL DEFAULT nextval('shared_sequence'),
 name TEXT,
 PRIMARY KEY (id)
);

CREATE TABLE second_table (
 id INTEGER NOT NULL DEFAULT nextval('shared_sequence'),
 description TEXT,
 PRIMARY KEY (id)
);

Both ‘first_table’ and ‘second_table’ will now use ‘shared_sequence’ for their primary keys.

Advanced Usage

Advanced usage scenarios may involve setting up triggers for custom behavior when a new row is inserted, updating multiple columns with values from a single sequence, or maintaining a centralized control over identifier allocation in your database.

CREATE OR REPLACE FUNCTION advance_shared_sequence()
 RETURNS trigger AS $
BEGIN
 NEW.id_1 := nextval('shared_sequence');
 NEW.id_2 := currval('shared_sequence');
 RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_advance_sequence
 BEFORE INSERT ON third_table
 FOR EACH ROW EXECUTE FUNCTION advance_shared_sequence();

Here, a trigger function ‘advance_shared_sequence’ ensures that ‘id_1’ and ‘id_2’ for ‘third_table’ are populated using the same sequence number during an insert.

Data Integrity and Performance Considerations

When sharing a sequence between tables, consider the implications on data integrity and performance. Using a single sequence guarantees unique values across tables, which can be advantageous for certain database designs. However, it also introduces a central point of contention when multiple tables are experiencing concurrent inserts, potentially leading to performance bottlenecks.

Sequence Management

Database administrators can manage shared sequences by altering sequence properties, such as changing the increment, resetting the value, or configuring caching.

ALTER SEQUENCE shared_sequence INCREMENT BY 2;
ALTER SEQUENCE shared_sequence RESTART WITH 100;
ALTER SEQUENCE shared_sequence CACHE 10;

These commands adjust the behavior of ‘shared_sequence’ to skip numbers, reset starting point, and cache sequence numbers for improved performance.

Conclusion

Sharing a sequence between tables in PostgreSQL is a powerful technique for maintaining consistent identifiers throughout your database. Though useful, careful planning around data integrity and performance is necessary to ensure efficient database operation. This tutorial outlined the steps to set up and use shared sequences, as well as addressing advanced use cases and management considerations.