使用聲明性分區和 postgres_fdw 進行分片時在 Postgresql 中插入緩慢？

我們一直在嘗試使用內置的 Postgres 聲明式分區在Google云上對 Postgres 數據庫進行分區，如此處postgres_fdw所述。

我們正在執行以下命令：

分片 1：

CREATE TABLE message_1 (
   id SERIAL,                                                                                        
   m_type character varying(20),
   content character varying(256) NOT NULL,
   is_received boolean NOT NULL,                                                              
   is_seen boolean NOT NULL,
   is_active boolean NOT NULL,
   created_at timestamp with time zone NOT NULL,
   room_no_id integer NOT NULL,
   sender_id integer NOT NULL
);

CREATE TABLE message_2 (
   id SERIAL,                                                                                        
   m_type character varying(20),
   content character varying(256) NOT NULL,
   is_received boolean NOT NULL,                                                              
   is_seen boolean NOT NULL,
   is_active boolean NOT NULL,
   created_at timestamp with time zone NOT NULL,
   room_no_id integer NOT NULL,
   sender_id integer NOT NULL
);

分片 2：

CREATE TABLE message_3 (
   id SERIAL,                                                                                        
   m_type character varying(20),
   content character varying(256) NOT NULL,
   is_received boolean NOT NULL,                                                              
   is_seen boolean NOT NULL,
   is_active boolean NOT NULL,
   created_at timestamp with time zone NOT NULL,
   room_no_id integer NOT NULL,
   sender_id integer NOT NULL
);

CREATE TABLE message_4 (
   id SERIAL,                                                                                        
   m_type character varying(20),
   content character varying(256) NOT NULL,
   is_received boolean NOT NULL,                                                              
   is_seen boolean NOT NULL,
   is_active boolean NOT NULL,
   created_at timestamp with time zone NOT NULL,
   room_no_id integer NOT NULL,
   sender_id integer NOT NULL
);  

源機：

CREATE SERVER shard_1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'shard_1_ip', dbname 'shard_1_db', port '5432');
CREATE SERVER shard_2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'shard_2_ip', dbname 'shard_2_db', port '5432');

CREATE USER MAPPING for source_user SERVER shard_1 OPTIONS (user 'shard_1_user', password 'shard_1_user_password');
CREATE USER MAPPING for source_user SERVER shard_2 OPTIONS (user 'shard_2_user', password 'shard_2_user_password');

CREATE TABLE room (
   id SERIAL PRIMARY KEY,
   name character varying(20) NOT NULL,
   created_at timestamp with time zone NOT NULL,
   updated_at timestamp with time zone NOT NULL,
   is_active boolean NOT NULL
);

insert into room (
   name, created_at, updated_at, is_active
)
select
   concat('Room_', floor(random() * 400000 + 1)::int, '_', floor(random() * 400000 + 1)::int),
   i,
   i,
   TRUE
from generate_series('2019-01-01 00:00:00'::timestamp, '2019-4-30 01:00:00', '5 seconds') as s(i);

CREATE TABLE message (
   id SERIAL,                                                                                        
   m_type character varying(20),
   content character varying(256) NOT NULL,
   is_received boolean NOT NULL,                                                              
   is_seen boolean NOT NULL,
   is_active boolean NOT NULL,
   created_at timestamp with time zone NOT NULL,
   room_no_id integer NOT NULL,
   sender_id integer NOT NULL
) PARTITION BY HASH (room_no_id);

CREATE FOREIGN TABLE message_1
   PARTITION OF message
   FOR VALUES WITH (MODULUS 4, REMAINDER 1)
   SERVER shard_1;

CREATE FOREIGN TABLE message_2
   PARTITION OF message
   FOR VALUES WITH (MODULUS 4, REMAINDER 2)
   SERVER shard_1;

CREATE FOREIGN TABLE message_3
   PARTITION OF message
   FOR VALUES WITH (MODULUS 4, REMAINDER 3)
   SERVER shard_2;

CREATE FOREIGN TABLE message_4
   PARTITION OF message
   FOR VALUES WITH (MODULUS 4, REMAINDER 0)
   SERVER shard_2;

我們面臨的問題是，當我們嘗試使用以下查詢插入數據時：

insert into message (
   m_type, content, is_received, is_seen, is_active, created_at, room_no_id, sender_id
)                                
select                                      
   'TEXT',                                                                                    
   CASE WHEN s.i % 2 = 0 THEN 'text 1'
       ELSE 'text 2'
   end,                                        
   TRUE,                      
   TRUE,                      
   TRUE,                        
   dr.created_at + s.i * (interval '1 hour'),
   dr.id,
   CASE WHEN s.i % 2 = 0 THEN split_part(dr.name, '_', 2)::int                                  
       ELSE split_part(dr.name, '_', 3)::int
   end,
from room as dr, generate_series(0, 10) as s(i);

這樣做需要將近1 小時 50 分鐘。當我們沒有對錶進行分片時，執行相同的操作大約需要**8 分鐘。所以，這基本上比沒有分片慢 14 倍。**我們是否在這裡遺漏了任何東西，或者插入使用這種方法進行分片的速度很慢？

如本影片所述，Citus 在插入中的表現似乎更好，所以對我來說，分片實際上會使性能降低這麼多似乎有點奇怪。因此，它可能不會像 Citus 那樣具有良好的性能，但為什麼性能如此之低。

Citus 的這一部分是一個有凝聚力的解決方案，其目的是分片。FDW 和分區都不是。它們都是獨立的功能，當它們一起工作時，它們會保持一定距離。

特別是，沒有用於大容量插入的 FDW API，因此將其轉換為每行一個插入語句，這相對於大容量插入來說速度較慢。也許這將在功能中得到改進。另請注意，postgres_fdw 目前禁止並行查詢執行，如果您在分片中的目的是為任務帶來更多 CPU，這也是非常令人失望的。

請注意，如果對錶進行了索引，或者沒有批量插入，則這種相對影響將被稀釋。更重要的是，一旦隨機更新的索引變得足夠大以至於它們不適合在一台機器上的 RAM 中，但會適合所有分片的集體 RAM。

聲明式分區和 FDW 不是 Citus 殺手。您應該使用 Citus 擴展測試您的案例（不僅僅是依賴影片），然後在它適合您的情況下使用它。

引用自：https://dba.stackexchange.com/questions/269759