PostgreSQL High Availability Cookbook - Second Edition

How to do it...

Execute the following SQL statements as the postgres user to create a function that can generate IDs as we described:

1. Create the schema to hold shard-related functionality:

        CREATE SCHEMA shard;

2. Create a sequence to act as an ID generator:

        CREATE SEQUENCE shard.table_id_seq;

3. Create the function that will generate IDs:

        CREATE OR REPLACE FUNCTION shard.next_unique_id(
          shard_id INT
        )
        RETURNS BIGINT AS
        $BODY$
        DECLARE
          epoch    DATE := '2016-01-01';
          epoch_ms BIGINT;
          now_ms   BIGINT;
          next_id  BIGINT;
        BEGIN
          epoch_ms := floor(
            extract(EPOCH FROM epoch) * 1000
          );
          now_ms := floor(
            extract(EPOCH FROM clock_timestamp()) * 1000
          );
          next_id := (now_ms - epoch_ms) << 22
              | (shard_id << 11)
     | (nextval('shard.table_id_seq') ...