Fastest simple key-value store, multiple writers, like Redis?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general