TL;DR: PostgreSQL solves the IoT data puzzle by unifying time-series and relational data in one database. Engineers can now query rich context alongside sensor readings, while TimescaleDB handles massive data volumes. From location tracking to custom data types, PostgreSQL delivers the reliability that IoT demands—all without juggling multiple databases. 👉 Jump to the full article
IoT generates massive streams of sensor data from connected physical systems. Teams often split this between SQL and NoSQL databases, creating needless complexity. PostgreSQL with TimescaleDB offers a simpler path: one database that handles both time-series and relational data at scale.
1. Scaling PostgreSQL for Sensor Data (With Timescale)
IoT backends need to support high data ingest rates, while writes on PostgreSQL can often slow to a crawl as your dataset grows.
🤯 Did you know you can scale PostgreSQL to ingest millions of rows per second, storing billions of rows, even on a single node with a modest amount of RAM (if your database is architected the right way).
How do we know this? Because we did it. 😎 For one of our features, we scaled PostgreSQL to petabytes of data (and counting), with over 800 billion new records per day. We flew past 100 trillion raw records in a single table recently.
2. A Simplified Stack: One IoT Database vs. Two
TimescaleDB lets PostgreSQL handle both your time-series and relational IoT data in a single database.
This eliminates the complexity of maintaining separate NoSQL and SQL databases with messy glue code between them. For backup scenarios, this is crucial - no more risk of time-series data pointing to missing device metadata because your databases were backed up at different times. From edge computing to large-scale deployments, one unified database makes system design dramatically simpler, echoed by CTO of a unicorn startup:
"Aggressively consolidating our tech stack lets us sharpen our engineering team's focus."
3. More Context for Your IoT Data
A single database for time-series data and metadata does more than just simplify your stack: it also lets you add context to your sensor data. Because what is the point of collecting data if you don’t know what you’re measuring?
With TimescaleDB and PostgreSQL, they can keep their metadata normalized and add the necessary context to their time-series data at query time (via a SQL JOIN). For example, via a query like this:
-- Show me the average temperature by machine, for machines of a
-- given type on a particular line, by 5 minute intervals
SELECT time_bucket('5 minutes', measurements.time) as five_min,
machine_id, avg(temperature)
FROM measurements
JOIN machines ON measurements.machine_id = machines.id
JOIN lines ON machines.line_id = lines.id
WHERE machines.type = 'extruder' AND lines.name = 'production'
AND measurements.time > now() - interval '36 hours'
GROUP BY five_min, machine_id
ORDER BY five_min, machine_id;
4. Power and Ease of Use
Let’s take a step back and keep in mind the value of an IoT project: to collect, analyze, and act on data to improve utility/efficiencies, reduce downtime/waste, and provide better products and services. In other words, you need more than a data store: you also need an easy way to wring actionable insights out of your data.
This is where SQL comes in. While it’s been quite fashionable in the past several years to denounce SQL and praise NoSQL, the truth is that SQL is quite powerful and is starting to make a comeback (which is one reason why “NoSQL” is now getting “backronymed” to “Not only SQL”).
-- Plot the change in temperature for ceiling sensors
-- on linear and logarithmic scales, by 10 second intervals
SELECT ten_second,
temperature / lead(temperature) OVER data AS temperature_rise_linear,
log(temperature / lead(temperature) OVER data) AS temperature_rise_log
FROM (
SELECT time_bucket('10 seconds', time) as ten_second,
last(temperature, time) as temperature
FROM measurements JOIN sensors ON measurements.sensor_id = sensors.id
WHERE sensors.type = 'ceiling' AND measurements.time >= '2017-06-01'
GROUP BY 1
ORDER BY 1
) sub window data AS (ORDER BY ten_second asc);
💡 SQL has another advantage: people across your organization already know it. You won’t need to train your engineers to learn a new specialized query language (or hire new ones), and non-technical users won’t need to rely as heavily on engineering (and engineering release cycles) to answer questions about the data.
5. Broad Data Type Support (Including JSON)
JSON in a relational database? Eat your heart out, MongoDB.
When you start building your IoT product, you may not know what data you will care about, nor have a specific data schema in mind. Later on, you may need very specific data structures (e.g., arrays).
Fortunately, PostgreSQL supports a broad spectrum of data types. It allows for semi-structured data (via JSON / JSONB support) but also a variety of other data types, including many numeric types, geometric types, arrays, range types, and date/time types.
-- Storing and querying JSON data in PostgreSQL
SELECT time, sensor_id, type, readings
FROM measurements
ORDER BY time DESC, sensor_id LIMIT 50;
time | sensor_id | type | readings
---------------+-----------+------+----------------------------------
1499789565000 | 330 | 1 | {"occupancy": 0, "lights": 1}
1499789565000 | 440 | 2 | {"temperature": 74.0, "humidity": 0.81}
1499789565000 | 441 | 2 | {"temperature": 72.0, "humidity": 0.78}
1499789560000 | 330 | 1 | {"occupancy": 1, "lights": 1}
1499789560000 | 440 | 2 | {"temperature": 73.9, "humidity": 0.81}
1499789560000 | 441 | 2 | {"temperature": 72.1, "humidity": 0.79}
1499789555000 | 330 | 1 | {"occupancy": 1, "lights": 1}
1499789555000 | 440 | 2 | {"temperature": 73.9, "humidity": 0.80}
1499789555000 | 441 | 2 | {"temperature": 72.1, "humidity": 0.78}
💡 Tips on picking data types in Postgres
6. Geospatial Support for Your IoT DataPostgreSQL with You can run many Postgres extensions in TimescaleDB.
One Powerful extension is PostGIS, and it tackles a core IoT challenge: tracking things through space and time. Fleet vehicles, delivery routes, and mobile assets all need both location and temporal data. Instead of juggling separate systems, these extensions turn PostgreSQL into a spatiotemporal database that handles it all - where things are, where they've been, and when.
7. Plenty of Integration Opportunities
Time-series databases need a rich ecosystem of connectors to tools like Kafka, Spark, and Tableau. While newer databases lack this support, PostgreSQL's 30-year head start means TimescaleDB inherits a vast network of ready-to-use integrations. If PostgreSQL connects to it, TimescaleDB does too.
8. Proven Reliability
IoT databases need to be dependable from day one. A factory line can't pause while you debug database issues at 3 AM. Period.
PostgreSQL has spent 30 years running mission-critical systems, and TimescaleDB inherits this stability along with built-in tools for replication and failover.
Try Timescale Today (Free)
There is a lot of noise in the IoT world right now, and deciding what database to use for your IoT project can be hard.
Sometimes, the best option is the boring option: the database that just works.
That’s PostgreSQL, which now (thanks to Timescale) finally scales to handle IoT workloads and optimizes SQL for time-series data.
If you’re building a new IoT project or currently wrestling with a complex IoT stack, choose PostgreSQL—but faster. Create a free Timescale account.
If you need help with anything, feel free to join our Community on Slack.
Top comments (0)