Database Reference
In-Depth Information
Chapter 9. Scaling Your Database with
PL/Proxy
If you have followed the advice in the previous chapters for doing all your database
access through functions, you are in a great position to scale your database by "hori-
zontally" distributing the data over multiple servers. Horizontal distribution means that
you keep just a portion of a table on each "partition" database, and that you have a
method to automatically access the right database when accessing the data.
We will gently introduce the concepts leading to the PL/Proxy partitioning language,
and then delve into the syntax and proper usage of the language itself. Let's start with
writing a scalable application from scratch. First, we will write it to be as highly per-
forming as possible on one server. Then, we will scale it by spreading it out on several
servers. We will first get this implemented in PL/Pythonu and then as samples done
in the theme special language for this chapter—PL/Proxy.
Note
This approach is worth taking only if you have (plans for) a really large database.
For most databases, one server plus one or perhaps two hot standby servers
should be more than enough.
Simple single-server chat
Perhaps, the simplest application needing this kind of scalability is a messaging (or
chat) application; so let's write one.
The initial single-server implementation has the following specifications:
• There should be users and messages.
• Each user has a username, password, e-mail, list of friends, and a flag to
indicate if the user wants to get messages from only their friends, or from
everybody.
• For users, there are methods for:
