Skip to main content
Status: Active Created: November 2025 Last Updated: November 2025

​
Summary

To position Polar for ingesting an increased number of events (100-1000x current number of events), and to minimize the number of data migrations that we will need to do the ambition of this document is to outline three alternatives for storing event data based on the current and imagined future query patterns.

​
Goals

  • List and filter events speedily (<200 ms) independent on number of events associated with organization.
    • Filter should be on arbitrary hierarchies of events.
    • Aggregate arbitrary fields in event hierarchies, such as duration or cost.
  • Generate metrics from event lists
    • Window events over time
    • Group events by arbitrary properties of events (name, description)
The workload we have fullfils the following aspects:
  • Events are immutable
  • Events can be ingested at any point in time (not only realtime)
  • We want to be able add more metrics and query patterns in the future
  • We want our users to be able to ingest a lot of metadata that they can query in the future

​
Current state

​
Events

Events are stored in an events table. The relationship / hierarchy between events are stored via the self referencing parent_id field, which refers to the ID of a different event in the same table. To allow for speedier lookups of events in an hierarchy there is a separate events_closure table which stores the relationship between two events (ancestor and descendant as well as the depth of which that relationship is.). The event_types table exists to allow us to group events that are of the same type and to assign a customizable label to them. If you ingest two events that are called “Support Request”, they are semantically identical even though they are separate events that have happened at different points in time (potentially to different customers).

​
Event Closure

What the diagram above aims to show is that the further down the hierarchy you go, the more entries into the closure table you will get, since each corresponding parent (or ancestral) relationship will be represented. This means that when you select all of an event which where ancestor_id = 1 you will get itself and all of its children, no mater which generation, as well as the depth from the parent they have.

​
Query patterns

The events are queried by joining the three tables: 
SELECT
	parent.id AS parent_id,
	parent.name AS parent_name,
	child.id AS child_id,
	child.name AS child_name,
    et.label
	ec.depth
FROM
	events parent
	JOIN events_closure ec ON ec.ancestor_id = parent.id
	JOIN events child ON child.id = ec.descendant_id
    JOIN event_types et ON et.id=child.type_id
WHERE
	parent.id = '5b300e94-498c-4a94-a85d-2d1b9b218354'
ORDER BY
	ec.depth,
	child.timestamp;
And if you wish to only get the direct children you would query for depth = 1, etc.

​
Metrics

Metrics are queried by building up a series of intervals based on the queried interval. Additionally we build a number of common table expressions (CTEs) all of which are then queried together. 
WITH timestamp_series AS (
    SELECT generate_series(
        date_trunc('day', '2025-01-01'::timestamptz),
        date_trunc('day', '2025-12-31'::timestamptz),
        '1 day'::interval
    ) AS timestamp
),
Finally, all of these CTEs are then queried in a JOIN query: 
  SELECT
      timestamp_series.timestamp,

      orders_metrics_cte.orders,
      orders_metrics_cte.revenue,
      orders_metrics_cte.cumulative_revenue,
      orders_metrics_cte.net_cumulative_revenue,
      orders_metrics_cte.average_order_value,
      orders_metrics_cte.one_time_products,

      active_subscriptions_cte.active_subscriptions,
      active_subscriptions_cte.new_subscriptions,
      active_subscriptions_cte.monthly_recurring_revenue,
      active_subscriptions_cte.average_revenue_per_user,

      checkouts_cte.checkouts,
      checkouts_cte.succeeded_checkouts,
      checkouts_cte.checkouts_conversion,

      canceled_subscriptions_cte.canceled_subscriptions,
      canceled_subscriptions_cte.canceled_subscriptions_too_expensive,
      canceled_subscriptions_cte.canceled_subscriptions_missing_features,
      canceled_subscriptions_cte.canceled_subscriptions_low_quality,
      canceled_subscriptions_cte.canceled_subscriptions_unused,

      churned_subscriptions_cte.churned_subscriptions,

      events_metrics_cte.costs,
      events_metrics_cte.cumulative_costs,
      events_metrics_cte.active_user_by_event,
      events_metrics_cte.cost_per_user
  FROM timestamp_series
  JOIN orders_metrics_cte ON orders_metrics_cte.timestamp = timestamp_series.timestamp
  JOIN active_subscriptions_cte ON active_subscriptions_cte.timestamp = timestamp_series.timestamp
  JOIN checkouts_cte ON checkouts_cte.timestamp = timestamp_series.timestamp
  JOIN canceled_subscriptions_cte ON canceled_subscriptions_cte.timestamp = timestamp_series.timestamp
  JOIN churned_subscriptions_cte ON churned_subscriptions_cte.timestamp = timestamp_series.timestamp
  JOIN events_metrics_cte ON events_metrics_cte.timestamp = timestamp_series.timestamp
  ORDER BY timestamp_series.timestamp ASC;
The metrics today are a combination of querying the different tables that contain state, as well as summing up data from historical events.

​
Alternatives

​
1. Keep Postgres and current event, metric structures

The current setup will hold up for some time forward. Since we are using a combination of querying the state tables as well as the events, and that we are always filtering by organization_id in the events filtering, we will most likely not have an entirely unmanageable growth. Some response times will start to creep up but we might be able to be smarter with the way we query data.

​
2. Expand events to include all data necessary to generate metrics

Today we are lacking some specific events, and some data in the events to be able to build out the full (or almost full) metrics from purely the events. We would need to add some information to the system events, and probably backfill the existing events so that we can move forward without having to consider before-and-after this migration.

​
Events

Enriching order.paid
  • currency
  • total_amount information
    • net_amount, tax_amount, applied_balance_amount
  • discount information
    • discount_amount, discount_id
  • platform_fee
  • subscription information
    • subscription_id, subscription_type (one time, monthly, yearly)
Adding subscription.created
  • amount
  • recurring_interval, recurring_interval_count
  • started_at, product_id
Adding subscription.canceled
  • To abe able to differnate churn and cancelation metrics.
  • cancellation_reason, cancellation_comment
  • canceled_at, ends_at
Enriching subscription.revoked
  • amount to keep track of the amount reduction
  • recurring_interval, recurring_interval_count
Adding checkout.created Adding checkout.updated

​
Metrics

Given the new events we have added, we should be able to migrate to a fully event-based metrics generation if we wish. Certain metrics might be more involved than others. A metrics query that only looks as events could look something like the following:

​
2A Enabling TimescaleDB

​
Metrics

Building on the work for alternative 2, we can enable TimescaleDB as an extension in postgres. Then we can set up materialized views with time buckets, such as:
System metrics

CREATE MATERIALIZED VIEW system_metrics_hourly
WITH (TimescaleDB.continuous) AS
SELECT
    time_bucket('1 hour', timestamp) AS bucket,
    organization_id,

    -- Order metrics
    COUNT(*) FILTER (WHERE name = 'order.paid') AS orders,
    SUM((user_metadata->>'subtotal_amount')::int
      - (user_metadata->>'discount_amount')::int
    ) FILTER (WHERE name = 'order.paid') AS revenue,

    SUM((user_metadata->>'subtotal_amount')::int
      - (user_metadata->>'discount_amount')::int
      - (user_metadata->>'platform_fee_amount')::int
    ) FILTER (WHERE name = 'order.paid') AS net_revenue,

    -- Subscription deltas
    SUM(CASE
        WHEN name = 'subscription.created' THEN 1
        WHEN name = 'subscription.revoked' THEN -1
        ELSE 0
    END) AS subscription_delta,

    -- MRR delta (with proper ELSE clause!)
    SUM(CASE
        WHEN name IN ('subscription.created', 'subscription.revoked') THEN
            (CASE WHEN name = 'subscription.created' THEN 1 ELSE -1 END)
            * CASE (user_metadata->>'recurring_interval')
                WHEN 'year' THEN ROUND((user_metadata->>'amount')::int / 12.0)
                WHEN 'month' THEN (user_metadata->>'amount')::int
                WHEN 'week' THEN ROUND((user_metadata->>'amount')::int * 4.0)
                WHEN 'day' THEN ROUND((user_metadata->>'amount')::int * 30.0)
                ELSE 0  -- Fixed: handle unknown intervals
            END
        ELSE 0
    END) AS mrr_delta,

    -- Pre-aggregate cancellation reasons
    COUNT(*) FILTER (WHERE name = 'subscription.canceled') AS canceled_subscriptions,
    COUNT(*) FILTER (
        WHERE name = 'subscription.canceled'
        AND user_metadata->>'customer_cancellation_reason' = 'too_expensive'
    ) AS canceled_too_expensive,
    -- ... other cancellation reasons ...

    COUNT(*) FILTER (WHERE name = 'checkout.created') AS checkouts,
    COUNT(*) FILTER (
        WHERE name = 'checkout.updated' AND user_metadata->>'status' = 'succeeded'
    ) AS succeeded_checkouts

FROM events
WHERE source = 'system'
  AND name IN ('order.paid', 'order.refunded', 'subscription.created',
               'subscription.canceled', 'subscription.revoked',
               'checkout.created', 'checkout.updated')
GROUP BY time_bucket('1 hour', timestamp), organization_id
User metrics

CREATE MATERIALIZED VIEW user_metrics_hourly
WITH (TimescaleDB.continuous) AS
SELECT
    time_bucket('1 hour', timestamp) AS bucket,
    organization_id,
    SUM((user_metadata->'_cost'->>'amount')::numeric(17,12)) AS costs,
    COUNT(DISTINCT COALESCE(customer_id::text, external_customer_id))
        FILTER (WHERE customer_id IS NOT NULL OR external_customer_id IS NOT NULL) AS active_users
FROM events
WHERE source = 'user'
GROUP BY time_bucket('1 hour', timestamp), organization_id
These would then be possible to query easily: 

SELECT
    time_bucket_gapfill('1 hour', bucket) AS timestamp,

    -- Simple column reads instead of complex aggregations
    COALESCE(s.orders, 0) AS orders,
    COALESCE(s.revenue, 0) AS revenue,
    COALESCE(s.net_revenue, 0) AS net_revenue,

    -- Cumulative values with gapfill
    SUM(COALESCE(s.revenue, 0)) OVER w AS cumulative_revenue,
    SUM(COALESCE(s.subscription_delta, 0)) OVER w AS active_subscriptions,
    SUM(COALESCE(s.mrr_delta, 0)) OVER w AS monthly_recurring_revenue,

    -- Costs
    COALESCE(u.costs, 0) AS costs,
    SUM(COALESCE(u.costs, 0)) OVER w AS cumulative_costs,

    -- Derived metrics
    SUM(COALESCE(s.revenue, 0)) OVER w - SUM(COALESCE(u.costs, 0)) OVER w AS gross_margin

FROM system_metrics_hourly s
FULL OUTER JOIN user_metrics_hourly u
    ON s.bucket = u.bucket AND s.organization_id = u.organization_id
WHERE s.organization_id = '00000000-0000-0000-0000-000000000000'
  AND s.bucket >= '2025-01-01' AND s.bucket < '2026-01-01'
WINDOW w AS (ORDER BY COALESCE(s.bucket, u.bucket))
ORDER BY 1;
The difference between plain Postgres and Postgres with TimescaleDB is the (easy / simple) pre-aggregation into materialized views that we can query windowed. Inserting data into events would trigger the need to refresh the materialized hourly view. If we want to have full support for :15 and :30 minute timezones, we might need to do 15-minute materialized views. For performance reasons we could determine which view we are querying.

​
Events

The querying of events will take a slight hit with TimescaleDB. The following applies to hypertables in TimescaleDB:
  • Foreign key constraints from a hypertable referencing a regular table
  • Foreign key constraints from a regular table referencing a hypertable
  • Foreign keys from a hypertable referencing another hypertable are not supported.
So we will not be able to have the self referencing parent_id and root_id foreign keys on the events table if we convert it to a TimescaleDB hypertable. However we will be able to have a foreign key relationship between events and events_closure. We probably will want to introduce timestamps on the events closure table since that is where TimescaleDB also shines: 
ALTER TABLE events_closure
    ADD COLUMN ancestor_timestamp timestamptz,
    ADD COLUMN descendant_timestamp timestamptz;

ALTER TABLE events_closure
    ADD CONSTRAINT events_closure_ancestor_fkey
    FOREIGN KEY (ancestor_id, ancestor_timestamp)
    REFERENCES events(id, timestamp) ON DELETE CASCADE;

ALTER TABLE events_closure
    ADD CONSTRAINT events_closure_descendant_fkey
    FOREIGN KEY (descendant_id, descendant_timestamp)
    REFERENCES events(id, timestamp) ON DELETE CASCADE;
These can then be queried via: 

SELECT
    parent.id AS parent_id,
    parent.name AS parent_name,
    child.id AS child_id,
    child.name AS child_name,
    et.label,
    ec.depth
FROM events parent
JOIN events_closure ec ON ec.ancestor_id = parent.id
    AND ec.ancestor_timestamp = parent.timestamp
JOIN events child ON child.id = ec.descendant_id
    AND child.timestamp = ec.descendant_timestamp
JOIN event_types et ON et.id = child.event_type_id
WHERE parent.id = '5b300e94-498c-4a94-a85d-2d1b9b218354'
  AND parent.timestamp = '2024-01-15T10:30:00Z'  -- Known timestamp helps chunk pruning
  AND parent.organization_id = 'YOUR_ORG_ID'
ORDER BY ec.depth, child.timestamp;

​
2B Moving over to Clickhouse

Based on us having full event coverage, we could denormalize (or just expand the JSONB metadata column into its own columns). Additionally we would denormalize the event_closure table. The Clickhouse event table would then look something similar to: 

CREATE TABLE events (
    id UUID,
    external_id Nullable(String),
    timestamp DateTime64(3),
    name LowCardinality(String),
    source LowCardinality(String),
    customer_id Nullable(UUID),
    external_customer_id Nullable(String),
    organization_id UUID,
    ingested_at DateTime64(3),

    parent_id Nullable(UUID),
    root_id Nullable(UUID),
    depth UInt8,
    ancestor_path Array(UUID),  -- [root_id, ..., parent_id] - full path to this event

    -- JSONB fields extracted at insert time
    subtotal_amount Nullable(Int64),
    discount_amount Nullable(Int64),
    platform_fee_amount Nullable(Int64),
    subscription_id Nullable(String),
    billing_reason LowCardinality(Nullable(String)),
    recurring_interval LowCardinality(Nullable(String)),
    amount Nullable(Int64),
    customer_cancellation_reason LowCardinality(Nullable(String)),
    refunded_amount Nullable(Int64),
    checkout_status LowCardinality(Nullable(String)),

    -- Keep raw JSON for flexibility
    user_metadata JSON  -- or JSON type in newer versions

    -- For deduplication
    INDEX idx_external_id external_id TYPE bloom_filter GRANULARITY 4
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (organization_id, timestamp, id);

​
Events

Selecting an event and all of its children would look like: 
SELECT
    id,
    name,
    event_type_label,
    depth,
    timestamp
FROM events
WHERE organization_id = 'YOUR_ORG_ID'
  AND has(ancestor_path, toUUID('5b300e94-498c-4a94-a85d-2d1b9b218354'))
ORDER BY depth, timestamp;
Selecting an event and all of its parents would look like: 

SELECT * FROM events
WHERE organization_id = 'YOUR_ORG_ID'
  AND id IN (
    SELECT arrayJoin(ancestor_path)
    FROM events
    WHERE id = '5b300e94-498c-4a94-a85d-2d1b9b218354'
  )
ORDER BY depth;

​
Metrics

Querying metric data from Clickhouse could look something similar to: 

SELECT
    toStartOfHour(timestamp) AS period,

    countIf(name = 'order.paid') AS orders,
    sumIf(subtotal_amount - discount_amount, name = 'order.paid') AS revenue,
    sumIf(
        subtotal_amount - discount_amount - platform_fee_amount,
        name = 'order.paid'
    ) AS net_revenue,

    sum(revenue) OVER (ORDER BY period) AS cumulative_revenue,

    sumIf(1, name = 'subscription.created')
      - sumIf(1, name = 'subscription.revoked') AS subscription_delta,

    sumIf(
        multiIf(
            recurring_interval = 'year', round(amount / 12.0),
            recurring_interval = 'month', amount,
            recurring_interval = 'week', round(amount * 4.0),
            recurring_interval = 'day', round(amount * 30.0),
            0
        ),
        name = 'subscription.created'
    ) - sumIf(
        multiIf(
            recurring_interval = 'year', round(amount / 12.0),
            recurring_interval = 'month', amount,
            recurring_interval = 'week', round(amount * 4.0),
            recurring_interval = 'day', round(amount * 30.0),
            0
        ),
        name = 'subscription.revoked'
    ) AS mrr_delta,

    sum(subscription_delta) OVER (ORDER BY period) AS active_subscriptions,
    sum(mrr_delta) OVER (ORDER BY period) AS monthly_recurring_revenue

FROM events
WHERE organization_id = '00000000-0000-0000-0000-000000000000'
  AND timestamp >= '2025-01-01'
  AND timestamp < '2026-01-01'
GROUP BY period
ORDER BY period
However there are a few caveats to moving events out from Postgres.
  1. More limited SQLAlchemy ORM magic in Clickhouse. There is support to use SQLAlchemy with Clickhouse, but it does not support everything.
  2. We can no longer join events and other tables in the same query. Independent on if we continue to store events in Postgres or if we fully migrate them to Clickhouse, we can’t join the data in the same query.
  3. Dual infrastructure / Operations. We will need to maintain two databases, ensure that they are kept up-to-date, etc. It will add some complexity to our operations.

​
Comparison

For certain organizations we are at >5-10 seconds response times for metrics queries today. Similarly for some organizations we are at >2s for event listing today. This hints that the current set up will not continue to scale with our customers for a lot longer, and we will not be able to onboard even larger customers that are ingesting more event data. Clickhouse is well understood in the industry to be extremely performant, albeit at a cost of heavier operations. Cloudflare1 has been using it for a long time. Posthog2 are also using it and are very happy about it3:
Using ClickHouse is one of the best decisions we’ve made here at PostHog. It has allowed us to scale performance all while allowing us to build more products on the same set of data.
There are different comparisons and benchmarks to find. Tiger Data4 and Tinybird5 have done different benchmarks. RTABench link.
  • TimescaleDB is 1.9x faster than ClickHouse on RTABench, even though it’s 6.8x slower on ClickBench. This is likely because TimescaleDB is optimized for real-time analytics applications, which often rely on normalized schemas and selective aggregations, while ClickHouse shines in denormalized, columnar analytics with large-scale aggregations.
  • Incremental materialized views offer massive speedups. They deliver up to hundreds or even thousands of times faster performance than querying the raw data (from seconds to a few milliseconds), demonstrating their value for real-time analytics. However, among the databases tested, only ClickHouse and TimescaleDB support them.
  • ClickHouse is the leader in data loading and storage efficiency. It’s 4.8x faster at loading data and uses 1.7x less disk than the next best database.
Quoted from TigerDatas4 comparison, leads us to think about what the main patterns of querying we will be doing. We know that we will want to generate metrics from our events (i.e. aggregate and group a lot of events), and we know that we will want to ingest a lot of events from customers externally. 1. Cloudflare 2. Posthog 3. Posthog on HackerNews 4. TigerData 5. Tinybird

​
Proposal

Given our ambition to move closer and make something similar to OpenTelemetry possible when it comes to aggregating and combining events at scale, it probably makes sense for us to take the leap and go toward Clickhouse already today. Even if it is a smaller step to enable TimescaleDB today, it is possible that it will only be performant on data that we control. When our users are ingesting event data in various hierarchies and in various sizes then it becomes more difficult for us to ensure performance. If we were to control all of the data, it is likely that TimescaleDB would support us for a long time forward, even if we were to have magnitudes more data (100x).