1

As we are building a multi-tenant app, we will be using one DB only approach, and having a column with customer_id to isolate accounts data.

The question is, is it possible in postgres to shard data based on a range of column values? (ex: customer_id > n & < n+ 100 ) thus not having all customers into one node? or its only possible to shard a specific customer id data?

  • Standard PostgreSQL doesn't have sharding built in. PostgreSQL "as is" is a single-node database, not a distributed one. If you need to scale-out, you most probably need some specific forks Postgres-XL, or maybe an older Postgres-XC, or maybe Citus. You may also use the "Foreign Data Wrappers" to your advantage. – joanolo Mar 15 '17 at 8:12
1

It depends on what you understand with "sharding" - a fully automated, declarative way of (automatically) distributing data across different servers?

Then the answer is no, there is nothing built-in.

There are some products/extensions that do this like BDR, Postgres-XL or Citus (which is the successor of an extension named pg_shard)


If you are just looking for some way to distribute data over multiple servers, this can be implemented using foreign data wrappers combined with inheritance. But that involves many manual steps.

A setup could look like this:

Say you have a "sales" table that you want to distribute over several countries. For each country you have your own Postgres server that only stores the country specific data. Something like this:

create table customer
(
   id integer, 
   .... other columns
);

The for each shard node you need to create the same table in the Postgres server for that shard.

Then create foreign servers for each node:

create server server_one foreign data wrapper postgres_fdw options (host 'host_one', port '5432', dbname 'postgres');
create server server_two foreign data wrapper postgres_fdw options (host 'host_two', port '5432', dbname 'postgres');
create server server_three foreign data wrapper postgres_fdw options (host 'host_three', port '5432', dbname 'postgres');

Then on the master server you create one foreign table for each country as a child table of the master table.

create foreign table customer_one() INHERITS (customer) server server_one options ( table_name 'customer' );
create foreign table customer_two() INHERITS (customer) server server_two options ( table_name 'sales' );
create foreign table customer_three() INHERITS (customer) server server_three options ( table_name 'customer' );

In order to tell Postgres that each child table has a distinct set of rows, you create the necessary check constraints:

alter table customer_one add constraint check_id check (id > 0 and id <= 1000);
alter table customer_two add constraint check_id check (id > 1000 and id <= 2000);
alter table customer_three add constraint check_id check (id > 2000 and id <= 3000);

You need to make sure that the ranges in the check constraints do not overlap.

Now if you query the master table, e.g. like this:

select *
from customer
where id = 100;

you will see that Postgres retrieves that data from the first server and that the condition is pushed down to the remote server.

You will also need to write triggers to allow DML statements on the master table that would insert into the approriate "shards".

  • What is the number or rows at which shard becomes necessary? – simo Mar 15 '17 at 11:54
  • @simo: hard to say. I would not make that dependent on the number of rows, rather then on the location of the data. If you e.g. have servers in Europe and US then I would shard the customers according to that. I don't think you need sharding for performance reasons if all nodes are local. Using many harddisks (RAID system) is usually more efficient - especially with the increasing abilities of Postgres to use multiple cores for a single query. – a_horse_with_no_name Mar 15 '17 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.