# Postgres analytics

> First-party lite AnalyticsSink — stores events in the main DataStore, cross-dialect, zero external infra. ~10M events/day ceiling.



---

<!-- source: en/plugins/analytics-postgres.md -->
## Postgres analytics

_First-party lite AnalyticsSink — stores events in the main DataStore, cross-dialect, zero external infra. ~10M events/day ceiling._

`@voltro/plugin-analytics-postgres` is the day-1 `AnalyticsSink` — it stores
events in an `_voltro_events` table on the **main DataStore**, so there's no
external service to run. It implements all four contract methods (`track` /
`aggregate` / `timeseries` / `topN`) by compiling to portable SQL that works on
every dialect (postgres / mysql / mariadb / mssql / sqlite). Pick it first;
swap to a real OLAP sink (DuckDB / ClickHouse / Tinybird) only once you outgrow
its **~10M events/day** ceiling — the swap is a one-line config change because
every sink shares the same contract. For the shared `AnalyticsSink` API and the
`useAnalytics()` read methods, see [Analytics & warehouse sinks](/docs/plugins/analytics).

## Install

```
pnpm add @voltro/plugin-analytics-postgres
```

## Wiring

```ts
// app.config.ts
import { postgresAnalytics } from '@voltro/plugin-analytics-postgres'

export default {
  type:      'api' as const,
  name:      'myApi',
  store:     'postgres' as const,
  analytics: postgresAnalytics(),
}
```

The framework calls the factory at boot, hands it the `SqlClient` bound to the
running DataStore, and provides the resulting sink under `AnalyticsSink`. The
plugin's `_voltro_events` table is auto-merged into your schema, so
`applySchema()` creates it on first boot — no `*.entity.ts` needed.

## Options

`postgresAnalytics(options?)` — all optional:

| Option | Type | Default | Notes |
|---|---|---|---|
| `sqlClient` | `SqlClient.SqlClient` | the main DataStore's client | Override to land events in a SEPARATE database (e.g. a dedicated analytics Postgres while the app runs MySQL). You must create the events table there yourself. |
| `mirrorTables` | `ReadonlyArray<string>` | `[]` (events-only) | Reactive tables to CDC-mirror into `_voltro_mirror_<table>` (`{ id, data, version, is_deleted }`) so analytical queries can JOIN events against live user data. Deletes write a tombstone — filter `is_deleted = false`. |
| `mirrorPrimaryKey` | `string` | `'id'` | Primary-key column on the mirrored source rows. |

```ts
analytics: postgresAnalytics({ mirrorTables: ['users', 'teams'] })
```

## Environment

- **`VOLTRO_EVENTS_TTL_HOURS`** — retention TTL for `_voltro_events`. Default
  `8760` (365 days). A boot retention sweep drains events past the TTL so the
  table never grows without bound.

## Honest ceiling

By ~10M events/day, range aggregates over 30 days take >5s on Postgres. Past
that, switch to [`@voltro/plugin-duckdb`](/docs/plugins/duckdb) (embedded) or
[`@voltro/plugin-clickhouse`](/docs/plugins/clickhouse) (external). Reads are
tenant-scoped automatically — `useAnalytics()` stamps the caller's `tenantId`
onto every `track` and every aggregate.

## Schema introspection

The plugin re-exports its events-table descriptor so you can extend the schema
(add a GIN index on `properties`, partition by month) via your own migration:

```ts
import { _voltroEventsTable } from '@voltro/plugin-analytics-postgres'
```

## See also

- [Analytics & warehouse sinks](/docs/plugins/analytics) — the shared
  `AnalyticsSink` contract, the `useAnalytics()` read API, `composeAnalytics`,
  and the CDC-mirror details.