31

I read about HStores in Postgres something that is offered by Redis as well.

Our application is written in NodeJS. Two questions:

  • Performance-wise, is Postgres HStore comparable to Redis?

  • for session storage, what would you recommend--Redis, or Postgres with some other kind of data type (like HStore, or maybe even the usual relational table)? And how bad is one option vs the other?

Another constraint, is that we will need to use the data that is already in PostgreSQL and combine it with the active sessions (which we aren't sure where to store at this point, if in Redis or PostgreSQL).

From what we have read, we have been pointed out to use Redis as a Session manager, but due to the PostgreSQL constraint, we are not sure how to combine both and the possible performance issues that may arise.

Thanks!

Sridhar Ratnakumar
  • 81,433
  • 63
  • 146
  • 187
jribeiro
  • 3,387
  • 8
  • 43
  • 70

2 Answers2

34

Redis will be faster than Postgres because Pg offers reliability guarantees on your data (when the transaction is committed, it is guaranteed to be on disk), whereas Redis has a concept of writing to disk when it feels like it, so shouldn't be used for critical data.

Redis seems like a good option for your session data, or heck even store in a cookie or in your client side Javascript. But if you need data from your database on every request then it might not be even worth involving Redis. It very much depends on your application.

Matt Sergeant
  • 2,762
  • 16
  • 12
  • 4
    Just a little comment: If you don't need the reliability guarantee with Postgres (risk of lost transactions in case of a crash but no risk of data corruption in case of a crash) and speed is more important to you, you may want to test turning off the 'synchronous_commit' setting within postgres. This may help with a lot of small writes but won't help with reads. – stereoscott Sep 19 '12 at 20:06
  • 3
    Redis has transactions, but the guarantee is that the whole transacted set of operations is committed to memory. There are also config options for the frequency of the data write-through onto the disk. – Evgeny Mar 03 '13 at 16:17
  • 1
    I finally understood that Redis is a completely new concept of database as a whole. So new that many people -_like me_- doesn't understand it the first moments. It does require time to really catch Redis, when you're used to Postgres. It is a true contemporary database. Saying that it is simply a cookie/session store is underestimating Redis. So new that many persons give opinions about it without really knowing. We use it in a non web project as a databank for millions of data and it does the job well! When it comes to reliability, no software in the world is 100% reliable, not even Postgres. – 1111161171159459134 Sep 15 '15 at 21:59
  • Here we prove this with actual stats: https://norman-lm-fung.medium.com/performance-redis-vs-postgres-e6df8f51f1b5 – user3761555 Jul 19 '21 at 03:43
  • Sample size one row: _publishToRedis took 0:00:00.024909 for SAMPLE_SIZE = 1 _saveToPostgres took 0:00:00.475719 for SAMPLE_SIZE = 1 Sample size a million rows: _publishToRedis took 0:00:00.064457 for SAMPLE_SIZE = 1000000 _saveToPostgres took 0:00:32.407136 for SAMPLE_SIZE = 1000000 – user3761555 Jul 19 '21 at 03:43
6

Using PostgreSQL as session manager is usually bad idea.

For older than 9.1 was physical limit of transaction per second based on persistent media parameters. For session management you usually don't need MGA (because there are not collision) and it means so MGA is overhead and databases without MGA and ACID must be significantly faster (10 or 100).

I know a use case, where PostgreSQL was used for session management and Performance was really terrible and unstable - it was eshop with about 10000 living sessions. When session management was moved to memcached, then performance and stability was significantly increased. PostgreSQL can be used for 100 living session without problem probably. For higher numbers there are better tools.

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • thanks! we actually were leaning towards redis but, as said above, we're unsure on how to query postgres to return info from active users in redis... any tip? – jribeiro Feb 05 '12 at 21:36
  • 2
    You can use memcache instead of redis for session handling. It's working very well https://github.com/elbart/node-memcache – Marek Tuchalski Feb 06 '12 at 17:19
  • @MarekTuchalski Hi! Sorry for the delay and thanks for your answer. My question remains... How would I connect data from memcached to postgres? I would have to iterate through all results and pass them to postgres right? That sounds like pain... – jribeiro Feb 14 '12 at 17:50
  • It should not be a problem - depends on size of dataset. You can use a plperlu functions for access to memcached from PostgreSQL – Pavel Stehule Feb 14 '12 at 19:03
  • 1
    Please explain why PostgreSQL would be a bad choice for a session manager. Are there cases when it would make sense to use it? – justis Feb 20 '12 at 01:38
  • 1
    Session manager doesn't need a transaction support - the changes should not be persistent - but PostgreSQL cannot to work without transaction (9.1 provides UNLOGGED tables that are cheeper). – Pavel Stehule Feb 22 '12 at 18:25