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 (Hubert depesz Lubaczewski's **absolute gem**]11) 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!
