Comparison of Sequence Usage Across Oracle, PostgreSQL, and MySQL

Oracle, PostgreSQL and MySQL all provide mechanisms for generating integer identifiers, but the way they implement and use sequences (or equivalents) varies considerably.

1. Introduction

Three relational database management systems (RDBMS) – Oracle, PostgreSQL and MySQL – are used at Qunar. The article explores the Sequence object, a common database object for generating unique numbers, and compares its usage across the three systems.

2. Sequence usage scenarios

Primary key : Typically one sequence per table, though multiple tables can share a sequence (e.g., partitioned tables).

Non‑primary key : A sequence can be used independently for auto‑incrementing values across tables.

Note: Sequence values are not guaranteed to be contiguous because of transaction rollbacks or instance restarts.

3. Explicit call (Sequence 用法 1)

Creating a sequence and invoking it directly in INSERT statements.

SQL> CREATE SEQUENCE seq_test;
SQL> CREATE TABLE tb_test (test_id NUMBER PRIMARY KEY);
SQL> INSERT INTO tb_test (test_id) VALUES (seq_test.nextval);

$ psql -U alvin -d alvindb
alvindb=> CREATE SEQUENCE seq_test;
alvindb=> CREATE TABLE tb_test (test_id INTEGER PRIMARY KEY);
alvindb=> INSERT INTO tb_test (test_id) VALUES (nextval('seq_test'));

MySQL does not support an explicit CREATE SEQUENCE ; it uses AUTO_INCREMENT instead.

4. Implicit call via trigger (Sequence 用法 2)

Define a BEFORE INSERT trigger that fetches the next value from a sequence.

SQL> CREATE SEQUENCE seq_test2;
SQL> CREATE TABLE tb_test2 (test_id NUMBER PRIMARY KEY, test_order NUMBER);
SQL> CREATE OR REPLACE TRIGGER trg_b_ins_tb_test2
BEFORE INSERT ON tb_test2
FOR EACH ROW
BEGIN
SELECT seq_test2.nextval INTO :new.test_id FROM dual;
END;

PostgreSQL uses a similar function‑based trigger.

alvindb=> CREATE SEQUENCE seq_test2;
alvindb=> CREATE TABLE tb_test2 (test_id INTEGER PRIMARY KEY, test_order INTEGER);
alvindb=> CREATE OR REPLACE FUNCTION trgf_b_ins_tb_test2()
RETURNS TRIGGER AS $$
BEGIN
NEW.test_id := nextval('seq_test2');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
alvindb=> CREATE TRIGGER trg_b_ins_tb_test2 BEFORE INSERT ON tb_test2 FOR EACH ROW EXECUTE PROCEDURE trgf_b_ins_tb_test2();

Both databases allow inserting NULL for the identity column; the trigger supplies the next value.

5. Implicit call via DEFAULT (Sequence 用法 3)

Define the column’s DEFAULT to call the sequence.

SQL> CREATE SEQUENCE seq_test3;
SQL> CREATE TABLE tb_test3 (test_id NUMBER DEFAULT seq_test3.nextval PRIMARY KEY, test_order NUMBER);

Oracle 11g does not support this; Oracle 12c adds support. PostgreSQL has always supported DEFAULT nextval('seq') .

6. Implicit call via AUTO_INCREMENT (Sequence 用法 4)

MySQL’s AUTO_INCREMENT automatically creates an internal sequence and uses it as the column default. Oracle 12c also supports GENERATED BY DEFAULT ON NULL AS IDENTITY , which behaves similarly.

SQL> CREATE TABLE tb_test4 (test_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, test_order NUMBER);

alvindb=> CREATE TABLE tb_test4 (test_id SERIAL PRIMARY KEY, test_order INTEGER);

Both systems bind the generated sequence to the column; dropping the table removes the sequence, while dropping the sequence alone requires CASCADE because of the dependency.

7. Version support

PostgreSQL: DEFAULT nextval supported since 7.1 (2001‑08‑15); SERIAL since 8.0 (2005‑01‑19).

Oracle: Sequence in DEFAULT supported from 12c (2013‑07).

MySQL: AUTO_INCREMENT has been available since early versions; no separate sequence object.

8. Impact of DML/DDL on sequences

Tests show that INSERT statements that specify a value larger than the current sequence do not reset the sequence in Oracle and PostgreSQL. In MySQL (InnoDB), INSERT with a larger value and TRUNCATE affect the auto‑increment counter, while UPDATE/DELETE do not. In MySQL (MyISAM), both INSERT with larger values and UPDATE affect the counter, but DELETE does not.

9. Summary (七、总结)

Oracle and PostgreSQL behave similarly: INSERT advances the sequence, while UPDATE, DELETE, and TRUNCATE have no effect on the underlying sequence. MySQL’s behavior depends on the storage engine: InnoDB isolates the counter from UPDATE/DELETE, whereas MyISAM ties it to INSERT and UPDATE.

10. Recruitment notice

Qunar is hiring a senior PostgreSQL DBA; the posting includes contact information and promotional images.