We have Postgres plus a separate analytics database for event data—how do teams consolidate without losing real-time dashboards and fast historical queries?

Most teams end up with two databases because they’re trying to solve two very different problems: reliable OLTP in Postgres and fast, large-scale analytics over event data. The cost is complexity—dual schemas, brittle pipelines, and dashboards that drift out of sync with production. You can consolidate back onto Postgres without losing real-time dashboards or fast historical queries, but you have to bring the right primitives into Postgres instead of spreading them across Kafka, Flink, and a separate analytics engine.

Quick Answer: Use Postgres as the single system of record and add time-series and analytics primitives (hypertables, row-columnar storage, compression, and tiered storage) so the same database can handle high-ingest event streams and OLAP-style queries. TigerData gives you that: a Postgres-native platform that keeps dashboards real-time while making historical queries fast and affordable at telemetry scale.

---

The Quick Overview

• What It Is: A Postgres-first way to consolidate your OLTP database and event analytics store into one system, using TigerData (PostgreSQL + TimescaleDB + Hypercore + tiered storage) to support both real-time dashboards and fast queries on months or years of events.
• Who It Is For: Engineering and data teams running Postgres for core app data plus a separate warehouse or analytics database (ClickHouse, Druid, Redshift, BigQuery, Snowflake, etc.) for events, and now looking to simplify their stack without sacrificing speed.
• Core Problem Solved: Dual-write and ETL-heavy architectures are fragile and high-maintenance. They slow down product work, create consistency gaps between production and analytics, and get very expensive as event volume grows.

---

How It Works

The core idea is simple: keep Postgres as your single database, then extend it so it behaves like an analytics engine for time-based event data. TigerData does this by adding explicit primitives on top of standard PostgreSQL:

• Hypertables for automatic time- and key-based partitioning
• Hypercore row-columnar storage for fast ingest and analytics in the same table
• Compression and tiered storage to keep huge event histories cheap while still queryable
• Continuous aggregates and time-series functions to power real-time dashboards
• Lakehouse integration to connect Kafka/S3/Iceberg without fragile custom glue

From your application and BI tools’ perspective, it’s all just Postgres: the same SQL, the same drivers, the same semantics. Under the hood, TigerData handles partitioning, columnar scans, compression, and storage tiering so you don’t have to split workloads across multiple systems.

Here’s how consolidation typically unfolds:

1. Phase 1 – Migrate event analytics into Postgres (hypertables):

   • Model your events in Postgres (if they aren’t already), and convert the event table into a hypertable keyed by time (and usually a tenant or device key).
   • Point streaming ingest (Kafka, application services) directly into this hypertable instead of the separate analytics store.
   • Keep your OLTP tables as regular Postgres tables next to this hypertable.

2. Phase 2 – Add analytics primitives for speed and cost (Hypercore + compression + tiering):

   • Enable Hypercore row-columnar storage and compression policies on the hypertable.
   • Use continuous aggregates (CREATE MATERIALIZED VIEW with Timescale’s policies) for pre-aggregated dashboards and rollups.
   • Turn on tiered storage so older event chunks live in low-cost object storage while staying queryable through the same hypertable.

3. Phase 3 – Cut over dashboards and pipelines (and decommission the separate store):

   • Re-point BI tools and dashboards to the consolidated Postgres service.
   • Rebuild key analytics queries using time-series functions and continuous aggregates—these power both real-time and historical views.
   • Gradually shut down the separate analytics system as you validate parity and performance.

Throughout, you keep a Postgres-native interface—SQL, standard drivers, and a single schema—while gaining the performance behaviors you’d expect from a columnar analytics engine.

---

Features & Benefits Breakdown

Core Feature	What It Does	Primary Benefit
Hypertables for event data	Automatically partition event tables by time (and optional key) while exposing them as a single logical Postgres table.	Sustains high ingest rates and keeps queries fast as tables grow to billions or trillions of rows.
Hypercore row-columnar storage	Stores recent data in row format for fast writes, then converts older chunks to columnar with compression for analytics.	Delivers fast OLTP writes and OLAP-style scans from the same table, with up to 98% compression on historical data.
Compression & tiered storage	Compresses older chunks and moves them to low-cost object storage, while keeping them transparently queryable.	Keeps years of event history online for dashboards and investigations without blowing up storage and infrastructure costs.

---

Ideal Use Cases

• Best for consolidating app + event analytics: Because it keeps everything in Postgres, you avoid dual schemas, dual write paths, and ETL jobs just to keep analytics up to date. Dashboards can join events with user, account, or device tables in a single query.

• Best for real-time telemetry at scale: Because hypertables and Hypercore are built for sustained ingest and time-based queries, you can power second-level or minute-level dashboards over live events while still querying trillions of historical data points.

---

Why teams split in the first place (and why it hurts)

If you’re running both Postgres and a separate analytics database for events, you probably recognize at least one of these patterns:

• Postgres slowed down under event load.
  Large append-only event tables caused bloated indexes, long VACUUM cycles, and queries that timed out when dashboards tried to scan multiple months.

• You wanted columnar-style analytics.
  Warehouses and OLAP engines advertise “fast aggregation over billions of rows,” which is exactly what event dashboards need.

• You needed cheap, long-term storage.
  Keeping raw events in “expensive” primary storage looked unsustainable, so you pushed them into a cheaper analytics system or object storage.

The trade-off: you bought speed for analytics at the cost of operational complexity.

Common symptoms:

• Laggy dashboards. ETL pipelines run every few minutes or hours, so your “real-time” charts are always behind production.
• Inconsistent metrics. Business logic diverges between application code, ETL scripts, and warehouse queries. Two teams run “the same” query and get different answers.
• Fragile and high-maintenance glue. Kafka, Flink, custom jobs, multiple schemas, and bespoke data contracts—every change ripples through the whole stack.
• Hidden costs. You pay for warehouse storage, compute, per-query fees, ETL infrastructure, and developer time to keep everything from breaking.

Consolidation is about reversing that trade: use Postgres as the single source of truth and extend it so it can support analytics natively.

---

How consolidation works in practice (with TigerData)

Let’s walk through the mechanics as a Postgres operator.

1. Model events as a hypertable in Postgres

You start where you are: with Postgres as the core database.

Say you have an events table capturing user actions:

CREATE TABLE events (
  id           BIGSERIAL PRIMARY KEY,
  occurred_at  TIMESTAMPTZ NOT NULL,
  user_id      BIGINT       NOT NULL,
  event_type   TEXT         NOT NULL,
  properties   JSONB        NOT NULL,
  shard_key    BIGINT       NOT NULL  -- e.g., tenant, customer, or device
);

Plain Postgres will happily ingest this at moderate scale, but once you’re into tens or hundreds of billions of rows, you hit the limits you’re feeling today.

With TimescaleDB (the time-series extension at the core of TigerData), you convert this into a hypertable:

SELECT create_hypertable(
  'events',
  by_range('occurred_at'),
  partitioning_key => 'shard_key',
  number_partitions => 32
);

What this does under the hood:

• Automatically partitions data into “chunks” by time and shard key.
• Keeps a single logical events table for your SQL and your application.
• Ensures inserts and queries hit relatively small physical tables, which keeps indexes lean and scans efficient.

This is the foundation for high-ingest event workloads in Postgres.

2. Add the right indexes (without overdoing it)

You still use standard Postgres indexes, but tuned for time-series:

CREATE INDEX ON events (shard_key, occurred_at DESC);
CREATE INDEX ON events (event_type, occurred_at DESC);

These help power:

• “Recent events for a tenant/user/device”
• “Count events of type X over time”
• “Funnels and cohorts filtered by event type”

Because hypertables keep chunks sized, these indexes don’t just grow without bound and become unmanageable.

3. Enable row-columnar storage (Hypercore) and compression

The main reason teams adopt a separate analytics database is to get columnar storage for efficient scanning and aggregation. Hypercore gives you that inside Postgres.

You configure a policy that:

• Keeps recent data in row-store (for fast inserts and point lookups).
• Converts older chunks to columnar with heavy compression.

Conceptually:

SELECT add_compression_policy(
  'events',
  compress_after => INTERVAL '7 days'
);

Now:

• Events from the last 7 days are row-based and optimized for ingest and real-time queries.
• Everything older is columnar and compressed, optimized for analytics queries that scan and aggregate over time ranges.

TigerData’s docs cite compression by up to 98%, which translates directly to cost savings for long-term event storage and faster scans (less data to read).

4. Turn on tiered storage for cheap, queryable history

With tiered storage in Tiger Cloud:

• Older compressed chunks get moved off primary storage into low-cost object storage.
• The hypertable still presents as a single table; queries don’t change.
• The system automatically retrieves and transparently queries cold data when needed.

This matters for your original requirement:

• Real-time dashboards hit recent row-store chunks on primary storage.
• Fast historical queries over weeks/months/years use compressed, columnar, and potentially tiered chunks that are still accessible but much cheaper.

Your query remains:

SELECT
  date_trunc('day', occurred_at) AS day,
  event_type,
  count(*) AS events
FROM events
WHERE occurred_at >= now() - INTERVAL '90 days'
GROUP BY 1, 2;

No separate warehouse, no custom tier-aware logic in your reports.

5. Use continuous aggregates for dashboard-grade performance

For dashboards with many users, you rarely want to run “raw” aggregates over billions of rows on every page load. That’s why teams adopt warehouses or OLAP engines.

In TigerData, you use continuous aggregates—materialized views that automatically maintain rollups over your hypertable.

Example: a daily event count per event_type:

CREATE MATERIALIZED VIEW events_daily
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 day', occurred_at) AS bucket,
  event_type,
  count(*) AS events
FROM events
GROUP BY 1, 2;

SELECT add_continuous_aggregate_policy(
  'events_daily',
  start_offset => INTERVAL '90 days',
  end_offset   => INTERVAL '1 hour',
  schedule_interval => INTERVAL '5 minutes'
);

What this gives you:

• Precomputed aggregates for the last 90 days, refreshed every 5 minutes.
• Dashboards can query events_daily for instant response times even at huge scale.
• You can still query raw events if you need deeper drills or different groupings.

Important:

Note: Continuous aggregates are eventually consistent over their refresh window. For second-level real-time dashboards, query the base hypertable for the last few minutes and rely on the aggregate for the longer time horizon.

A common pattern:

-- Last 15 minutes: hit raw events (row-store)
SELECT ... FROM events WHERE occurred_at >= now() - INTERVAL '15 minutes';

-- Beyond 15 minutes: hit the continuous aggregate
SELECT ... FROM events_daily WHERE bucket < now() - INTERVAL '15 minutes';

6. Replace fragile pipelines with native ingest & lakehouse integration

You likely have a pipeline that looks like:

Postgres → Kafka → Stream processor (e.g., Flink) → Analytics DB → BI

With TigerData, you can simplify to:

Producers (apps/devices) → Kafka/S3 → TigerData hypertable → BI

Key primitives:

• Kafka ingest: Stream events directly into Postgres hypertables, without custom consumer logic that reimplements partitioning, backpressure handling, and retries.
• S3 ingest: Bulk-load historical or batch data with native integration.
• Lakehouse replication (Iceberg): Stream out to an Iceberg table in parallel for offline analytics, ML workloads, or long-term archiving.

You still get a lakehouse when you want one but without the “stitched together Kafka, Flink, and custom code” failure mode. Tiger Lake (TigerData’s lakehouse component) replaces that with native infrastructure.

---

Limitations & Considerations

• You still have to design schemas and indexes for time-based workloads:
  Hypertables and Hypercore don’t magically fix poor schema design. You need clear partitioning keys, well-chosen indexes, and an understanding of your query patterns. The benefit is that you do this once—inside Postgres—rather than both in Postgres and an external analytics engine.

• Real-time vs. materialized behavior needs explicit handling:
  Continuous aggregates introduce watermarks and refresh intervals by design. You’ll want to:

  • Use raw hypertable data for “last N minutes” where strict real-time accuracy matters.
  • Use continuous aggregates for longer windows to keep dashboards fast and predictable.
    This is the same trade-off you navigate with warehouses; the difference is that it’s all in one system with explicit SQL and policies.

---

Pricing & Plans

TigerData’s posture is straightforward: transparent billing with no per-query fees and no surprise charges for ingestion or egress. You pay for the resources you provision, with automated backups included.

At a high level:

• Compute and storage are billed based on your chosen plan and usage, monthly in arrears.
• There are no separate charges for features like continuous aggregates or compression.
• On Tiger Cloud, you get automated backups and point-in-time recovery by default.

Example positioning (details vary by region and configuration):

• Performance: Best for product teams and early-stage platforms that need to consolidate app + analytics on Postgres, sustain high event ingest, and power production dashboards without running infrastructure themselves.
• Scale / Enterprise: Best for organizations with large telemetry workloads, stricter compliance (SOC 2 Type II, GDPR, HIPAA on Enterprise), HA requirements, multi-AZ deployments, and support SLAs with 24/7 incident handling.

For specific numbers, you’d work with TigerData to size your service (ingest rate, data retention, query patterns) and pick a plan that fits.

---

Frequently Asked Questions

Can we really shut down our separate analytics database without losing performance?

Short Answer: Yes—if you move event workloads onto hypertables with Hypercore, compression, and continuous aggregates, and you design queries to use those primitives.

Details:
Teams successfully run both OLTP and OLAP-style event queries on a single TigerData service because:

• Hypertables keep inserts and time-based queries efficient even at massive scale.
• Hypercore ensures recent data is row-optimized while historical data is columnar and compressed, enabling warehouse-like scan performance.
• Compression reduces I/O and storage footprint dramatically, which speeds analytical queries.
• Continuous aggregates precompute the heavy metrics your dashboards need.

In practice, you:

1. Move event ingest into TigerData hypertables.
2. Configure compression and tiered storage for long-term retention.
3. Recreate key warehouse queries as continuous aggregates.
4. Repoint BI tools to TigerData.

You’ll often see dashboards get faster because you eliminate ETL lag and query directly against data that’s designed for these access patterns.

---

Will this overload our main Postgres instance and hurt OLTP?

Short Answer: Not if you architect with separation-of-concerns in mind—use dedicated services, read replicas, and resource isolation inside Tiger Cloud.

Details:
Consolidation doesn’t mean a single VM doing everything. It means:

• Single logical database engine (Postgres + TimescaleDB) rather than multiple different systems.
• Right-sized deployment topology for your workloads.

Common patterns:

• Separate TigerData services: one for mission-critical OLTP, another for mixed OLTP + analytics over events. Both are still Postgres, which simplifies tooling and expertise.
• Read replicas for heavy analytics: dashboards, ad-hoc queries, and data science workloads can hit replicas while primary handles writes.
• Compute and storage scaling: On Tiger Cloud, you scale the service vertically or horizontally based on ingest and query demand without touching your application code.

You maintain Postgres semantics (transactions, SQL, drivers) while giving analytics workloads their own capacity envelope so they don’t interfere with latency-sensitive operations.

---

Summary

Running both Postgres and a separate analytics database for event data solves one problem (fast dashboards) by creating another (complexity, inconsistency, and cost). The way out isn’t to add yet another system; it’s to evolve Postgres so it can natively handle time-series and analytics workloads.

TigerData does that with:

• Hypertables for automatic time- and key-based partitioning
• Hypercore row-columnar storage for fast ingest and analytics
• Compression and tiered storage for cheap, queryable history
• Continuous aggregates and time-series functions for real-time dashboards
• Lakehouse integration for Kafka/S3/Iceberg without fragile glue

You keep the simplicity of “just Postgres”—SQL, drivers, and operational model—but gain the scale and performance numbers you’d expect from dedicated analytics systems: up to 98% compression, trillions of metrics per day, and petabytes of data on a single TigerData service.

---

Next Step

Get Started