Κλείσιμο
Κλείσιμο
Αρχική » Databases » PostgreSQL » Πώς κάνουμε Partitioning σε υφιστάμενο πίνακα στη PostgreSQL και πώς το συντηρούμε

Πώς κάνουμε Partitioning σε υφιστάμενο πίνακα στη PostgreSQL και πώς το συντηρούμε

Πώς κάνουμε Partitioning σε υφιστάμενο πίνακα στη PostgreSQL και πώς το συντηρούμε
, , , , Αφήστε ένα σχόλιο
Πώς κάνουμε Partitioning σε υφιστάμενο πίνακα στη PostgreSQL και πώς το συντηρούμε
Τελευταίες δημοσιεύσεις από Stratos Matzouranis (Προβολή όλων)

Αφού είδαμε αντίστοιχες πρακτικές στον SQL Server, ήρθε η ώρα να περάσουμε στη PostgreSQL και να δούμε πώς μπορούμε να κάνουμε partitioned έναν υφιστάμενο πίνακα που είναι ήδη γεμάτος δεδομένα, πώς ελέγχουμε τα υπάρχοντα partitions, πώς αυτοματοποιούμε τη δημιουργία μελλοντικών partitions και πώς καθαρίζουμε τα παλιά δεδομένα με ασφάλεια.

Η PostgreSQL δεν επιτρέπει τη μετατροπή ενός κανονικού πίνακα σε partitioned in-place. Η τυπική διαδικασία περιλαμβάνει τη μετονομασία του παλιού πίνακα, τη δημιουργία του νέου partitioned πίνακα, τη μαζική μεταφορά των δεδομένων και τέλος τον καθαρισμό.

Τα βήματα

Πρώτα κάνουμε μετονομασία τον υφιστάμενο πίνακα:

ALTER TABLE public.orders RENAME TO orders_old;

Μετά δημιουργούμε τον νέο Partitioned πίνακα με Range Partition βάσει ημερομηνίας:

CREATE TABLE public.orders (
    order_id integer,
    customer_id integer,
    order_date timestamp without time zone NOT NULL,
    status character varying(20),
    total_amount numeric(10,2),
    PRIMARY KEY (order_id, order_date)
) PARTITION BY RANGE (order_date);

Έπειτα δημιουργούμε τα μηνιαία partitions για το έτος:

CREATE TABLE orders_2026m01 PARTITION OF public.orders
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE orders_2026m02 PARTITION OF public.orders
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE orders_2026m03 PARTITION OF public.orders
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE orders_2026m04 PARTITION OF public.orders
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE orders_2026m05 PARTITION OF public.orders
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
CREATE TABLE orders_2026m06 PARTITION OF public.orders
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
CREATE TABLE orders_2026m07 PARTITION OF public.orders
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE orders_2026m08 PARTITION OF public.orders
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
CREATE TABLE orders_2026m09 PARTITION OF public.orders
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE orders_2026m10 PARTITION OF public.orders
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE orders_2026m11 PARTITION OF public.orders
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
CREATE TABLE orders_2026m12 PARTITION OF public.orders
    FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');

Στη συνέχεια δημιουργούμε index στο πεδίο που κάναμε το partition:

CREATE INDEX idx_order_date ON public.orders (order_date);

Το επόμενο βήμα ειναι να μεταφέρουμε τα δεδομένα από τον παλιό πίνακα στον νέο:

INSERT INTO public.orders SELECT * FROM orders_old;

Στο τέλος μπορούμε να διαγράψουμε τον παλιό πίνακα:

DROP TABLE IF EXISTS public.orders_old CASCADE;

Πώς βλέπουμε τα υπάρχοντα Partitions και Ranges

Για να επιβεβαιώσουμε ποια partitions είναι ενεργά και ποιο range καλύπτει το καθένα, τρέχουμε το παρακάτω query αξιοποιώντας τη συνάρτηση pg_get_expr:

SELECT 
    nmsp.nspname AS schema_name,
    parent.relname AS parent_table,
    child.relname AS partition_name,
    pg_get_expr(child.relpartbound, child.oid) AS partition_range
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace nmsp ON nmsp.oid = child.relnamespace
WHERE parent.relname = 'orders';
Πώς κάνουμε Partitioning σε υφιστάμενο πίνακα στη PostgreSQL και πώς το συντηρούμε

Πώς μπορούμε να διαγράψουμε άμεσα και με ασφάλεια παλιά Partitions

Όταν θέλουμε να εφαρμόσουμε retention policy και να διαγράψουμε παλιά δεδομένα π.χ. Γενάρη του 2026 απελευθερώνοντας άμεσα χώρο, ακολουθούμε τη λογική του Metadata Switch του SQL Server, αποσυνδέουμε το partition από τον πίνακα και μετά το σβήνουμε:

ALTER TABLE public.orders DETACH PARTITION orders_2026m01;
DROP TABLE orders_2026m01;

Πώς μπορούμε να αυτοματοποιήσουμε την δημιουργία Partitions του επόμενου έτους

Για να μην χρειάζεται να δημιουργούμε χειροκίνητα τα partitions κάθε χρόνο, μπορούμε να κατασκευάσουμε μια function η οποία υπολογίζει αυτόματα την επόμενη χρονιά και δημιουργεί τους 12 μήνες:

CREATE OR REPLACE FUNCTION create_next_year_monthly_partitions_orders() 
RETURNS void AS $$
DECLARE
    target_year int;
    m int;
    partition_name text;
    start_date text;
    end_date text;
BEGIN
    target_year := EXTRACT(YEAR FROM CURRENT_DATE) + 1;

    FOR m IN 1..12 LOOP
        
        partition_name := format('orders_%sm%s', target_year, lpad(m::text, 2, '0'));
        
        IF m = 12 THEN
            start_date := format('%s-12-01', target_year);
            end_date   := format('%s-01-01', target_year + 1);
        ELSE
            start_date := format('%s-%s-01', target_year, lpad(m::text, 2, '0'));
            end_date   := format('%s-%s-01', target_year, lpad((m + 1)::text, 2, '0'));
        END IF;

        IF NOT EXISTS (
            SELECT 1 FROM pg_class c 
            JOIN pg_namespace n ON n.oid = c.relnamespace 
            WHERE c.relname = partition_name
        ) THEN
            EXECUTE format(
                'CREATE TABLE %I PARTITION OF public.orders FOR VALUES FROM (%L) TO (%L);',
                partition_name, start_date, end_date
            );
        END IF;
        
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Για να εκτελείται αυτόματα η συνάρτηση κάθε μήνα στις 06:00 το πρωί, ρυθμίζουμε το crontab του χρήστη postgres:

sudo -u postgres crontab -e

Προσθέτοντας την παρακάτω γραμμή προγραμματισμού:

0 6 1 * * psql -d db_test -c "SELECT create_next_year_monthly_partitions_orders();"

Πηγές:

Μοιράσου το

Αφήστε μία απάντηση