Log Analytics with OpenTelemetry

pg_mooncake provides a Postgres-native solution for high-volume log analytics and observability data, making it an excellent alternative to ClickHouse or dedicated log analytics platforms.

Why pg_mooncake for Logging

It offers several advantages for log analytics workloads:

• High-throughput ingestion - Handle ~1M events per second
• Zero-disk architecture - All data stored in cost-effective object storage
• Simple self-hosting - Just Postgres + object storage, leverage your existing infrastructure
• Postgres-native queries - Use familiar SQL and Postgres tooling
• Cost-effective storage - Object storage pricing for historical logs

Current Implementation

Today's recommended pattern for log analytics follows the standard write-to-rowstore, read-from-columnstore approach:

-- Create your log table
CREATE TABLE application_logs (
    timestamp TIMESTAMP NOT NULL,
    level TEXT NOT NULL,
    service TEXT NOT NULL,
    message TEXT,
    trace_id TEXT,
    span_id TEXT,
    attributes JSONB
);

-- Create columnstore mirror for analytics
CALL mooncake.create_table('application_logs_analytics', 'application_logs');

-- Write logs to the rowstore table
INSERT INTO application_logs VALUES 
    (NOW(), 'ERROR', 'api-service', 'Database connection failed', 'trace-123', 'span-456', '{"user_id": 1001}');

-- Query logs from the analytics table
SELECT service, level, COUNT(*) as error_count
FROM application_logs_analytics 
WHERE timestamp >= NOW() - INTERVAL '1 hour'
  AND level = 'ERROR'
GROUP BY service, level;

After ingesting logs, you can archive old partitions from the rowstore while retaining complete history in the columnstore for long-term analytics.

Roadmap: Direct Columnstore Ingestion

We're developing an enhanced experience that will provide:

1. Standalone columnstore tables - Skip Postgres storage entirely
2. Direct INSERT/UPSERT - Bypass Postgres WAL for maximum throughput
3. OpenTelemetry sink integration - Native OTEL collector support with any configuration

Reach out to us if this is interesting – we'd love to jam together on the experience here.