1

We want to shard a single PostgreSQL 10.2 database by tenant (client id) to multiple servers. One of the easiest approach is to use Foreign Data Wrapper (postgres_fdw extension).

It will looks like:

We have a single "master" and several data nodes with equal schema. Master node has log table replaced with a view. View consists of unions of foreign tables. There are rules for insert/update/delete on view to forward operations to specific server. Foreign tables are created with check constraint on tenant field so SELECT from the view only requests required servers (if tenant is specified in query).

Simplified example:

CREATE FOREIGN TABLE log_data_01 (
    tenant varchar(10) NOT NULL,    

    CONSTRAINT tenant_check CHECK (tenant = '01')
)
SERVER logstore_01
OPTIONS (schema_name 'public', table_name 'log_data');

CREATE FOREIGN TABLE log_data_02 (
    tenant varchar(10) NOT NULL,    

    CONSTRAINT tenant_check CHECK (tenant = '02')
)
SERVER logstore_02
OPTIONS (schema_name 'public', table_name 'log_data');

CREATE VIEW log_data AS
    SELECT * FROM log_data_01
        UNION ALL
    SELECT * FROM log_data_02;

CREATE RULE log_data_insert AS ON INSERT TO log_data DO INSTEAD NOTHING;
CREATE RULE log_data_update AS ON UPDATE TO log_data DO INSTEAD NOTHING;
CREATE RULE log_data_delete AS ON DELETE TO log_data DO INSTEAD NOTHING;

CREATE RULE log_data_insert_01 AS ON INSERT TO log_data 
    WHERE (tenant = '01')
DO INSTEAD INSERT INTO log_data_01 VALUES (NEW.*);

CREATE RULE log_data_insert_02 AS ON INSERT TO log_data 
    WHERE (tenant = '02')
DO INSTEAD INSERT INTO log_data_02 VALUES (NEW.*);

The question is:

  1. Is it "production ready" approach? Are there known issues with postgres_fdw on high load (there will a lot of INSERTs to the table)?
  2. Is postgres_fdw can handle network issues (temporary connection lost)? Are there automatic reconnect or something?
  3. All queries will be handled by "master" node and then forwarded. So I will have a single point of failure. Is it a good idea to have multiple replicas of master and load balance queries between them?
  • 1
    What is the actual requirement for this? Why have separate servers? Is there an actual problem you are trying to solve? Seems a strange way of going about things (ie: having multiple servers). All you're doing is introducing multiple additional points of failure via postgres_fdw. I'd be worried about query pushdown too. – Philᵀᴹ Aug 15 '18 at 10:04
  • Requirements: tenant isolation to guarantee resources to client and store data separately because of regulatory requirements (personal data etc...) – Philipp Bocharov Aug 15 '18 at 10:20
  • 1
    You can do all that with a TABLESPACE and a custom user.... it's way more sane. – Evan Carroll Aug 15 '18 at 10:30
  • 3
    In agreement with Evan, there are more sensible ways of dealing with isolation on a single server. Why not ask a question about it? – Philᵀᴹ Aug 15 '18 at 10:46

Your Answer

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

Browse other questions tagged or ask your own question.