Postgres

Notes
backend
database

Partitioning

Requirement for partitioning increases when tasks and maintenance task cost increases

Considerations

  • partitioning is part of table definition and are significantly harder to change
  • partitioning when done wrong, it can make the performance worse or can make database unstable

Rules based on

  • table size
  • response time
  • table bloat - vacuum process
  • access patterns

How to partition

  • taget columns to separate more accessed data How to find access patterns
  • generate logs

Partitioning Methods

  • List Partition
  • Range Partition
  • Hash Partition
  • Composite Partition

Types of Paritioning

  • Declarative Partitioning - Minimal Maintenance
  • Inheritance Partitioning - Difficult to setup, but flexible

Range Partitioning

CREATE TABLE customers (columns) PARTITION BY range(age);
CREATE TABLE cust_young PARTITION OF customers FOR values FROM (MINVALUE) to (25);
CREATE TABLE cust_medium PARTITION OF customers FOR values FROM (25) to (75);
CREATE TABLE cust_old PARTITION OF customers FOR values FROM (75) to (MAXVALUE);

Migration

Migration Strategies

  • Traditional Migration
  • Blue green migration
  • Logical Replication

Backlinks