Data Partitioning - Range partitioning

Giriş

Açıklaması şöyle

The table is partitioned into “ranges’’ defined by a key column or set of columns, with no overlap between the ranges of values assigned to different partitions. This partitioning is perfect for time series data

Açıklaması şöyle

In PostgreSQL, table partitioning is achieved by using the built-in table inheritance feature, where a table is split into multiple smaller tables called partitions. Each partition can have its indexes, constraints, and storage parameters.

One of the most common uses of partitioning in PostgreSQL is range partitioning. This method divides a large table into smaller, more manageable pieces based on a range of values. The most frequent use case for this type of partitioning is to partition a table of time-based data, such as a table of sales data or a table of log data, by a date or timestamp column.

Şeklen şöyle

PARTITION BY RANGE + PARTITION OF + FOR VALUES FROM

1. PARTITION BY RANGE ile bir sütuna göre bölümleneceğini belirtir

2. CREATE TABLE + PARTITION OF + FOR VALUES FROM ile bölümlemelere ait yeni tablo yarat

Örnek

Şöyle yaparız. Önce transactions tablosu PARTITION BY RANGE ile bölümlendiriliyor. Daha sonra belirli bölümler için tablolar yaratılıyor

CREATE TABLE transactions (
    transaction_id SERIAL PRIMARY KEY,
    transaction_date DATE,
    user_id INTEGER,
    amount DECIMAL(10, 2),
    ...
)
PARTITION BY RANGE (transaction_date);

CREATE TABLE transactions_2022_01 PARTITION OF transactions
  FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE transactions_2022_02 PARTITION OF transactions
  FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');

Örnek

Şöyle yaparız. Burada bir partition tablosu arşivleniyor

-- Detach the partition for transactions in 2021 from the main table
ALTER TABLE transactions DETACH PARTITION transactions_2021;

-- Attach the detached partition to the archive table
ALTER TABLE transactions_archive ATTACH PARTITION transactions_2021;

Açıklaması şöyle

Move older partitions to an archive table: Once you have partitioned the main table, you can move older partitions to a separate archive table using a script or tool. For example, you can use the ALTER TABLE command to detach a partition from the main table and attach it to the archive table

Örnek

Şöyle yaparız

CREATE TABLE mytable (id SERIAL PRIMARY KEY, created_at TIMESTAMP);
CREATE TABLE mytable_2019 PARTITION OF mytable FOR VALUES FROM ('2019–01–01')
  TO ('2020–01–01');
CREATE TABLE mytable_2020 PARTITION OF mytable FOR VALUES FROM ('2020–01–01') 
  TO ('2021–01–01');
CREATE TABLE mytable_2021 PARTITION OF mytable FOR VALUES FROM ('2021–01–01') 
  TO ('2022–01–01');

ALTER TABLE

ADD PARTITION + CHECK ile yapılır. CHECK ile bir constraint yaratılır

Örnek

Şöyle yaparız

-- create the sales table
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    date DATE,
    product VARCHAR(255),
    quantity INTEGER,
    price DECIMAL(10,2)
);

-- create the partition tables
CREATE TABLE sales_2022_Q1 (LIKE sales);
CREATE TABLE sales_2022_Q2 (LIKE sales);
CREATE TABLE sales_2022_Q3 (LIKE sales);
CREATE TABLE sales_2022_Q4 (LIKE sales);

-- attach the partition tables to the sales table
ALTER TABLE sales ADD PARTITION sales_2022_Q1 
  CHECK (date >= '2022-01-01' AND date < '2022-04-01');
ALTER TABLE sales ADD PARTITION sales_2022_Q2 
  CHECK (date >= '2022-04-01' AND date < '2022-07-01');
ALTER TABLE sales ADD PARTITION sales_2022_Q3 
  CHECK (date >= '2022-07-01' AND date < '2022-10-01');
ALTER TABLE sales ADD PARTITION sales_2022_Q4 
  CHECK (date >= '2022-10-01' AND date < '2023-01-01');

İşlemler sales tablosu üzerinde yapılır. PostgreSQL otomatik olarak doğru tabloya yönlendirir. Şöyle yaparız

INSERT INTO sales (id, date, quantity, price)
VALUES (101, '2022-01-15', 5, 100.00);

-- Retrieve sales data for January
SELECT * FROM sales WHERE date >= '2022-01-01' AND date < '2022-02-01';