Partitioning existing table in PostgreSQL
Let's create a table with 20 millions of random data. Be sure to index data column for comparing performance results. CREATE TABLE not_partitioned_table ( id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL, transaction_date DATE NOT NULL, amount DECIMAL(10, 2), status VARCHAR(20), description TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Create an index on the transaction_date column CREATE INDEX idx_transaction_date ON not_partitioned_table(transaction_date); DO $$ DECLARE i INT; BEGIN FOR i IN 1..20000000 LOOP INSERT INTO partitioned_table (user_id, transaction_date, amount, status, description) VALUES ( (RANDOM() * 100000)::INT, CURRENT_DATE - ((RANDOM() * 3650)::INT), -- Random date within the last 10 years (RANDOM() * 1000)::DECIMAL(10,2), CASE WHEN RANDOM() < 0.5 THEN 'completed' ELSE 'pending