In this tutorial, we'll learn how to use Redis for caching PostgreSQL queries.
Redis (Remote Dictionary Server) is a popular in-memory data structure store used as a database, cache, and message broker. This guide provides step-by-step instructions for setting up Redis as a caching layer for web applications to improve database query performance.
Prerequisites
- A server running Ubuntu 24.04 dedicated server or KVM VPS.
- Database: MySQL or PostgreSQL (Optional: An application using the database)
- Basic knowledge of the command line and Linux administration.
Use Redis for Caching PostgreSQL Queries
Step 1: Installing Redis
1.1 Update the system packages
First, update your system packages to ensure you have the latest versions.
sudo apt update && sudo apt upgrade -y
1.2 Install Redis
Next, install Redis using following set of commands (We have copied it from Redis official documentation) :
sudo apt-get install lsb-release curl gpg
curl -fsSL https://packages.redis.io/gpg | sudo gpg --dearmor -o /usr/share/keyrings/redis-archive-keyring.gpg
sudo chmod 644 /usr/share/keyrings/redis-archive-keyring.gpg
echo "deb [signed-by=/usr/share/keyrings/redis-archive-keyring.gpg] https://packages.redis.io/deb $(lsb_release -cs) main" | sudo tee /etc/apt/sources.list.d/redis.list
sudo apt-get update
sudo apt-get install redis
1.3 Verify Redis Installation
Check the status of Redis to ensure it is running:
sudo systemctl status redis-server
You should see an output indicating that Redis is active and running.
Step 2: Configure Redis for Caching
By default, Redis is configured to run as a general-purpose key-value store. To optimize Redis for caching, some configuration changes are needed.
2.1 Edit Redis Configuration
Open the Redis configuration file:
sudo nano /etc/redis/redis.conf
2.2 Set Max Memory Usage
Redis will work as a cache by evicting older data when memory is full. To limit Redis memory usage and configure eviction policies:
Find the following line and uncomment it (remove the #):
maxmemory <value>
Set the maximum memory limit, for example, 512MB:
maxmemory 512mb
2.3 Configure Eviction Policy
Redis uses eviction policies to decide how to handle memory limits. Below are the common policies:
- noeviction: Returns an error when memory limit is reached.
- allkeys-lru: Removes the least recently used (LRU) key across all keys.
- volatile-lru: Removes the least recently used (LRU) key with an expiration.
For caching, it’s recommended to use the allkeys-lru policy:
maxmemory-policy allkeys-lru
2.4 Save and Restart Redis
Save the changes and restart Redis:
sudo systemctl restart redis
Integrate Redis and PostgreSQL in a Python Application
To demonstrate how Redis caching works with PostgreSQL, we’ll walk through installing PostgreSQL and setting up Redis as a cache to improve query performance. The steps below will show how to install PostgreSQL, integrate Redis with a Python application, and run a demo to verify successful integration.
Step 1: Install PostgreSQL
1.1 Update the system packages
Start by updating the system packages.
sudo apt update
1.2 Install PostgreSQL
Install PostgreSQL using the package manager:
sudo apt install postgresql postgresql-contrib -y
1.3 Start and Enable PostgreSQL
Make sure the PostgreSQL service is running and enabled on startup.
sudo systemctl start postgresql
sudo systemctl enable postgresql
1.4 Set PostgreSQL Password and Access
Switch to the postgres user to configure the database.
sudo -i -u postgres
Set the password for the postgres user:
psql
ALTER USER postgres PASSWORD 'your_password';
\q
Step 2: Create a Demo Database and Table
Now, create a sample database and table.
2.1 Create a Database
Run the following commands to create a database:
psql
CREATE DATABASE demo_db;
\q
2.2 Create a Table and Insert Data
Connect to the newly created demo_db:
psql -d demo_db
Create a simple table called users:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
Insert some sample data:
INSERT INTO users (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com');
Exit:
\q
exit
Step 3: Set up Python Project
Install the Python virtual environment
apt install python3.12-venv -y
Note: Python version might be different for you.
Create project directory:
mkdir project && cd project
Create virtual environment and activate it:
python3 -m venv venv
source venv/bin/activate
Install Required Python Libraries
We’ll need the following Python libraries: psycopg2 for PostgreSQL interaction and redis for Redis interaction.
Install them using pip:
pip install psycopg2-binary redis
Python Script to Use Redis Cache with PostgreSQL
Create Python script file.
nano redis_postgresql_demo.py
Add following Python code that demonstrates caching PostgreSQL query results in Redis.
import psycopg2
import redis
import json
# Connect to PostgreSQL
conn = psycopg2.connect(
dbname="demo_db",
user="postgres",
password="your_password",
host="localhost"
)
# Create a Redis client
redis_client = redis.StrictRedis(host='localhost', port=6379, db=0)
def get_user_from_db(user_id):
"""Fetch user details from PostgreSQL."""
cur = conn.cursor()
cur.execute(f"SELECT * FROM users WHERE id = {user_id};")
user = cur.fetchone()
cur.close()
return user
def get_user(user_id):
"""Fetch user details from Redis cache or PostgreSQL."""
# Check if the user is in Redis cache
cache_key = f"user:{user_id}"
cached_user = redis_client.get(cache_key)
if cached_user:
print("Fetching from Redis Cache...")
return json.loads(cached_user)
else:
# If not in cache, fetch from PostgreSQL and cache the result
print("Fetching from PostgreSQL...")
user = get_user_from_db(user_id)
if user:
redis_client.setex(cache_key, 600, json.dumps(user))
return user
# Example: Fetch user with ID 1
user = get_user(1)
if user:
print(f"User Details: ID = {user[0]}, Name = {user[1]}, Email = {user[2]}")
else:
print("User not found")
Save and exit the file.
Step 4: Run the Demo
Run the Python Script: Execute the Python script to verify that Redis is caching the query result. When running the script for the first time, it will fetch the data from PostgreSQL. Subsequent runs will fetch the data from Redis.
python redis_postgresql_demo.py
Output:
On the first run, you should see:
Fetching from PostgreSQL...
User Details: ID = 1, Name = Alice, Email = alice@example.com
On subsequent runs, you should see:
Fetching from Redis Cache...
User Details: ID = 1, Name = Alice, Email = alice@example.com
This confirms that the data is being cached in Redis after the first query from PostgreSQL.
Step 5: Verify Redis Caching
You can verify that the data has been cached in Redis using the redis-cli.
redis-cli
Inside the Redis CLI, check for the cached key:
keys *
You should see the user:1 key in the output, which holds the cached result of the query.
To inspect the value:
get user:1
This will return the cached JSON value of the user data.
Step 6: Best Practices for Redis Caching
Set Expiration for Cached Data: Always set an expiration time to ensure old data is removed and new queries get fresh data.
redis.setex('key', expiration_in_seconds, 'value')
Avoid Caching Too Much Data: Cache only frequent queries to avoid overloading Redis with unnecessary data.
Monitor Cache Hits and Misses: Keep track of how often your application retrieves data from the cache vs. the database to ensure Redis is improving performance.
Step 7: Clean Up (Optional)
Check for all keys:
redis-cli keys '*'
Clear Redis Cache: You can remove all keys from Redis with the following command:
redis-cli flushall
Stop PostgreSQL and Redis:
sudo systemctl stop postgresql
sudo systemctl stop redis
Sample Integrate Redis Caching For PHP, Python, and NodeJS
To use Redis as a cache, your application needs to be configured to connect to Redis. Below are the steps for PHP, Python (Flask/Django), and Node.js applications.
Using Redis in PHP
Install the Redis PHP extension:
sudo apt install php-redis
Configure Redis caching in your PHP application:
$redis = new Redis();
$redis->connect('127.0.0.1', 6379);
// Cache a database query result
$cacheKey = 'user_1_profile';
if ($redis->exists($cacheKey)) {
$userProfile = $redis->get($cacheKey);
} else {
// Fetch from the database
$userProfile = fetchUserProfileFromDB(1);
// Cache the result for 10 minutes
$redis->setex($cacheKey, 600, json_encode($userProfile));
}
Using Redis in Python (Flask/Django)
Install the Redis Python client:
pip install redis
Example usage in a Flask or Django app:
import redis
import json
# Connect to Redis
redis_cache = redis.StrictRedis(host='localhost', port=6379, db=0)
cache_key = 'user_1_profile'
cached_profile = redis_cache.get(cache_key)
if cached_profile:
user_profile = json.loads(cached_profile)
else:
# Query from the database
user_profile = fetch_user_profile_from_db(1)
# Cache for 10 minutes
redis_cache.setex(cache_key, 600, json.dumps(user_profile))
Using Redis in Node.js
Install the Redis client for Node.js:
npm install redis
Implement Redis caching in a Node.js app:
const redis = require('redis');
const client = redis.createClient();
client.on('error', (err) => {
console.log('Error ' + err);
});
const cacheKey = 'user_1_profile';
client.get(cacheKey, (err, result) => {
if (result) {
const userProfile = JSON.parse(result);
console.log('Data from cache:', userProfile);
} else {
// Fetch from the database
const userProfile = fetchUserProfileFromDB(1);
client.setex(cacheKey, 600, JSON.stringify(userProfile));
console.log('Data from DB and cached');
}
});
Conclusion
In this tutorial, we've learnt how to use Redis for caching PostgreSQL queries.
Setting up Redis for database caching can significantly reduce the load on your database and improve the performance of your web application. With the steps outlined in this tutorial, you can configure Redis and integrate it into your application for efficient caching. Remember to monitor your Redis instance and configure memory management policies suited to your application’s needs.
By integrating Redis with PostgreSQL, you can reduce the load on your database and speed up query responses by caching frequent database queries. This setup shows how Redis works as a caching layer for PostgreSQL data, fetching results from Redis on repeated queries.
