Skip to content

Instantly share code, notes, and snippets.

@sylr
Last active June 11, 2019 21:23
Show Gist options
  • Save sylr/623bab09edd04d53ee4e to your computer and use it in GitHub Desktop.
Save sylr/623bab09edd04d53ee4e to your computer and use it in GitHub Desktop.
Postgresql 9.5 sharding example
echo master shard_{0,1,2,3} | xargs -n1 /usr/local/bin/dropdb -p 6432 -h /tmp -U postgres
echo master shard_{0,1,2,3} | xargs -n1 /usr/local/bin/createdb -p 6432 -h /tmp -U postgres
for a in {0..3}; do
echo "
CREATE TABLE users (id serial PRIMARY KEY, username TEXT NOT NULL);
ALTER SEQUENCE users_id_seq INCREMENT BY 4 RESTART WITH $a;
" | /usr/local/bin/psql -p 6432 -h /tmp -U postgres -d shard_$a;
done
cat <<'EOF' | /usr/local/bin/psql -p 6432 -h /tmp -U postgres -d master
CREATE TABLE users (id serial PRIMARY KEY, username TEXT NOT NULL);
CREATE EXTENSION postgres_fdw;
CREATE SERVER shard_0 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'shard_0', host '/tmp', port '6432');
CREATE SERVER shard_1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'shard_1', host '/tmp', port '6432');
CREATE SERVER shard_2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'shard_2', host '/tmp', port '6432');
CREATE SERVER shard_3 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'shard_3', host '/tmp', port '6432');
CREATE USER MAPPING FOR POSTGRES SERVER shard_0 OPTIONS (user 'postgres');
CREATE USER MAPPING FOR POSTGRES SERVER shard_1 OPTIONS (user 'postgres');
CREATE USER MAPPING FOR POSTGRES SERVER shard_2 OPTIONS (user 'postgres');
CREATE USER MAPPING FOR POSTGRES SERVER shard_3 OPTIONS (user 'postgres');
CREATE FOREIGN TABLE users_shard_0 () INHERITS (users) SERVER shard_0 OPTIONS (table_name 'users');
CREATE FOREIGN TABLE users_shard_1 () INHERITS (users) SERVER shard_1 OPTIONS (table_name 'users');
CREATE FOREIGN TABLE users_shard_2 () INHERITS (users) SERVER shard_2 OPTIONS (table_name 'users');
CREATE FOREIGN TABLE users_shard_3 () INHERITS (users) SERVER shard_3 OPTIONS (table_name 'users');
ALTER FOREIGN TABLE users_shard_0 ADD CHECK (id % 4 = 0);
ALTER FOREIGN TABLE users_shard_1 ADD CHECK (id % 4 = 1);
ALTER FOREIGN TABLE users_shard_2 ADD CHECK (id % 4 = 2);
ALTER FOREIGN TABLE users_shard_3 ADD CHECK (id % 4 = 3);
CREATE OR REPLACE FUNCTION __trigger_users_before_insert(
) RETURNS trigger AS $__$
BEGIN
EXECUTE $$
INSERT INTO $$ || ('users_shard_' || (NEW.id % 4)::text)::regclass || $$ VALUES (
$1, $2
)
$$ USING
NEW.id,
NEW.username;
RETURN null;
END;
$__$ LANGUAGE plpgsql;
CREATE TRIGGER users_before_insert BEFORE INSERT ON users FOR EACH ROW EXECUTE PROCEDURE __trigger_users_before_insert();
INSERT INTO users (username) SELECT 'random user ' || i FROM generate_series(1, 100) i;
SELECT * FROM users_shard_0 LIMIT 10;
SELECT * FROM users_shard_1 LIMIT 10;
SELECT * FROM users_shard_2 LIMIT 10;
SELECT * FROM users_shard_3 LIMIT 10;
EOF
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment