Postgresql – Distributing a semi-large key/value list

postgresqlredisreplication

I have a semi-large (around 30 million records) key/value list which I want to load into redis.

The data is stored in PostgreSQL (as master data) and we want to distribute this to clients for faster (local) lookups.

The keys are Belgium telephone numbers (national format, so strings between 9 and 10 digits). The values are 5 letter strings.

There would be a redis-server on each client (around 10 clients per customer). The full dump will distributed once or maybe twice a year, while the updates are between 100-1000 a day (only business days between 08:00 and 18:00).

Is this an efficient way to do this?

Side remark: if redis isn't the correct choice, other suggestions are also welcome.

Best Answer

An RDB would be the fastest way to replace/create an entire dataset. You can prepare the bi-annual forklift update beforehand at HQ at your convenience and ship it out to the customers. Do note that loading from RDB requires downtime.

As for the 0.1-1K updates/day per client-local Redis database - some of the real questions are:

How many updates are done to the master database?
Are all updates applied to all client/local databases?
Can the updates be batched?
Is there a latency budget for updates to be shipped to clients?
10 clients/database per customer, but how many customers overall?

I assume there are many possible solutions to this challenge, but I'm for KISSing it pimple :)

Related Solutions

REDIS Bulk RPUSH set values to a list

Actually there is but not where you'd intuitively think of looking for it. Have a look at SORT - this should do what you need:

SORT myset BY nosort STORE mylist

PostgreSQL Aggregate – How to Get First Value from List

You can use the DISTINCT ON of SELECT as in the documentation

SELECT DISTINCT ON (id)
  id, col1, col2
FROM TABLE
ORDER BY id, col1, col2

The difference between SELECT DISTINCT <columns> and SELECT DISTINCT ON (<columns>) <columns> is that the first gives you unique rows across all selected columns .. the second gives you one unique row per column set defined within the parenthesis, but allows for additional columns to be returned.

To specify WHICH additional data is returned, you need to ORDER BY.. DISTINCT will then return the first row for each DISTINCT ON set of columns.

Best Answer

Related Solutions

REDIS Bulk RPUSH set values to a list

PostgreSQL Aggregate – How to Get First Value from List

Related Question