How to Use the pgmemcache Extension in PostgreSQL

Caching is essential when applications repeatedly fetch the same data. Without caching, PostgreSQL must repeatedly execute the same query, leading to high CPU usage, slow response times, and unnecessary load.

A simple and effective solution is to store frequently accessed data in memory and retrieve it instantly.

PostgreSQL enables this capability using the pgmemcache extension.

This guide walks you through installation, configuration, examples, and real use cases, using real SQL outputs for complete clarity

1. Understanding pgmemcache

The pgmemcache extension connects PostgreSQL directly to a memcached server. Memcached is an in-memory key–value store widely used for high-speed caching.

Once pgmemcache is enabled, your SQL queries can:

• store values in RAM
• retrieve cached results
• update or delete cache entries
• run atomic counters
• collect memcached statistics

This means PostgreSQL can handle caching internally instead of depending only on the application layer.

2. Why Use pgmemcache?

1. Reduce PostgreSQL load

Cache heavy or frequently repeated queries to avoid executing them repeatedly.

2. Improve performance for read-heavy applications

Dashboards, product lists, and analytics benefit the most from caching.

3. Build caching logic inside PostgreSQL

Stored procedures, triggers, and functions can interact with memcached.

4. Faster-than-disk access

Memcached stores data in RAM, enabling microsecond-level access times.

3. Installing pgmemcache on Ubuntu

Step 1: Clone the source

git clone https://github.com/ohmu/pgmemcache.git

Step 2: Install required library

sudo apt update
sudo apt install libmemcached-dev

This installs the header files needed to compile pgmemcache.

Step 3: Build and install pgmemcache

cd pgmemcache
make
sudo make install

Step 4: Enable pgmemcache in PostgreSQL

CREATE EXTENSION pgmemcache;

Verify installation:

SELECT * FROM pg_available_extensions WHERE name = 'pgmemcache';

4. Installing and Enabling Memcached

pgmemcache requires a running memcached server.

Install memcached:

sudo apt update
sudo apt install memcached
sudo apt install libmemcached-tools

Why install these packages?

memcached

• Runs the memory-based key–value store
• Stores cached results used by pgmemcache
• Needed for all cache operations

libmemcached-tools

• Provides command-line utilities to test memcached
• Useful for debugging cache issues

Start and enable memcached

sudo systemctl status memcached.service

If it is not active, then activate it with the command below

sudo systemctl start memcached
sudo systemctl enable memcached

Once memcached is running, PostgreSQL can connect to it.

5. Connecting PostgreSQL to Memcached

If you try using pgmemcache without configuring any server:

SELECT memcache_set('product_1', 'Laptop');

Output:

WARNING: pgmemcache: memcached_set: NO SERVERS DEFINED
memcache_set | f

Purpose of this error message:

1. PostgreSQL does not know which memcached server to use.
2. pgmemcache requires at least one active server.
3. Caching cannot start until a memcached instance is added.

Add the memcached server

SELECT memcache_server_add('127.0.0.1:11211');

Output:

memcache_server_add | t

Purpose:

1. Tells PostgreSQL where the memcached server is running.
2. Establishes a connection for all further cache operations.
3. Required once per session (or can be scripted in functions).

Retry the operation:

SELECT memcache_set('product_1', 'Laptop');

Output:

memcache_set | t

Now caching works.

6. Using the Top pgmemcache Functions 

Below are real pgmemcache examples, each followed by clear explanations.

1. Store a value — memcache_set

SELECT memcache_set('product_1', 'Laptop');

Output:

memcache_set | t

Purpose:

1. Saves a key–value pair in memcached.
2. Useful for caching frequently requested results.
3. Makes future access faster by avoiding repeated SQL queries.

2. Retrieve a value — memcache_get

SELECT memcache_get('product_1');

Output:

memcache_get | Laptop

Purpose:

1. Reads data from memory instead of querying PostgreSQL.
2. Improves performance for read-heavy workloads.
3. Helps verify if caching is working correctly.

3. Store a value with expiry time

SELECT memcache_set('otp_user_5', '783421', interval '1 minutes');

Output:

memcache_set | t

Retrieve before expiry:

SELECT memcache_get('otp_user_5');

Output:

memcache_get | 783421

After 1 minute:

memcache_get |

Purpose:

1. Ideal for temporary data such as OTPs or session tokens.
2. Automatically expires, reducing manual cleanup.
3. Prevents storing short-lived data inside PostgreSQL tables.

4. Delete a value: memcache_delete

SELECT memcache_delete('product_1');

Output:

memcache_delete | t

Purpose:

1. Removes outdated or invalid cache entries.
2. Useful when underlying PostgreSQL data changes.
3. Ensures cached values never become stale.

5. Increment a number: memcache_incr

Initialize a value:

SELECT memcache_set('counter', '10');

Increment by 5:

SELECT memcache_incr('counter', 5);

Output:

memcache_incr | 15

Purpose:

1. Allows atomic counter operations in memory.
2. Perfect for page views, likes, counters, and rate limiting.
3. Faster and more scalable than updating a row in PostgreSQL.

6. Decrement a number: memcache_decr

SELECT memcache_decr('counter', 3);

Output:

memcache_decr | 12

Purpose:

1. Adjusts counters safely in high concurrency scenarios.
2. Very useful for quotas, remaining stock, or countdowns.
3. Avoids locking overhead inside PostgreSQL.

7. Add value only if key does NOT exist: memcache_add

SELECT memcache_add('state_1', 'active');

Output:

memcache_add | t

Trying to add again:

SELECT memcache_add('state_1', 'inactive');

Output:

WARNING: CONNECTION DATA EXISTS
memcache_add | f

Purpose:

1. Prevents overwriting existing cache keys accidentally.
2. Good for preserving initial states or immutable values.
3. Useful in race-condition-prone environments.

8. Replace existing value: memcache_replace

SELECT memcache_replace('state_1', 'inactive');

Output:

memcache_replace | t

Purpose:

1. Updates only if the key already exists.
2. Ensures you don’t insert unwanted new cache keys.
3. Helps maintain clean and predictable cache structure.

9. Fetch multiple keys: memcache_get_multi

SELECT memcache_get_multi(ARRAY['counter', 'state_1', 'otp_user_5']);

Output:

(counter,12)
(state_1,inactive)
(783421)

Purpose:

1. Improves speed by reducing multiple network calls.
2. Useful in dashboards requesting several cached values together.
3. More efficient than calling memcache_get repeatedly.

10. View memcached statistics: memcache_stats

SELECT memcache_stats();

You get output like this

[ RECORD 1 ]--+--------------------------
memcache_stats | Server: 127.0.0.1 (11211)+
               | pid: 1324431             +
               | uptime: 280              +
               | time: 1764351562         +
               | version: 1.6.14          +
               | pointer_size: 64         +
               | rusage_user: 0.44782     +
               | rusage_system: 0.64816   +
               | curr_items: 2            +
               | total_items: 5           +
               | bytes: 142               +
               | curr_connections: 3      +
               | total_connections: 4     +
               | connection_structures: 4 +
               | cmd_get: 9               +
               | cmd_set: 6               +
               | get_hits: 6              +
               | get_misses: 3            +
               | evictions: 0             +
               | bytes_read: 763          +
               | bytes_written: 621       +
               | limit_maxbytes: 67108864 +
               | threads: 4               +
               |                          +
               | Server: 127.0.0.1 (11211)+
               | pid: 1324431             +
               | uptime: 280              +
               | time: 1764351562         +
               | version: 1.6.14          +
               | pointer_size: 64         +
               | rusage_user: 0.44855     +
               | rusage_system: 0.64922   +
               | curr_items: 2            +
               | total_items: 5           +
               | bytes: 142               +
               | curr_connections: 3      +
               | total_connections: 5     +
               | connection_structures: 4 +
               | cmd_get: 9               +
               | cmd_set: 6               +
               | get_hits: 6              +
               | get_misses: 3            +
               | evictions: 0             +
               | bytes_read: 777          +
               | bytes_written: 2840      +
               | limit_maxbytes: 67108864 +
               | threads: 4               +
               |                          +

PostgreSQL prints detailed information about the connected memcached server.

This helps you understand performance, usage, and health of your cache.

1. Server Information

Server: 127.0.0.1 (11211)

The IP address and port of the memcached server you are connected to.

pid: 1324431

The process ID of the memcached server running on your machine.

2. Time and Uptime

uptime: 280

How long memcached has been running (in seconds).

Useful for checking if the server restarted recently.

time: 1764351562

The current UNIX timestamp on the memcached server.

3. Version and Architecture

version: 1.6.14

The memcached version you are running.

pointer_size: 64

Indicates whether memcached is running in 32-bit or 64-bit mode.

• 64-bit allows larger memory allocations
• 32-bit has lower memory limits

4. Resource Usage

rusage_user: 0.44782

Amount of CPU time spent in user mode.

rusage_system: 0.64816

Amount of CPU time spent in kernel mode.

These help you monitor CPU consumption.

5. Items Stored in Cache

curr_items: 2

How many items are currently stored.

total_items: 5

Total number of items that have been stored since memcached started.

Example:

If curr_items is 2 and total_items is 5, it means 3 items expired or were deleted.

6. Memory Usage

bytes: 142

Amount of RAM used currently by memcached.

limit_maxbytes: 67108864

Total memory allocated to memcached (64 MB in this case).

7. Connection Information

curr_connections: 3

Active connections to the memcached server right now

(PostgreSQL sessions, memcached tools, etc.)

total_connections: 4

Total number of connections since memcached started.

connection_structures: 4

Number of connection objects allocated internally.

8. Commands Execution Counters

These numbers help you understand how frequently cache is accessed.

cmd_get: 9

Number of GET commands executed.

cmd_set: 6

Number of SET commands executed.

get_hits: 6

Number of successful GET operations (key existed).

get_misses: 3

Number of failed GET operations (key not found).

You can compute cache hit ratio:

Hit ratio = get_hits / cmd_get = 6/9 = 66%

Higher hit ratio = better caching performance.

9. Cache Evictions

evictions: 0

Number of items removed due to memory full.

• 0 is ideal
• High evictions means cache is too small

10. Network Statistics

bytes_read: 763

Total data received from clients.

bytes_written: 621

Total data sent back to clients.

This helps assess cache activity volume.

11. Threads Info

threads: 4

The number of worker threads memcached is using.

More threads = better concurrency support.

Purpose:

1. Helps monitor cache health and efficiency.
2. Useful to identify cache hit ratios and tune cache size.
3. Helps diagnose issues like evictions, connection failures.

Conclusion

pgmemcache offers an efficient way to add fast, RAM-based caching directly inside PostgreSQL, making it possible to reduce repeated query execution and improve application performance with minimal effort. By integrating memcached and using a few simple SQL functions, you can offload heavy read operations, lower database load, and deliver much quicker response times.

Throughout this guide, you explored how pgmemcache works, how to install and configure memcached, how to connect PostgreSQL to a running memcached server, and how to apply caching using real SQL examples. You also learned where caching is most effective, such as handling repeated reads, temporary values, counters, and session-like data, as well as when pgmemcache may not be the right choice.