Thread: [GENERAL] Fastest simple key-value store, multiple writers, like Redis?
Hi,
I'm working on a project with multiple different data storage backends. I'd like to consolidate and use Postgres for more things. In one new situation we're starting to use Redis, thinking it will perform better than Postgres for a table that is updated a lot by concurrent background jobs.
I'm skeptical of no-sql stuff for various reasons. I'm wondering what PG experts think -- is there is a way to configure Postgres to handle a table differently, so that it could compete with Redis? Or are there some workloads where it is definitely better to use an alternative data store?
This table will have a few million rows, five small columns. Rows will be updated, read, or inserted 5-10 million times a day, by concurrent processes. It operates like a key-value store in that most selects will be getting one row, and maybe updating that row. Ideally these processes could work without stepping on each other's toes and competing for locks.
Rob
On 2 February 2017 at 19:00, Rob Nikander <rob.nikander@gmail.com> wrote: > I'm working on a project with multiple different data storage backends. I'd > like to consolidate and use Postgres for more things. In one new situation > we're starting to use Redis, thinking it will perform better than Postgres > for a table that is updated a lot by concurrent background jobs. > > I'm skeptical of no-sql stuff for various reasons. I'm wondering what PG > experts think -- is there is a way to configure Postgres to handle a table > differently, so that it could compete with Redis? Or are there some > workloads where it is definitely better to use an alternative data store? > > This table will have a few million rows, five small columns. Rows will be > updated, read, or inserted 5-10 million times a day, by concurrent > processes. It operates like a key-value store in that most selects will be > getting one row, and maybe updating that row. Ideally these processes could > work without stepping on each other's toes and competing for locks. Difficult to know precisely. What I encourage you to do is write a benchmark that is representative of what you are trying to achieve, using the pgbench utility's ability to handle custom scripts. https://www.postgresql.org/docs/devel/static/pgbench.html Please post it to the project as a submission that we can then use for performance discussions. No need to use precise business terms when you name tables and columns, keep it generic. You'll get a precise measurement of whether it works for you. And the project will get a representative test case that we can understand and tune for. And if everyone does that we'll get a set of use cases that will help demonstrate our performance and to allow people to discuss improvements. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: [GENERAL] Fastest simple key-value store, multiple writers, like Redis?
From
Khalil Khamlichi
Date:
You can keep using redis and use FDW to access it as if it was a postgres table.
It does read and write to redis and can present redis data as regular tables to you.
I never really had to choose between redis and postgres. Postgres was always for safety and persistance and redis for sharing data and for providing realtime services with fast access to data.
On Feb 2, 2017 7:02 PM, "Rob Nikander" <rob.nikander@gmail.com> wrote:
Hi,I'm working on a project with multiple different data storage backends. I'd like to consolidate and use Postgres for more things. In one new situation we're starting to use Redis, thinking it will perform better than Postgres for a table that is updated a lot by concurrent background jobs.I'm skeptical of no-sql stuff for various reasons. I'm wondering what PG experts think -- is there is a way to configure Postgres to handle a table differently, so that it could compete with Redis? Or are there some workloads where it is definitely better to use an alternative data store?This table will have a few million rows, five small columns. Rows will be updated, read, or inserted 5-10 million times a day, by concurrent processes. It operates like a key-value store in that most selects will be getting one row, and maybe updating that row. Ideally these processes could work without stepping on each other's toes and competing for locks.Rob
Le 02 févr. 2017 à 20:00, Rob Nikander écrivait : > Hi, > > I'm working on a project with multiple different data storage backends. I'd > like to consolidate and use Postgres for more things. In one new situation > we're starting to use Redis, thinking it will perform better than Postgres for > a table that is updated a lot by concurrent background jobs. > > I'm skeptical of no-sql stuff for various reasons. I'm wondering what PG > experts think -- is there is a way to configure Postgres to handle a table > differently, so that it could compete with Redis? Or are there some workloads > where it is definitely better to use an alternative data store? > > This table will have a few million rows, five small columns. Rows will be > updated, read, or inserted 5-10 million times a day, by concurrent processes. > It operates like a key-value store in that most selects will be getting one > row, and maybe updating that row. Ideally these processes could work without > stepping on each other's toes and competing for locks. > > Rob > > Hi! While I have found existing tools for benchmarking both postgres and redis (hammerdb by eg), AFAIK they are not compatible with custom queries. I wonder if such procedure would give some precise insight: For each database kind: 1. ask the community to optimise your design 2. create your optimised table 3. populate the database 4. write a scripting program with both postgres/redis connectors and able to run concurent queries (python?) 5. write methods to run queries (insert/update) for both database kind 6. get the timing 7. return to 1. or next 8. give your feedback to both communities