Skip to main content
Version: 0.2

Create Columnstore Table

Columnstore tables provide significant compression and query performance benefits for analytical queries while maintaining full PostgreSQL compatibility.

Today, all columnstore tables must be created as a replica of an existing PostgreSQL table:

  • Writes → PostgreSQL rowstore table
  • Analytics → pg_mooncake columnstore table
   transactions             analytics   
      │   │                   ▲   ▲       
      ▼   ▼                   │   │       
┌────────────────┐      ┌─────┴───┴──────┐
│                │      │                │
│                ├──────┤                │
│ Postgres       ├──────┤ Postgres with  │
│ Primary        ├──────┤ 🥮             │
│                ├──────┤                │
│                │      │                │
└────────────────┘      └────────────────┘
                                          
 RDS,Supabase,                            
 GCP, Azure, Neon

Soon (Q4 2025), we will support creating a stand-alone columnstore table, where you can insert/upsert data directly. This is helpful for event stream / logging workloads.

Quick Start​

Create a columnstore table from an existing rowstore table:

CALL mooncake.create_table('trades_analytics', 'trades');

Write and Read Patterns​

Write Operations - All writes go to the original rowstore table:

INSERT INTO trades (symbol, quantity, price) VALUES ('AAPL', 100, 150.00);
UPDATE trades SET status = 'filled' WHERE trade_id = 1001;
DELETE FROM trades WHERE trade_id = 1002;

Read Operations - Choose the right table for your query:

-- Transactional reads from rowstore (OLTP queries)
SELECT * FROM trades WHERE trade_id = 1001;

-- Analytical reads from columnstore (OLAP queries)  
SELECT 
    symbol,
    SUM(quantity * price) as total_volume
FROM trades_analytics  -- Use the columnstore replica
WHERE trade_date >= '2024-01-01'
GROUP BY symbol;

The columnstore table will always read the latest transactions from the rowstore table.

Schema Change Propagation​

Schema changes made to the rowstore table are automatically propagated to the columnstore table. This includes:

  • ADD COLUMN - New columns are automatically added to the columnstore table
  • DROP COLUMN - Columns are removed from the columnstore table
-- Example: Adding a new column to the rowstore
ALTER TABLE trades ADD COLUMN trade_status VARCHAR(20);

-- The columnstore table automatically reflects this change
SELECT symbol, quantity, price, trade_status 
FROM trades_analytics  -- New column is available immediately
WHERE trade_date >= '2024-01-01';

Creating Columnstore Tables​

Within your Primary Deployment​

When pg_mooncake is installed on the same PostgreSQL instance:

CALL mooncake.create_table('trades_analytics', 'trades');

The original trades table remains your primary table for writes. The trades_analytics columnstore table is automatically synchronized for analytical queries.

As a separate analytics instance​

Deploy pg_mooncake on a dedicated analytics instance:

CALL mooncake.create_table(
    'trades_analytics', 
    'trades',
    source_uri => 'postgresql://analytics_user:password@prod-db.example.com:5432/myapp'
);

Your production trades table remains unchanged. This provides workload isolation and zero production impact.

URI Configuration​

The source URI follows standard PostgreSQL connection string format:

postgresql://[username]:[password]@[hostname]:[port]/[database]

Examples:

-- AWS RDS connection
'postgresql://replicator:secure123@myapp.abc123.us-east-1.rds.amazonaws.com:5432/production'

-- Local development
'postgresql://dev_user:dev_pass@localhost:5432/myapp_dev'

-- Neon connection  
'postgresql://user:pass@ep-cool-darkness-123456.us-east-1.aws.neon.tech/neondb'

Setting Up Replication Users​

pg_mooncake automatically creates the required replication user and grants necessary permissions. No manual user setup is required.

PostgreSQL Configuration for Replication​

Configure these parameters on the source instance:

# Enable logical replication
wal_level = logical

Restart required after changing wal_level.

Cloud Provider Setup​

See cloud provider specific setup guides: