Skip to main content
Version: 0.2

Archiving Data into Columnstore

Mooncake’s columnstore writes data as Parquet to object storage, giving you cheap, durable history while Postgres only keeps the hot partitions. This is ideal for time‑partitioned tables.

Pattern​

  1. Create a columnstore mirror of a (time‑)partitioned Postgres table.
  2. Periodically truncate/drop old partitions from the rowstore.
  3. The columnstore keeps the complete history; the rowstore only holds the recent subset. --

Example​

Below, we use an INT range as a stand‑in for time.

1) Create a partitioned rowstore table​

CREATE TABLE r (
    a INT PRIMARY KEY,
    b TEXT
) PARTITION BY RANGE (a);

2) Create a columnstore mirror​

CALL mooncake.create_table('c', 'r');

3) Add the first partition and insert data​

CREATE TABLE r1 PARTITION OF r FOR VALUES FROM (0) TO (50);
INSERT INTO r1 VALUES (1, 'a'), (2, 'b'), (3, 'c');

4) Drop the old partition​

DROP TABLE r1;

5) Query the columnstore (history is retained)​

SELECT * FROM c;

6) Add a new partition and insert more data​

CREATE TABLE r2 PARTITION OF r FOR VALUES FROM (50) TO (100);   
INSERT INTO r2 VALUES (51, 'd'), (52, 'e'), (53, 'f');
SELECT * FROM c;  -- returns all six rows

Notes​

  • After you drop/truncate a rowstore partition, do not write to it again—you’ll fork history and create conflicts.
  • The columnstore ignores truncates on the rowstore; partitioned tables are the safe, recommended path.

Reach out to us founders@mooncakelabs.com. We can develop syntax here to make this experience better.