- Intro:
- Partition-able Table Characteristics:
- Building Use Case – Analysis of Query Plan:
- Declarative or Inheritance Partitioning:
- Aftermath:
- Partition Assessment Checks Script (Paid Content)
- Next Steps
- References:
Intro:
At work I created a new set of tables to fulfill the business needs. Unfortunately for the first time around, I couldn’t set aside the time consider maintenance of the tables as it was premature to even consider if the business idea would be successful. So, I decided to work on it another time. 🙂
About 7 months later, most of the queries became slower and the data seemingly grows infinitely. The good thing is I have a set of queries that I know is underperforming. Having this information sets the stage to consider if a table should be partitioned or not!
In this post series, I will share with you some real world benchmarks and preparation steps I’ve done to make a fully successful data partitioning migration with near zero downtime.
Partition-able Table Characteristics:
Generally a table is a good candidate for partitioning based on couple of characteristics:
- You’ll typically run queries against the partitioning key. This also depends on the type of partition you’re using:
- Range Type – on timestamp or numeric data type columns
- List Type – on enum type or string type columns. Usually list of discrete values.
- Let’s say you’re insurance mega corp. You need to divide your table by 50 states. Your discrete values would be NY, CA, TX, etc…
- Hash Type – your custom hash function to create the partition.
- For my case, it was a time-series data on Range Type and this is what this post will primarily be about. In almost all my query patterns, there is is typically time filtering such as:
select col1 from table where timestamp >= '2025-01-01' AND timestamp <= '2025-02-01'
- Not many “updates” operations on existing data, majority “inserts” and “reads”.
- Having “updates” operations is still fine. There is a little more complexity for the setup. You basically need to have idempotency key. Make sure your partitioning consistently routes it to the correct sub partitions.
- The idempotency key would need to be part of the partitioning key. So if you had a idempotency column and timestamp column, both of them will need to be part of the constraints in parent and children columns.
- You want to archive the table for cold storage like SOX compliance. Alternatively, you want to keep your DB storage light and remove inactive data.
- Most importantly the performance would dramatically improve if a table was partitioned. This requires certain understanding of the Query Planning.
Building Use Case – Analysis of Query Plan:
Dalibo:
There are some great tools out there that can read the query planning output. I actually found one out while writing this post. Kudos to the people at Dalibo! It has easy to understand breakdowns.
Large screenshot of dalibo
Raw Query Planning Interpretations:
Feel free to skip this section as this piece may be overtly detailed on the inner workings of the query planner.
EXPLAIN (ANALYZE, BUFFERS)
-- # Output
HashAggregate (cost=432331.89..432600.43 rows=3465 width=745) (actual time=5283.788..5283.824 rows=3 loops=1)
Group Key: product_codes.name, event_codes.code, price_lists.rate, price_lists.discount, price_lists.unit_of_measure
Batches: 1 Memory Usage: 121kB
Buffers: shared hit=55028 read=120297
I/O Timings: shared read=2532.284
-> Nested Loop (cost=128622.00..431945.72 rows=25745 width=581) (actual time=668.921..5186.008 rows=144513 loops=1)
Join Filter: ((event_transactions.event_code)::text = (event_codes.code)::text)
Buffers: shared hit=55028 read=120297
I/O Timings: shared read=2532.284
-> Hash Join (cost=5.26..16.57 rows=2 width=587) (actual time=0.062..0.097 rows=6 loops=1)
Hash Cond: ((product_codes.code)::text = (event_codes.product_code)::text)
Buffers: shared hit=4
-> Seq Scan on product_codes (cost=0.00..10.90 rows=45 width=574) (actual time=0.009..0.021 rows=23 loops=1)
Filter: is_active
Rows Removed by Filter: 1
Buffers: shared hit=1
-> Hash (cost=5.20..5.20 rows=5 width=71) (actual time=0.042..0.045 rows=6 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=3
-> Hash Join (cost=1.44..5.20 rows=5 width=71) (actual time=0.025..0.041 rows=6 loops=1)
Hash Cond: ((price_lists.event_code)::text = (event_codes.code)::text)
Buffers: shared hit=3
-> Seq Scan on price_lists (cost=0.00..3.70 rows=19 width=20) (actual time=0.007..0.018 rows=19 loops=1)
Filter: (is_active AND (billing_policy_id = 67))
Rows Removed by Filter: 117
Buffers: shared hit=2
-> Hash (cost=1.35..1.35 rows=7 width=51) (actual time=0.012..0.013 rows=7 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on event_codes (cost=0.00..1.35 rows=7 width=51) (actual time=0.004..0.007 rows=7 loops=1)
Filter: (is_active AND ((category)::text = 'engagement'::text))
Rows Removed by Filter: 26
Buffers: shared hit=1
-> Bitmap Heap Scan on event_transactions (cost=128616.73..215619.66 rows=27593 width=14) (actual time=316.065..859.687 rows=24086 loops=6)
Recheck Cond: (((event_code)::text = (price_lists.event_code)::text) AND ((mbid)::text = '0294'::text) AND ("timestamp" >= '2025-05-01 05:00:00+00'::timestamp with time zone) AND ("timestamp" <= '2025-06-01 04:59:59+00'::timestamp with time zone))
Rows Removed by Index Recheck: 264835
Filter: ((email IS NULL) OR ((email)::text = ''::text) OR ((email IS NOT NULL) AND ((email)::text <> ''::text) AND ((email)::text <> ALL ('{foobar@google.com}'::text[])) AND ((email)::text !~~ '%@foobar.com'::text)))
Rows Removed by Filter: 1
Heap Blocks: exact=16759 lossy=113560
Buffers: shared hit=55024 read=120297
I/O Timings: shared read=2532.284
-> BitmapAnd (cost=128616.68..128616.68 rows=27796 width=0) (actual time=312.209..312.210 rows=0 loops=6)
Buffers: shared hit=35095 read=9907
I/O Timings: shared read=463.423
-> Bitmap Index Scan on ix_billing_event_transactions_event_code (cost=0.00..26255.02 rows=2254026 width=0) (actual time=35.631..35.631 rows=758473 loops=6)
Index Cond: ((event_code)::text = (price_lists.event_code)::text)
Buffers: shared hit=6656
-> Bitmap Index Scan on ix_billing_event_transactions_mbid (cost=0.00..27607.06 rows=1958749 width=0) (actual time=150.594..150.594 rows=1989873 loops=3)
Index Cond: ((mbid)::text = '0294'::text)
Buffers: shared hit=8487
-> Bitmap Index Scan on ix_billing_event_transactions_timestamp (cost=0.00..74595.45 rows=3870301 width=0) (actual time=386.129..386.129 rows=5638516 loops=3)
Index Cond: (("timestamp" >= '2025-05-01 05:00:00+00'::timestamp with time zone) AND ("timestamp" <= '2025-06-01 04:59:59+00'::timestamp with time zone))
Buffers: shared hit=19952 read=9907
I/O Timings: shared read=463.423
Planning:
Buffers: shared hit=154
Planning Time: 1.157 ms
Execution Time: 5285.118 ms
- I have highlighted in bold key information.
- On the initial outlook, it seems most of the columns are already indexed. For the ones that are missing, they are negligible.
Evidences supporting the need to Partition
- Heap Blocks: exact=16759 lossy=113560 –> Lossy Bitmap scans is caused by running out of working memory when reading index and data. There are several issues when running out of WM.
- First the data it required to load into memory was massive.
- -> BitmapAnd Buffers: shared hit=35095 read=9907
- Total index blocks: 35,095 + 9,907 = 45,002 blocks
- 45,002 blocks × 8KB = 352 MB
- -> Bitmap Heap Scan on event_transactions Buffers: shared hit=55024 read=120297
- Total data blocks: 55,024 + 120,297 = 175,321 blocks
- 175,321 blocks × 8KB = 1,370 MB
- If the query planner decides it couldn’t process the data in memory, it would write to disk and process the operations there instead.
- Disk scan(50x-100x slower) > Memory scan
- Problem is worst if HDD is used instead of SSD for DB storage.
- -> BitmapAnd Buffers: shared hit=35095 read=9907
- Second problem, it also triggers an operation called “rechecks”. Rechecks are usually skipped because relevant data are kept in the memory and even cached. But with writing to disk, the query planner now need to store an entire “page” of data instead of “rows” of data. See References below what is a “page” and what is “row”.
- The page of data may contain the relevant data or may not, this is why “rechecks” are ran to confirm.
- First the data it required to load into memory was massive.
- Pruning unrelated data – there is an index scan on the timestamp column and here we see there are rows=5,638,516 million rows.
- The entire timestamp column is loaded into memory to find relevant data. If we prune the data monthly, and the query only looks at one month of data, the # rows of index required to be scan would become 5.7milllion / 12 = 470,000 rows instead.
- The sheer amount of data needing to process for the db as result of partitioning would greatly improve.
query to check table and index size
SELECT
pg_size_pretty(pg_relation_size('<your table>')) AS table_size,
pg_size_pretty(pg_total_relation_size('<your table>')) AS table_with_indexes_size,
pg_size_pretty(pg_total_relation_size('<your table>') - pg_relation_size('billing.event_transactions')) AS indexes_only_size,
COUNT(*) AS row_count,
pg_size_pretty(pg_relation_size('<your table>')::bigint / NULLIF(COUNT(*), 0)) AS avg_row_size
FROM <your table>;
-- output
[
{
"table_size": "12 GB",
"table_with_indexes_size": "18 GB",
"indexes_only_size": "6850 MB",
"row_count": 24730291,
"avg_row_size": "509 bytes"
}
]The query shows the index already took up 1/3 of the total table data size including index. This is 6GB. The database size is just 12GB, not that big actually.
I can’t exactly say how much it would improve after partitioning but the IO time “I/O Timings: shared read=2532.284″ likely would be completely removed. The rest of the operations should improve as well due to sheer reduction of data volume.
Data Growth:
The table I was working on was a Financial transaction data.
- The finance team had informed me that the company has a policy of 7 years of data retention for auditing.
- I rounded up that the first year the amount of rows required to be stored would be around 50 million with a ten percent growth year over year assuming business is booming.
- By the end of 7 years, we would have about one billion rows.
- The data volume was not that bad, but also not very small.
Declarative or Inheritance Partitioning:
There are two flavors of partitioning mode in Postgresql starting on version 10+ since Oct 2017.
- TLDR: Just use declarative partitioning!
- Declarative Partitioning – This is the recommended approach for the majority of all partitioning uses cases. It handles the heavy lift of query planning optimizer, automatic index creation for children tables, partition pruning at query time
- Inheritance Partitioning – legacy approach. NOT RECOMMENDED TO USE. Requires creating custom triggers to insert data, custom query routing, manual creation of parent and children tables, etc.
- Yet, there are several benefits:
- In Declarative partitioning, the parent and children must have exact same columns and structure. Inheritance partitioning children can have extra tables
- Multiple table inheritances with possibilities of partitioning with multiple views/keys
- Data divided among children can be highly customized. You’re creating the data triggers yourself. Be very careful, as it may not improve query performance. This occurs if constraint exclusions are not able to prune children tables.
- Yet, there are several benefits:
Aftermath:
By the end of completing the partitioned table migration process, I saw significant changes. Running the same queries on the new tables resulted in a whopping 680% performance improvement!
Below with more details
5.2 seconds → 0.756 seconds == 680%!
Buffer Usage
- Before:
- Shared hit: 55,028
- Shared read: 120,297 (physical disk reads)
- After:
- Shared hit: 120,792
- No physical disk reads (all in cache)
- Temp buffers: 1,523 read, 1,527 written (for sorting)
I/O Performance
- Before: 2,532.284 ms spent on disk reads
- After: 13.75 ms (4.226 + 9.524) for temp file I/O
- Improvement: 99.5% reduction in I/O time
Row Processing Efficiency
- Before:
- Rows removed by index recheck: 264,835
- Heap blocks: exact=16,759, lossy=113,560
- After:
- Rows removed by index recheck: 50,244
- Heap blocks: exact=28,628, lossy=22,681
- Improvement: 81% fewer unnecessary rows processed
EXPLAIN ANALYZE output
|QUERY PLAN -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|GroupAggregate (cost=152265.39..152734.88 rows=4942 width=745) (actual time=752.004..753.123 rows=3 loops=1) |
| Group Key: product_codes.name, event_codes.code, price_lists.rate, price_lists.discount, price_lists.unit_of_measure |
| Buffers: shared hit=120792, temp read=1523 written=1527 |
| I/O Timings: temp read=4.226 write=9.524 |
| -> Sort (cost=152265.39..152277.75 rows=4942 width=581) (actual time=555.554..670.403 rows=144513 loops=1) |
| Sort Key: product_codes.name, event_codes.code, price_lists.rate, price_lists.discount, price_lists.unit_of_measure |
| Sort Method: external merge Disk: 12184kB |
| Buffers: shared hit=120792, temp read=1523 written=1527 |
| I/O Timings: temp read=4.226 write=9.524 |
| -> Nested Loop (cost=6461.23..151962.18 rows=4942 width=581) (actual time=111.543..457.397 rows=144513 loops=1) |
| Join Filter: ((event_transactions.event_code)::text = (event_codes.code)::text) |
| Buffers: shared hit=120792 |
| -> Hash Join (cost=7.16..18.61 rows=4 width=587) (actual time=0.066..0.099 rows=6 loops=1) |
| Hash Cond: ((product_codes.code)::text = (event_codes.product_code)::text) |
| Buffers: shared hit=5 |
| -> Seq Scan on product_codes (cost=0.00..10.90 rows=45 width=574) (actual time=0.007..0.013 rows=23 loops=1) |
| Filter: is_active |
| Rows Removed by Filter: 1 |
| Buffers: shared hit=1 |
| -> Hash (cost=7.07..7.07 rows=7 width=71) (actual time=0.053..0.058 rows=6 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| Buffers: shared hit=4 |
| -> Hash Join (cost=1.44..7.07 rows=7 width=71) (actual time=0.027..0.048 rows=6 loops=1) |
| Hash Cond: ((price_lists.event_code)::text = (event_codes.code)::text) |
| Buffers: shared hit=4 |
| -> Seq Scan on price_lists (cost=0.00..5.55 rows=28 width=20) (actual time=0.007..0.021 rows=19 loops=1) |
| Filter: (is_active AND (billing_policy_id = 67)) |
| Rows Removed by Filter: 136 |
| Buffers: shared hit=3 |
| -> Hash (cost=1.35..1.35 rows=7 width=51) (actual time=0.013..0.014 rows=7 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 9kB |
| Buffers: shared hit=1 |
| -> Seq Scan on event_codes (cost=0.00..1.35 rows=7 width=51) (actual time=0.004..0.008 rows=7 loops=1) |
| Filter: (is_active AND ((category)::text = 'engagement'::text)) |
| Rows Removed by Filter: 26 |
| Buffers: shared hit=1 |
| -> Append (cost=6454.07..37632.88 rows=28241 width=14) (actual time=32.126..72.436 rows=24086 loops=6) |
| Buffers: shared hit=120787 |
| -> Bitmap Heap Scan on event_transactions_partitioned_p20250501 event_transactions_1 (cost=6454.07..35734.39 rows=28151 width=14) (actual time=26.770..59.519 rows=23991 loops=6) |
| Recheck Cond: (((mbid)::text = '0294'::text) AND ((event_code)::text = (price_lists.event_code)::text)) |
| Rows Removed by Index Recheck: 50244 |
| Filter: (("timestamp" >= '2025-05-01 05:00:00+00'::timestamp with time zone) AND ("timestamp" <= '2025-06-01 04:59:59+00'::timestamp with time zone) AND ((email IS NULL) OR ((email)::text = ''::text) OR ((email IS NOT NULL) AND ((email)::text <> ''::text) AND ((email)::text <> ALL ('foobar@mail.com}'::text[])) AND ((email)::text !~~ '%@foobar.com'::text))))|
| Rows Removed by Filter: 191 |
| Heap Blocks: exact=28628 lossy=22681 |
| Buffers: shared hit=53317 |
| -> BitmapAnd (cost=6454.02..6454.02 rows=28734 width=0) (actual time=25.167..25.167 rows=0 loops=6) |
| Buffers: shared hit=2008 |
| -> Bitmap Index Scan on event_transactions_partitioned_p20250501_mbid_idx (cost=0.00..3071.49 rows=287341 width=0) (actual time=17.900..17.900 rows=288227 loops=6) |
| Index Cond: ((mbid)::text = '0294'::text) |
| Buffers: shared hit=1404 |
| -> Bitmap Index Scan on event_transactions_partitioned_p20250501_event_code_idx (cost=0.00..3304.87 rows=382611 width=0) (actual time=4.183..4.183 rows=119773 loops=6) |
| Index Cond: ((event_code)::text = (price_lists.event_code)::text) |
| Buffers: shared hit=604 |
| -> Index Scan using event_transactions_partitioned_p20250601_timestamp_idx on event_transactions_partitioned_p20250601 event_transactions_2 (cost=0.48..1757.29 rows=90 width=14) (actual time=6.137..10.985 rows=95 loops=6) |
| Index Cond: (("timestamp" >= '2025-05-01 05:00:00+00'::timestamp with time zone) AND ("timestamp" <= '2025-06-01 04:59:59+00'::timestamp with time zone)) |
| Filter: (((mbid)::text = '0294'::text) AND ((event_code)::text = (price_lists.event_code)::text) AND ((email IS NULL) OR ((email)::text = ''::text) OR ((email IS NOT NULL) AND ((email)::text <> ''::text) AND ((email)::text <> ALL ('{foobar@mail.com}'::text[])) AND ((email)::text !~~ '%@foobar.com'::text)))) |
| Rows Removed by Filter: 22305 |
| Buffers: shared hit=67470 |
|Planning: |
| Buffers: shared hit=54 |
|Planning Time: 1.183 ms |
|Execution Time: 756.323 ms |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Partition Assessment Checks Script (Paid Content)
You may have many tables that might need to consider partitioning, or you don’t have the time to read the explain analyze query plan. Below is a script below that may make the decision easier.
partition_assessment_checks.sql
-- ====================================================================
-- PARTITION ASSESSMENT QUERIES
-- ====================================================================
-- INSTRUCTIONS:
-- 1. Update the values in the 'params' CTE below
-- 2. Run the entire script to get all assessments
-- ====================================================================
-- <script>
Next Steps
- Head over to the Part II of this post series to see how I got there!
- Please like and subscribe!
Coffee Break 
Leave a comment