{"id":6142,"date":"2026-10-12T07:00:00","date_gmt":"2026-10-12T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=6142"},"modified":"2026-10-11T16:16:08","modified_gmt":"2026-10-11T13:16:08","slug":"pos-kanoyme-partitioning-se-yfistameno-pinaka-st","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-kanoyme-partitioning-se-yfistameno-pinaka-st\/","title":{"rendered":"How to Partition an Existing Table in PostgreSQL and How to Maintain It"},"content":{"rendered":"

After seeing similar practices in SQL Server, it's time to move on to PostgreSQL<\/strong> and see how we can partition an existing table that is already full of data, how we control existing partitions, how we automate the creation of future partitions, and how we safely clean up old data.<\/p>\n\n\n\n

PostgreSQL does not allow converting a regular table to partitioned in-place. The typical process involves renaming the old table, creating the new partitioned table, bulk transferring the data, and finally cleaning it up.<\/p>\n\n\n\n

<\/p>\n\n\n\n

The footsteps<\/h2>\n\n\n\n

First we rename the existing table:<\/p>\n\n\n\n

ALTER TABLE public.orders RENAME TO orders_old;<\/pre>\n\n\n\n
Then we create the new Partitioned table with Range Partition<\/code> by date:<\/p>\n\n\n\n
CREATE TABLE public.orders (\n    order_id integer,\n    customer_id integer,\n    order_date timestamp without time zone NOT NULL,\n    status character varying(20),\n    total_amount numeric(10,2),\n    PRIMARY KEY (order_id, order_date)\n) PARTITION BY RANGE (order_date);<\/pre>\n\n\n\n
Then we create the monthly partitions for the year:<\/p>\n\n\n\n
CREATE TABLE orders_2026m01 PARTITION OF public.orders\n    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');\nCREATE TABLE orders_2026m02 PARTITION OF public.orders\n    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');\nCREATE TABLE orders_2026m03 PARTITION OF public.orders\n    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');\nCREATE TABLE orders_2026m04 PARTITION OF public.orders\n    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');\nCREATE TABLE orders_2026m05 PARTITION OF public.orders\n    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');\nCREATE TABLE orders_2026m06 PARTITION OF public.orders\n    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');\nCREATE TABLE orders_2026m07 PARTITION OF public.orders\n    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');\nCREATE TABLE orders_2026m08 PARTITION OF public.orders\n    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');\nCREATE TABLE orders_2026m09 PARTITION OF public.orders\n    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');\nCREATE TABLE orders_2026m10 PARTITION OF public.orders\n    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');\nCREATE TABLE orders_2026m11 PARTITION OF public.orders\n    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');\nCREATE TABLE orders_2026m12 PARTITION OF public.orders\n    FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');<\/pre>\n\n\n\n
Then we create an index on the field we partitioned:<\/p>\n\n\n\n
CREATE INDEX idx_order_date ON public.orders (order_date);<\/pre>\n\n\n\n
The next step is to transfer the data from the old table to the new one:<\/p>\n\n\n\n
INSERT INTO public.orders SELECT * FROM orders_old;<\/pre>\n\n\n\n
Finally we can delete the old table:<\/p>\n\n\n\n
DROP TABLE IF EXISTS public.orders_old CASCADE;\n<\/pre>\n\n\n\n
How to view existing Partitions and Ranges<\/h2>\n\n\n\n
To confirm which partitions are active and which range each one covers, we run the following query using the function pg_get_expr<\/code>:<\/p>\n\n\n\n
SELECT \n    nmsp.nspname AS schema_name,\n    parent.relname AS parent_table,\n    child.relname AS partition_name,\n    pg_get_expr(child.relpartbound, child.oid) AS partition_range\nFROM pg_inherits\nJOIN pg_class parent ON pg_inherits.inhparent = parent.oid\nJOIN pg_class child ON pg_inherits.inhrelid = child.oid\nJOIN pg_namespace nmsp ON nmsp.oid = child.relnamespace\nWHERE parent.relname = 'orders';<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
How can we quickly and safely delete old Partitions?<\/h2>\n\n\n\n
When we want to apply retention policy and delete old data, e.g. January 2026, immediately freeing up space, we follow the logic of Metadata Switch<\/strong> of SQL Server, we detach the partition from the table and then delete it:<\/p>\n\n\n\n
ALTER TABLE public.orders DETACH PARTITION orders_2026m01;\nDROP TABLE orders_2026m01;<\/pre>\n\n\n\n
<\/p>\n\n\n\n
How can we automate the creation of next year's Partitions?<\/h2>\n\n\n\n
To avoid having to manually create partitions every year, we can create a function that automatically calculates the next year and creates the 12 months:<\/p>\n\n\n\n
CREATE OR REPLACE FUNCTION create_next_year_monthly_partitions_orders() \nRETURNS void AS $$\nDECLARE\n    target_year int;\n    m int;\n    partition_name text;\n    start_date text;\n    end_date text;\nBEGIN\n    target_year := EXTRACT(YEAR FROM CURRENT_DATE) + 1;\n\n    FOR m IN 1..12 LOOP\n        \n        partition_name := format('orders_%sm%s', target_year, lpad(m::text, 2, '0'));\n        \n        IF m = 12 THEN\n            start_date := format('%s-12-01', target_year);\n            end_date   := format('%s-01-01', target_year + 1);\n        ELSE\n            start_date := format('%s-%s-01', target_year, lpad(m::text, 2, '0'));\n            end_date   := format('%s-%s-01', target_year, lpad((m + 1)::text, 2, '0'));\n        END IF;\n\n        IF NOT EXISTS (\n            SELECT 1 FROM pg_class c \n            JOIN pg_namespace n ON n.oid = c.relnamespace \n            WHERE c.relname = partition_name\n        ) THEN\n            EXECUTE format(\n                'CREATE TABLE %I PARTITION OF public.orders FOR VALUES FROM (%L) TO (%L);',\n                partition_name, start_date, end_date\n            );\n        END IF;\n        \n    END LOOP;\nEND;\n$$ LANGUAGE plpgsql;<\/pre>\n\n\n\n
To automatically run the function every month at 06:00 in the morning, we configure the user's crontab postgres<\/code>:<\/p>\n\n\n\n
sudo -u postgres crontab -e<\/pre>\n\n\n\n
By adding the following line of programming:<\/p>\n\n\n\n
0 6 1 * * psql -d db_test -c \"SELECT create_next_year_monthly_partitions_orders();\"<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n