Comment by skywhopper | Hacker News Reader

Comment by skywhopper | Hacker News Reader

Alas, there's not enough context or code here to even really know what's going on (or maybe I just don't know Django well enough to guess at what isn't said). It sounds like the author is comparing storing and retrieving a record with a JSON field in a PostgreSQL table versus storing and retrieving a raw string (the raw JSON) in Redis?

So it's no surprise that the Redis solution is faster. Besides parsing and indexing the data, PostgreSQL will also prioritize reliability and won't confirm your transaction until it's actually persisted to disk. Whereas Redis is prioritizing speed, and working straight to RAM--the disk serialization is an asynchronous process. If anything, I'm surprised that the Redis solution is _only_ 16-20x faster than PostgreSQL given what appears to be happening.

So, for this use case--storing a cache of Amazon API results for mostly-static data--the Redis solution is a no-brainer: durability of the data is not a serious concern; there's no real risk of data corruption because there's only one schema and no interrelations; you don't need the data store to know anything about the data; you don't need indexing beyond straight key-value. For other use cases, Redis might be entirely unsuitable because of durability or data integrity reasons, in which case the speed benefits wouldn't even matter.

Now, all that said, in the real world there are lots of other considerations: Are you using either of these databases already for some other purpose in the same app? Which data store is easier to set up? Which is easier to code against? Do you even care how fast it is if the slower speed is 11ms on a single-user system?

I found the thinking in the article to be mushy.

The title asks a reasonably specific question, but the article then goes on to talk about things in ill-defined ways. It mixes in other layers which aren't directly part of the titular question, but nonetheless influence the answer. In an update, the author goes so far as to state that a new test done differently resulted in a different answer. As you point out... very little relevant detail is included for PostgreSQL (which I know well) and I'm sure insufficient detail for Redis (which I don't know well)... certainly not enough detail and well reasoned testing to merit a public posting on the subject.

That's not to say that the gist of the article is incorrect or unexpected... I would expect a well configured Redis to perform better than PostgreSQL for reading and writing JSON data. But there's nothing I can really learn here that makes clear differences with well understood external influences.

The article is just anecdote, not really good testing.

The article should be titled something along the lines of, "How much faster is my way of using Redis at storing JSON than my way of using PostgreSQL?". Perhaps "Medium" would have been a more appropriate venue as well.... ;-)

> The article is just anecdote, not really good testing.

Mayhaps. Or, just a snapshot from the trenches of real application development.

We all have to make choices. Different pros and cons. Before jumping into, perhaps run some tests with real stuff and change direction based on that.

In application development is usually doesn't matter what the core difference is between two databases because there are drivers and mappers that you more or less have to use. I.e. the Django ORM makes PostgreSQL convenient.

Normally, I would agree that it's a snapshot or data point. But one of the things that I don't see mentioned anywhere else is that this is running on a macbook. I don't know how it works with his setup, but I get significantly different performance results on my local dev system (windows) than a linux server.

I run everything in hyper-v docker, which should actually get pretty close to bare-metal performance (as hyper-v sort of runs an instance alongside windows, rather than virtualized on top).

However, due to some quirkyness, maybe something with docker networking, I rarely get very good performance and I assume in this case, they are either using ported versions of the software or also running virtualized.

So I'm not sure if these kind of benchmarks add data points to anything but "performance on a dev machine".

Then cat your data to /dev/null because it will blow both Redis and PostgreSQL out of the water when it comes to benchmarks.

Point being they serve very different purposes, it's not about benchmarks.

I'm glad you wrote this article because I think people should put themselves out there more (myself included). By doing so, you open yourself up to feedback, both positive and negative, that can help you improve. You have a good example of this with your 9/29 update.

In the spirit of constructive criticism, I second the "not really good testing" feedback, mostly due to the lack of tuning effort and omission of performance-affecting details like configuration, the wire protocols used, how data is being serialized/deserializaed, etc. (though I am no expert with these technologies so some of this may have been implied). All of that may have been overkill for what you were trying to do, but it's what people look for in benchmarking / "good testing". I hope this helps.

A reasonably configured Postgres is easily sub-millisecond for reads and writes on a small dataset like this. I suspect that the author is doing something quite wrong.

People generally reach for Redis too quickly, IMO. Postgres is about as fast, has better tooling, and will slow down gracefully as the dataset increases, rather than evicting records as the limit is hit.

That said, I guess if you're relatively junior, it's hard to grossly misconfigure Redis, so that's a point in its favor. Somewhat like Java vs Node.js--you can write higher performance network services in Java, but it's a lot easier to make mistakes.

> That said, I guess if you're relatively junior, it's hard to grossly misconfigure Redis, so that's a point in its favor.

Redis persistence (or possibly lack-thereof) is probably quite complicated for a junior developer to understand. I'd actually expect a better outcome with PSQL.

I disagree. But anytime I've been exposed to redis, it's always been as an ephemeral data store.

Not sure what you are getting at here.

Redis is one of the simplest data stores around to use and its persistence is solid.

> Redis is one of the simplest data stores around to use and its persistence is solid.

I've lost countless amount of data in production systems running Redis. Haven't ever lost anything in Postgres. (I still run both.)

Isn’t the fundamental difference also use case? You would have to use Postgres if you need actual durability. AFAIK, one outage and your redis cache is gone... unless you’re running an HA cluster (which I dunno if that’s offered by redis). But even that doesn’t mean you have durability.

In general caching systems are volatile and not meant to be used to store data that cannot be afforded to be lost. Of course that doesn't stop clients from storing critical data on there in return for the simple GET/PUT APIs and low latencies.

You can go for more complex caches such as Hazelcast which offers hot restarts by persisting data to disk.

Redis can be configured as `appendonly yes; appendfsync always`, which the Redis docs say is “very very slow”, but really would just make Redis work the way Postgres does with its WAL, with probably equivalent performance (or still slightly better, since there’s no checkpointing going on.)

You have no evidence that:

a) PostgreSQL has sub-millisecond read/writes for storing JSON blobs than Redis.

b) Java results in buggier code than Javascript.

If you're going to make bold claims the onus is on you to provide evidence to back it up.

There’s

  class AmazonAffiliateLookup(models.Model, TotalCountMixin)

I guess that TotalCountMixin adds some triggers that maintain a count of the records in the table in a separate table (could or could not be a good idea, depending on the table size, relative frequency of ‘give me the number of items’ calls relative to inserts and deletes, etc, but might introduce a bottleneck on updating that count)

There also is:

  song = models.ForeignKey(Song, on_delete=models.CASCADE)

I don’t understand why that foreign key is there, but it can’t speed up inserts and deletes.

Then, we have:

  created = models.DateTimeField(auto_now_add=True, db_index=True)

I think creating an index is done to implement the equivalent of Redis’ auto-expiry.

Again, that may be suboptimal if there are few records in the database. DateTimeField also may be ‘too structured’ for the task at hand (it might needlessly parse year/month/day out of time stamp in nanoseconds, for example, or even do time zone calculations to convert a local time to UTC), but I don’t know. It would be helpful if the text showed the actual SQL DDL.

Probably, hard to tell, the biggest difference, is Redis is likely storing the JSON as a string, and Posgres is storing it as binary json (separated out into distinct values/collections). I'd guess if Postgres was using UTF8 text for the JSON it would be closer. Also, Postgres is probably a bit less efficient on persistance requirements in favor of higher stability and preventing data loss.

In any case, Redis is probably a more natural and better fit (remote API result caching). That said, the comparison is awkward at best. If it were to cache localized results, where the dbms (PostgreSQL) is the source of truth, then adding a caching layer above the db may or may not be worth it.

I think the performance comparisons come down to Granny Smith Apples vs Rome Apples... you can bake a pie with either, but they don't taste the same.

I guess the answer to does apple smell more apple than orange is yes

As he chose to show us Django ORM code, instead of the postgres table definition, I'm guessing he's not exactly a postgres wizard. It reads to me like to me like he's writing to a normalized table (complete with foreign key constraints and likely indexes) and comparing against writing a single serialized blob to to a Redis key.

I too would expect the performance improvement to be significantly faster. This guy probably doesn't know how to benchmark his code, either.

Another way to look at this is that he's using Postgres the way most people use Postgres + Django.

I likewise suspect the FK and the index on the created timestamp account for most of the difference.

I've used Postgres in production since 2002 and speaker that PgOpen. But I'm not an expert. I never will be because I make things with tools. What do you do?

Try not to take it personally. With relevant details not made clear, people have to try to fill in the blanks. Wondering about the table definition is definitely understandable.

Personally, I try to understand at least conceptually how the underlying data store connects and serializes the data in question. I'm not a fan of ORM tooling, quite frankly and find it often gets in the way more than helps.

I tend to track versioned changes in scripts, with a config table with a lock and a record of which the current version is, locking and upgrading the db on api service startup. It tends to be very solid, and doesn't rely on potentially unexpected behaviors. At work, a project I'm on uses SQL Server projects which has had a great many issues tracking versions. I've also used several ORM tools across differing platforms and they all have issues. More often than not, I'd rather do things the "hard" way. It's a little more initial effort, but saves in the long run.

Beyond this, without understanding how the data is persisted, it's hard to compare equally. If you used pgsql how you used Redis, I'd be surprised if it was more than 5x as slow. There's also less risk of data loss with PostgreSQL. In this case, data caching foreign api results, Redis is probably the more appropriate tool anyway, but the comparisons in TFA are muddy at best.

"As he chose to show us C++ code, instead of the assembly code, I'm guess he's not exactly a compiler expert."

Reply on news.ycombinator.com