Using table partitioning v17

In a partitioned table, one logically large table is broken into smaller physical pieces. Partitioning can provide several benefits:

• Query performance can improve dramatically, particularly when most of the heavily accessed rows of the table are in a single partition or a small number of partitions. Partitioning allows you to omit the partition column from the front of an index. This approach reduces index size and makes it more likely that the heavily used parts of the index fit in memory.
• When a query or update accesses a large percentage of a single partition, performance might improve. This improvement happens because the server performs a sequential scan of the partition instead of using an index and random-access reads scattered across the whole table.
• If you plan the requirement into the partitioning design, you can implement a bulk load or unload by adding or removing partitions. ALTER TABLE is far faster than a bulk operation. It also avoids the VACUUM overhead caused by a bulk DELETE.
• You can migrate seldom-used data to less-expensive or slower storage media.

Table partitioning is worthwhile when a table is becoming very large. The exact point at which a table benefits from partitioning depends on the application. A good guideline is for the size of the table not to exceed the physical memory of the database server.