Generate Unique Identifiers With Postgres Sequences

Hello. Enough chit chat, let’s blog.

Recently, I was working with an API that required every API request to include a unique identifier. Weird, but okay, easy enough. One problem: where do I get that identifier? Some ideas:

1. Ignore the unique part and just send the same number every time. In my case this would’ve worked, but I’m a #cooldev and I do things the right way.
2. Use the primary key of a record associated with the API request. Good thought, but I don’t have any database tables that match one-to-one with API requests.
3. Stick a UUID in there and call it a day. Nice, and I can get it from Ruby’s standard library, but the API only accepts integers. Okay.
4. Store the identifier in the database by creating a new table with two columns, name and value. Insert a row named api_request_id and increment value for each API request.

Option 4 sounds like a neat idea. It solves the problem and makes it easy to add other unique identifiers, just in case I need it down the road for some other goofy API.

I almost did some code, but something was nagging at me. This feels like a thing the database should do for me. I mean, this is exactly what a primary key is, right? Plus, I’m using Postgres and it’s always got my back. A few minutes googling around on The Google and there it is: CREATE SEQUENCE.

This feature is super powerful and can do a lot of cool things: counting backwards, cycles, temporary sequences. Postgres has some good and thorough documentation, and I encourage you to spend some time with it. For now, let’s solve the problem at hand. We can create our new sequence like so:

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

Pretty straightforward. We name our sequence api_request_id_seq, start at 1, increment by 1, and set no maximum (in practice, the max is 2⁶³ - 1). That’s all there is to it.

☞ A note about the CACHE option: setting this to a value N greater than one causes each database connection to preallocate N sequence values, rather than generating them on demand. This could have performance benefits in certain situations, but it can also lead to values being used in the wrong order and can cause holes in the sequence if the database connection closes before the preallocated values are used. Unless you know what you’re doing, maybe just set CACHE to 1, okay?

We can verify our sequence was created by firing up psql and using the \ds command to list sequences:

my_cool_database=# \ds;
                 List of relations
 Schema |          Name      |   Type   | Owner
--------+--------------------+----------+-------
 public | api_request_id_seq | sequence | Chris
(1 rows)

How do we get the next value? With the simplest SELECT statement you’ll ever write:

my_cool_database=# SELECT nextval('api_request_id_seq');
 nextval
---------
       1
(1 row)

my_cool_database=# SELECT nextval('api_request_id_seq');
 nextval
---------
       2
(1 row)

my_cool_database=# SELECT nextval('api_request_id_seq');
 nextval
---------
       3
(1 row)

Let’s wrap that in some Ruby (you heard about ApplicationRecord, right?):

module Sequences
  module ApiRequestId
    NAME = 'api_request_id_seq'

    def self.next
      result = ApplicationRecord.connection.execute("SELECT nextval('#{NAME}')")

      result.first['nextval']
    end
  end
end

And give it a try:

pry(main)> Sequences::ApiRequestId.next
4

pry(main)> Sequences::ApiRequestId.next
5

pry(main)> Sequences::ApiRequestId.next
6

Now we have a tiny one-liner we can drop in where we need a guaranteed unique, incrementing identifier.

So much for that feature. Project completed. With the time we saved, let’s dig a little deeper. What even is a sequence? According to the documentation, “CREATE SEQUENCE creates a new sequence number generator. This involves creating and initializing a new special single-row table with the name name.”

Hmm, so a sequence is a table with a single row. Let’s see what’s in that table.

my_cool_database=# SELECT * FROM api_request_id_seq;
 sequence_name      | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called
--------------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 api_request_id_seq |          6 |           1 |            1 | 9223372036854775807 |         1 |           1 |      31 | f         | t
 (1 row)

Huh. Neat. So it’s a table with some metadata, a name, and a value. Sounds familiar.

Remember how we said a sequence looks like a primary key? Let’s create a table and poke around:

my_cool_database=# CREATE TABLE my_cool_friends (id serial PRIMARY KEY, name character varying NOT NULL);
CREATE TABLE

my_cool_database=# INSERT INTO my_cool_friends (name) VALUES ('David'), ('Zachary');
INSERT 0 2

my_cool_database=# SELECT * FROM my_cool_friends;
 id |  name
----+---------
  1 | David
  2 | Zachary
(2 rows)

my_cool_database=# \d my_cool_friends;
                              Table "public.my_cool_friends"
 Column |       Type        |                       Modifiers
--------+-------------------+-------------------------------------------------------
 id     | integer           | not null default nextval('my_cool_friends_id_seq'::regclass)
 name   | character varying | not null
Indexes:
    "my_cool_friends_pkey" PRIMARY KEY, btree (id)

Oh hey, look at that. Our id column’s default value is nextval('my_cool_friends_id_seq'::regclass). Is it true? Could it be??

my_cool_database=# \ds
                 List of relations
 Schema |          Name          |   Type   | Owner
--------+------------------------+----------+-------
 public | api_request_id_seq     | sequence | Chris
 public | my_cool_friends_id_seq | sequence | Chris
(2 rows)

my_cool_database=# SELECT * FROM my_cool_friends_id_seq;
         sequence_name  | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called
------------------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 my_cool_friends_id_seq |          2 |           1 |            1 | 9223372036854775807 |         1 |           1 |      31 | f         | t
(1 row)

Super cool. Postgres is the best.

What's your favorite little-known Postgres gem? Let me know in the comments down there.