Peter Morgan

Peter Morgan

World ! hello.

Consistent caching with PostgreSQL logical replication and a Redis API

2 minute read

pgec is an Apache licensed real-time in memory PostgreSQL logical replication cache with Redis, Memcached and REST APIs. It supports column lists and row filters with the latest features of replication in PostgreSQL 15.

High Level Architecture

pgec is available as a docker container for both AMD64 and ARM64 processors. The replication process creates a transaction checkpoint ensuring data integrity. Once the initial data has been collected, streaming replication starts, receiving changes that have been applied subsequent to the checkpoint, ensuring no loss of data. Streaming replication continues keeping pgec up to date in real-time.

Lets take a look at simple deployment by cloning the repository containing the docker compose.yaml with sample PostgreSQL data, Prometheus and Grafana setup ready to go!

git clone https://github.com/shortishly/pgec.git

Alternatively, with the Github CLI installed use:

gh repo clone shortishly/pgec

demo

Change to the newly cloned directory:

cd pgec

Start up everything with:

docker compose --profile all up --detach --remove-orphans

Sample data is populated from the scripts in this directory, using this publication. The compose includes a small load generator using table randload. Grafana dashboards: http://localhost:3000/.

Replication Dashboard

The grades table is populated with data from:

"Last name","First name","SSN","Test1","Test2","Test3","Test4","Final","Grade"
"Alfalfa","Aloysius","123-45-6789",40.0,90.0,100.0,83.0,49.0,"D-"
"Alfred","University","123-12-1234",41.0,97.0,96.0,97.0,48.0,"D+"
"Gerty","Gramma","567-89-0123",41.0,80.0,60.0,40.0,44.0,"C"
"Android","Electric","087-65-4321",42.0,23.0,36.0,45.0,47.0,"B-"
"Bumpkin","Fred","456-78-9012",43.0,78.0,88.0,77.0,45.0,"A-"
"Rubble","Betty","234-56-7890",44.0,90.0,80.0,90.0,46.0,"C-"
"Noshow","Cecil","345-67-8901",45.0,11.0,-1.0,4.0,43.0,"F"
"Buff","Bif","632-79-9939",46.0,20.0,30.0,40.0,50.0,"B+"
"Airpump","Andrew","223-45-6789",49.0,1.0,90.0,100.0,83.0,"A"
"Backus","Jim","143-12-1234",48.0,1.0,97.0,96.0,97.0,"A+"
"Carnivore","Art","565-89-0123",44.0,1.0,80.0,60.0,40.0,"D+"
"Dandy","Jim","087-75-4321",47.0,1.0,23.0,36.0,45.0,"C+"
"Elephant","Ima","456-71-9012",45.0,1.0,78.0,88.0,77.0,"B-"
"Franklin","Benny","234-56-2890",50.0,1.0,90.0,80.0,90.0,"B-"
"George","Boy","345-67-3901",40.0,1.0,11.0,-1.0,4.0,"B"
"Heffalump","Harvey","632-79-9439",30.0,1.0,20.0,30.0,40.0,"C"

Using the redis API we can fetch Betty Rubble’s grades using the HGETALL command:

redis-cli HGETALL pub.grades.234-56-7890

 1) "test4"
 2) "90"
 3) "test3"
 4) "80"
 5) "test2"
 6) "90"
 7) "test1"
 8) "44"
 9) "ssn"
10) "234-56-7890"
11) "last"
12) "Rubble"
13) "grade"
14) "C-"
15) "first"
16) "Betty"
17) "final"
18) "46"

A C- seems harsh, lets give her a C instead:

docker compose exec \
    --no-TTY \
    postgres \
    psql \
    --command="update grades set grade='C' where ssn='234-56-7890'"

On update the PostgreSQL logical replication protocol will stream the change to pgec, updating the cache in real-time.

Using the redis API again we can see that Betty Rubble’s grade is now a C:

redis-cli HGETALL pub.grades.234-56-7890

 1) "test4"
 2) "90"
 3) "test3"
 4) "80"
 5) "test2"
 6) "90"
 7) "test1"
 8) "44"
 9) "ssn"
10) "234-56-7890"
11) "last"
12) "Rubble"
13) "grade"
14) "C"
15) "first"
16) "Betty"
17) "final"
18) "46"

pgec creates a replication slot from which a snapshot is created by the database. Using this snapshot, pgec retrieves all data from the tables in the publication, in a single transaction (using an extended query with a batched execute). Once the initial data has been collected, streaming replication is then started, receiving changes that have applied since the transaction snapshot to ensure no loss of data.

Introspection on the PostgreSQL metadata is done automatically by pgec supporting both primary and composite keys, which are replicated into an in memory ETS table.

Thereafter, CRUD changes on the underlying PostgreSQL table will be automatically pushed to pgec and reflected in the ETS table which are used by the redis API.

pgec also has memcached and REST APIs using JSON to represent the replicated relations described.

You May Also Enjoy