Is it common for people to use PostGres for a key-value store in production (rather than redis)?. This is the first time I have heard of it, and I am just starting to use PostGres now, so I was a bit surprised
Redis doesn't have a great solution for the D(urability) in ACID yet. PostgreSQL can ensure that changes are durable while being performant; redis can't do that as well.
Redis does offer AOS for durability, but it's not nearly as mature as PostgreSQL (and comes with all sorts of caveats).
Depends what other features you need. redis will be better if you need to do things like time-based collections, sorting by z-score, etc. Postgres gives you mature clustering (at least for master-slave), a mature tooling ecosystem (PgAdmin has been around for awhile, I didn't say it was good), better access controls, etc.
Like anything else in engineering, it's all a series of trade-offs to figure out what fits your needs the best.
We're trying out Postgres as a partially schema-less store.
We store arbitrary JSON in some fields, and even build indexes on that data. It's been remarkably fast so far, and other than a few small gotchas relating to type conversions and indexing, is really easy to use.
For very good engineering reasons, Redis is limited to what it can keep in memory. Postgres, for equally good reasons, can use disk. That makes a difference if you have a lot of key-value pairs to store, and are willing to accept a few disk seeks.
It's still relatively new functionality, so I wouldn't expect to see it in wide use, but we're currently trying it out in a limited, point-solution kind of role. (We're a Postgres-mostly shop already.)
So far, everything's working as well as I'd have expected from something released by the PostgreSQL community.
I'm talking about the JSON-specific functionality, which tends to make doing JSON-based, document-oriented, key-value-ish things in PostgreSQL significantly better, easier, faster, whatever-er.
Yes, in general it is pretty common for web developers to misuse relational databases as key-value stores. There's a common misconception that "joins are slow", so people write the joins in their application code instead, thus making it several orders of magnitude slower.
I didn't mean people using hstore, I mean people having relational data, putting it in tables like you would with a normal relational model, but just not using foreign keys and joins because they mistakenly think "joins are slow".
Comments
Is it common for people to use PostGres for a key-value store in production (rather than redis)?. This is the first time I have heard of it, and I am just starting to use PostGres now, so I was a bit surprised
Redis doesn't have a great solution for the D(urability) in ACID yet. PostgreSQL can ensure that changes are durable while being performant; redis can't do that as well.
Redis does offer AOS for durability, but it's not nearly as mature as PostgreSQL (and comes with all sorts of caveats).
Depends what other features you need. redis will be better if you need to do things like time-based collections, sorting by z-score, etc. Postgres gives you mature clustering (at least for master-slave), a mature tooling ecosystem (PgAdmin has been around for awhile, I didn't say it was good), better access controls, etc.
Like anything else in engineering, it's all a series of trade-offs to figure out what fits your needs the best.
We're trying out Postgres as a partially schema-less store.
We store arbitrary JSON in some fields, and even build indexes on that data. It's been remarkably fast so far, and other than a few small gotchas relating to type conversions and indexing, is really easy to use.
For very good engineering reasons, Redis is limited to what it can keep in memory. Postgres, for equally good reasons, can use disk. That makes a difference if you have a lot of key-value pairs to store, and are willing to accept a few disk seeks.
It's still relatively new functionality, so I wouldn't expect to see it in wide use, but we're currently trying it out in a limited, point-solution kind of role. (We're a Postgres-mostly shop already.)
So far, everything's working as well as I'd have expected from something released by the PostgreSQL community.
The functionality was always there:
Or are you referring to hstore?I'm talking about the JSON-specific functionality, which tends to make doing JSON-based, document-oriented, key-value-ish things in PostgreSQL significantly better, easier, faster, whatever-er.
And this is how reddit does it, reddit doesn't use hstore.
Yes, in general it is pretty common for web developers to misuse relational databases as key-value stores. There's a common misconception that "joins are slow", so people write the joins in their application code instead, thus making it several orders of magnitude slower.
In the case of Postgres, it has indexable json and hstore datatypes that enable the use of a table as a key-value store.
And, it has good performance when compared to MongoDB: http://thebuild.com/presentations/pg-as-nosql-pgday-fosdem-2...
So in this case, it is using Postgres as intended by the datatype designers.
May be you are thinking of the Ruby Rails ORMs.
I didn't mean people using hstore, I mean people having relational data, putting it in tables like you would with a normal relational model, but just not using foreign keys and joins because they mistakenly think "joins are slow".