6

I have a use-case where the data is many to many and needs a wide range of querying functionality.

Participants and Events

A User/Participant can register for multiple events. And each event can have many participants. It's a many-to-many relationship.

Consider a data set like this.

  • Each event can have 10Million users registered.
  • Each User can register a max of 1000 events
  • There are 1000 Events running

The following queries are required:

  • Query 1. Get all Participants who registered for an event
  • Query 2. Get all Events registered by a Participant
  • Query 3. Get all Events which are upcoming for a participant

For handling Query 1 & Query 2

EventParticipantTable : (eventId, participantId) : 1000 x 10M records

This needs to search 1000 x 10M records?

The dataset can be split into blocks per eventId to make it ideally scan only 10M records but not sure how this can be handled in PostgreSQL.

For handling Query 3

Event Table + EventParticipantTable Join

This needs a join of two tables where I first fetch the Events table for upcoming events(based on start and end timestamps) and for each eventId matched need to find if queried participant id exists in in EventParticipantTable.

This needs to search 1000 events * (1000 * 10M) event-participant-table entries ?

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

6

To resolve your issues, I did the following (all the code below is available on the fiddle here):

These tests have been run on the db<>fiddle server - we don't exactly know the configuration of the machine(s) nor do we know what else is happening while we're running our queries.

I also ran the tests on my home laptop:

  • Linux Fedora 34
  • 1TB Samsung SSD
  • 4 CPUs, 2 cores
  • nothing else running apart from standard Linux processes

The PostgreSQL 12.7 instance was compiled from source with the following options:

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

The system settings are the defaults except for the recommendations of pgtune as follows:

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

Recommended changes from defaults:

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

The min_ and max_wal settings were bumped up because of stuff I read for systems with heavy writes to speed up loading - shouldn't affect reads - lost references(s)...

Firstly, I created a function to generate random strings (from here):

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) );
$$;

Then, I created an event table:

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

gave it an index on event_name also - I can imagine many scenarios where one would wish to search by name.

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

and also on event_date:

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

Then I created 100 (1,000 on laptop) events as follows:

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

BUT!, you might scream... all the event dates are in the past - yes, but if you do this, then you'll have 50% in the past and 50% in the future:

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
  );

Checking with SELECT * FROM event; - result:

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
...

Doing it this way (rather than with literal dates) means that the fiddle will work years from now because the event_date depends only on when the fiddle is run and not some constant!

The participant table:

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

participant_name index:

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

Then created 10,000 (10,000,000 - 10M on laptop) participants:

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

Now, our joining table (or 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)
);

Now, this is where things become interesting. Running Query 1 on the laptop (see below) gives a response time of ~ 25 mins - not ideal!

I tried all sorts of "tricks" (SET enable_seqscan = off and SET enable_bitmapscan = off - see here) - basically, I was just flailing around trying anything I could find on the web...

I finally bit the bullet and went for partitioning - so, what's a logical partition key for the ev_par table? Well, the event_id appears to be the best candidate - there's 1,000 of them - the entire table (data only) was ~ 350GB so that would give 1,000 tables of ~ 350MB - more manageable!

With indexes (PK + par_ev_ix - see below), the table is ~ 750GB!

So, after the final bracket ();) and before the semi-colon, we put:

) PARTITION BY LIST (ev_id);

I found useful information here (the most helpful), here, here & here.

Basically (simplifying), there are 3 types of partitioning:

  • List
    CREATE TABLE customers (id INTEGER, status TEXT, arr NUMERIC) PARTITION BY LIST(status);
    (parent) and a typical partition would be created by running something like this:
    CREATE TABLE cust_active PARTITION OF customers FOR VALUES IN ('ACTIVE');

  • Range
    CREATE TABLE customers (id INTEGER, status TEXT, arr NUMERIC) PARTITION BY RANGE(arr);
    (parent) and a typical partition would be created by running something like this:
    CREATE TABLE cust_arr_small PARTITION OF customers FOR VALUES FROM (MINVALUE) TO (25);

  • Hash
    CREATE TABLE customers (id INTEGER, status TEXT, arr NUMERIC) PARTITION BY HASH(id);
    (parent) and a typical partition would be created by running something like this:
    CREATE TABLE cust_part1 PARTITION OF customers FOR VALUES WITH (modulus 3, remainder 0);

We now have to create 1,000 partitions using the LIST method - so, what do we do, bash script, PL/pgSQL... other? Searching, I found this **absolute gem** of a page with the following snippets:

$ 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

So, I adapted this code as follows:

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

and this generates our desired 1,000 partitions (first two DDL partitioning statements shown):

                             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);

I left-padded the partition names with 0 so that they would sort correctly when using \d+ ev_par.

Finally, we put an index on the "inverse" of the PRIMARY KEY for the ev_par table - i.e.

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

So that searches using par_id first will also be indexed.

Before populating the table, I disabled the constraints on the table by running the following command (from here):

ALTER TABLE reference DISABLE TRIGGER ALL;

and then I populated this by using a CROSS JOIN between the two tables. I split this process into 1,000 separate transactions adapting the partitioning code above as follows:

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

So, now we have 1,000,000 records in our ev_par table. On the laptop, this amounts to 10,000,000,000 (10Bn) records. Be warned - this takes approximately 6 hours even with an SSD and no constraints!

Then, we reactivate the constraints:

ALTER TABLE reference ENABLE TRIGGER ALL;

Then, I ran this query (your Query 1 - get all participants who registered for an event):

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;

Result:

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

But, we require a performance analysis, so I ran

EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE, COSTS)
<above query>

The one line we're interested in was this one:

Execution Time: 70.484 ms

Pretty impressive! However, it wasn't so impressive when run against the 10Bn record table:

Execution Time: ~ 25 mins

However, after partitioning the data, Query 1 returns:

Execution Time: 5795.941 ms

So, from 25 mins to 5 seconds - how come?

The answer lies in the plans - the plan for the unpartitioned tables (both fiddle and laptop) are the same:

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

and for the partitioned data:

    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)

The 2 critical lines are:

Non partitioned: ->  Seq Scan on public.participant p 

Parititioned:    ->  Index Scan using participant_pkey  

In the first case, it scans the entire participant table (10Bn records) and in the second, it uses the participant PRIMARY KEY - that's how the query went from 25 mins to 5 seconds!

Then I ran this (Query 2 - get all events registered by a participant):

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;

Result:

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

And:

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

This query ran on the non-partitioned 10Bn table very quickly also - as it as the only Seq Scan is on the small event table. Both large tables returned a result in approx. 0.5s!

Plans:

dbfiddle & laptop (non-partitioned):

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

Partitioned table:

    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

So, the partitioned table runs an Index Only Scan on the 1,000 partitions with a Seq Scan on the small event table - so it's quick also!

Finally, I ran your Query 3 - all events which are upcoming for a participant. Basically, this simply involves getting the events for a participant (Query 2) and adding a predicate to the WHERE clause - event_date > NOW() as follows:

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();

Result:

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 is half of the 100 events. Execution time was 0.4ms (~ 0.5s for both large tables), so we're looking good!

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!

11
  • 1
    Thanks for the detailed answer! I'm going through it! Would love to see how it performs on your local test!
    – Ayyappa
    Aug 6 at 13:42
  • 2
    @Ayyappa - I'm loading data as we speak! :-) I'm 7 COMMITs into a 1000 fold scenario! It turned out that I was a bit quick off the mark in saying that there'd be no problems with query times... If you're just looking for events, then Q2 is sub-ms, Q3 is 10ms (10Bn records) but Q1 ~ 25 mins! Will you really need to list 10M participants? Anyways, unless you're doing point selects on ev_par, it's going to take a while - getting 10M records is never a doodle any which way you look at it! Am looking at a partitioning strategy - have learnt a shitload - thanks for the question. Aug 9 at 19:47
  • 2
    @Ayyappa Didn't realise I hadn't upvoted it (done). At this rate, it's going to take a few hours - I'll have to leave it overnight (19:49 UTC now - reckon it'll finish sometime during the night) - I'll report back tomorrow! Aug 9 at 19:51
  • 2
    @Ayyappa - well, don't forget I've CROSS JOINed the two tables, so for every event, there's 10M participants - that'll be lower in a real life scenario (what is it?), but still, 10M is a lot of data - I'm hoping the partitioning scheme (on events as you say) will improve things... but hey, I won't know till I run the query tomorrow... Aug 9 at 19:55
  • 2
    @Ayyappa 25 mins to 5 seconds - I'm pretty damn pleased with myself!!! :-) First time I've done partitioning with PostgreSQL - it certainly won't be the last! Aug 10 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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