CODEWITHSUNDEEP
postgresqlredis
sgon00
sgon00

Reputation: 5827

Store IDs in Redis sorted set and then select value from Postgresql?

for better pagination performance, instead of doing this in postgresql:

SELECT * FROM message ORDER BY created_at DESC limit 30 offset 30;

Can I store the IDs in redis sorted set and then simply get the IDs from redis by:

ZREVRANGE message_ids 30 39

And then query those IDs in the postgresql to get values.

The reason why I don't store value directly in Redis is because the value can be big and RAM is expensive. (I don't have enough RAM).

If this is recommened or OK to do to improve the performance, how can I query the values from postgresql by IDs properly?

I found the following ways and not sure if this is the best approach:

SELECT m.*
FROM   unnest('{17579, 17580, 17582}'::int[]) id
JOIN   message m USING (id);

OR simply:

SELECT * FROM message WHERE id IN (17579, 17580, 17582);

The above query is from this link

Btw, I am also not sure if the above command will give me the correct order based on the order of id list and whether or not I need a ORDER BY id in the end.

In summary, will this redis+postgresql solution be much faster than only postgresql solution?

Upvotes: 2

Views: 1441

Answers (1)

Anuvrat Parashar
Anuvrat Parashar

Reputation: 3120

In summary, will this redis+postgresql solution be much faster than only postgresql solution?

In my opinion, the end result will not be worth the effort / complexity that you would introduce into the system.

Databases offer very good performance for such queries, provided you index the fields you are querying on and select only the fields you need.

In your proposed solution following would happen while the application is trying to serve the page (its a web application right?):

  • tcp call to redis to fetch ids
  • frame sql query with the ids
  • sql query over another tcp call.

In comparison, there is only one sql query being sent to the database if we choose the straight forward way.

Meanwhile, also consider the development effort involved in ensuring that the ids fed into redis stays consistent with the database server.

That said, if your database server is under heavy load and you need to take some of the burden off its shoulders you may consider including elasticsearch / solr into your application stack.

  • You will still have to ensure consistency between database and elasicsearch / solr. And if your use case permits you may be bale to do that with a background job.
  • Your read requests will rarely hit your database server.
  • Elasticsearch / solr use the hard disk to store data: excessive amounts of ram will not be required.

You may also consider setting up replication for postgresql, to distribute the load that only one database server may not be able to handle.

Upvotes: 3

Related Questions