PL/Proxy

Project Overview

PL/Proxy is database partitioning system implemented as PL language. Main idea is that proxy function will be created with same signature as remote function to be called, so only destination info needs to be specified inside proxy function body.

Project Status

Stable, in production.

Features

• PL/Proxy functions detect remote functions to be called from their own signature.
• Function can be run on one, some or all members of the cluster.
• If query is executed on several partitions, it will happen in parallel.
• Queries are run in auto-commit mode on the remote server.
• Query parameters are sent separately from query body, thus avoiding quoting/unquoting overhead on both sides.
• Does not contain code connection pooling, works with external pooler if needed, preferably PgBouncer.

Documentation

• Language syntax.
• Cluster config functions.

• FAQ, describes few design decisions.
• Official tutorial

• Kristo Kaiv's tutorial: part 1, part 2.

Downloads

• Source releases: https://plproxy.github.io/
• GIT: https://github.com/plproxy/plproxy

Binary packages (may not be up-to-date)

• Deb: PL/Proxy is included in Debian and Ubuntu official repositories as postgresql-X.Y-plproxy package, where X.Y is postgres major version.
• RPM: PGDG Yum repository

Community Support

• Discussion: https://gitter.im/plproxy/plproxy
• Bugtracker: https://github.com/plproxy/plproxy/issues

Quick Examples

Simple remote function call

Connect to database "users" on localhost and call SQL function there: SELECT * FROM get_user_email($1);

CREATE FUNCTION get_user_email(username text) RETURNS text AS $$

   CONNECT 'dbname=users';
 
$$ LANGUAGE plproxy;

Partitioned remote function call

Users are spread over several databases, partition number is acquired by taking hashtext(username). This needs also configuring the cluster, described in documentation. After this is done, actual proxy function looks following:

CREATE FUNCTION get_user_email(username text) RETURNS text AS $$

   CLUSTER 'userdb';
   RUN ON hashtext(username);

$$ LANGUAGE plproxy;