Skip to main content
Version: 0.2

Create Columnstore Table

You can create a columnstore copy of your rowstore tables in the same Postgres instance or across separate instances.

In the same instance with pg_mooncake​

CALL mooncake.create_table('columnstore_table', 'rowstore_table');

Note: In the current preview version, you can only create a columnstore of a rowstore table that does not have any data in it. This is actively being worked on.

You can create a columnstore copy of a rowstore table with:

  • Indexes
  • Constraints
  • Default values
  • Check constraints

Deploying pg_mooncake a read replica​

This model allows you to deploy pg_mooncake on a read replica while keeping your primary OLTP instance unchanged. You can create columnstore tables in a separate analytics instance that replicate data from your primary database.

CALL mooncake.create_table('columnstore_table', 'rowstore_table', 'uri');

URI Settings​

When creating a columnstore replica from a separate instance, the uri parameter specifies the connection details for the source PostgreSQL instance. The URI format follows the standard PostgreSQL connection string format:

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

For example:

postgresql://replicator:abc123@source-db.example.com:5432/mydb

pg_mooncake automatically creates a publication on the primary instance. To ensure proper replication, the user specified in the URI must have replication permissions:

-- Run on the source PostgreSQL instance
CREATE USER replicator WITH REPLICATION PASSWORD 'abc123';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;

Tuning Knobs​

For optimal replication performance, configure these parameters on the source instance:

  • wal_level = logical - Required for logical replication
  • max_wal_senders - Set higher than the number of concurrent replication connections
  • max_replication_slots - Set higher than the number of tables being replicated
  • wal_keep_size - Increase to retain sufficient WAL segments for replication

For detailed guidance on setting up logical replication between managed PostgreSQL services and self-managed PostgreSQL instances, refer to the following resources:

Limitations​

Some limitations remain during the preview period:

  1. Unsupported Data Types - If your rowstore table contains any of the following types, you cannot create a columnstore copy:

    • Enum types
    • Composite types
    • Multi-dimensional arrays

  2. Empty Table Requirement - In the preview version, you must create the columnstore copy of a rowstore table before adding data to the rowstore table.

  3. Full Copy Required in Rowstore - Currently, the rowstore table must contain the complete dataset. We're working on a solution where only 'hot data' remains in rowstore while historical moves to columnstore, benefiting from columnar compression.