主页 / dba / 问题 / 297662
Accepted
Ayyappa
Asked: 2021-08-06 20:06:48 +0800 CST

Postgres 是否适合不断增加的多对多数据 (M:N)？

  • 772

我有一个用例，其中数据是多对多的，需要广泛的查询功能。

参与者和活动

用户/参与者可以注册多个事件。每个事件可以有很多参与者。这是一个多对多的关系。

考虑这样的数据集。

  • 每个活动可以有 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 记录不是问题吗？

postgresql scalability

1 个回答

  1. Best Answer

    为了解决您的问题，我执行了以下操作（下面的所有代码都可以在此处的小提琴中找到）：

    这些测试已经在 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)
<above query>

    我们感兴趣的一行是这一行：

    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
  ->  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
  ->  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
   ->  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
   ->  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: ->  Seq Scan on public.participant p 

Parititioned:    ->  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
  ->  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
  ->  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
   ->  Append  (cost=0.43..2657.50 rows=1000 width=2) (actual time=0.016..4.866 rows=1000 loops=1)
         Buffers: shared hit=4000
         ->  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
         ->  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
...
  ->  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
         ->  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 > 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 > 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 秒），所以我们看起来不错！

    As you can see, the queries with good indexes are pretty fast - obviously you'll have more records in your database, but since we're using BTREEs, the slowdown won't be O(n) - as long as they do use them - the partitioning scheme means that Query 1 does in the large table - not for the unpartitioned one though!

    However, I think that the numbers shown give a good indication that PostgreSQL will have absolutely no problems running your queries. If you have a decent server with RAID and SSDs, you'll be humming!

    You'll will require more partitions as you add events, but that shouldn't be too onerous - it'll take a couple of minutes at most to fill a single partition.

    Obviously, you should benchmark on your own systems to obtain a realistic idea of real world performance for your own users.

    So, to answer the question:

    Is 1000 x 10M records per table is not an issue in this scenario?

    No, it is not an issue!

    p.s. welcome to the forum! p.p.s. please always include your server version when asking questions!

    • 7

相关问题