To be fair to PostgreSQL, one should probably highlight the following comment left by "Anonymous" at the bottom of the blog post:
This seems like an apples and oranges comparison. You're using the JSONB type in postgres, which allows you to index and query specific fields in the blob, and your testing doesn't seem to be doing that at all. A fairer comparison would be to use postgres' text type, or a separate comparison that does query for specific fields with both.
Worth including authors response to that comment (which completely ignores the suggestion)
"Peter Bengtsson 02 October 2019
Yes, it is apples and oranges but they are both things you can use for a juicy and healthy snack. You don't have to turn the apple into an orange (or the other way around) but it's about understanding the pros and cons of the apples and the pros and cons of the oranges. Then, equipped with that you can make informed decisions."
Seriously, that's the answer?
Really sad that blog post from this author made it to the front page of HN.
That's a great way of shutting down discussion without having to address any of the points AND has the advantage that it looks like you know all the subtleties.
The only downside of this technique is that people become less likely to collaborate or help you -- but who needs help when you have great techniques like this to fall back on?
"""
For example, the PostgreSQL speeds depend on the Django ORM code that makes the SQL and sends the query and then turns it into the model instance. I don't know what the proportions are between that and the actual bytes-from-PG's-disk times. But I'm not sure I care either. The tooling around the database is inevitable mostly and it's what matters to users.
"""
I'd say it's pretty important to know what proportion is taken up by the ORM when comparing the speed of two DBs. It's not clear to me from the article if the redis implementation even uses an ORM.
Around 10ms for trivial reads in Postgres feels a bit slow to me, if the amount of data in the blob is small and the database (or just this table) fits into RAM. Not sure where exactly the overhead is in this case, but I wouldn't attribute it to Postgres alone without further investigation.
Even though Redis might be 16 times faster than PostgreSQL at reading JSON blobs, it doesn't mean that it is using 16 times less CPU doing so.
A while ago I benchmarked Redis vs PostgreSQL and I was initially fooled by the difference in query time. But after a deeper investigation, I realised that, even though each individual PostgreSQL request was slower, the maximum request per second when the CPU is maxed to 100% usage was pretty similar between Redis and PostgreSQL. I was benchmarking a count query with a multi-column partial index, so this might not translate at all for reading json blobs. But I think with those kind of bechmarks, one need to be very careful and shouldn't jump to conlusions that easily.
If the authors concern really is the 10ms difference it makes for each individual request, then the benchmark makes sense. But if the concern is to make the best out his server's hardware, I think his benchmark needs more work. no conclusion can be drawn at this point.
Knee-jerk reaction: this is a strange/unfair comparison of two datastores.
Slightly more considered reaction: ok, this is a microbenchmark comparison across two different datastores.
Acceptance: sure, in scenarios where request volume is particularly large and/or performance constraints are tight, this could be valuable to refer to.
Reflection: benchmarking and time spent evaluating technical systems: how much work do we (as an industry) collectively duplicate reacting to articles, systems and concepts that we haven't put in the work to read and understand? And do we learn effectively after-the-fact?
Second although people critize the comparision, I do think many people would use PG with an ORM and a JSONB data type, not with a TEXT and async direct SQL query. But this also means it is not comparing Redis to PG but people.
In addition to other complaints about this article (old, pretty shallow, testing methodology etc), one thing caught my eye:
Title: "How much faster is Redis at storing a blob of JSON compared to Postgres?"
First sentence: "tl;dr; Redis is 16 times faster at reading these JSON blobs."
I know there are numbers for "writing" as well (20x), but this
is just poor writing (pun intended).
What the hell HN, how come stuff like this bubbles up to the front page?
I've become familiar with this through the work I do on a webapp that processes and displays environmental and meteorological data for farmers and researchers.
For many of the monitoring sites the app manages, there could be 10-15 metrics (e.g., air temperature, wind speed/direction, soil moisture, rainfall, humidity and assorted others), with time series data at 10 minute intervals over several years, all stored in a single object to allow for fast updating of graphs, tables, alerts, etc.
I found that writing/reading it in/out of the Postgres database was too slow (given that new data comes in every 10 minutes and everything needs to be updated each time), so instead I use JSON->Redis as the primary storage layer, which is very fast, then periodically save it to Postgres (text column) via async queue for persistence.
It's very easy to work with, and it's allowed us to deliver an app that is very fast-updating and much faster for end-users to view data than any of our competitors' products.
The first version worked that way, and I struggled with that architecture for the first ~3 years, but I moved away from it for two reasons:
- Unpredictability of required data attributes; It turns out there's an effectively infinite number of different metrics that users may want to monitor, and different numbers of each type of metric on any given monitoring site. E.g., users may want to monitor anywhere between 1 and 30 soil moisture readings (different depths from different probes). This year, seven years into the project, we started supporting sap flow and dendrometer (stem width) readings. I didn't know these existed even two years ago. Next year we'll likely support new types of data that we've never seen before. So it's impractical to add new columns for each data type.
- Performance: It's just too slow to write a database row for each reading (sometimes data is uploaded in batches of 1000+ samples), then query and transform at display time. It's so much faster - both computationally and in the writing of the code - to just put everything into a big hash object and store it in Redis.
But when I look at competitors' products and see the limitations they have in their app's data display performance and their ability to support different kinds of input devices, I can tell they seem to be stuck with these issues, likely due to being beholden to SQL tables for their primary data storage.
Interesting, thanks for your reply. How do you deal with timezones? In our case, for our default dashboard view, we could pre-aggregate everything per hour but often daily, weekly, monthly denominations are needed, which is where the timezone comes into play. Do you pre-aggregate every possible timezone or just update the views for each user individually?
The time series data is stored as arrays of two-item arrays: [UTC-timestamp-in-ms, value].
We have some aggregated and calculated displays by day/week/month/etc, and these are updated/cached when new data is uploaded, which normally only takes a few seconds or less for each view.
Data is shown to end-users in the timezone where the monitoring site is located, which normally is moot as the user looking at the data is located in the same timezone, but where they are not, the timezone is clearly identified on the graph/table.
21 comments
[ 4.6 ms ] story [ 52.5 ms ] threadThis seems like an apples and oranges comparison. You're using the JSONB type in postgres, which allows you to index and query specific fields in the blob, and your testing doesn't seem to be doing that at all. A fairer comparison would be to use postgres' text type, or a separate comparison that does query for specific fields with both.
"Peter Bengtsson 02 October 2019
Yes, it is apples and oranges but they are both things you can use for a juicy and healthy snack. You don't have to turn the apple into an orange (or the other way around) but it's about understanding the pros and cons of the apples and the pros and cons of the oranges. Then, equipped with that you can make informed decisions."
Seriously, that's the answer?
Really sad that blog post from this author made it to the front page of HN.
The only downside of this technique is that people become less likely to collaborate or help you -- but who needs help when you have great techniques like this to fall back on?
""" For example, the PostgreSQL speeds depend on the Django ORM code that makes the SQL and sends the query and then turns it into the model instance. I don't know what the proportions are between that and the actual bytes-from-PG's-disk times. But I'm not sure I care either. The tooling around the database is inevitable mostly and it's what matters to users. """
I'd say it's pretty important to know what proportion is taken up by the ORM when comparing the speed of two DBs. It's not clear to me from the article if the redis implementation even uses an ORM.
A while ago I benchmarked Redis vs PostgreSQL and I was initially fooled by the difference in query time. But after a deeper investigation, I realised that, even though each individual PostgreSQL request was slower, the maximum request per second when the CPU is maxed to 100% usage was pretty similar between Redis and PostgreSQL. I was benchmarking a count query with a multi-column partial index, so this might not translate at all for reading json blobs. But I think with those kind of bechmarks, one need to be very careful and shouldn't jump to conlusions that easily.
If the authors concern really is the 10ms difference it makes for each individual request, then the benchmark makes sense. But if the concern is to make the best out his server's hardware, I think his benchmark needs more work. no conclusion can be drawn at this point.
Slightly more considered reaction: ok, this is a microbenchmark comparison across two different datastores.
Acceptance: sure, in scenarios where request volume is particularly large and/or performance constraints are tight, this could be valuable to refer to.
Reflection: benchmarking and time spent evaluating technical systems: how much work do we (as an industry) collectively duplicate reacting to articles, systems and concepts that we haven't put in the work to read and understand? And do we learn effectively after-the-fact?
Second although people critize the comparision, I do think many people would use PG with an ORM and a JSONB data type, not with a TEXT and async direct SQL query. But this also means it is not comparing Redis to PG but people.
Title: "How much faster is Redis at storing a blob of JSON compared to Postgres?" First sentence: "tl;dr; Redis is 16 times faster at reading these JSON blobs."
I know there are numbers for "writing" as well (20x), but this is just poor writing (pun intended).
What the hell HN, how come stuff like this bubbles up to the front page?
For many of the monitoring sites the app manages, there could be 10-15 metrics (e.g., air temperature, wind speed/direction, soil moisture, rainfall, humidity and assorted others), with time series data at 10 minute intervals over several years, all stored in a single object to allow for fast updating of graphs, tables, alerts, etc.
I found that writing/reading it in/out of the Postgres database was too slow (given that new data comes in every 10 minutes and everything needs to be updated each time), so instead I use JSON->Redis as the primary storage layer, which is very fast, then periodically save it to Postgres (text column) via async queue for persistence.
It's very easy to work with, and it's allowed us to deliver an app that is very fast-updating and much faster for end-users to view data than any of our competitors' products.
- Unpredictability of required data attributes; It turns out there's an effectively infinite number of different metrics that users may want to monitor, and different numbers of each type of metric on any given monitoring site. E.g., users may want to monitor anywhere between 1 and 30 soil moisture readings (different depths from different probes). This year, seven years into the project, we started supporting sap flow and dendrometer (stem width) readings. I didn't know these existed even two years ago. Next year we'll likely support new types of data that we've never seen before. So it's impractical to add new columns for each data type.
- Performance: It's just too slow to write a database row for each reading (sometimes data is uploaded in batches of 1000+ samples), then query and transform at display time. It's so much faster - both computationally and in the writing of the code - to just put everything into a big hash object and store it in Redis.
But when I look at competitors' products and see the limitations they have in their app's data display performance and their ability to support different kinds of input devices, I can tell they seem to be stuck with these issues, likely due to being beholden to SQL tables for their primary data storage.
The time series data is stored as arrays of two-item arrays: [UTC-timestamp-in-ms, value].
We have some aggregated and calculated displays by day/week/month/etc, and these are updated/cached when new data is uploaded, which normally only takes a few seconds or less for each view.
Data is shown to end-users in the timezone where the monitoring site is located, which normally is moot as the user looking at the data is located in the same timezone, but where they are not, the timezone is clearly identified on the graph/table.