Built first‑party analytics inside PostgreSQL
Built first‑party product analytics in PostgreSQL — 47 functions
over partitioned event tables that prune themselves — pseudonymised
behind a rotating salt and gated on the visitor's consent.
2025

Situation. Product decisions need numbers, and the ordinary way to
get them is to put a third‑party tag on every page and let somebody
else's servers watch the users. That is fast, it is free at small
volume, and it means visitor behaviour on a professional networking
platform — who looked at which company, who searched for what —
becomes an asset held by an advertising business. For a platform
whose users are identifiable maritime professionals, that is a poor
trade.

Task. The product team needed funnels, retention and event data,
collected in a way the platform could stand over and a user could
refuse.

Action. Analytics went into PostgreSQL, in a schema of 7 tables and
47 functions. Event tables are partitioned — 22 partition
definitions — and prune themselves on a schedule, because the cost
of first‑party analytics is not collecting the events, it is holding
them forever. Identity is pseudonymised behind a salt that rotates,
so a visitor cannot be followed across the rotation boundary even
from inside the database; that is a deliberate ceiling on what the
data can answer. The client is consent‑aware and sends nothing
before consent is given, rather than sending and filtering
afterwards. Conversion funnels are computed in the database, next to
the data, instead of exported to somewhere else to be joined back.

Result. Questions about how the product is used are answered from
the platform's own tables, with nothing leaving and no tag in the
page. The limits are the honest part: rotating the salt costs
long‑horizon cohort analysis, and none of this arrives with the
dashboards a hosted tool gives you free. Both were accepted
knowingly.

Categories:
1Databases	/categories/databases/	engineer.company	70
1PostgreSQL	/categories/postgresql/	engineer.company	70
1SQL	/categories/sql/	engineer.company	70
1Data Engineering	/categories/data-engineering/	engineer.company	70
1Data Analytics	/categories/data-analytics/	engineer.company	70
1Data Governance	/categories/data-governance/	engineer.company	70
1Performance Tuning	/categories/performance/	engineer.company	70

Services:
1Database Design & Modeling	/services/database-design/	engineer.company	70
1Data Pipeline Development (ETL/ELT)	/services/data-pipeline-development/	engineer.company	70
1Data Analytics & BI Dashboards	/services/data-analytics-bi/	engineer.company	70
1Data Governance & Quality	/services/data-governance/	engineer.company	70

hThis page on the web	URL:https://engineer.company/portfolio/built-first-party-analytics-inside-postgresql/	engineer.company	70
