Postgresql 9.5 sharding example

postgres-9.5-sharding.sh

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