CODEWITHSUNDEEP
jsonpostgresql
xpanta
xpanta

Reputation: 8418

Need help deciding between json and jsonb regarding time series storage in postgresql

I want to store time series data into postgresql. Data are coming into json packages through an http request (using Django). The format is the following:

{
"2018-01-01 21:44": 53.2,    
"2018-01-01 21:48": 51.7,    
"2018-01-01 21:52": 48.5,    
...
}

I usually get around 700-800 values per day for each registered device. This is not much and I have decided to store all values to one json field (since it can hold data up to 1GB).

However I need to make slices of those data (e.g. from March, 2018 to April 2019, or any other arbitrary - chosen by the user - interval) and I don't think that it is optimal to retrieve the whole json dictionary and make slices in my application. I think slicing should be done in Postgres.

I have read that regarding performance JSONB is better but it does not keep the key sorting (i.e. the timestamps) -- So, I guess JSONB is preferred in cases when the selection of individual records is the priority. Am I right?

Application has some real-time uses (watching data change on a real-time web application) so, performance is critical.

Please, keep in mind that there are no duplicate keys in my timestamped data.

I am inclined towards JSON instead of JSONB. BUt I am not very sure about this.

What do you suggest?

Upvotes: 0

Views: 502

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246798

Both choices are wrong. Your data are tabular, and I guarantee that you will be more happy if you store each datum in its own table row, using normal "scalar" data types.

Databases are optimized for storing tables with large numbers of rows, so don't worry.

JSON will make you unhappy as soon as you want to (for example) calculate aggregates on all these values.

Upvotes: 3

Related Questions