pgec reads: memory replicated cache, writes: PostgreSQL with a Redis API
pgec is an Apache licensed
real-time in memory
PostgreSQL logical replication
cache with Redis, Memcached and REST
API. Mutating operations (e.g., DEL or HSET) update PostgreSQL
directly. Read-only operations (e.g., HGET or EXISTS) are serviced
directly by the cache. Streaming replication ensures that the cache
remains consistent with any changes on the underlying PostgreSQL
database in real-time. Change notifications can also be sent to
clients using PSUBSCRIBE.
Each replicated PostgreSQL table is represented as a Redis Hash. The primary or compound key of the table is used as the key for the hash. Columns are represented as fields in the hash. Only those fields matching the column names present in the table may be stored or retrieved.
Read only operations such as EXISTS, HGETALL or HGET are handled directly by the in memory ETS cache. These commands ultimately call ets:lookup/2, with the resultant tuple converted into a (typically) string representation for Redis.
Mutating operations, such as DEL or HSET automatically write to PostgreSQL (after checking the cache to determine whether an insert or update is the appropriate SQL statement to use). Streaming replication updates the in memory cache with the updated values from the database.
The real-time replication stream from PostgreSQL is also published to
subscribers with table level granularity. For example, a subscription
to __key*__:pub.grades.* will result in any changes to the grades
table being published to that subscriber. Internally erlang message
passing is used with a process group per
table. Row level granularity for subscriptions is not
currently implemented, but is something being considered.
In this example we will be using the grades sample data. The data is loaded as part of the initialisation of PostgreSQL, in the docker compose used by the pgec repository.
The details of cloning the repository and using compose to bring all the services up are detailed in this previous article.
In particular, we will be looking at this entry for Betty Rubble and her grade:
"Rubble","Betty","234-56-7890",44.0,90.0,80.0,90.0,46.0,"C-"
Looking at the current state of the row in SQL:
➜ select grade from grades where ssn='234-56-7890'
grade
-------
C-
(1 row)
Using the Redis API in pgec to examine the same data:
➜ redis-cli hget pub.grades.234-56-7890 grade
C-
HGET uses the pgec in memory cache, and does not result in a query to PostgreSQL for this data.
In a separate terminal we can setup a Redis subscription to monitor
changes to the grades table as follows:
➜ redis-cli psubscribe '__key*__:pub.grades.*'
Reading messages... (press Ctrl-C to quit)
1) "subscribe"
2) "__key*__:pub.grades.*"
3) (integer) 1
We can use HSET to update Betty’s grade to a C:
➜ redis-cli hset pub.grades.234-56-7890 grade C
(integer) 1
The (integer) 1 is telling us that one row was updated, pgec
has issued a SQL statement to update Betty’s grade in PostgreSQL.
In the subscription terminal we can also see a notification telling us
that the key pub.grades.234-56-7890 has been set:
➜ redis-cli psubscribe '__key*__:pub.grades.*'
...
1) "message"
2) "__keyspace@0__:pub.grades.234-56-7890"
3) "set"
We can confirm the updated data in PostgreSQL with:
➜ select grade from grades where ssn='234-56-7890'
grade
-------
C
(1 row)
Logical streaming replication from PostgreSQL into pgec also means that the cache is updated in real-time:
➜ redis-cli hget pub.grades.234-56-7890 grade
C
As well as updates to existing data, pgec will also insert new
data with HSET.
➜ redis-cli hset pub.grades.321-21-4321 last Zella first Xiggy test1 47 test2 68 test3 54 test4 73 final 23 grade D+
(integer) 1
The subscription terminal, pings with a new message:
➜ redis-cli psubscribe '__key*__:pub.grades.*'
...
1) "message"
2) "__keyspace@0__:pub.grades.321-21-4321"
3) "set"
Looking at the inserted row in PostgreSQL:
➜ select * from grades where ssn='321-21-4321'
last | first | ssn | test1 | test2 | test3 | test4 | final | grade
-------+-------+-------------+-------+-------+-------+-------+-------+-------
Zella | Xiggy | 321-21-4321 | 47 | 68 | 54 | 73 | 23 | D+
(1 row)
The Redis protocol typically uses strings for most values, pgec understands the underlying SQL types being used and converts appropriately (text and floats in this example).
Replication means that the pgec cache remains consistent with the recently inserted data:
➜ redis-cli hgetall pub.grades.321-21-4321
1) "test4"
2) "73"
3) "test3"
4) "54"
5) "test2"
6) "68"
7) "test1"
8) "47"
9) "ssn"
10) "321-21-4321"
11) "last"
12) "Zella"
13) "grade"
14) "D+"
15) "first"
16) "Xiggy"
17) "final"
18) "23"
(note the strings being used for some of the types that are actually floats).
Updates can be from any source, not just through pgec, so if a another process updates this record directly in PostgreSQL:
➜ update grades set grade='D' where ssn='321-21-4321'
UPDATE 1
We would receive a notification of the change in our Redis subscription:
➜ redis-cli psubscribe '__key*__:pub.grades.*'
...
1) "message"
2) "__keyspace@0__:pub.grades.321-21-4321"
3) "set"
With pgec remaining consistent with PostgreSQL too, the HGET
is reading from the cache (it has been updated by streaming
replication):
➜ redis-cli hget pub.grades.321-21-4321 grade
D
A DEL will result in pgec issuing a delete statement to PostgreSQL for the affected row.
➜ redis-cli del pub.grades.321-21-4321
(integer) 1
Together with a notification to our subscription:
➜ redis-cli psubscribe '__key*__:pub.grades.*'
...
1) "message"
2) "__keyspace@0__:pub.grades.321-21-4321"
3) "del"
Which is reflected in table:
➜ select * from grades where ssn='321-21-4321'
last | first | ssn | test1 | test2 | test3 | test4 | final | grade
------+-------+-----+-------+-------+-------+-------+-------+-------
(0 rows)
And also in our cache:
➜ redis-cli exists pub.grades.321-21-4321
(integer) 0
Commands
The following Redis commands are currently implemented by pgec:
del
Delete a key.
redis-cli del pub.grades.321-21-4321
(integer) 1
exists
Determine if a key exists.
$ redis-cli exists pub.grades.234-56-7890
(integer) 1
hexists
Determine if a hash field exists.
$ redis-cli hexists pub.grades.234-56-7890 test1
(integer) 1
$ redis-cli hexists pub.grades.234-56-7890 test6
(integer) 0
hget
Get the value of a hash field stored in memory.
$ redis-cli hget pub.grades.234-56-7890 test1
"44.0"
Returns nil when the key is not present, or the field is not present
in the hash.
$ redis-cli hget pub.grades.234-56-7890 test6
(nil)
hgetall
Get all fields and values in a hash for a key stored in memory.
$ redis-cli hgetall pub.grades.234-56-7890
1) "test4"
2) "90.0"
3) "test3"
4) "80.0"
5) "test2"
6) "90.0"
7) "test1"
8) "44.0"
9) "ssn"
10) "234-56-7890"
11) "last"
12) "Rubble"
13) "grade"
14) "C-"
15) "first"
16) "Betty"
17) "final"
18) "46.0"
hkeys
Get all the fields in a hash.
$ redis-cli hkeys pub.grades.234-56-7890
1) "test4"
2) "test3"
3) "test2"
4) "test1"
5) "ssn"
6) "last"
7) "grade"
8) "first"
9) "final"
An empty array is returned when the key is not present.
$ redis-cli hkeys pub.grades.234-56-7823
(empty array)
hlen
Get the number of fields in a hash.
$ redis-cli hlen pub.grades.234-56-7890
(integer) 9
Zero is returned when the key is not present.
$ redis-cli hlen pub.grades.234-56-8790
(integer) 0
hset
Set the string value of a hash field.
insert
A SQL statement is executed by pgec to insert the row into the underlying PostgreSQL table. The inserted row is replicated updating the in memory cache.
redis-cli hset pub.grades.321-21-4321 last Zella first Xiggy test1 47 test2 68 test3 54 test4 73 final 23 grade D+
update
A SQL statement is executed by pgec to update the row in the underlying PostgreSQL table. The updated row is replicated updating the in memory cache.
$ redis-cli hset pub.grades.234-56-7890 test1 45.0
(integer) 1
Zero is returned if the field is not a member of the hash.
psubscribe
Subscriptions are supported at present to table level granularity.
$ redis-cli psubscribe '__key*__:pub.grades.*'
Reading messages... (press Ctrl-C to quit)
1) "subscribe"
2) "__key*__:pub.grades.*"
3) (integer) 1
In another shell update one of the grades:
redis-cli hset pub.grades.234-56-7890 test1 45.0
(integer) 1
Or via SQL:
docker compose exec \
--no-TTY \
postgres \
psql \
--command="update grades set grade='C' where ssn='234-56-7890'"
A message will be streamed to the subscriber detailing the affected key.
1) "message"
2) "__keyspace@0__:pub.grades.234-56-7890"
3) "set"