Deploy Schemata Like a Boss

One of the many new features in Postgres 9.1 is Extensions. In their simplest form they are a collection of database objects. Think of it as package management for your database. It lets you add, remove and upgrade a collection of objects. All contrib modules shipped with Postgres now use this method.

This is just the beginning though. Normally when you think of contrib modules for postgres you think of some new functions or data types. With extensions you can just as easily manage relations. Create a table in one version of the extension and then add a column in another version. Be sure which versions of your functions and relations exist on a database by managing them as a group instead of as individuals. You can break up your schema into logical chunks and give responsibility of each chunk to an appropriate team in your development group to distribute the workload.


One extension usually has multiple versions. The version identifiers don't need to be numeric. It doesn't understand the concept that version 2 might be newer than version 1. So rather than upgrading, you are merely transitioning between versions. You can think of this like a directed graph. Each node is a version and each edge is an update path. You can also chain paths if there is no direct route. In the example to the right, there is no direct path from β → γ but you can go β → δ → γ. Below you can see all the permutations listed from the database.

example=# SELECT * FROM pg_extension_update_paths('myext') ORDER BY source,target;
 source | target |    path  
--------+--------+------------
 α      | β      | α--β
 α      | γ      | α--δ--γ
 α      | δ      | α--δ
 β      | α      | β--δ--γ--α
 β      | γ      | β--δ--γ
 β      | δ      | β--δ
 γ      | α      | γ--α
 γ      | β      | γ--β
 γ      | δ      | γ--α--δ
 δ      | α      | δ--γ--α
 δ      | β      | δ--γ--β
 δ      | γ      | δ--γ
(12 rows)

There is also a special source called 'unpackaged' that you can use to take an existing schema and put it into an extension. It is simply a script that takes every object in your database and adds it to your extension. This way you don't have to do a big migration to get your schema working out of an extension.

So you've decided you want to try this? Great! Here is what you will need:

A control file (<extname>.control)

default_version = 'β'
comment = 'my extension'
relocatable = true

default_version is the version that will be installed unless otherwise specified. relocatable means that it can be put in an arbitrarily named schema and also move from one schema to another. The only reason that wouldn't be true is if you explicitly reference a schema name in your scripts. There are some other settings you can use as well, such as requires which adds a dependency to another extension. For example if your extension requires the pgcrypto extension.

A Makefile:

EXTENSION = myext
DATA = myext--α.sql myext--α--β.sql myext--α--δ.sql myext--β.sql myext--β--δ.sql myext--γ.sql myext--γ--α.sql myext--γ--β.sql myext--δ.sql myext--δ--γ.sql
PG_CONFIG = /usr/pgsql-9.1/bin/pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

EXTENSION is the name of your extension. DATA is the list of all the install and update SQL scripts. Every time you add a new version you need to append to this line. PG_CONFIG is the path to your pg_config binary for the install you want to use. The last two lines are just standard Postgres Makefile code for pulling in all the install specific information it needs.

The other pieces you need are the SQL scripts to do all the work. The install scripts (<extname>--<version>.sql) are basically just a bunch of create statements. Something like you would get from a pg_dump. They create all the objects for a particular version. The update scripts (<extname>--<source>--<target>.sql) consist of a combination of create, alter and drop statements. This is all the SQL that you would need to modify a schema of the source version to convert it to a schema of the target version. This will usually be a create table or alter table or a create or replace function. If you need to remove an object you will first need to disassociate it from the extension.

ALTER EXTENSION myext DROP TABLE foo;

Then you can run a standard drop table. If you want to be able to "update" to the previous version, you will need a separate script for that as well. I would suggest not trying to make a graph as complex as the one above. Most likely yours will be completely linear.

So how easy is it to install the extension? Run make install in your extension directory, then:

example=# CREATE SCHEMA myext;
CREATE SCHEMA
example=# CREATE EXTENSION myext WITH SCHEMA myext;
CREATE EXTENSION

That easy. And to update?

example=# ALTER EXTENSION myext UPDATE TO 'γ';
ALTER EXTENSION

You can use \dx to list installed extensions:

example=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description        
---------+---------+------------+------------------------------
 myext   | γ       | myext      | my extension
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

As you can see from this brief introduction this is pretty powerful stuff. It's still a little rough around the edges, but it will be getting better. I recommend taking a look at the documentation to get a more complete understanding of all this can do.

Can a guy get a little TRIM?

I don't want to try to convince you to use an SSD. I'll just assume you already have one. I'm not going to tell you about all the myriad ways you can improve your performance. I'll just tell you about one, TRIM, and why almost everyone should be using it.

Writing to flash is very different from writing to a traditional HDD. You must first erase the flash cells before writing to them. You'll probably remember this from days of flashing your BIOS. You must erase an entire block at a time (usually 256k), but can write to individual pages (usually 4k) within the block. The flash cells also have a limited lifetime of erase cycles. If certain parts of a filesystem were written to more often than others, you will burn out those flash cells faster and then the whole drive will be unusable. The way SSD's get around this is a process called wear leveling. Instead of reading/erasing/writing to the same block, it writes to free pages in the same or other blocks. This is known as dynamic wear levelling. The drive keeps a mapping of Logical Block Addresses (LBA's) to physical pages in flash and swaps the used page for an unused one. There are also extra "hidden" blocks that the OS is unaware of that the drive can use for this purpose. Drives will also move infrequenty written blocks so that they can get more use. This is know as static wear levelling. When a page is moved the old location is marked stale. Then a process called garbage collection is used to move the good pages to a new block and then erase the block so that there will be free blocks when new data needs to be written. This may also be done in the background during idle time. All of this leads to something called write amplification. This is basically when you want to write some small quantity of data, say 4k, to the drive, but the drive ends up writing much more because it needs to do wear levelling and garbage collection. Obviously this causes reduced performance and more erase/write cycles and lowers the drives longevity.

So the problem here is that the drive only knows that a page is stale once the OS tells it to write to an existing page and it performs wear levelling and places that page somewhere else. Normally when the OS deletes data on a disk it just updates a map somewhere saying an inode is no longer in use. What TRIM does is tell the SSD that the page is now stale much earlier so it can make better choices when wear levelling and it won't copy data around with garbage collection that the OS doesn't care about. This will lower write amplification and increase performance and drive longevity. This works especially well for cases where data is read and over written frequently, like databases.

At the OS level TRIM is usually referred to by a more generic term 'discard'. This is because TRIM is specific to the ATA command set, but discard support can be used for other types of devices. The way to enable this in Linux is by simply adding 'discard' to the mount options for the filesystem. As of RHEL/CentOS 6.1 only Ext4 and LVM have support for discard, but in the kernel.org mainline kernel most FS's have it. Support for other OS's varies. If you decide to try this out, I suggest you benchmark before and after and also make sure you have the lastest firmware for your SSD.

Disclaimer: TRIM is not a magical wand you can wave. It will not fix all your problems nor will it always make the drive perform better. Some newer drives that use compression won't see any benefit and some firmware implementations may do "the wrong thing" with TRIM information, or perhaps nothing at all. Also all your layers need to be TRIM aware. That includes LVM and RAID.

What's in a name?

I'm starting this blog to share some of my professional experiences with the world. I hope some people find it insightful, but if not I needed a break from real work anyway. I'll keep this post short, but I wanted to advise you to take anything I write with a grain of salt, because it's all low sodium.