== Splitting the data into several parts, which are stored separately

– Method to deal with very large amounts of data

– Partitions are objects, tables, intelligent heap (ORC), metadata in index (BRIN)

partitioning 1

Types of partitioning

Vertical partitioning

Individual data records are divided and stored in a distributed manner

Horizontal partitioning

logically related data sets are stored separately

hor vert partitioning 1

Partitioning methods

  • Range (range of timestamps)

  • List (e.g. text strings by list)

  • Hash (randomly puts data in partitions)


Partition bounds

Avoid sorting when partitions referenced in order


SELECT ORDER BY TIMESTAMP -> scan the partitions in time order

Allow expressions as partition bounds, but they don’t sort, so cancels out the optimization


for a table

– is a percentage between 10 and 100.

– 100 (complete packing) is the default

– When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page

– For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate.

for an index

– is a percentage that determines how full the index method will try to pack index pages.

– For B-trees, leaf pages are filled to this percentage during initial index build, and also when extending the index at the right (largest key values). If pages subsequently become completely full, they will be split, leading to gradual degradation in the index’s efficiency.

– but for heavily updated tables a smaller fillfactor is better to minimize the need for page splits.


– keeps your tables and indexes bloat-free

– reclaims storage occupied by dead tuples

– In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done.


Avoids updating the index when doing an update


Rebuilds an index using the data stored in the index’s table, replacing the old copy of the index.