Determining a key field to partition over

There are two potential ways that you could split this data into smaller pieces. The first would be to partition the table into sections based on the orderid field. This is probably what a real-world deployment would need to do here, because if the orders table is too large the orderlines table would be even larger. Both tables could usefully be partitioned by orderid.

However, imagine that orders are only kept for a period of time—perhaps a couple of years. Older orders could therefore be deleted in that situation. The problem with mass deletion in PostgreSQL is that it leaves a lot to be cleaned up after. First, you'll need to vacuum the table to mark the deleted rows as dead. This might return space to the operating system, but it's quite possible it will not. You can also end up with bloated indexes from this usage pattern, if data from various time periods is mixed together in the same data block.

If the orders table were...