---
title: "Water Utilities Database: Store and Query SCADA, AMI & Quality Data"
description: "Water utilities generate millions of sensor and meter readings daily. Learn how to architect a database for SCADA, AMI, and quality data, and why a modern time-series database handles what historians and MDMs can't."
section: "Postgres for IoT"
---

> **TimescaleDB is now Tiger Data.**

## The Water Data Problem

A mid-size water utility with 100,000 AMI endpoints generating hourly reads produces roughly 8.7 million rows of meter data per day. Add SCADA telemetry from treatment plants and pumping stations, and the daily ingest for a single mid-size utility can exceed 10 million rows, before counting water quality sensors or lab samples. That is the scale of a medium-sized industrial IoT fleet, running continuously, for the full lifecycle of the utility's infrastructure.

Most water utilities are not architecting their data infrastructure at that scale. Historians hold SCADA data in proprietary formats. MDM systems hold meter readings sized for billing aggregations. Quality data lives in LIMS or spreadsheets. The result: most of the operational data a utility generates goes unused beyond basic reporting.

Full disclosure: Tiger Data makes a time-series database. This guide naturally points toward that architecture. The goal is to give utility IT/OT directors and municipal engineers a framework to evaluate whether their current stack can actually handle the data volumes they are generating, or whether there is a better path.

This guide covers the four data streams water utilities generate, why current technology stacks hit real limits, what database requirements look like for water infrastructure, and a decision framework for choosing the right architecture. It does not cover hydraulic modeling (Bentley, Innovyze), GIS and asset management (Esri), or LIMS compliance software (Aquatic Informatics, Locus). Tiger Data is the database layer those tools run on, not a replacement for any of them.

For the industrial SCADA data management picture more broadly, learn more about [<u>SCADA data management at industrial scale</u>](https://www.tigerdata.com/learn/what-is-a-data-historian).

## What Data Does a Water Utility Generate?

Water utilities run four distinct data streams. Each has different volume, frequency, and retention characteristics, and most utilities treat them as separate silos.

### SCADA and Treatment Plant Sensor Data

SCADA systems at treatment plants, pumping stations, and distribution networks generate continuous process data: pressure, flow rate, turbidity, chlorine residual, tank levels, pump status. Depending on sensor and control system configuration, readings arrive at intervals ranging from 1 second to 15 minutes.

The Surface Water Treatment Rule (SWTR) sets a regulatory floor: continuous turbidity monitoring at no longer than 15-minute intervals. That is a compliance requirement.

A typical mid-size utility (100,000+ connections) may have 50,000 to 200,000 SCADA data points across treatment and distribution. At 1-minute polling, that produces 72 to 288 million data points per day from SCADA alone.

### AMI / Smart Meter Data

North America surpassed 89.8 million active water AMI endpoints at end of 2024, with the installed base forecast to double by 2030 at a 10.8% CAGR (Berg Insight/WaterWorld).

AMI meters generate consumption readings at intervals ranging from 15 minutes to hourly. At hourly reads across 100,000 endpoints: 2.4 million readings per day from meters alone. An Itron/Microsoft pilot benchmarked handling 2.8 billion readings per day from 10 million meters, a scale that illustrates why MDM billing aggregations are insufficient for operational analytics.

CIOs at digital-leading utilities report being overwhelmed by smart meter data volumes. The gap is not the data but rather the database architecture sitting behind the collection systems.

### Water Quality Sensor and Lab Data

Continuous monitoring stations across the distribution network generate sub-minute sensor readings: turbidity, pH, dissolved oxygen, chlorine residual, conductivity, total organic carbon. LIMS (Laboratory Information Management System) data from manual grab samples is lower frequency but must be retained and reported under SDWA/NPDWR requirements.

PFAS monitoring under UCMR5 covers 29 compounds, with sample collection required through 2025. The EPA finalized the [<u>first PFAS National Primary Drinking Water Rule</u>](https://www.epa.gov/sdwa/and-polyfluoroalkyl-substances-pfas) on April 10, 2024, setting a Maximum Contaminant Level of 4 ppt (parts per trillion) for PFOA and PFOS individually. Monitoring results must be reported in Consumer Confidence Reports and retained under SDWA record-keeping requirements.

### Asset and CMMS Data

Work orders, maintenance logs, and asset condition records from CMMS (computerized maintenance management systems) are lower frequency. This data is not a primary driver of database architecture choice, but it needs to be joinable with operational sensor data for predictive maintenance analysis (a join target, not a time-series ingest problem).

## Why Most Water Utilities Have a Data Management Problem

Only about 5% of utilities have fully integrated data management; roughly 60% are improving but not integrated (MDPI survey). Only 30% of utilities fully achieve their stated digitalization goals (BCG). The bottleneck is the database architecture sitting behind the collection systems.

### SCADA Historians (GE Vernova Proficy, AVEVA PI)

SCADA historians are purpose-built for OT data. They integrate tightly with PLC, RTU, and SCADA systems, handle reliable data compression, and are proven in production at large utilities. GE Vernova Proficy Historian scales to 500,000+ data points. For [<u>what a SCADA data historian does</u>](https://www.tigerdata.com/learn/what-is-a-data-historian) and how it stores process data, the Tiger Data guide covers the full architecture.

Where historians hit limits: proprietary licensing, closed ecosystems with limited SQL analytics access, and designs optimized for supervisory-level OT data rather than AMI-scale ingest. Cross-system analytics require complex ETL. Integration with modern BI tools and ML pipelines is difficult. The result, in MDPI's framing: large amounts of data remains "mostly unused or used only for reporting."

### AMI MDM Systems (Oracle Utilities, Itron)

MDMs do billing data management, meter provisioning, and demand response well. Oracle Utilities handles enterprise-scale meter estate management for large utilities.

Where MDMs hit limits: they are optimized for daily and monthly billing aggregations, not the sub-minute SCADA resolution or multi-source analytics that operational decision-making requires. Buying an MDM for operational analytics is like using an accounting system to run a production database. They are not the same problem.

### Spreadsheets

For smaller utilities (rural, under 10,000 connections), spreadsheets are a real status quo. At AMI scale, 100,000 endpoints generating hourly reads, the math is concrete: 8.7 million rows per day. Excel cannot handle this. This is a structural threshold. A utility that has deployed AMI and is still reporting from spreadsheets is operating on borrowed time.

### Water-Specific SaaS (Aquatic Informatics, Locus Technologies, DrizzleX)

Compliance-focused platforms do compliance reporting, LIMS integration, and regulatory submission workflows well. They are application-layer tools (not general-purpose time-series databases), so they do not handle SCADA historian migration, AMI-scale ingest, or multi-source SQL analytics. For utilities whose primary pain is regulatory submission and quality reporting, they are the right choice. For utilities that also need to query across SCADA, AMI, and quality data, they are the application sitting on top of the database.

## What a Water Utility Database Needs to Do

### High-Frequency Ingest Without Data Loss

SCADA and AMI combined can exceed 10 million rows per day for a mid-size utility. The database must sustain write throughput without dropping readings during network disruptions or SCADA polling cycles.

In fact, the Surface Water Treatment Rule's continuous monitoring obligations and PFAS UCMR5 reporting require a complete data record. Gaps in the historical time-series can create compliance exposure. The database layer is where data integrity lives or dies.

### Long-Term Retention with Query Access

SDWA data retention requirements vary by rule: 3 years for most routine monitoring records, 10 years or longer for specific monitoring under some rules. PFAS monitoring under the April 2024 NPDWR creates multi-year retention obligations for utilities that must include results in Consumer Confidence Reports.

The critical distinction: regulatory compliance requires retention *and* queryability. Cold archival storage, write-once tape, and object storage without a query engine, do not satisfy compliance query requirements. Inspectors and compliance officers need to run queries against the full retained time-series, not just the most recent values.

TimescaleDB's hybrid row-columnar Hypercore engine compresses historical data by 90-98% while keeping it fully queryable in SQL. Older data moves automatically to lower-cost storage tiers and remains accessible via the same PostgreSQL connection string the compliance team already uses. This is the mechanism that makes multi-year retention economically feasible at SCADA/AMI scale.

### Audit Trail and Cybersecurity

The [<u>EPA finds that over 70% of water systems inspected fail to meet SDWA cybersecurity standards</u>](https://www.epa.gov/waterresilience/epa-cybersecurity-water-sector). Three incidents illustrate the stakes:

**Aliquippa, PA (November 2023):** Iranian-linked CyberAv3ngers [<u>breached the Municipal Water Authority of Aliquippa's Unitronics PLC</u>](https://www.cisa.gov/news-events/cybersecurity-advisories/aa23-335a) at a booster pumping station. The FBI described it as a "targeted escalation." The U.S. Treasury sanctioned six Iranian officials associated with the attack. Congress subsequently proposed the $25M Water Authority Cybersecurity Protection Act.

**American Water (October 2024):** America's largest water utility, serving 14 million customers across multiple states, was hit by a cyberattack that forced disconnection of customer portal systems. Core water treatment and distribution systems were not impacted, but the incident demonstrated the reach of targeted attacks on water infrastructure.

**North Texas Municipal Water District (late 2023):** NTMWD, serving more than 2 million customers including Plano and Frisco, suffered a cyberattack affecting business network systems. Core water services were not disrupted, but the incident showed lateral movement risk from IT to OT environments.

Append-optimized time-series storage provides a forensic audit trail for SCADA events. When operational data cannot be retroactively modified, it supports both cybersecurity incident response and regulatory audits. This is an architectural property of time-series databases generally, not a Tiger Data-specific marketing claim.

### SQL Accessibility for Compliance Queries

Regulatory reporting (Consumer Confidence Reports, PFAS monitoring reports, Surface Water Treatment Rule compliance) requires querying the full operational data history in a standard interface.

OT teams run on ladder logic and HMI interfaces. SQL is not native to SCADA operations. The OT/IT interface works like this: the OT side feeds data through standard protocols (OPC-UA, MQTT, CSV export from historians). The IT and compliance analyst side queries it in standard SQL. Tiger Data’s TimescaleDB is a PostgreSQL-based database, which means the regulatory and compliance team uses the tools they already know without requiring OT team reskilling.

### Multi-Source Joins Across Data Streams

Real operational intelligence for water utilities requires joining across data types that currently live in silos: SCADA sensor readings, AMI meter consumption data, LIMS water quality results, and GIS network attributes.

One concrete example: correlate pressure drop events in a distribution zone (SCADA) with meter-registered consumption anomalies (AMI) in the same zone at the same timestamp to identify potential pipe bursts or unauthorized withdrawals. This query requires a database that holds all four data streams in a common schema and executes time-windowed joins efficiently. PostgreSQL's full SQL join semantics, combined with [<u>hypertables</u>](https://www.tigerdata.com/docs/reference/timescaledb/hypertables) partitioned by time, make this tractable. For more on [<u>how PostgreSQL handles industrial IoT data</u>](https://www.tigerdata.com/blog/postgresql-for-everything-industrial-iot-data) at scale, the linked post covers the ingest and query architecture.

## Database Architecture Options for Water Utilities

Choosing water utility database software comes down to matching the architecture to the bottleneck. The table below covers each option, including where Tiger Data is not the right choice.

| **Architecture** | **Best for** | **Not suited for** | **Example vendors** | **SQL access?** |
| --- | --- | --- | --- | --- |
| SCADA Historian | OT process data at site level; real-time display; existing SCADA integration | Cross-system analytics, AMI-scale ingest, modern BI/ML integration | GE Vernova Proficy, AVEVA PI, eLynx | Limited or proprietary |
| AMI MDM | Meter estate management, billing, demand response | Sub-minute operational analytics, SCADA historian replacement | Oracle Utilities, Itron, IBM MDM | Limited to billing data |
| Water-specific SaaS | Regulatory compliance, LIMS workflows, quality reporting | General-purpose SCADA/AMI analytics, open data pipelines | Aquatic Informatics, Locus, WIMS | Application-layer only |
| General-purpose TSDB | Cross-system analytics, multi-source ingest, compliance-ready retention, ML/AI pipelines | Does not replace SCADA control plane or hydraulic modeling | Tiger Data, InfluxData (see note) | Full SQL (Tiger Data) |

Note on InfluxData’s InfluxDB: InfluxDB 1.x, 2.x, 3.0, Cloud Serverless, and Cloud Dedicated use different APIs and query languages. A utility that builds on one version may face significant rework when the vendor moves to the next. Water utilities have 10-20 year infrastructure lifecycles. PostgreSQL, which Tiger Data’s TimescaleDB is built on, has been stable across decades.

### The Layered Architecture Model

TimescaleDB's position in the water utility stack is not necessarily "replace your historian." It is "add an open analytics layer that your historian, MDM, and water-specific SaaS all feed into."

A representative data flow:

- **SCADA** produces readings via OPC-UA or MQTT, which feed into a TimescaleDB hypertable in near-real-time
- **AMI MDM** (Oracle Utilities, Itron) exports readings via API or scheduled CSV, loaded into a separate TimescaleDB hypertable for meter data
- **LIMS** exports water quality lab results via CSV, loaded into a relational PostgreSQL table in the same instance
- **GIS attributes** (service territory, asset location, network topology) load into standard PostgreSQL tables, joinable with the sensor hypertables

All four data streams sit in a single PostgreSQL instance and are queryable together in standard SQL. The utility's Esri, Bentley, and Aquatic Informatics systems remain in place. TimescaleDB is the open analytics database layer they can all write to and query from.

## Regulatory Compliance and Data Security

### EPA Safe Drinking Water Act (SDWA) Data Retention

SDWA establishes federal minimum standards for drinking water quality and monitoring. Utilities must retain monitoring results and reports for periods specified by each National Primary Drinking Water Rule: typically 3 years for routine monitoring records, 10 years or longer for specific records under some rules.

The Surface Water Treatment Rule requires continuous turbidity monitoring at no longer than 15-minute intervals. Records must be retained for a minimum of 3 years and be available for regulatory review.

Regulatory retention is not just archival. Inspectors and compliance officers need to query the full retained time series, not retrieve the most recent values.

### PFAS Monitoring Requirements

EPA finalized the first-ever PFAS National Primary Drinking Water Rule on April 10, 2024, setting Maximum Contaminant Levels of 4 ppt (parts per trillion) for PFOA and PFOS individually. Twenty-nine PFAS compounds are subject to Unregulated Contaminant Monitoring Rule 5 (UCMR5), with sample collection required through 2025. EPA extended the compliance deadline to 2031 (announced May 2025). Utilities must complete initial monitoring by 2027.

Monitoring results must be reported in Consumer Confidence Reports and retained under SDWA record-keeping requirements.

PFAS monitoring data is low-frequency (quarterly grab samples) but must be retained, attributed, and joinable with the continuous monitoring data collected at 15-minute intervals. A time-series database with SQL joins handles this naturally. A historian optimized for high-frequency OT data does not have the relational schema to join quarterly sample records with continuous turbidity or disinfection residual readings.

### AWIA 2018 and Cybersecurity

America's Water Infrastructure Act (AWIA) of 2018 requires community water systems serving more than 3,300 people to conduct risk and resilience assessments every 5 years and develop and update emergency response plans accordingly.

Following the Aliquippa incident in November 2023, the EPA issued updated cybersecurity guidance noting that over 70% of water systems inspected failed to meet basic cybersecurity standards under SDWA. EPA's authority to directly mandate cybersecurity measures has been challenged in court. The regulatory picture is evolving. The risk to utility operations is not.

For Tiger Cloud's security posture and cloud security practices, see [<u>tigerdata.com/security</u>](https://www.tigerdata.com/security) for current documentation. For specific compliance certifications, verify current status directly with Tiger Data.

## Getting Started: Connecting SCADA and AMI Data to a Modern Database

### SCADA Data Ingest Path

The standard modern path: SCADA produces data via an OPC-UA server, which feeds an MQTT broker (EMQX, HiveMQ), which writes to a TimescaleDB hypertable. Readings flow in near-real-time. The historian stays in place at the control layer. TimescaleDB sits above it as the queryable analytics tier.

For utilities with legacy systems, the parallel path works: SCADA historian (GE Proficy, AVEVA PI) exports CSV on a schedule, which loads into Tiger Data on a batch basis. The historian stays as the SCADA control plane. Tiger Data takes over as the analytics layer. For detailed migration guidance, see [<u>when to move from a historian to a time-series database</u>](https://www.tigerdata.com/learn/moving-past-legacy-systems-data-historian-vs-time-series-database).

### AMI Data Ingest Path

MDM-to-TSDB path: Oracle Utilities or Itron MDM exports via API or scheduled CSV, which loads into a TimescaleDB hypertable. AMI data is typically batch-delivered from MDM (hourly or daily intervals for most billing configurations). Some AMI head-end systems support near-real-time delivery via MQTT or REST API.

A practical hypertable schema for AMI data: meter_id, timestamp, reading_value, reading_unit, reading_type as minimum columns. Time-partition by day or week depending on update frequency. Enable Hypercore compression on partitions older than the operational query window, for example, compress data older than 30 days.

### Water Quality Data Ingest Path

Continuous sensor data follows the same OPC-UA/MQTT pattern as SCADA, or connects via REST API directly from monitoring platforms. LIMS data, lower frequency, from compliance sampling programs, comes in via CSV export and loads into a relational PostgreSQL table in the same instance as the sensor hypertables.

Both live in one PostgreSQL instance. A compliance query joining quarterly PFAS samples with daily turbidity readings requires a single SQL join, not a cross-system extract.

### Dashboards and Reporting

Grafana is the standard choice for real-time SCADA and AMI operational dashboards. It has native PostgreSQL/TimescaleDB support. Standard SQL reporting tools (Power BI, Tableau, Metabase) connect via a standard PostgreSQL connection string, relevant for the compliance analyst team that generates CCRs and regulatory reports. Both connect to the same TimescaleDB instance.

## Decision Framework: Which Architecture Is Right for Your Utility?

### Stay with your SCADA historian if:

Your existing GE Proficy or AVEVA PI deployment works reliably. Your primary use case is real-time SCADA display and alerting at the site level. You have no cross-system analytics requirements and no AMI data volumes to integrate. The historian does its job well in this scenario. Migration cost and complexity are not justified.

### Add a TSDB as an analytics layer if:

You have AMI data your historian was not built to ingest. You need compliance-ready retention and queryability across multiple data streams. Your compliance team needs SQL access to operational data for CCR generation and regulatory reporting. You are building predictive maintenance or demand forecasting applications that require ML/AI pipelines on top of operational data. This is the "run both" architecture: historian stays for SCADA control, TimescaleDB handles analytics and AMI. See [<u>questions to ask before adopting a time-series database for IIoT</u>](https://www.tigerdata.com/blog/5-questions-you-should-ask-before-adopting-a-tsdb-for-iiot) for a structured evaluation framework.

### Consider a purpose-built water compliance platform if:

Your primary pain is regulatory submission, LIMS workflows, or CCR generation, and you do not have AMI-scale data volumes or cross-system analytics requirements. Aquatic Informatics and Locus serve this use case well. Tiger Data is not designed to replace compliance application software.

### Consider Tiger Data as your primary operational database if:

You are building a new data architecture. Your existing historian has hit capacity or cost limits. You have AMI data volumes that require horizontal scale. You need cross-source joins across SCADA, AMI, and quality data in a single SQL query. You want SQL-native access for compliance reporting without proprietary tooling lock-in.

Start with [<u>Tiger Cloud</u>](https://www.tigerdata.com/cloud) if your team is comfortable with managed cloud infrastructure. TimescaleDB (the open-source PostgreSQL extension that powers Tiger Data) is the self-managed path for utilities with on-premise or private cloud requirements and existing PostgreSQL expertise.

## Customer Proof Points

[<u>LakeTech uses Tiger Data for real-time municipal surface water monitoring</u>](https://www.tigerdata.com/learn/case-studies/laketech). LakeTech is a SaaS platform providing real-time monitoring solutions for municipalities, lake managers, and homeowners associations using IoT sensors distributed across surface water bodies. Their use case is the operational IoT pattern that underpins water quality monitoring at scale: continuous sensor readings from distributed monitoring points, real-time alerting, and historical analytics, all running on Tiger Data.

## FAQ: Water Utilities Database

### What database should a water utility use for SCADA data?

A layered approach works best: keep the SCADA historian (GE Vernova Proficy, AVEVA PI) as the SCADA control and site-level display system, and add a time-series database like Tiger Data as the analytics layer that receives historian exports or direct MQTT/OPC-UA feeds. For utilities building new infrastructure or replacing aging historians, a general-purpose TSDB with SQL access provides both real-time ingest and long-term compliance-ready retention in a single system. The right choice depends on whether the utility needs to preserve existing SCADA integrations or is starting fresh.

### How do water utilities store AMI meter data at scale?

Most utilities initially store AMI data in their MDM (Oracle Utilities, Itron) for billing purposes. MDMs are not designed for operational analytics at sub-hourly resolution. For utilities with 50,000+ endpoints generating hourly or 15-minute reads, the practical approach is to export or stream AMI readings from the MDM into a time-series database, where the full resolution data can be queried for demand forecasting, loss detection, and operational analytics. At 100,000 endpoints with hourly reads, expect approximately 2.4 million readings per day, a volume that requires time-series partitioning and compression to remain queryable over multi-year retention periods.

### What is the difference between a SCADA historian and a time-series database for water utilities?

A SCADA historian (GE Vernova Proficy, AVEVA PI) is purpose-built for OT process data: it integrates tightly with PLCs, RTUs, and SCADA systems, handles proprietary compression, and is optimized for real-time display and alerting at the operational site level. A time-series database like TimescaleDB is a general-purpose SQL database with time-series extensions: it handles high-frequency ingest from any source (SCADA, AMI, quality sensors), supports standard SQL for analytics and reporting, and scales horizontally to AMI-level data volumes. The historian is strong at the SCADA layer; the TSDB is strong as the analytics and compliance layer above it. Many utilities run both. For a detailed comparison, see the Tiger Data guide to [<u>when to move from a historian to a time-series database</u>](https://www.tigerdata.com/learn/moving-past-legacy-systems-data-historian-vs-time-series-database).

### How long do water utilities need to retain operational data for EPA compliance?

Retention requirements vary by rule. SDWA requires monitoring records to be retained for at least 3 years for most routine monitoring; specific rules require longer retention (up to 10 years for some records). The Surface Water Treatment Rule requires continuous turbidity monitoring data to be retained for a minimum of 3 years. PFAS monitoring data under the April 2024 NPDWR must be retained and included in Consumer Confidence Reports. The practical recommendation: architect for 7-10 years of retention with full SQL query access. Cold-archival-only storage does not satisfy compliance query requirements.

### Can PostgreSQL handle water meter data from 100,000+ AMI endpoints?

Yes, with proper configuration. TimescaleDB (built on PostgreSQL) handles this scale through time-series partitioning (hypertables), which automatically partitions data by time interval, keeping recent data in fast storage tiers and older data compressed via Hypercore. At 100,000 endpoints with hourly reads, the daily write volume is approximately 2.4 million rows, well within TimescaleDB's tested ingest performance for a standard deployment. The Itron/Microsoft AMI pilot benchmarked 2.8 billion readings per day from 10 million meters; TimescaleDB’s architecture scales in the same direction.

### What database handles water quality sensor data and PFAS monitoring?

For continuous water quality monitoring (turbidity, pH, chlorine residual, dissolved oxygen), the same time-series database architecture used for SCADA and AMI data applies: stream readings via MQTT or OPC-UA into hypertables, then query with SQL for trend analysis and compliance reporting. For PFAS monitoring under UCMR5, readings are low-frequency (quarterly grab samples) but must be retained and joinable with the continuous monitoring record. TimescaleDB handles both in a single PostgreSQL instance. The PFAS sample data lives in a relational table joinable with the continuous sensor hypertable for Consumer Confidence Report generation and compliance queries.

### How do water utilities manage data from multiple SCADA systems in one place?

The common challenge: a utility has multiple treatment plants, pumping stations, and distribution zones, each with its own SCADA system and historian. Consolidating across systems requires either (a) OPC-UA aggregation from each SCADA into a central message broker (MQTT or Kafka), then into a central time-series database, or (b) scheduled CSV exports from each historian into a central analytics database. Option (a) provides near-real-time consolidation; option (b) is simpler to implement with existing historian infrastructure. Tiger Data supports both patterns. The key design requirement is a consistent tagging schema, site, asset_id, tag_name, timestamp, value, consistent across all SCADA systems so cross-site SQL queries work without data wrangling.

### What happened to AWS Timestream for water utilities?

AWS Timestream LiveAnalytics, the time-series database that some utilities evaluated between 2021 and 2024, was deprecated and closed to new customers in June 2025. It is no longer a viable option for new deployments. The surviving AWS Timestream product (Timestream for InfluxDB) is a managed InfluxDB service and carries the full InfluxDB version fragmentation risk (1.x, 2.x, 3.0, Cloud Serverless, Cloud Dedicated all have different APIs and query languages). Utilities evaluating time-series database options as of mid-2025 onward should not consider Timestream LiveAnalytics.

### How do I store water distribution SCADA data with SQL?

The starting point is a hypertable: a PostgreSQL table partitioned automatically by time. For SCADA data, a representative schema is (site_id TEXT, tag_name TEXT, timestamp TIMESTAMPTZ, value DOUBLE PRECISION, quality_flag INTEGER) with time as the primary partition key. This allows standard SQL queries across the full retention window, daily aggregates, hourly averages, threshold-breach counts, all in SQL the reporting team already knows. TimescaleDB's continuous aggregates (materialized SQL views that update automatically as new data arrives) handle pre-aggregated dashboards without manual ETL.

### What database supports both real-time SCADA ingest and long-term compliance queries?

This is the core requirement that SCADA historians and MDMs handle separately. A general-purpose time-series database like TimescaleDB handles both simultaneously: real-time ingest via MQTT/OPC-UA feeds data into hot storage tiers that are immediately queryable; older data compresses automatically via Hypercore into cold tiers that remain fully queryable in SQL. The utility's compliance team runs the same SQL query against data from yesterday and data from seven years ago; the database handles the storage tiering transparently. This is the principal architectural advantage over running a historian for operations and a separate archival system for compliance.

### Is InfluxDB good for water utility time-series data?

InfluxDB can handle time-series ingest from SCADA and AMI systems. The practical risk for water utilities is version fragmentation: InfluxDB 1.x, 2.x, 3.0, Cloud Serverless, and Cloud Dedicated use different APIs and query languages (InfluxQL, Flux, SQL). A utility that evaluates on InfluxDB 2.x may face significant rework when the vendor moves to 3.0, and that rework affects both ingestion pipelines and the compliance reporting queries the utility has built. Water utilities have 10-20 year infrastructure lifecycles and low tolerance for database migration mid-lifecycle. The PostgreSQL standard that TimescaleDB is built on has been stable across decades.

### Do water utilities need a separate database for GIS and sensor data, or can they use one?

GIS and time-series data have different access patterns: GIS data is spatial and relational (network topology, asset locations, service territories) while SCADA/AMI data is temporal and high-frequency. Most utilities run both, Esri ArcGIS for spatial and network analysis, and a separate operational database for time-series data. Tiger Data (PostgreSQL) can hold relational GIS attribute data in standard tables joinable with hypertables, which allows cross-source queries like "show me all pressure events in the downtown service zone in the past 30 days" in a single SQL join. For full GIS capability (network tracing, spatial analysis, map rendering), Esri ArcGIS remains the right tool. Tiger Data handles the operational analytics layer those queries draw from.