Craig Kerstiens: Postgres + Citus + Partman, Your IoT Database

Postgres is a robust data platform. Yes, it's more than a boring old relational database. It has rich indexing, data types (including JSON), and so much more. It also has support for a variety of extensions that can further broaden it's already great functionality. Two of those extensions when coupled together make Postgres a very compelling approach for IoT architectures. Today we're going to start from the ground up on how you would design your architecture with Postgres along with the Citus and pg_partman extensions. Citus and sharding Citus is an extension that allows you to shard your database across multiple nodes, while allowing your application to remain largely unaware. Citus can be beneficial to your app if: You expect to outgrow the performance a single Postgres instance can deliver Your schema/data model can be mapped cleanly to Citus The queries/workload pattern can be mapped cleanly to Citus Lucky for us, IOT workloads check the boxes for all of the above. Starting with our IoT dataset We're going to begin with a simple schema that relates to vehicles and tracks a few basic measurements against them. We'll also have a table that tracks the location of the vehicle at the time of each sensor sampling as well. CREATE TABLE sensor_data ( id SERIAL, car_id VARCHAR(17) NOT NULL, sensor_type VARCHAR(20) NOT NULL, sensor_value INT NOT NULL, timestamp TIMESTAMP WITH TIME ZONE NOT NULL ); CREATE TABLE location_data ( id SERIAL, car_id VARCHAR(17) NOT NULL, latitude float8, longitude float8, timestamp TIMESTAMP WITH TIME ZONE NOT NULL ); While our above schema is simply it's not unrealistic of many IoT data models, though yours could be more complex. How do we shard this dataset? The key to sharding is that you can push down most of your joins to the node where the data is located. If you're having to move data in between nodes in order to join your performance will suffer. In the case of IoT workloads device_id is a very common pattern[...]