PostgreSQL is a powerful database which supports partitioning. In contrast to Oracle partitioning is part of the PostgreSQL core engine and does not need any additional licensing or extensions. If you migrate from Oracle to PostgreSQL this will be a major advantage.
Table of Contents
However, just like any other technology, partitioning is not without risk. While it might help to handle large quantities of data efficiently it can also have downsides which have to be taken into consideration when using this powerful feature.
The most basic example involves a table consisting of two columns:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
test=# CREATE TABLE t_simple ( id serial, val int DEFAULT random()*100000 ); CREATE TABLE test=# INSERT INTO t_simple SELECT FROM generate_series(1, 10000000); INSERT 0 10000000 test=# CREATE INDEX ON t_simple (val); CREATE INDEX test=# VACUUM ANALYZE; VACUUM |
In this case we have created a table and loaded 10 million rows. The column we want to query is indexed. When looking for a handful of values we will end up with a single index scan:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
test=# explain (analyze, buffers, costs) SELECT * FROM t_simple WHERE val = 454650; QUERY PLAN -------------------------------------------------------------- Bitmap Heap Scan on t_simple (cost=5.20..388.39 rows=99 width=8) (actual time=0.010..0.011 rows=0 loops=1) Recheck Cond: (val = 454650) Buffers: shared hit=3 -> Bitmap Index Scan on t_simple_val_idx (cost=0.00..5.18 rows=99 width=0) (actual time=0.009..0.010 rows=0 loops=1) Index Cond: (val = 454650) Buffers: shared hit=3 Planning Time: 0.066 ms Execution Time: 0.028 ms (8 rows) |
This is not really a surprise. What is noteworthy here is that the query is executed in a fraction of a millisecond. PostgreSQL can execute thousands of such queries per second per CPU core. On a large server we can easily reach more than 1 million queries per second.
Let's deploy the same data using partitions. To prove our point we'll use hash partitions for this purpose:
|
1 2 3 4 5 |
test=# CREATE TABLE t_part ( id serial, val int DEFAULT random()*100000) PARTITION BY HASH (id); CREATE TABLE |
After deploying the parent table, we can create the partitions. For the sake of simplicity, I have created a set of only 8 partitions which is enough to make a point. The effect you are going to see is even larger if the number of partitions increases:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE TABLE t_part_1 PARTITION OF t_part FOR VALUES WITH (MODULUS 8, REMAINDER 0); CREATE TABLE t_part_2 PARTITION OF t_part FOR VALUES WITH (MODULUS 8, REMAINDER 1); CREATE TABLE t_part_3 PARTITION OF t_part FOR VALUES WITH (MODULUS 8, REMAINDER 2); CREATE TABLE t_part_4 PARTITION OF t_part FOR VALUES WITH (MODULUS 8, REMAINDER 3); CREATE TABLE t_part_5 PARTITION OF t_part FOR VALUES WITH (MODULUS 8, REMAINDER 4); CREATE TABLE t_part_6 PARTITION OF t_part FOR VALUES WITH (MODULUS 8, REMAINDER 5); CREATE TABLE t_part_7 PARTITION OF t_part FOR VALUES WITH (MODULUS 8, REMAINDER 6); CREATE TABLE t_part_8 PARTITION OF t_part FOR VALUES WITH (MODULUS 8, REMAINDER 7); |
|
1 2 3 4 5 6 7 8 9 10 |
test=# INSERT INTO t_part SELECT FROM generate_series(1, 10000000); INSERT 0 10000000 Again an index is created to make a fair comparison: test=# CREATE INDEX ON t_part (val); CREATE INDEX test=# VACUUM ANALYZE; VACUUM |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 |
test=# explain (analyze, buffers, costs) SELECT * FROM t_part WHERE val = 454650; QUERY PLAN ------------------------------------------------------ Append (cost=4.53..438.92 rows=104 width=8) (actual time=0.141..0.145 rows=0 loops=1) Buffers: shared read=24 -> Bitmap Heap Scan on t_part_1 (cost=4.53..54.80 rows=13 width=8) (actual time=0.024..0.025 rows=0 loops=1) Recheck Cond: (val = 454650) Buffers: shared read=3 -> Bitmap Index Scan on t_part_1_val_idx (cost=0.00..4.53 rows=13 width=0) (actual time=0.023..0.023 rows=0 loops=1) Index Cond: (val = 454650) Buffers: shared read=3 -> Bitmap Heap Scan on t_part_2 (cost=4.53..54.80 rows=13 width=8) (actual time=0.018..0.019 rows=0 loops=1) Recheck Cond: (val = 454650) Buffers: shared read=3 -> Bitmap Index Scan on t_part_2_val_idx (cost=0.00..4.53 rows=13 width=0) (actual time=0.018..0.018 rows=0 loops=1) Index Cond: (val = 454650) Buffers: shared read=3 -> Bitmap Heap Scan on t_part_3 (cost=4.53..54.80 rows=13 width=8) (actual time=0.014..0.015 rows=0 loops=1) Recheck Cond: (val = 454650) Buffers: shared read=3 -> Bitmap Index Scan on t_part_3_val_idx (cost=0.00..4.53 rows=13 width=0) (actual time=0.014..0.014 rows=0 loops=1) Index Cond: (val = 454650) Buffers: shared read=3 -> Bitmap Heap Scan on t_part_4 (cost=4.53..54.80 rows=13 width=8) (actual time=0.014..0.014 rows=0 loops=1) Recheck Cond: (val = 454650) Buffers: shared read=3 -> Bitmap Index Scan on t_part_4_val_idx (cost=0.00..4.53 rows=13 width=0) (actual time=0.014..0.014 rows=0 loops=1) Index Cond: (val = 454650) Buffers: shared read=3 -> Bitmap Heap Scan on t_part_5 (cost=4.53..54.80 rows=13 width=8) (actual time=0.016..0.017 rows=0 loops=1) Recheck Cond: (val = 454650) Buffers: shared read=3 -> Bitmap Index Scan on t_part_5_val_idx (cost=0.00..4.53 rows=13 width=0) (actual time=0.016..0.016 rows=0 loops=1) Index Cond: (val = 454650) Buffers: shared read=3 -> Bitmap Heap Scan on t_part_6 (cost=4.53..54.80 rows=13 width=8) (actual time=0.018..0.018 rows=0 loops=1) Recheck Cond: (val = 454650) Buffers: shared read=3 -> Bitmap Index Scan on t_part_6_val_idx (cost=0.00..4.53 rows=13 width=0) (actual time=0.017..0.017 rows=0 loops=1) Index Cond: (val = 454650) Buffers: shared read=3 -> Bitmap Heap Scan on t_part_7 (cost=4.53..54.80 rows=13 width=8) (actual time=0.017..0.017 rows=0 loops=1) Recheck Cond: (val = 454650) Buffers: shared read=3 -> Bitmap Index Scan on t_part_7_val_idx (cost=0.00..4.53 rows=13 width=0) (actual time=0.017..0.017 rows=0 loops=1) Index Cond: (val = 454650) Buffers: shared read=3 -> Bitmap Heap Scan on t_part_8 (cost=4.53..54.80 rows=13 width=8) (actual time=0.017..0.018 rows=0 loops=1) Recheck Cond: (val = 454650) Buffers: shared read=3 -> Bitmap Index Scan on t_part_8_val_idx (cost=0.00..4.53 rows=13 width=0) (actual time=0.017..0.017 rows=0 loops=1) Index Cond: (val = 454650) Buffers: shared read=3 Planning: Buffers: shared hit=100 Planning Time: 0.769 ms Execution Time: 0.235 ms (54 rows) |
Wow, what an execution plan. Partitioning has not done us any favors here. The reason is that at this point, we have to scan every single partition to find the data. This causes a significantly higher runtime, since it is simply more work for the database engine.
The takeaway is that if the partitioning criteria is not part of the query or if many partitions have to be touched in general, runtime will suffer, which is exactly what happened here. It is also important to note that using too many partitions will significantly increase the time the planner needs to do its job. Having hundreds of partitions can easily lead to a real disaster.
Partitioning has many advantages. However, it has to be used cleverly and not blindly. There is no feature which is always advantageous. The same is true for partitioning and it makes sense to take that into consideration.
For further blogs on the topic of partitioning and performance, see these posts:
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.
+43 (0) 2622 93022-0
office@cybertec.at
The choice of partitioning strategy depends on the use case. For example, if you have a table with 2 billion rows and you mostly query it using the user_id column, then you might want to use hash partitioning. This is because hash partitioning will allow the database to quickly scan the relevant partition when you pass the user_id as a WHERE clause value.
In your query, you used SELECT * wihtout any where clause, which means that the database will have to scan all of the rows in all the table. This will make the query slow, regardless of the partitioning strategy.
I guess you didn't read carefully enough - there is a
WHEREcondition. The query is not very slow, but quite a bit slower than on the unpartitioned table. That is normal.Use range for partitioning!
That wouldn't make a difference in this case.
I'm kind of surprised that this happens with an equality test. For a range test, sure you can't eliminate any fragment; but the optimiser could (and IMHO should!) apply 454650%8 to the query plan, so for an equality test I would expect fragment elimination.
Maybe in 16...?
The column used in the
WHEREcondition is not the partitioning column. How should PostgreSQL know in which partition a matching column lies?