4 ms·

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 start

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

mason55 13y ago
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.
falcolas 13y ago
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).
rosser 13y ago
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.
- yummyfajitas 13y ago
  The functionality was always there: CREATE TABLE kvstore ( key VARCHAR(128) NOT NULL UNIQUE, value VARCHAR(128) NOT NULL ); Or are you referring to hstore?
  - ketralnis 13y ago
    And this is how reddit does it, reddit doesn't use hstore.
  - rosser 13y ago
    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.
asdasf 13y ago
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.
- Shorel 13y ago
  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-2013.pdf 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.
  - asdasf 13y ago
    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".
jeffasinger 13y ago
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.
pjscott 13y ago
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.

mason55 13y ago

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.

falcolas 13y ago

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).

rosser 13y ago

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.

yummyfajitas 13y ago
The functionality was always there: CREATE TABLE kvstore ( key VARCHAR(128) NOT NULL UNIQUE, value VARCHAR(128) NOT NULL ); Or are you referring to hstore?
- ketralnis 13y ago
  And this is how reddit does it, reddit doesn't use hstore.
- rosser 13y ago
  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.

asdasf 13y ago

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.

Shorel 13y ago
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-2013.pdf 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.
- asdasf 13y ago
  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".

jeffasinger 13y ago

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.

pjscott 13y ago

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.