PGEC - PostgreSQL Edge Cache - A JSON cache of PostgreSQL data with a simple REST API

Hello -

PostgreSQL Edge Cache is a JSON cache of PostgreSQL data with a simple REST API. PostgreSQL logical database replication pushes changes ensuring that pgec remains up to date. Built on PostgreSQL Message Protocol, Cowboy and JSX. Packaged as a from scratch docker` image using BEAM Docker Release Action on GitHub CI.

A simple example, with a local postgres via docker to demonstrate
the concepts:

docker run \
    --rm \
    --name postgres \
    -d \
    -p 5432:5432 \
    -e POSTGRES_PASSWORD=postgres \
    postgres:14 \
    -c wal_level=logical

Run an interactive SQL shell so that we can create some data:

docker exec --interactive --tty postgres psql postgres postgres

Create a demo table:

create table xy (x integer primary key, y text);
insert into xy values (1, 'foo');

With a PostgreSQL publication for that table:

create publication pub for table xy;

Leaving the SQL shell running, start pgec in another terminal. pgec will act as an edge cache for publication we have just created.

All data from the tables in the publication are retrieved, from a transaction snapshot (automatically created as part of the replication process, using an extended query with batched execute). Once the
initial data has been collected, streaming replication starts, receiving changes that have been applied since the transaction snapshot ensuring no loss of data. Streaming replication continues keeping pgec as an up to date cache of data.

docker run \
    --rm \
    -d \
    --name pgec \
    -p 8080:80 \
    -e PGMP_REPLICATION_LOGICAL_PUBLICATION_NAMES=pub \
    -e PGMP_DATABASE_USER=postgres \
    -e PGMP_DATABASE_PASSWORD=postgres \
    -e PGMP_DATABASE_HOSTNAME=host.docker.internal \
    ghcr.io/shortishly/pgec:0.2.2

The -p 8080:80 option above, is linking port 8080 on localhost to port 80 on the pgec container. We can make http requests directly to pgec.

Taking a look at the xy table via the JSON API:

curl http://localhost:8080/pub/xy
{"rows": [{"x":1,"y":"foo"}]}

Where, pub is the publication that we have created, and xy is a table that is part of that publication.

Changes that are applied to the PostgreSQL table are automatically streamed to pgec and applied to the edge cache.

insert into xy values (2, 'bar');

Any CRUD changes to the table are automatically pushed via logical replication to the pgec cache:

curl http://localhost:8080/pub/xy
{"rows": [{"x":1, "y":"foo"}, {"x":2, "y":"bar"}]}

To request the value for key 2:

curl http://localhost:8080/pub/xy/2
{"x":2,"y":"bar"}

Regards,
Peter.

6 Likes