Postgresql

Postgres 是否適合不斷增加的多對多數據 (M:N)？

  • December 9, 2021

我有一個案例，其中數據是多對多的，需要廣泛的查詢功能。

參與者和活動

使用者/參與者可以註冊多個事件。每個事件可以有很多參與者。這是一個多對多的關係。

考慮這樣的數據集。

  • 每個活動可以有 1000 萬使用者註冊。
  • 每個使用者最多可以註冊 1000 個事件
  • 有 1000 個事件正在執行

需要以下查詢：

  • 查詢 1. 獲取所有註冊活動的 Participants
  • 查詢 2. 獲取參與者註冊的所有事件
  • 查詢 3. 獲取參與者即將發生的所有事件

用於處理查詢 1和查詢 2****

EventParticipantTable：（eventId，participantId）：1000 x 10M 記錄

這需要搜尋 1000 x 10M 記錄？

數據集可以根據 eventId 拆分為塊，以使其理想地僅掃描 10M 記錄，但不確定如何在 PostgreSQL 中處理。

用於處理查詢 3

事件表 + EventParticipantTable 加入

這需要兩個表的連接，我首先獲取即將發生的事件的事件表（基於開始和結束時間戳），並且對於每個匹配的 eventId，需要查找 EventParticipantTable 中是否存在查詢的參與者 ID。

這需要搜尋 1000 個事件 * (1000 * 10M) 事件參與者表條目？

在這種情況下，每個表 1000 x 10M 記錄不是問題嗎？

為了解決您的問題，我執行了以下操作（下面的所有程式碼都可以在此處的小提琴中找到）：

這些測試已經在 db<>fiddle 伺服器上執行——我們不完全了解機器的配置，也不知道在執行查詢時還會發生什麼。

我還在家用筆記型電腦上進行了測試：

  • Linux Fedora 34
  • 1TB 三星固態硬碟
  • 4 個 CPU，2 個核心
  • 除了標準的 Linux 程序，沒有別的東西在執行

PostgreSQL 12.7 實例是使用以下選項從原始碼編譯的：

./configure --prefix=/home/pol/Downloads/db/dba_test/12.7/inst --enable-nls --with-python --with-icu --with-openssl --with-uuid=e2fs

系統設置是預設設置，除了pgtune的建議如下：

DB Version: 12
OS Type: linux
DB Type: dw
Total Memory (RAM): 32 GB
CPUs num: 4
Data Storage: ssd

建議的預設更改：

max_connections = 40
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 52428kB
min_wal_size = 4GB   -- used 16GB for this setting
max_wal_size = 16GB  -- used 64GB for this setting
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2

由於我為具有大量寫入以加快載入速度的系統閱讀的內容而增加了min_和設置 - 不應該影響讀取 - 失去的引用……max_wal

首先，我創建了一個函式來生成隨機字元串（從這裡）：

CREATE FUNCTION random_text(INTEGER)
RETURNS TEXT
LANGUAGE SQL
AS $$ 
 select upper(
   substring(
     (SELECT string_agg(md5(random()::TEXT), '')
      FROM generate_series(
          1,
          CEIL($1 / 32.)::integer) 
      ), 1, $1) );
$$;

然後，我創建了一個event表：

CREATE TABLE event 
(
 event_id SMALLINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 event_name TEXT NOT NULL UNIQUE,
 event_date DATE NOT NULL
);

也給它一個索引event_name- 我可以想像許多希望按名稱搜尋的場景。

CREATE INDEX ev_name_ix ON event USING BTREE
(event_name ASC);

還有event_date：

CREATE INDEX ev_date_ix ON event USING BTREE
(event_date ASC);

然後我創建了 100 個（筆記型電腦上為 1,000 個）事件，如下所示：

INSERT INTO event (event_name, event_date)
SELECT random_text(10), CURRENT_DATE - INTERVAL '7 DAY'
FROM GENERATE_SERIES(1, 100);

但！，你可能會尖叫……所有的活動日期都是過去的 - 是的，但如果你這樣做，那麼你將擁有過去的 50% 和未來的 50%：

UPDATE event 
SET event_date = 
 (
   CASE 
     WHEN MOD(event_id, 2) = 1 THEN event_date  -- i.e. no change!
     ELSE CURRENT_DATE + INTERVAL '7 DAY'
   END
 );

檢查SELECT * FROM event;- 結果：

event_id    event_name  event_date
      1    A653585119  2021-07-30
      2    01563801BB  2021-08-13
      3    4ED87ABDEC  2021-07-30
      4    EF0394645B  2021-08-13
...     
... snipped for brevity
...

這樣做（而不是使用文字日期）意味著小提琴將在幾年後工作，因為event_date這只取決於小提琴何時執行而不是某個常數！

表participant：

CREATE TABLE participant
(
 participant_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 participant_name TEXT NOT NULL  -- might not be UNIQUE
);

participant_name指數：

CREATE INDEX par_name_ix ON participant USING BTREE
(participant_name ASC);

然後創建了 10,000 個（筆記型電腦上為 10,000,000 - 10M）參與者：

INSERT INTO participant (participant_name)
SELECT random_text(10)
FROM GENERATE_SERIES(1, 10000);

現在，我們的連接表（或Associative Entity）：

CREATE TABLE ev_par
(
 ev_id SMALLINT NOT NULL,
 par_id INTEGER NOT NULL,
 CONSTRAINT ev_par_pk PRIMARY KEY (ev_id, par_id),
 CONSTRAINT ev_id_fk  FOREIGN KEY (ev_id)  REFERENCES event (event_id),
 CONSTRAINT par_id_fk FOREIGN KEY (par_id) REFERENCES participant (participant_id)
);

現在，這就是事情變得有趣的地方。在筆記型電腦上執行查詢 1（見下文）給出的響應時間約為 25 分鐘 -不理想！

我嘗試了各種“技巧”（SET enable_seqscan = off和SET enable_bitmapscan = off- 見這裡） - 基本上，我只是四處尋找我能在網上找到的任何東西……

我終於硬著頭皮進行了分區——那麼，ev_par表的邏輯分區鍵是什麼？好吧，event_id似乎是最好的候選者 - 其中有 1,000 個 - 整個表（僅數據）約為 350GB，因此這將提供 1,000 個 ~ 350MB 的表 - 更易於管理！

使用索引（PK + par_ev_ix - 見下文），該表約為 750GB！

因此，在最後一個括號 ( );) 和分號之前，我們輸入：

) PARTITION BY LIST (ev_id);

我在這裡、這里和這裡找到了有用的資訊（最有幫助的）。

基本上（簡化），有 3 種類型的分區：

  • 列表

CREATE TABLE customers (id INTEGER, status TEXT, arr NUMERIC) PARTITION BY LIST(status);

（父）和一個典型的分區將通過執行這樣的東西來創建：

CREATE TABLE cust_active PARTITION OF customers FOR VALUES IN ('ACTIVE');

  • 範圍

CREATE TABLE customers (id INTEGER, status TEXT, arr NUMERIC) PARTITION BY RANGE(arr);

（父）和一個典型的分區將通過執行這樣的東西來創建：

CREATE TABLE cust_arr_small PARTITION OF customers FOR VALUES FROM (MINVALUE) TO (25);

  • 雜湊

CREATE TABLE customers (id INTEGER, status TEXT, arr NUMERIC) PARTITION BY HASH(id);

（父）和一個典型的分區將通過執行這樣的東西來創建：

CREATE TABLE cust_part1 PARTITION OF customers FOR VALUES WITH (modulus 3, remainder 0);

我們現在必須使用該LIST方法創建 1,000 個分區 - 那麼，我們該怎麼做，bash 腳本、PL/pgSQL… 其他？搜尋時，我發現了這個（Hubert depesz Lubaczewski’s **absolute gem**] 11）的頁面，其中包含以下片段：

$ CREATE TABLE test_ranged (
   id serial PRIMARY KEY,
   payload TEXT
) partition BY range (id);

$ select format('CREATE TABLE %I partition OF test_ranged FOR VALUES FROM (%s) to (%s);', 'test_ranged_' || i, i, i+1)
   FROM generate_series(1,10000) i \gexec

所以，我修改了這段程式碼如下：

SELECT FORMAT('CREATE TABLE %I PARTITION OF ev_par FOR VALUES IN (%s);', 'ev_par_' || i, x)
FROM
(
 SELECT LPAD (x, 4, '0') AS i, x
 FROM
 (
   SELECT x::TEXT FROM GENERATE_SERIES (1, 1000) AS x
 ) AS tab1
) AS tab2 \gexec

這會生成我們想要的 1,000 個分區（顯示了前兩個 DDL 分區語句）：

                            format                              
-----------------------------------------------------------------
CREATE TABLE ev_par_0001 PARTITION OF ev_par FOR VALUES IN (1);
CREATE TABLE ev_par_0002 PARTITION OF ev_par FOR VALUES IN (2);

我用左填充了分區名稱，0以便它們在使用時正確排序\d+ ev_par。

PRIMARY KEY最後，我們在表的“逆”上放置一個索引ev_par——即

CREATE INDEX par_ev_ix ON ev_par USING BTREE
(par_id, ev_id);

這樣使用par_idfirst 的搜尋也將被編入索引。

在填充表之前，我通過執行以下命令（從此處）禁用了表上的約束：

ALTER TABLE reference DISABLE TRIGGER ALL;

然後我通過CROSS JOIN在兩個表之間使用 a 來填充它。我將此過程拆分為 1,000 個單獨的事務，以適應上面的分區程式碼，如下所示：

SELECT FORMAT(
'
BEGIN TRANSACTION;
INSERT INTO ev_par 
SELECT e.event_id, p.participant_id 
FROM event e, participant p 
WHERE e.event_id = %s; 
COMMIT;', i)
FROM
(
 SELECT i::TEXT FROM GENERATE_SERIES (1, 1000) AS i
) AS tab1 \gexec

所以，現在我們的 ev_par 表中有 1,000,000 條記錄。在筆記型電腦上，這相當於 10,000,000,000 (100 億) 條記錄。請注意- 即使使用 SSD並且沒有任何限制，這也需要大約 6 個小時！

然後，我們重新啟動約束：

ALTER TABLE reference ENABLE TRIGGER ALL;

然後，我執行了這個查詢（您的查詢 1 - 獲取所有註冊活動的參與者）：

SELECT ep.par_id, p.participant_name
FROM participant p
JOIN ev_par ep ON p.participant_id = ep.par_id
WHERE ev_id = 9;

結果：

par_id  participant_name
    1        E036FD8DA0
    2        7CC689B41F
    3        E7F1508EE7
    4        3CEF3FC3BD
    5        9BF603F525
...
... snipped for brevity
...

但是，我們需要進行性能分析，所以我跑了

EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE, COSTS)
&lt;above query&gt;

我們感興趣的一行是這一行：

Execution Time: 70.484 ms

相當令人印象深刻！但是，在 100 億記錄表上執行時並沒有那麼令人印象深刻：

Execution Time: ~ 25 mins

但是，在對數據進行分區後，查詢 1 會返回：

Execution Time: 5795.941 ms

那麼，從 25 分鐘到 5 秒 - 怎麼會？

答案在於計劃 - 未分區表（小提琴和筆記型電腦）的計劃是相同的：

QUERY PLAN
Nested Loop  (cost=0.43..4266.03 rows=5310 width=36) (actual time=0.127..69.629 rows=10000 loops=1)
 Output: ep.par_id, p.participant_name
 Inner Unique: true
 Buffers: shared hit=35545 read=4510 written=1002
 -&gt;  Seq Scan on public.participant p  (cost=0.00..124.85 rows=6985 width=36) (actual time=0.058..2.070 rows=10000 loops=1)
       Output: p.participant_id, p.participant_name
       Buffers: shared read=55 written=13
 -&gt;  Index Only Scan using ev_par_pk on public.ev_par ep  (cost=0.43..4.89 rows=27 width=4) (actual time=0.006..0.006 rows=1 loops=10000)
       Output: ep.ev_id, ep.par_id
       Index Cond: ((ep.ev_id = 9) AND (ep.par_id = p.participant_id))
       Heap Fetches: 10000
       Buffers: shared hit=35545 read=4455 written=989
Planning Time: 0.167 ms
Execution Time: 70.494 ms
14 rows

對於分區數據：

   QUERY PLAN                                                                                      
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join  (cost=0.87..635564.73 rows=10000000 width=15) (actual time=0.096..5465.321 rows=10000000 loops=1)
  Output: ep.par_id, p.participant_name
  Merge Cond: (p.participant_id = ep.par_id)
  Buffers: shared hit=152953
  -&gt;  Index Scan using participant_pkey on public.participant p  (cost=0.43..234216.54 rows=9999860 width=15) (actual time=0.032..1234.914 rows=10000000 loops=1)
        Output: p.participant_id, p.participant_name
        Buffers: shared hit=81380
  -&gt;  Index Only Scan using ev_par_0009_par_id_ev_id_idx on public.ev_par_0009 ep  (cost=0.43..251348.54 rows=10000000 width=4) (actual time=0.055..2005.403 rows=10000000 loops=1)
        Output: ep.par_id
        Index Cond: (ep.ev_id = 9)
        Heap Fetches: 10000000
        Buffers: shared hit=71573
Planning Time: 0.559 ms
Execution Time: 5795.941 ms
(14 rows)

兩條關鍵線是：

Non partitioned: -&gt;  Seq Scan on public.participant p 

Parititioned:    -&gt;  Index Scan using participant_pkey

在第一種情況下，它掃描整個參與者表（100 億條記錄），在第二種情況下，它使用參與者PRIMARY KEY——這就是查詢從 25 分鐘縮短到 5 秒的方式！

然後我執行了這個（查詢 2 - 獲取參與者註冊的所有事件）：

SELECT ep.ev_id, e.event_name
FROM event e
JOIN ev_par ep ON e.event_id = ep.ev_id
WHERE ep.par_id = 5432;

結果：

ev_id   event_name
1   CC69EBE53E
2   FD8BD9E311
3   FC94119C5A
4   511EA750E1
5   9956514FAA
...
... snipped for brevity
...

和：

EXPLAIN (ANALYZE &c... Execution Time: 0.279 ms

該查詢在未分區的 10Bn 表上執行也非常快——因為它是唯一Seq Scan在小event表上的。兩個大表在大約返回一個結果。0.5秒！

計劃：

dbfiddle 和筆記型電腦（未分區）：

QUERY PLAN
Nested Loop  (cost=0.43..1366.00 rows=5310 width=34) (actual time=0.017..0.270 rows=100 loops=1)
 Output: ep.ev_id, e.event_name
 Inner Unique: true
 Buffers: shared hit=402
 -&gt;  Seq Scan on public.event e  (cost=0.00..22.30 rows=1230 width=34) (actual time=0.008..0.017 rows=100 loops=1)
       Output: e.event_id, e.event_name, e.event_date
       Buffers: shared hit=2
 -&gt;  Index Only Scan using ev_par_pk on public.ev_par ep  (cost=0.43..18.40 rows=27 width=2) (actual time=0.002..0.002 rows=1 loops=100)
       Output: ep.ev_id, ep.par_id
       Index Cond: ((ep.ev_id = e.event_id) AND (ep.par_id = 5432))
       Heap Fetches: 100
       Buffers: shared hit=400
Planning Time: 0.109 ms
Execution Time: 0.290 ms

分區表：

   QUERY PLAN                                                                                
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join  (cost=35.94..2695.64 rows=1000 width=13) (actual time=0.259..5.333 rows=1000 loops=1)
  Output: ep.ev_id, e.event_name
  Inner Unique: true
  Hash Cond: (ep.ev_id = e.event_id)
  Buffers: shared hit=4013
  -&gt;  Append  (cost=0.43..2657.50 rows=1000 width=2) (actual time=0.016..4.866 rows=1000 loops=1)
        Buffers: shared hit=4000
        -&gt;  Index Only Scan using ev_par_0001_par_id_ev_id_idx on public.ev_par_0001 ep  (cost=0.43..2.65 rows=1 width=2) (actual time=0.015..0.016 rows=1 loops=1)
              Output: ep.ev_id
              Index Cond: (ep.par_id = 5432)
              Heap Fetches: 1
              Buffers: shared hit=4
        -&gt;  Index Only Scan using ev_par_0002_par_id_ev_id_idx on public.ev_par_0002 ep_1  (cost=0.43..2.65 rows=1 width=2) (actual time=0.007..0.007 rows=1 loops=1)
              Output: ep_1.ev_id
              Index Cond: (ep_1.par_id = 5432)
              Heap Fetches: 1
              Buffers: shared hit=4
...
... 998 more Index Only Scans - snipped for brevity
...
 -&gt;  Hash  (cost=23.00..23.00 rows=1000 width=13) (actual time=0.248..0.248 rows=1000 loops=1)
        Output: e.event_name, e.event_id
        Buckets: 1024  Batches: 1  Memory Usage: 53kB
        Buffers: shared hit=13
        -&gt;  Seq Scan on public.event e  (cost=0.00..23.00 rows=1000 width=13) (actual time=0.029..0.113 rows=1000 loops=1)
              Output: e.event_name, e.event_id
              Buffers: shared hit=13
Planning Time: 497.960 ms
Execution Time: 8.995 ms
(5016 rows)

Time: 538.058 ms

因此，分區表Index Only Scan在 1,000 個分區上執行，而Seq Scan在小event表上執行 - 所以它也很快！

最後，我執行了您的查詢 3 - 參與者即將發生的所有事件。基本上，這僅涉及獲取參與者的事件（查詢 2）並向WHERE子句添加謂詞 -event_date &gt; NOW()如下所示：

SELECT ep.ev_id, e.event_name, e.event_date
FROM event e
JOIN ev_par ep ON e.event_id = ep.ev_id
WHERE ep.par_id = 5432 AND e.event_date &gt; NOW();

結果：

ev_id   event_name  event_date
2   D980DE4C4E  2021-08-13
4   83DC72EF65  2021-08-13
6   CFFF3F2BAC  2021-08-13
8   0B07F148E8  2021-08-13
...
...  snipped for brevity
...
10 rows of 50

50 個是 100 個事件的一半。執行時間為 0.4 毫秒（兩個大表約為 0.5 秒），所以我們看起來不錯！

如您所見，具有良好索引的查詢非常快 - 顯然您的數據庫中會有更多記錄，但由於我們使用的是 BTREE，因此減速不會是 O(n) - 只要它們確實使用他們 - 分區方案意味著查詢 1 在大表中執行 - 但不適用於未分區的表！

但是，我認為顯示的數字很好地表明 PostgreSQL 在執行您的查詢時絕對沒有問題。如果您有一台配備 RAID 和 SSD 的不錯的伺服器，那您一定會嗡嗡作響！

添加事件時您將需要更多分區，但這不應該太繁重 - 最多需要幾分鐘來填充單個分區。

顯然，您應該在自己的系統上進行基準測試，以便為您自己的使用者獲得真實世界性能的真實想法。

所以，回答這個問題：

在這種情況下，每個表 1000 x 10M 記錄不是問題嗎？

不，這不是問題！

ps 歡迎來到論壇！pps 請在提問時始終包括您的伺服器版本！

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

相關問答

Postgresql

使用 HAProxy 和 PGBouncer 的 PostgreSQL 高可用性/可擴展性

  • October 27, 2020
檢視問答
Postgresql

縮放 PostgreSQL 觸發器

  • August 23, 2019
檢視問答
Postgresql

具有 pgpool 架構的 Postgres

  • November 20, 2018
檢視問答
Mysql

PostgreSQL 和 MySQL 的可擴展性限制

  • August 6, 2018
檢視問答
Postgresql

PostgreSQL 的正確儲存大小估計技術

  • September 5, 2017
檢視問答
Postgresql

儲存電子郵件收件箱資訊

  • May 26, 2016
檢視問答