Real-Time Analytics

If you're running slow analytics queries on Postgres tables, pg_mooncake gives you a simple route to speed up these queries with:

1. Keeping your Application / Write path unchanged.
2. Simple routing of analytics to the columnstore mirror.
3. Often keeping the read queries unchanged.

This has some benefits like:

• Schema changes supported - ADD COLUMN and DROP COLUMN automatically sync to analytics tables
• Great support for updates/deletes - Better performance for UPDATE/DELETE operations than any other analytical system
• ZeroETL - No Debezium + Kafka stack. Leverage Postgres logical replication.
• Transactional consistency - Analytics queries see a consistent view of your data

The general pattern for this workload is to write to rowstore, read from columnstore.

-- Your existing OLTP table (for transactions)
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    order_date TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Create analytics replica (for analytics)
CALL mooncake.create_table('orders_analytics', 'orders');

-- Write operations: use the original table
INSERT INTO orders (customer_id, total_amount) VALUES (123, 99.99);
UPDATE orders SET total_amount = 149.99 WHERE order_id = 1;

-- Read operations: use the analytics table  
SELECT customer_id, SUM(total_amount) 
FROM orders_analytics 
WHERE order_date >= CURRENT_DATE
GROUP BY customer_id;

Manual Query Routing

Important: You need to manually route your queries to the appropriate table.

pg_mooncake doesn't automatically route queries - you choose which table to query based on your workload:

-- Transactional queries → Original table
INSERT INTO orders (customer_id, total_amount) VALUES (123, 99.99);
UPDATE orders SET order_status = 'shipped' WHERE order_id = 1;
DELETE FROM orders WHERE order_id = 1;

-- Analytical queries → Analytics table  
SELECT customer_id, SUM(total_amount) FROM orders_analytics GROUP BY customer_id;
SELECT COUNT(*) FROM orders_analytics WHERE order_date >= CURRENT_DATE;
SELECT * FROM orders_analytics ORDER BY total_amount DESC LIMIT 10;

This gives you full control over performance - transactional operations stay fast on the rowstore, while analytical queries leverage the columnstore's performance advantages.