in Databases

The Postgres equivalent of MySQL’s “auto_increment”

MySQL

Creating a table in MySQL with an auto increment is straightforward. The value is stored as part of the table definition and updated in that location. Creating a table with an auto_incrementing id in MySQL can be done as shown below.

CREATE TABLE t1 (
  id int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1 ;

Postgres

Postgres tracks this separately in the database with a “sequence.” You can stand up a new table using the “SERIAL” keyword to denote a type of auto_increment for a field, but behind the scenes, it creates a sequence for the table and tracks the integer in that sequence table. Below is an example of a sequence in Postgres.

test=# \d
No relations found.

test=# CREATE TABLE t1 (
  id SERIAL PRIMARY KEY
);

CREATE TABLE

test=# \d
            List of relations
 Schema |   Name    |   Type   |  Owner   
--------+-----------+----------+----------
 public | t1        | table    | postgres
 public | t1_id_seq | sequence | postgres
(2 rows)

test=# \x
Expanded display is on.
test=# select * from t1_id_seq;
-[ RECORD 1 ]-+--------------------
sequence_name | t1_id_seq
last_value    | 1
start_value   | 1
increment_by  | 1
max_value     | 9223372036854775807
min_value     | 1
cache_value   | 1
log_cnt       | 0
is_cycled     | f
is_called     | f

test=# \x
Expanded display is off.

As you can see, a sequence is generated from the use of the SERIAL data type. This sequence lists the last value, the start value, how much to increment by and many other values. If we were to pg_dump this database, we will see the below information regarding the sequence.

bash-4.2$ pg_dump test 
...
CREATE TABLE t1 (
    id integer NOT NULL
);


ALTER TABLE public.t1 OWNER TO postgres;

--
-- Name: t1_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE t1_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.t1_id_seq OWNER TO postgres;

--
-- Name: t1_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE t1_id_seq OWNED BY t1.id;
...

Summary

We can tell by the Postgres output that it is possible to change the values for “INCREMENT BY” for this individual sequence. In MySQL changing the amount the integer increments by is done at a global level with auto_increment_increment and auto_increment_offset. The increment by and offset values can be important to pay attention to when setting up multi-master replication, or when you are bringing data from multiple write masters back to a central server. Utilizing offsets when managing sequences or incrementing values can help to avoid key collisions when data is replicated or copied between servers.

Write a Comment

Comment

  1. if you change the INCREMENT BY value in a multi-master replicated servers then that change is also applied to all the replicated servers (DDL changes) thus causing insert collisions again. Have I misunderstood your conclusion?

    • This is what I’ve seen when setting up multi master replication in postgres using BDR. Any DDL changes are applied to all replicated severs. so is there a way to set auto increment offset for individual masters/servers in postgres.

      • Sam,

        Re-reading over my summary, I can see where it may have introduced some confusion. I wrote this article shortly after starting to work with postgres and that summary denotes my thoughts on the importance of sequences in mysql and how they are used.

        The way postgres has gotten around the offset/sequence issue in a master-master (bdr) setup is by introducing a new data type for global sequences. The sequence itself will coordinate id reservation between nodes ensuring no duplication. I think this is actually a pretty elegant way of handling it. Below is their link on how they work.
        http://bdr-project.org/docs/next/global-sequences.html

        We’ve been somewhat reluctant to use BDR in our environment due to the fact that it globally locks all servers in the cluster for ddl changes. From the docs located here: http://bdr-project.org/docs/next/ddl-replication-advice.html

        DDL is a heavier weight operation than on standalone PostgreSQL. Performing DDL on any node will cancel (abort) currently running transactions on all nodes with an ERROR, and will reject new DML (INSERT, UPDATE and DELETE on all nodes with an ERROR with SQLSTATE 55P03:

        I’d advise you to use caution with DBR for this reason based on your deployment schedule and outage window availability.

        The traditional way of managing offsets that you would use in mysql cannot be used with streaming replication in postgres because streaming replication requires the slaves to exactly match the master, even at the configuration level. You can’t introduce differences between the master or slave like offsets or do things that are available in mysql like `set sql_log_bin = 0; [query]; set sql_log_bin = 1;` in streaming replication. Since the sequences aren’t managed globally from a config file, you’ll run into the issue that you have with BDR where changes to the sequences replicate between clusters.

        If you want to implement this type of strategy in postgres you would need to consider running each node as a postgres master and then using trigger based replication like bucardo or londiste. In those replication strategies you can introduce the offsets for each sequence and the trigger based replication software will handle data copy back and forth between your master nodes. These solutions don’t replicate DDL, so you may run into a more complicated deployment situation handling things that way since you’d need to manage and coordinate deployments to multiple nodes in the same cluster independently.

        Postgres development has seemed to really go in the direction of streaming replication and DBR, so i think you are on the right track investigating those options.

        Overall I think Postgres is headed in the right direction with their replication strategies, but I don’t feel it is at the maturity/enterprise level of mysql from a multi-master/high availability front.

        • Thank you for your response. its very beneficial and I was able to use global sequences to get the offset working within BDR.

          From various vlog that I’ve found, BDR intends to get rid of global locks but make it more granular to have specific locks along side merging into the 9.5 postgres version.

          When I investigated older blogs it seemed like there was intention to introduce multi master replication to be built into postgres with the postgres-r project, but this was only a prototype and never got around to merge it to the latest postgres version http://www.postgres-r.org/