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?
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".