Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?
share|improve this question
1  
It's likely that constraint pushdown will be added in 9.4. For now you'll probably have to live with doing application-side sharding. –  Craig Ringer Aug 5 '13 at 15:23
    
I don't understand why you need the condition in the first place. If those are true shards, the IDs shouldn't overlap, right? And the row counts from the execution plan seem to indicate that there are only 4 rows in the table_2, so Postgres would never use an index lookup for that anyway. –  a_horse_with_no_name Aug 5 '13 at 22:31
    
Good news about adding it to 9.4. About shards, this is just an example to illustrate our issue. The conditions on this view are needed to avoid a foreign scan, as the result will be empty. –  Leon Gautier Aug 6 '13 at 16:02

1 Answer 1

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

share|improve this answer
    
We would prefer relying on an existing reliable tool, as it looks like a quite heavy task for complex queries. Thanks for the link, we will consider this solution. –  Leon Gautier Aug 5 '13 at 14:37
1  
@LeonGautier: I would go with a server side function for now. Would not even have to be dynamic SQL for a simple query like in your example. Just a bunch of CASE branches ... –  Erwin Brandstetter Aug 5 '13 at 19:51
    
Thanks, do you have references on how to proceed for complex queries ? –  Leon Gautier Aug 6 '13 at 16:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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