Preloading or Caching the table in PostgreSQL is a tough task, because
PostgreSQL doesnt offer a Single big synchronize-level memory managment.
All the memories are independent. Caching is possible with the third
party tools like memcached.
pgmemcache is a set of PostgreSQL user-defined functions(API’s) that
provide an interface to memcached. pgmemcache, pre-requisites recommends
to have libmemcached, however its also recommended to install memcached
along with it. My presentation consist of
installation/caching/monitoring using pgmemcache API’s. As am not the
Developer or Hacker :), so my way of implementation is in very simple
- Stores value in cache on the basis of Key/Value means, keeping table
with primary key/unique key is recommended.
- No Data redundancy - If memcached goes down or runs out of space,
new records and updates will be lost.
- Supports all memcached commands
- After keeping the data into memcached and if you drop the table from
backend, memcached won’t throw any errors. Its all your management
how you maintain it.
- No ability to iterate over data or determine what keys have been
- You can never bring a memcached server down or add a new one to the
pool while people are playing or connected.
- If the background updating process stops for any reason, updates do
not occur and there is a possiblity that the memcached server could
- Every PostgreSQL backend has to bind to memcached port before
accessing the data.
- Memcached runs on default port 11211
- PostgreSQL 8.4. or above
- Monitoring-Tools (monitoring-tools,damemtop,etc.,)
Step 1. (libevent)
Libevent API is important when configuring pgmemcache, I prefer to have
libraries as first step of installation. So lets start with libevent
library configuring in default location.
Download link for libevent:
tar -xvf libevent-2.0.12-stable.tar.gz
Step 2 (memcached)
Install memcached by enabling the libevent.
Download link for memcached:
./configure --prefix=/opt/PostgreSQL/9.0/bin/ --with-libevent=/usr/lib
./configure --prefix=/opt/PostgreSQL/9.0/bin/ --with-libevent=/usr/lib64
Step 3. (libmemcached)
pgmemcache is built on top of libmemcached. Libmemcached looks for
memcache binary location, so set the path to memcached binaries before
Step 4 (pgmemcache)
pgmemcache API will help in, interacting with memcached like
PATH=/opt/PostgreSQL/9.0/bin:$PATH make USE_PGXS=1 install
Installation will create pgmemcache.sql file with all API’s to interact
with memcache under PG contrib location. To create pgmemcache API’s,
just exectute pgmemcache.sql file in all the database.
psql -p PGPORT -d PGDATABASE -f /opt/PostgreSQL/9.0/share/postgresql/contrib/pgmemcache.sql
pgmemcache API’s list:
Note: While executing .sql file you may face error like “ISTFATAL:
could not load library
cannot open shared object file: No such file or directory”. Means, PG
instance didnt loaded with newly created library. Resolution, set the
PATH and LD_LIBRARY_PATH and restart the instance to recognize the
$pg_ctl -D $PGDATA restart
If you want to load the pgmemcache as default to your PG instance, edit
the postgresql.conf file and change the following parameters and restart
For caching data, first you need to initialize the memory, once the
memory is allotted, later PG backends responsibility to bind and push
the data into the cache. Here, I have started my memcache on localhost
with 512MB on default port 11211. -d means start the daemon. All
my exercise is on localhost.
$./memcached -d -m 512 -u postgres -l localhost -p 11211
Note: To retreive data from the cache, every PostgreSQL backend should
first bind and retreive the data.
Bind the instance to the running memcache port. After binding, checkout
for the memcached statistics.
Now, its time to cache data into memcached, Memcached uses keys/value to
reside data in its memory, so make sure your table has Primary/Unique
key so retrieving will be easy. As mentioned, there are very good API’s
to play around on keeping the value and accessing them, in my example, I
use memcache_set() to keep the value and memcache_get() to retrive
Once the value is set in the memcached, now its your responsibility to
bind your backend to memcached and with the help of pgmemcache API’s you
can access the data. Each Postgres backend must bind before accessing.
Please find the example below.
Getting data from cache
If you are very good in linux you can pull maximum information on
memcached memory, however there are few tools which come along with
memcached source pack like monitoring-tools,damemtop etc.,. Am using
monitoring-tools utility for monitoring memcache.
memcached-tool localhost display
memcached-tool localhost dump
memcached-tool localhost stats | grep bytes
A small effort from my side to setup pgmemcache and understand the
basics. Hope it was helpful. Keep posting your comments or suggestion
which are highly appreciated.