4

I've set up a PostgreSQL FDW server with the following table, sharded by user_id over four servers:

CREATE TABLE my_big_table
(
    user_id bigint NOT NULL,
    serial bigint NOT NULL,         -- external, incrementing only
    some_object_id bigint NOT NULL,
    timestamp_ns bigint NOT NULL,
    object_type smallint NOT NULL,
    other_type smallint NOT NULL,
    data bytea
) PARTITION BY HASH (user_id) ;
CREATE SERVER shardA
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '192.168.200.11', port '5432', dbname 'postgres', fetch_size '10000');
 .
 .
 .
CREATE SERVER shardD
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '192.168.200.14', port '5432', dbname 'postgres', fetch_size '10000');
create foreign table my_big_table_mod4_s0 partition of my_big_table
    FOR VALUES WITH (MODULUS 4, REMAINDER 0) server shardA
    OPTIONS (table_name 'my_big_table_mod4_s0');
 .
 .
 .
create foreign table my_big_table_mod4_s3 partition of my_big_table
    FOR VALUES WITH (MODULUS 4, REMAINDER 3) server shardD
    OPTIONS (table_name 'my_big_table_mod4_s3');

On the backend servers I have set up a table with several indexes, its data CLUSTERed by (user_id, serial) across several partitions. I don't think those details are very relevant for my actual question, though.

The common query against my cluster is in the pattern of:

SELECT * from my_big_table
WHERE
  user_id = 12345     -- only 1 user, always! --> single foreign server.
ORDER BY serial DESC  -- get 'newest' 90% of the time, 10% ASC
LIMIT 1000;           -- max limit 1000, sometimes less

For users with < 1000 records: all fine, no problem.

For users with > 100.000 records I see the issue leading to poor performance: explain shows LIMIT and sorting happens on FDW, not pushed down. Why?

 Limit  (cost=927393.08..927395.58 rows=1000 width=32)
   Output: my_big_table_mod4_s0.serial, my_big_table_mod4_s0.some_object_id, my_big_table_mod4_s0.timestamp_ns, my_big_table_mod4_s0.object_type, my_big_table_mod4_s0.other_type, (length(my_big_table_mod4_s0.data))
   ->  Sort  (cost=927393.08..931177.06 rows=1513592 width=32)
         Output: my_big_table_mod4_s0.serial, my_big_table_mod4_s0.some_object_id, my_big_table_mod4_s0.timestamp_ns, my_big_table_mod4_s0.object_type, my_big_table_mod4_s0.other_type, (length(my_big_table_mod4_s0.data))
         Sort Key: my_big_table_mod4_s0.serial DESC
         ->  Foreign Scan on public.my_big_table_mod4_s0  (cost=5318.35..844404.46 rows=1513592 width=32)
               Output: my_big_table_mod4_s0.serial, my_big_table_mod4_s0.some_object_id, my_big_table_mod4_s0.timestamp_ns, my_big_table_mod4_s0.object_type, my_big_table_mod4_s0.other_type, length(my_big_table_mod4_s0.data)
               Remote SQL: SELECT serial, some_object_id, timestamp_ns, object_type, other_type, data FROM public.my_big_table_mod4_s0 WHERE ((user_id = 4560084))
 JIT:
   Functions: 3
   Options: Inlining true, Optimization true, Expressions true, Deforming true

Takeaway from the above is:

  • Single backend server selected: OK! (solved with this)
  • Remote SQL: SELECT [...] indicates no ORDER BY, no LIMIT. Problem.

Executed on the backend server directly shows this:

 Limit  (cost=1.74..821.42 rows=1000 width=32)
   Output: my_big_table_mod4_s0_part123.serial, my_big_table_mod4_s0_part123.some_object_id, my_big_table_mod4_s0_part123.timestamp_ns, my_big_table_mod4_s0_part123.object_type, my_big_table_mod4_s0_part123.other_type, (length(my_big_table_mod4_s0_part123.data))
   ->  Append  (cost=1.74..1240669.45 rows=1513592 width=32)
         ->  Index Scan Backward using my_big_table_mod4_s0_part123_pkey on public.my_big_table_mod4_s0_part123  (cost=0.43..290535.67 rows=355620 width=32)
               Output: my_big_table_mod4_s0_part123.serial, my_big_table_mod4_s0_part123.some_object_id, my_big_table_mod4_s0_part123.timestamp_ns, my_big_table_mod4_s0_part123.object_type, my_big_table_mod4_s0_part123.other_type, length(my_big_table_mod4_s0_part123.data)
               Index Cond: (my_big_table_mod4_s0_part123.user_id = 4560084)
         ->  Index Scan Backward using [... other partitions ...]

What have I tried:

  • As FDW is still active in development, I tried using a newer version: both 11.4 and 12-beta2 for both FDW as well as backend servers. No difference observed.
  • Running ANALYZE against the foreign table (on FDW instance). Takes a huge amount of time; looks like it's full tablescanning the remote table? No difference in query planning.
  • Changing the value of fetch_size on the remote SERVER object. No difference.
  • Set use_remote_estimate=true on the remote SERVER object. No difference.
  • Set fdw_tuple_cost=100 on the remote SERVER object. Sorting now happens on the remote server, but LIMIT still not pushed down.
  • Looking online for other people seeing this, only showing this related post: Missed LIMIT clause pushdown in FDW API

    But this thread mentions commits fixing this in 9.7 and whatnot, but I'm using 11.4 and 12-beta2. Or do I misunderstand this?

    And post: Strange cost estimation for foreign tables shows a nice example on tuning FDW, but unfortunately does not cover my issue with LIMITs.

  • Having a quick look in the PostgreSQL source code, I noticed this statement, perhaps relevant for FDW, maybe not (source).

    We can't push sub-select containing LIMIT/OFFSET to workers as there is no guarantee that the row order will be fully deterministic, and applying LIMIT/OFFSET will lead to inconsistent results at the top-level. (In some cases, where the result is ordered, we could relax this restriction. But it doesn't currently seem worth expending extra effort to do so.)

  • Having another look at the source code, I found this promising commit (d50d172e51):

    This provides the ability for postgres_fdw to handle SELECT commands so that it 1) skips the LockRows step (if any) (note that this is safe since it performs early locking) and 2) pushes down the LIMIT and/or OFFSET restrictions (if any) to the remote side. This doesn't handle the INSERT/UPDATE/DELETE cases.

    It adds a unit test case exactly for my case!

    -- and both ORDER BY and LIMIT can be shipped
    EXPLAIN (VERBOSE, COSTS OFF)
      SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1;
                                                                            QUERY PLAN                                                                         
    ------------------------------------------------------------------------------------------------------------------------------------------------------------
    Foreign Scan on public.ft1 t1
      Output: c1, c2, c3, c4, c5, c6, c7, c8
      Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(public.===) c2)) ORDER BY c2 ASC NULLS LAST LIMIT 1::bigint
    

    which should be part of 12-beta2, which I'm running already...

I noticed I should use indexes in DESC order here, but that's not so relevant now.

8
  • Are you sure you actually need the sharding across multiple server? Parallel query together with partitioning can get you a long way with the right hardware and saves you all the trouble you have with the FDWs. Are you expecting billions of rows in that table?
    – user1822
    Jul 8, 2019 at 15:59
  • Yep, billions. :-( will be more data than I feel comfortable with on a single server, > 10 TB. And I'm almost there actually! This is basically the last thing on my list keeping me from the actual implementation.
    – gertvdijk
    Jul 8, 2019 at 16:02
  • I still wonder if putting everything on a single box (with a lot of CPUs) and putting each partition on a different SSD might give you the same performance but with less headache. Are you aware that FDWs can't be updated properly in a transactional manner ?
    – user1822
    Jul 8, 2019 at 16:05
  • Scaling vertically is an option, yeah, but would really love to see a horizontal scaling of 4-16x, so that we could scale vertically later if that is needed. Also: WDYM with FDW can't do transactional updates? It does 2PC-XA under water for you against the backend servers according to documentation, proving all guarantees of a single server. (And I don't do updates. Only INSERT, then many SELECTs against single user_id/shard.) By the way, MariaDB Spider did not have this issue (but I had other issues).
    – gertvdijk
    Jul 8, 2019 at 16:13
  • re: "it does 2PC-XA under water for you ... according to documentation", can you tell where you read that? Vanilla PostgreSQL can't commit transactions that span multiple machines transparently, so you can't really expect the updates to sharded tables to always work correctly in the face of partial failures of participating servers.
    – amitlan
    Jul 16, 2019 at 2:59

1 Answer 1

2

This works as expected since version 12 with commit d50d172e51 indeed, but only for non-partitioned (non-sharded) tables.

Running the query directly against the foreign table name (my_big_table_mod4_s0), the LIMIT is pushed down correctly.

Reported as a bug ; I don't see a technical reason why this should not work with partitioning (partition pruning) involved.

Update: turns out this is not really a bug, given the complexity of the planner and partition pruning in combination with FDW, but more of a feature request. The original author of the aforementioned commit indicates work on this may be done for PostgreSQL 13. :-)

Lesson learned: FDW is not really an efficient query router for all type of queries (yet).

Work-around for now with partitioned (sharded) tables on FDW for me is to create a function in plpgsql to determine the foreign table name based on the declarative partitioning layout (mod(user_id, 4) in my case). (I believe it's getting out of scope to fully include that here.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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