A common requirement I've seen in several client deployments is the need to offload reporting and analytical queries from the primary Granola transactional database. Directly querying the production instance for large historical aggregations creates unacceptable load and performance contention. The correct pattern is to establish a dedicated, read-only replica streamed into your data warehouse environment.
The architecture involves three key stages: enabling logical replication on the source Granola PostgreSQL database, configuring a secure subscription on the analytics side, and then transforming the raw change stream into a query-optimized schema. Below is a high-level workflow, with specific configuration snippets for the most critical and often misconfigured steps.
**1. Source (Granola Production) Configuration:**
You must ensure `wal_level` is set to `logical` and adequate replication slots are allocated. This is typically managed via your cloud provider's parameter group or a custom `postgresql.conf`.
```sql
-- On the Granola primary database
ALTER SYSTEM SET wal_level = logical;
-- Restart required
-- Create a publication for the necessary tables.
-- Be selective; publishing everything adds unnecessary overhead.
CREATE PUBLICATION granola_analytics_pub
FOR TABLE projects, experiments, measurements, users;
```
**2. Replica (Analytics Warehouse) Setup:**
I recommend using a separate PostgreSQL instance (or a compatible service like Amazon Aurora) as the initial landing zone. This decouples the replication stream from your final warehouse (e.g., BigQuery, Snowflake). Use a subscription with a network security group allowing only the replica to connect to the source on port 5432.
```sql
-- On the analytics replica instance
CREATE SUBSCRIPTION granola_analytics_sub
CONNECTION 'host= port=5432 user=replicator password=... dbname=granola_prod'
PUBLICATION granola_analytics_pub
WITH (copy_data = true, create_slot = true);
```
**3. Transformation & Loading:**
The replica now contains identical tables. Do not query these directly for analytics. Instead, use a scheduled process (Airflow, dbt, or a simple cron-triggered Kubernetes Job) to incrementally transform this data into a dimensional model in your actual warehouse. This is where you can join, slowly change dimension (SCD) type-2, and pre-aggregate without any performance impact on Granola.
**Key Pitfalls to Avoid:**
* **Replication Slot Bloat:** Monitor the replication slot's lag and disk usage. A stalled consumer will cause WAL retention indefinitely, filling the source disk. Implement alerting.
* **Schema Changes:** Granola application updates may include DDL. Logical replication can fail on breaking schema changes. Test updates in a staging environment first and consider a brief pause/restart of the subscription during major migrations.
* **Data Volume:** Initial `copy_data = true` can be heavy. Schedule it during a maintenance window. For terabyte-scale deployments, consider a base snapshot from object storage instead.
This pattern has provided a robust, low-impact data pipeline for teams needing to build comprehensive dashboards and historical trend analysis without touching the operational integrity of Granola.
- Mike
Mike
Spot on about the three stage approach, it's exactly how we handle this with our Salesforce analytics pipeline too. One thing I'd add from our setup, you really need to watch out for how Granola handles schema changes, especially with custom objects. If you add a new field after the publication's created, the replica won't pick it up automatically. We had to build a small monitoring job that alerts us when DDL events happen so we can refresh the publication. It's a bit of a gotcha.
Oh that's a good callout. The schema change gotcha bit us early on too, but with a different twist.
We used logical replication for our VWO session data and found that DDL changes *do* replicate, but any new columns just get added as NULL for all existing rows in the replica. That can silently break some of our downstream aggregation jobs if they aren't expecting those new null columns. So now, any schema change in the source triggers a full data refresh for that table on our end, not just a publication refresh.
It adds a bit more orchestration but keeps the dashboards from breaking. Anyone else handle it this way?
✌️