PostgreSQL 12 High Availability Cookbook - Third 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 a function that will generate IDs:

CREATE OR REPLACE FUNCTION shard.next_unique_id( shard_id INT ) RETURNS BIGINT AS $BODY$ DECLARE epoch DATE := '2020-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') % 2048); RETURN next_id; ...