---
title: "How ApexAnalytica Runs Building Telemetry, Transactional Data, and RAG on a Single Postgres Instance"
published: 2026-05-27T08:47:00.000-04:00
updated: 2026-05-27T09:15:25.000-04:00
excerpt: "See how open source TimescaleDB powers a year of hourly building energy data in a single heat map view — rendered in under a second across 38 live sites."
tags: Dev Q&A, TimescaleDB
authors: Andrew Stebbins
---

> **TimescaleDB is now Tiger Data.**

_A solo developer with 25 years in building automation uses TimescaleDB hypertables and time\_bucket materialized views to power a 12-month hourly heat map dashboard that used to scan millions of half-hourly meter readings on vanilla Postgres._ _He builds a production SaaS that ingests BMS telemetry from 38 buildings across care homes, hotels, gyms, garden centers, offices, veterinary practices, and demand-flexibility sites, then renders a year of hourly consumption per building as a single heat map in under a second._

_This is an installment of our Community Member Spotlight series, in which we invite our open source community members to share their work, spotlight what they have built, and inspire others with new ways to use TimescaleDB._

_Today we hear from Andrew McKenna, founder of Apex Innovation Labs and the developer of ApexAnalytica, an AI-powered building intelligence platform for facility managers, energy managers, and engineers. ApexAnalytica brings a 12-month hourly aggregation query from 6+ seconds on vanilla Postgres down to under a second on open source TimescaleDB, which is what lets a building operator scrub a year of consumption patterns inside a single dashboard view._

## About ApexAnalytica

Facility managers and energy managers sit on a problem that has been on industry whiteboards for two decades. Building Management Systems generate enormous volumes of telemetry: half-hourly meter readings, BMS point data refreshing every 60 seconds, IAQ sensors, fault and alert streams. Almost none of it gets analyzed in a way that produces a decision. It sits on head-end controllers as raw values waiting for an engineer to look at the right point at the right moment.

ApexAnalytica is the analytics layer the industry has not had. It connects to existing BMS systems over BACnet, Tridium Niagara, Modbus TCP, MQTT, REST, and SQL Server, and turns the raw telemetry into real-time analytics, ASHRAE Guide 36-aligned fault detection, IPMVP-compliant measurement and verification, and an AI agent that reasons over building documentation alongside live data. The platform runs across single buildings and multi-hundred-building portfolios. Six clients are live today across 38 sites in care and social housing, hospitality, leisure, retail, commercial offices, veterinary, and the energy sector. A 1,700-property care and social housing estate is in the deployment pipeline.

ApexAnalytica is built by Andrew McKenna, who spent 25 years in building automation before founding Apex Innovation Labs with his wife Michelle in September 2025. Andrew's prior roles include Head of BMS, Global IoT Lead, Technical Director, and Head of Technology at major UK integrators. His background is engineering, not software, and he had never shipped production TypeScript before this project. AI-assisted development closed that gap, and ApexAnalytica is the result: a production SaaS architected end-to-end by a single domain expert with two and a half decades of operational context.

> _After two and a half decades watching valuable BMS data sit unused on head-ends, I wanted to build the analytics layer the industry needed but never had._  _\- Andrew McKenna, Founder, Apex Innovation Labs_

## The Challenge: Twelve Months of Building Telemetry Doesn't Render in Six Seconds

The dashboard that broke first is the 12-month hourly heat map. Hour-of-day on one axis, day on the other, twelve months of meter consumption rendered as a single visual so an operator can spot weekend baselines, out-of-hours waste, and seasonal patterns at a glance. On vanilla Postgres the underlying aggregation ran 6+ seconds. That is the latency where users assume the platform is broken.

The shape of the problem is well understood by anyone running operational analytics on telemetry. Raw half-hourly readings land in a single wide table. To render hourly buckets for a 12-month window per building, the query has to scan tens of thousands of underlying readings and roll them up on every dashboard load. The cost grows linearly with the time window and the number of meters per building. ApexAnalytica was not yet at meaningful scale when the query started crossing six seconds. One customer's portfolio was enough. The trajectory was obvious.

## Why TimescaleDB

Andrew evaluated time-series options the way an AI-assisted developer working alone in 2025 evaluates them. Claude Code recommended TimescaleDB based on the workload shape, he read the docs for an evening, and the team migrated. The decision saved a fortnight of comparison shopping against InfluxDB and ClickHouse, and the bet paid off in production.

> _Trusted the recommendation. Claude Code suggested TimescaleDB based on the workload shape, I read the docs for an evening, and we migrated. It saved me a fortnight of comparison shopping against InfluxDB and ClickHouse. The bet paid off._   _- Andrew McKenna_

The reason it works is that TimescaleDB is an extension on Postgres, not a different database. The same SQL ApexAnalytica's backend already writes for transactional state continues to work for time-series rollups. There is no second system, no separate query language, no pipeline to keep in sync between operational and analytical stores. The ingestion rate at 1,000 to 10,000 data points per second across BMS protocols arrives in the same Postgres instance that holds the building metadata, the user accounts, the FDD rule definitions, and the vector embeddings that power the AI agent.

## The ApexAnalytica Stack

ApexAnalytica's pipeline starts at the building. BMS telemetry, meter data, IAQ sensors, and weather feeds arrive over BACnet, Niagara, Modbus TCP, MQTT, REST, and SQL Server. A Node.js and TypeScript backend running on DigitalOcean Kubernetes ingests the data and writes raw half-hourly meter readings into a TimescaleDB hypertable called `energy_data`. The hypertable uses a 30-day chunk interval and currently holds 72 chunks and 1.32 million rows of meter consumption.

A family of materialized views sits above the hypertable. `energy_hourly`, the workhorse for the dashboard, uses `time_bucket('1 hour', timestamp)` to roll the half-hourly raw data up to hourly buckets per building. Sibling views (`energy_daily`, `energy_weekly`, `energy_monthly`, `energy_yearly`) handle longer time windows, plus per-meter variants for drill-downs. A custom Node.js service refreshes the materialized view family every 15 minutes, sequentially across the views, with a Redis distributed lock that prevents concurrent refreshes across Kubernetes pod replicas. The refresh uses `REFRESH MATERIALIZED VIEW CONCURRENTLY` so dashboards never block on refresh cycles.

The AI agent, Ask Apex, runs on an open-source LLM hosted inside Apex Innovation Labs' own infrastructure so customer telemetry never leaves the environment. The vector store is `pgvector` inside the same Postgres instance as the time-series data. Four embedding tables (`document_chunks`, `building_document_chunks`, `bms_point_embeddings`, `fdd_requirement_embeddings`) keep the RAG layer inside one database, one backup, one security boundary, and no second connection pool to manage.

The application surface is a React 18 frontend that consumes pre-aggregated rollups from the materialized view family for portfolio-wide dashboards and reads the raw hypertable directly for half-hourly drill-downs. Chunk exclusion on the raw table means a single-day drill-down for a single meter scans roughly 48 rows out of the 1.32 million in `energy_data`.

![ApexAnalytica's data flow: BMS, meter, and IAQ telemetry land via a Node.js ingestion service into a TimescaleDB hypertable on DigitalOcean Managed Postgres. A materialized view family powers dashboard reads. pgvector handles RAG for the Ask Apex agent. The application surface is React 18.](https://storage.ghost.io/c/6b/cb/6bcb39cf-9421-4bd1-9c9d-fa7b6755ba0e/content/images/2026/05/apex-analytica.png)

__ApexAnalytica's data flow: BMS, meter, and IAQ telemetry land via a Node.js ingestion service into a TimescaleDB hypertable on DigitalOcean Managed Postgres. A materialized view family powers dashboard reads.__ _`_pgvector_`_ __handles RAG for the Ask Apex agent. The application surface is React 18.__

## Results: A Single Visual, Under a Second

### From 6 Seconds to Under a Second

The 12-month hourly heat map renders a year of hourly consumption for a building, or per-meter via a sibling view, in under a second. The same workload on vanilla Postgres ran 6+ seconds. The heat map query reads from `energy_hourly`, joins two anomaly-count CTEs (one from the ML pipeline, one statistical), and returns 5,452 pre-aggregated rows for a 12-month window. The raw scan that produced those 5,452 rows would have hit 65,000 to 130,000 underlying half-hourly readings depending on building meter count.

> _Charts that used to keep users waiting now load instantly because of materialized views._  _\- Andrew McKenna_

The materialized view definition driving the dashboard:

```SQL
CREATE MATERIALIZED VIEW energy_hourly AS
SELECT
  time_bucket('1 hour', timestamp) AS hour,
  building_id,
  SUM(COALESCE(kwh_usage, 0)::numeric) AS consumption,
  COUNT(*)                            AS record_count,
  MIN(kwh_usage::numeric)             AS min_consumption,
  MAX(kwh_usage::numeric)             AS max_consumption,
  AVG(kwh_usage::numeric)             AS avg_consumption
FROM energy_data
WHERE kwh_usage IS NOT NULL
GROUP BY hour, building_id;
 
CREATE INDEX idx_energy_hourly_building_hour
  ON energy_hourly(building_id, hour DESC);
```

The heat map query itself, with anomaly joins simplified for readability:

```SQL
SELECT
  eh.hour       AS timestamp,
  eh.consumption,
  COALESCE(ml.ml_anomaly_count, 0)
    + COALESCE(sa.stat_anomaly_count, 0) AS anomaly_count
FROM energy_hourly eh
LEFT JOIN ml_anomaly_counts        ml ON ml.building_id = eh.building_id AND ml.period = eh.hour
LEFT JOIN statistical_anomaly_counts sa ON sa.building_id = eh.building_id AND sa.period = eh.hour
WHERE eh.building_id = $1
  AND eh.data_type   = $2
  AND eh.hour >= $3
  AND eh.hour <  $4
ORDER BY eh.hour;
```

### One Postgres, One Backup, One Boundary

The decision compounds for a solo developer. Raw telemetry, materialized aggregates, vector embeddings for the AI agent, building metadata, and FDD rule definitions all live in the same database. There is no pipeline to maintain between an operational store and an analytical one, no schema drift between systems, no second backup window, no second security boundary to audit. For a one-person engineering team running production for six clients, the architectural coherence matters as much as the query speed.

## Looking Ahead

The next phase of ApexAnalytica is gated on TimescaleDB capabilities the platform does not yet use. Three roadmap items push the time-series workload harder than the current stack: a UK Net Zero Carbon Buildings Standard rating framework that benchmarks multi-year weather-normalized consumption per asset class across a portfolio; ASHRAE 231-2026 Control Description Language auto-generated FDD rules whose execution scales with rules times points times query frequency; and a single pane of glass MSI data layer that pulls from BMS, energy meters, IAQ sensors, weather, and lighting over UDMI on MQTT, with Google's Digital Buildings Ontology as the entity and relationship model.

Lighting is the workload that will properly stretch the time-series layer. Per-fixture state changes can run at sub-second frequency across thousands of points per building. At that ingest rate, the custom Node.js materialized view refresh service becomes a bottleneck and the static row format on cold chunks becomes a cost problem. The two TimescaleDB features next on the evaluation list are continuous aggregates, to replace the refresh service with incremental refresh policies, and Hypercore compression on older chunks, to bring storage cost in line with the data ingest rate. Both move ApexAnalytica toward the shape of platform that can handle the 1,700-property care and social housing estate already in the deployment pipeline.

* * *

_Are you building something interesting with TimescaleDB? Get featured. The Community Member Spotlight series is where we highlight work like Andrew's: real problems solved on open source TimescaleDB by developers in the community. If you've shipped something on TimescaleDB - a weekend project, a research tool, or production infrastructure - we'd love to feature yours next in the series._

_The one requirement is that the project runs on open source TimescaleDB (self-hosted or on the free tier both work). Fill out_ [_our short form_](https://forms.gle/1oBGNUuQDurLVSXb8) _and we'll follow up to talk through what you've built._