How much faster is Redis at storing a blob of JSON compared to Postgres? (2019)
peterbe.com
peterbe.com
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.
"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.
Peter Bengtsson 02 October 2019
Thanks for that mansplaining. I had no idea that there are more nuances to this.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.
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.
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.
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?
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?