Data Archiving

pg_mooncake slashes storage costs by compressing your massive PostgreSQL tables into efficient columnstore format. Keep your data in PostgreSQL while reducing storage footprint by 3-10x.

How It Works

-- Create columnstore archive table
CREATE TABLE orders_archive (
    order_id INT,
    customer_id INT,
    order_date TIMESTAMP
    -- other columns
) USING columnstore;

-- Move old data to archive
INSERT INTO orders_archive
SELECT * FROM orders
WHERE order_date < CURRENT_DATE - INTERVAL '1 year';

-- Verify and remove from source
DELETE FROM orders
WHERE order_date < CURRENT_DATE - INTERVAL '1 year';

-- Reclaim space
VACUUM FULL orders;

Query Archived Data

Query your archived data just like any PostgreSQL table:

-- Query across current and archived data
SELECT 
    date_trunc('month', order_date) AS month,
    COUNT(*) AS orders,
    SUM(amount) AS revenue
FROM (
    SELECT * FROM orders          -- Current data
    UNION ALL
    SELECT * FROM orders_archive   -- Archived data
) all_orders
WHERE order_date >= CURRENT_DATE - INTERVAL '3 years'
GROUP BY date_trunc('month', order_date)
ORDER BY month;

Roadmap: indexes + operational queries on columnstore

Columnstore tables today are designed for analytic queries; and are not optimized for operational queries. We are working on adding indexes and operational queries on columnstore tables.