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 ?