Overview

== 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)

Terms

Partition bounds

Avoid sorting when partitions referenced in order

e.g.

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

Fillfactor

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.

Vacuum

– 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.

Hot

Avoids updating the index when doing an update

Reindex

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