Choosing Between json and jsonb for Time Series Storage in PostgreSQL

Learn the best way to store time series data in PostgreSQL and why using `scalar` data types is often more effective than `json` or `jsonb`.
---
This video is based on the question https://stackoverflow.com/q/67615836/ asked by the user 'xpanta' ( https://stackoverflow.com/u/356875/ ) and on the answer https://stackoverflow.com/a/67615902/ provided by the user 'Laurenz Albe' ( https://stackoverflow.com/u/6464308/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Need help deciding between json and jsonb regarding time series storage in postgresql

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Choosing Between json and jsonb for Time Series Storage in PostgreSQL

When it comes to storing time series data in PostgreSQL, many developers face a common dilemma: should they opt for json or jsonb? This question arises particularly when data is received in JSON format through HTTP requests, as is the case in many Django applications. In this guide, we will break down this problem and explore the best practices for effective time series data storage in PostgreSQL.

The Problem: Storing Time Series Data

Consider the scenario where you're receiving time series data in JSON format, such as:

[[See Video to Reveal this Text or Code Snippet]]

With approximately 700-800 values per day per device, storing all these values in a single JSON field that can hold up to 1GB seems feasible. However, one crucial requirement is the ability to slice this data based on user-defined intervals, such as specific months or years. The question then arises: is it optimal to pull the entire JSON object for manipulation in the application layer, or should the slicing occur within PostgreSQL?

Understanding json vs jsonb

Before deciding which data type to use, let's understand the differences between the two:

json

Preserves the Key Order: The original order of keys (or timestamps in this case) is maintained.

Less Efficient: It does not support indexing, making retrieval and querying slower when dealing with large datasets.

jsonb

Performance Benefits: It offers better performance for querying and indexing, making it ideal for selecting individual records.

No Key Order Preservation: The order of keys is not guaranteed, which may impact how you retrieve time series data.

The common belief is that jsonb is more efficient and preferred for aggregate calculations, while json is simpler when the maintenance of order is vital. Given the nature of time series data, one could lean toward using json. However, this choice may lead to complications down the line.

The Best Solution: Use Scalar Data Types Instead

While choosing between json and jsonb may seem crucial, there's a more effective solution: storing each datum in its own row within a table using scalar data types.

Benefits of Using Scalar Data Types:

Optimized for Querying: Databases excel at handling tabular data with numerous rows, facilitating quick retrieval and calculations.

Ease of Aggregation: Performing mathematical operations on scalar types provides greater flexibility and speed compared to extracting values from a JSON object.

Simplicity in Design: Maintaining a flat table structure can simplify the application logic, allows for easier index creation, and enhances performance.

Example Table Design:

Consider a simple design with two columns:

timestamp (TIMESTAMP data type)

value (FLOAT data type)

Each time series record could be stored as follows:

[[See Video to Reveal this Text or Code Snippet]]

This design would allow you to quickly query, filter, and aggregate time series data without the overhead associated with JSON types.

Conclusion: Choose Simplicity and Performance

In conclusion, while the choice between json and jsonb may seem significant, the best solution for handling time series data in PostgreSQL is to embrace a more traditional approach: use scalar data types to create a well-structured table. This method ensures performance, simplifies the process of running queries and aggregates, and adheres to the fundamental design principles of relational databases.

When in doubt, remember that databases are optimized for tables, and deviating from this can lead to unexpected challenges in data retrieval and manipulation. By leveraging standard data types, you’ll set yourself up for success i