PostgreSQL Edge Cache (PGEC)

pgec replicates data from PostgreSQL accessed with a Redis, Memcached and REST API with persistent storage.

Features

PostgreSQL logical replication support for cache consistency
In memory cache with expiry, backed by a persistent store using leveled (from riak).
Redis compatible API
Memcached compatible API
REST API
a compose having PostgreSQL with example data, Grafana, and Prometheus.
a GitHub Codespace for build and development
Support for row filters and column lists in PostgreSQL 15

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.

Quick Start

Clone this repository for the docker compose.yaml with sample PostgreSQL data installed.

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

Alternatively, with the Github CLI installed use:

gh repo clone shortishly/pgec

Change to the newly cloned directory:

cd pgec

Start up everything with:

docker compose 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/, more details of monitoring are here

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"

Betty Rubble's grades are http://localhost:8080/pub/grades/234-56-7890:

curl -s http://localhost:8080/pub/grades/234-56-7890 | jq

{
  "final": 46,
  "first": "Betty",
  "grade": "C-",
  "last": "Rubble",
  "ssn": "234-56-7890",
  "test1": 44,
  "test2": 90,
  "test3": 80,
  "test4": 90
}

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'"

Fetching the same row, with the redis API instead:

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"

Fetching the same row, but with the memcached API instead:

telnet localhost 11211
Trying ::1...
Connected to localhost.
Escape character is '^]'.
get pub.grades.234-56-7890
VALUE pub.grades.234-56-7890 0 120
{"final":46,"first":"Betty","grade":"C","last":"Rubble","ssn":"234-56-7890","test1":44,"test2":90,"test3":80,"test4":90}
END

Primary keys via REST API:

curl -s http://localhost:8080/pub/deniro/Casino | jq

{
  "score": 80,
  "title": "Casino",
  "year": 1995
}

The same via the Redis API:

redis-cli HGETALL pub.deniro.Casino

1) "year"
2) "1995"
3) "title"
4) "Casino"
5) "score"
6) "80"

Composite keys:

curl -s http://localhost:8080/pub/cities/Tulsa/OK | jq

{
  "city": "Tulsa",
  "ew": "W",
  "lat_d": 36,
  "lat_m": 9,
  "lat_s": 35,
  "lon_d": 95,
  "lon_m": 54,
  "lon_s": 36,
  "ns": "N",
  "state": "OK"
}

JSONB Support

Hi Peter,

I encountered an issue when dealing with JSONB columns in a publication. When I add the test data like this:

CREATE TABLE public.json_sample (id serial PRIMARY KEY, keyval jsonb);
INSERT INTO public.json_sample (keyval) VALUES ('{"foo": "bar", "baz": 1}');

When I change the publication to this dataset, pgec seem to crash and the REST API is not available anymore:

=CRASH REPORT==== 3-Mar-2023::19:10:32.062821 === <0.6203.0> proc_lib:crash_report/4:539
  crasher:
    initial call: cowboy_stream_h:request_process/3
    pid: <0.6203.0>
    registered_name: []
    exception error: bad argument
      in function  ets:safe_fixtable/2
         called as ets:safe_fixtable(osm_rivers_sample,true)
         *** argument 1: the table identifier does not refer to an existing ETS table
      in call from ets:foldl/3 (ets.erl, line 632)
      in call from pgec_h:init/3 (src/pgec_h.erl, line 51)
      in call from cowboy_handler:execute/2 (src/cowboy_handler.erl, line 37)
      in call from cowboy_stream_h:execute/3 (src/cowboy_stream_h.erl, line 306)
      in call from cowboy_stream_h:request_process/3 (src/cowboy_stream_h.erl, line 295)
    ancestors: [<0.6201.0>,<0.217.0>,<0.216.0>,ranch_sup,<0.116.0>]
    message_queue_len: 0
    messages: []
    links: [<0.6201.0>]
    dictionary: []
    trap_exit: false
    status: running
    heap_size: 376
    stack_size: 28
    reductions: 323
  neighbours:

=ERROR REPORT==== 3-Mar-2023::19:10:32.064281 === <0.6201.0>
Ranch listener pgec, connection process <0.6201.0>, stream 1 had its request process <0.6203.0> exit with reason badarg and stacktrace [{ets,safe_fixtable,[osm_rivers_sample,true],[{error_info,#{cause => id,module => erl_stdlib_errors}}]},{ets,foldl,3,[{file,"ets.erl"},{line,632}]},{pgec_h,init,3,[{file,"src/pgec_h.erl"},{line,51}]},{cowboy_handler,execute,2,[{file,"src/cowboy_handler.erl"},{line,37}]},{cowboy_stream_h,execute,3,[{file,"src/cowboy_stream_h.erl"},{line,306}]},{cowboy_stream_h,request_process,3,[{file,"src/cowboy_stream_h.erl"},{line,295}]},{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,240}]}]

Could look into this?

shortishly / pgec Goto Github PK

pgec's Introduction

PostgreSQL Edge Cache (PGEC)

Features

Quick Start

pgec's People

Contributors

Stargazers

Watchers

pgec's Issues

JSONB Support

PostGIS support

Feature idea: PostgreSQL API

Recommend Projects

React

Vue.js

Typescript

TensorFlow

Django

Laravel

D3

Recommend Topics

javascript

web

server

Machine learning

Visualization

Game

Recommend Org

Facebook

Microsoft

Google

Alibaba

D3

Tencent