PostgreSQL
This guide will walk you through the process of configuring a Postgres 13.0 or higher database to work with Readyset Cache. Please note that other versions are untested so you might run into issues.
Instructions
The logical replication and superuser settings below are required for deep caching, where Readyset Cache snapshots your tables and follows the replication stream to keep cached results up to date. Shallow (TTL-based) caching does not require them; it needs only standard read access to run your queries. See Shallow Caching.
To ensure you have the correct permissions set, run the following commands as the database user you will use Readyset Cache with:
1. Ensure Postgres version 13.0 or higher is running.
testdb=> SELECT version();
version
-----------------------
PostgreSQL 14.8
(1 row)2. Ensure that logical replication is enabled.
testdb=> SHOW WAL_LEVEL;
wal_level
-----------
logical
(1 row)LOGICAL is required to ensure sufficient data is present in the replication stream.
3. Ensure superuser permissions are enabled.
Readyset Cache uses superuser access to be notified of changes to table DDL.
testdb=> SELECT * FROM pg_user where usename = CURRENT_USER;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
postgres | 10 | t | t | t | t | ******** | |
(1 row)Ensure the usesuper variable is true.
4. Decide on how to deal with unsupported features.
Some Postgres features, like row-level security, are not yet supported by Readyset Cache. You can either skip replicating those tables into Readyset Cache (in Step 2 – Install Readyset Cache), or alternatively, you can disable these features on tables replicated by Readyset Cache prior to proceeding.
For managed Postgres services such as AWS RDS and AWS Aurora, see Cloud Databases.
Connecting to Readyset Cache
Readyset Cache is wire-compatible with Postgres, so you can use psql to connect to it in the same way you would an ordinary database.
Readyset Cache uses the same username, password and database name as your upstream database, so to connect, fill in those values in your connection string and run:
psql 'postgresql://<username>:<password>@127.0.0.1:5433/<database name>'Once connected, you can see the status of tables in Readyset Cache by running:
testdb=> SHOW READYSET STATUS;