sharding id at instagram

postgresql_version.sql

-- usage here: https://github.com/ichux/nzbackend/tree/main/core/migrations
-- creates the old 'our_epoch bigint := 1314220021721;'
CREATE OR REPLACE FUNCTION get_year_start_timestamp_ms()
RETURNS BIGINT AS $$
BEGIN
  RETURN (EXTRACT(EPOCH FROM date_trunc('year', now())) * 1000)::BIGINT;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- create the needed sequence
CREATE SEQUENCE table_id_seq;

-- change the public to schema of choice
CREATE OR REPLACE FUNCTION public.next_id(OUT result bigint) AS $$
DECLARE
--    our_epoch bigint := 1314220021721;
    seq_id bigint;
    now_millis bigint;
    shard_id int := 5;
BEGIN
    SELECT nextval('table_id_seq'::regclass) % 1024 INTO seq_id;

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

-- create table
CREATE TABLE IF NOT EXISTS public.our_table (
    id BIGINT DEFAULT public.next_id() NOT NULL,
    enabled BOOLEAN NOT NULL
);

-- insert into the table
INSERT INTO public.our_table (enabled) VALUES(TRUE) RETURNING id;
INSERT INTO public.our_table (enabled) VALUES(False) RETURNING id;

-- select from the table
SELECT * FROM our_table ORDER BY id DESC LIMIT 100;

sqlite_version.sql

-- STAGE 1: Create sequence table (if not exists)
CREATE TABLE IF NOT EXISTS id_sequence (
    value INTEGER DEFAULT 0
);

-- Initialize sequence
INSERT OR IGNORE INTO id_sequence (value) VALUES (0);

-- STAGE 2: Create our_table (if not exists)
CREATE TABLE IF NOT EXISTS our_table (
    id INTEGER NOT NULL,
    enabled INTEGER NOT NULL
);

-- 1) Create a view over the real table
CREATE VIEW IF NOT EXISTS our_table_view AS
SELECT id, enabled
  FROM our_table;

-- 2) Create an INSTEAD OF INSERT trigger on the view
CREATE TRIGGER IF NOT EXISTS trg_insert_our_table
INSTEAD OF INSERT ON our_table_view
BEGIN
  -- bump the sequence
  UPDATE id_sequence
     SET value = value + 1;

  -- insert into the real table using NEW.enabled
  INSERT INTO our_table (id, enabled)
  VALUES (
    (
      (
        -- milliseconds since Jan-1 of current year
        (CAST(strftime('%s','now') AS INTEGER) * 1000)
        + ((CAST(strftime('%f','now') AS REAL)
           - CAST(strftime('%S','now') AS INTEGER)) * 1000)
        - (CAST(strftime('%s',strftime('%Y-01-01 00:00:00','now'))
               AS INTEGER) * 1000)
      )
      << 23
    )
    | (5 << 10)
    | (SELECT value % 1024 FROM id_sequence)
    ,
    NEW.enabled
  );
END;

/**
rm -f ~/.sqlite_history moniker.db

sqlite3 moniker.db <<'EOF'
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA temp_store=MEMORY;
PRAGMA cache_size=-10000;
EOF

sqlite3 moniker.db

seq 1 5000 \
  | xargs -P 1 -I{} bash -c '
      enabled=$(( RANDOM % 2 ))
      sqlite3 moniker.db "INSERT INTO our_table_view (enabled) VALUES (${enabled});"'

## error code
seq 1 5000 \
  | xargs -P 100 -I{} bash -c '
      enabled=$(( RANDOM % 2 ))
      sqlite3 moniker.db "INSERT INTO our_table_view (enabled) VALUES (${enabled});"'

sqlite> INSERT INTO our_table_view (enabled) VALUES (1);
sqlite> INSERT INTO our_table_view (enabled) VALUES (0);
sqlite> SELECT * FROM our_table_view;
sqlite> SELECT COUNT(*) FROM our_table_view;
**/

z-reference.md

Modified from

ichux

Modified by