We use Postgres 9.3, we want to split a large database (evaluated final size will be > 100 T) on many servers. As our users already have a set of existing queries, we cannot use solutions like PL/Proxy. We would like to use the postgres_fdw contrib to externalize some part of the data.
To illustrate our problem, we created 2 foreign tables foreign_table_1 and foreign_table_2. As inheritance seems not available on foreign tables, we created an union view like this :
CREATE VIEW union_table (id, name)
AS ( SELECT id, name FROM foreign_table_1 where id BETWEEN 1 AND 100000
UNION ALL SELECT id, name FROM foreign_table_2 where id > 100000
) ;
The problem is that Postgres does not push the constraints into the query plan. For instance here both foreign tables are scanned :
postgres=# explain analyze select * from union_table where id = 1234;
Append (cost=100.00..303.35 rows=5 width=8) (actual time=1.113..1.113 rows=0 loops=1)
-> Foreign Scan on master_table_1 (cost=100.00..154.82 rows=1 width=8) (actual time=0.567..0.567 rows=0 loops=1)
-> Foreign Scan on master_table_2 (cost=100.00..148.48 rows=4 width=8) (actual time=0.538..0.538 rows=0 loops=1)
Total runtime: 1.876 ms
(4 rows)
- Is it possible to push the WHERE clause into the query plan ?
- Is it possible to use inheritance with postgres_fdw ?
- Is there any other way to obtain a sharded table with Postgres ?
Best Answer
If the view isn't pushing down the WHERE predicates, maybe a db function that dynamically creates the necessary query and only included the Foreign Tables (or shards) that are necessary?
If you need distributed Postgres, have you looked into Postgres-XC? http://postgresxc.wikia.com/wiki/Postgres-XC_Wiki