postgres-as-a-cache

• postgres-as-a-cache
  • Start Postgres and install cron & pg_cron
    • What does it do?
    • Docker Postgres SQL Connection String
  • open a bash shell on Postgres container
  • stop postgres
    • What it does
  • Leverages
    • pg_cron
    • Credits
  • Create a Cache
    • Prerequisites
    • Preparation
  • Make a cache
    • Make-cache: What does it do?
  • Client Samples
  • Cron
    • Cron Jobs

• License

Use postgres as a cache

Start Postgres and install cron & pg_cron

.\start-pg.ps1

What does it do?

1. Creates a custom variation of posgres from Dockerfile, Adds in plugins we want (see file above), Configures plugins

2. Starts container running, which starts base postgres

3. Reconfigures postgres + Restarts postgres, via:

./data/configure_pg.sh

4. Finishes up by running SQL script:

./data/pg_cron_add.sh

5. Postgres w. plugins ready for use

Notes:

• Horrible work arounds, if you have a better way, create an issue, or put in a PR
• It works though.

Docker Postgres SQL Connection String

postgresql://postgres:password123-@localhost:5432/postgres

open a bash shell on Postgres container

.\bash-pg.ps1

It opens a bash shell...with handy guidance

SQL Scripts Folder: /var/lib/postgresql/data
Postgres Logs: /var/log/postgresql
Postgres Utilities Folder: /usr/lib/postgresql/16/bin
In general to run postgres commands you will have to run as the 'postgres' user
su -- postgres -c {pg_command}
root@9022c51945a7:/var/lib/postgresql/data# 

stop postgres

.\stop-pg.ps1

What it does

1. Stops image
2. Does a tear down (you can customize to stop this, change start too.

Leverages

pg_cron

• pg_cron

Credits

I got the idea by reading this fantastic article by info@dizzy.zone you can find it https://dizzy.zone/2025/09/24/Redis-is-fast-Ill-cache-in-Postgres/

Create a Cache

Create a handy cache with few simple methods, see Data Schema

Prerequisites

Make sure you have: Powershell 7+

Preparation

Checklist

Prefer: the snake_case convention for postgres names, avoiding reserved words

• Decide on the DB to host your cache
• Decide on a unique schema name to host your cache artifacts, thus deleting the schema deletes the cache
• Decide if you need a custom role, and if so, pick a unique name

Make a cache

.\make-cache.ps1 `
    -ConnectionString "postgresql://postgres:password123-@localhost:5432/postgres" `
    -SchemaName "test_cache_01" `
    -RoleName "test-cache-role"

Arguments:

• ConnectionString: valid Postgres Connection String (sample is the docker one)
• SchemaName: (required) schema to put the cache into
• RoleName: (unused, future)

Make-cache: What does it do?

1. Takes the schema in sql\ that start with ##_, starting at the minimum index of 110 inclusive
2. In each file replaces {schema} token with your schema name, and {rolename} with your role name (not used for now)
3. Copies transformed files into temp\ folder which is emptied first
4. Execute scripts in numeric order ascending at the postgres instance and database in the connection string
5. When done, the cache is ready for use

This will create the objects in Data Schema

Client Samples

Sample clients

Cron

Copied from pg_cron

 ┌───────────── min (0 - 59)
 │ ┌────────────── hour (0 - 23)
 │ │ ┌─────────────── day of month (1 - 31) or last day of the month ($)
 │ │ │ ┌──────────────── month (1 - 12)
 │ │ │ │ ┌───────────────── day of week (0 - 6) (0 to 6 are Sunday to
 │ │ │ │ │                  Saturday, or use names; 7 is also Sunday)
 │ │ │ │ │
 │ │ │ │ │
 * * * * *

An easy way to create a cron schedule is: crontab.guru.

Cron Jobs

See: Cron Setup