PostgreSQL 10 High Performance - Third Edition

Sizing the partitions

A useful initial step to breaking a table into partitions is to figure out the range of data it contains, relative to the candidate field, and how large it is:

SELECT min(orderdate),max(orderdate) FROM orders;
    min     |    max     
------------+------------
 2004-01-01 | 2004-12-31
    
      
    
SELECT relname,relpages FROM pg_class WHERE relname LIKE 'orders%' ORDER BY relname;
      relname       | relpages
--------------------+----------
 orders             |      100
 orders_orderid_seq |        1
 orders_pkey        |       29 

This is obviously too small to be worth partitioning but, for a demonstration sample, it's large enough to demonstrate how splitting the table would look. Since there's a year of data here, breaking that into month-sized pieces would be appropriate.