postgres-scalable-sequence.sql

postgres-scalable-sequence.sql

--# postgresql 12 high availability cookbook ch14: creating a scalable nextval replacement
--## this example has 2048 shards (schemas) and 2048 unique values per millisecond (seq % 2048)
--# ref: [Sharding & IDs at Instagram](https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c)

CREATE SCHEMA shard;
CREATE SEQUENCE shard.table_id_seq;

CREATE OR REPLACE FUNCTION shard.next_unique_id(
	shard_id INT
)
RETURNS BIGINT AS
$BODY$
DECLARE
	epoch DATE := '2021-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;
END;
$BODY$ LANGUAGE plpgsql;

SELECT 	(newval & 2047) AS id_value,
		(newval >> 11) & 2047 AS shard_id,
		(newval >> 22) / 1000 / 3600 / 24 AS days
FROM (SELECT shard.next_unique_id(15) AS newval) nv;


CREATE SCHEMA myapp;
CREATE TABLE myapp.msg_log (
	id BIGINT DEFAULT shard.next_unique_id(0)
	,created_ts TIMESTAMP
	,msg TEXT
	,PRIMARY KEY(id)
);
CREATE INDEX msg_log_m1 ON myapp.msg_log (created_ts);
\d+ myapp.msg_log

CREATE SCHEMA myapp1;
CREATE TABLE myapp1.msg_log (
LIKE myapp.msg_log INCLUDING INDEXES
);
\d+ myapp1.msg_log

ALTER TABLE myapp1.msg_log
ALTER id TYPE BIGINT,
ALTER id SET DEFAULT shard.next_unique_id(1);
\d+ myapp1.msg_log