Database Reference
In-Depth Information
• First, it opens a new database connection each time the function is called,
which kills performance
• Second, it is a maintenance nightmare if you hard-wire the partition in-
formation in full in all functions
The performance problem can be solved by caching the open connections, and
the maintenance problem can be solved by having a single function returning the
partition information. However, even when we do these changes and stay with PL/
Pythonu for partitioning, we will still be doing a lot of copy and paste programming in
each of our proxy functions.
Once we had reached the preceding conclusions when growing our database sys-
tems at Skype, the next logical step was quite obvious. We needed a special parti-
tioning language, which would do just this one thing—calling remote SQL functions,
and then make it as fast as possible; and thus the PL/Proxy database partitioning
language was born.
PL/Proxy - the partitioning language
The rest of this chapter is devoted to the PL/Proxy language. First, we will install it.
Then, we will look at its syntax and ways to configure the partitions for its use. Fin-
ally, we will discuss how to do the actual data migration from a single database to a
partitioned one and then look at several usage examples.
Installing PL/Proxy
If you are on Debian, Ubuntu, or a Red Hat variant, installing the language is easy.
1. First, you have to install the required packages on your operating system:
sudo apt-get install
postgresql-9.2-plproxy
Or:
sudo yum install plproxy92
