Table of Contents
== 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)
Types of partitioning
Individual data records are divided and stored in a distributed manner
logically related data sets are stored separately
Range (range of timestamps)
List (e.g. text strings by list)
Hash (randomly puts data in partitions)
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.