Generate unique cross-shard ids in PostgreSQL

psql.rb

execute <<-EOSQL

CREATE SEQUENCE #{schema}.id_generator_seq;

CREATE OR REPLACE FUNCTION #{schema}.current_shard_id(OUT result int) AS $$
BEGIN
  result := #{shard_id};
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION #{schema}.id_generator(OUT result bigint) AS $$
DECLARE
    our_epoch bigint := 1433806952013;
    seq_id bigint;
    now_millis bigint;
    shard_id int;
BEGIN
    SELECT #{schema}.current_shard_id() into shard_id;
    SELECT nextval('#{schema}.id_generator_seq') % 1024 INTO seq_id;

    SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
    result := (now_millis - our_epoch) << 23;
    result := result | (shard_id << 10);
    result := result | (seq_id);
END;
$$ LANGUAGE PLPGSQL;

EOSQL

variables.rb

## Settings
#  shards:
#    default:
#      schema: public
#      shard_id: 0
#    other_shard:
#      schema: shard1
#      shard_id: 1
schema = ActiveRecord::Base.connection.instance_variable_get(:@config)[:schema_search_path]
shard_entry = Settings.shards.values.find { |v| v['schema'] == schema }
shard_id = shard_entry['shard_id']