How about a ZFS-Extension module for Postgres?

Now that ZFS on Linux is live  one can argue that ZFS is available on multiple OSes including Solaris, FreeBSD (thanks Marc), Linux and Mac OS X (almost). I started thinking about how ZFS can simplify Open Source Databases effort to provide features that ZFS offers and thats still available in OSDB.

 What features of ZFS will be appealing to OSDB would be an obvious question. I think the following ZFS features provides quite a bit of excitement to any DBA. In this example I will take a Postgres view (based on what I know about Postgres and that may not be much and I might be missing some features in my evaluation here)

• ZFS offers simple commands to setup bare disks
• ZFS offers simple commands to quickly monitor the status of the disks
• ZFS offers snapshot features allowing to roll back to a previous snapshot ("Time Machine")
• ZFS offers to add devices quickly without causing a down time
• ZFS offers checksum, self healing, compression capabilities

So for this example how can PostgreSQL take advantage of these features?

My first reaction will be the extension to the SQL standard "TABLESPACE" commands. If I were to create a ZFS feature extension to PostgreSQL, the first command that I would add more options will be the CREATE TABLESPACE command. For example PostgreSQL will have a default ZFS pool option and every "CREATE TABLESPACE" can be created as an additional dataset within ZFS with its own checksum, compression, snapshot options. That itself is a huge gain in the number of features. Of course snapshots here will be tricky since you still have to worry about LOGS to be in sync and could require lot of handling here. But the simplest option will be to put snapshots on the default pool itself and roll back the whole database to the point when snapshot was last taken, this is great when you are about to do migrations to a new version, or going to do lots of changes  to the database. Ahh I am deviating from the point of CREATE TABLESPACE to the whole database. Anyway getting back to the CREATE TABLESPACE, I think it will be easier to have an extension in postgresql which will eventually create ZFS datasets with the mount point as determined by the syntax. The advantage of doing this then the DBA can quickly use zfs list to determine all the tablespaces, amount of space used, fine tune it to the workload using the "recordsize" tunable of ZFS.

Similarly I think "initdb" and "CREATE DATABASE" can be extended to have and set default ZFS pools to have the snapshot features work at the database level itself. Now that's cool... Imagine if you have a testbed where you destruct the database every now and then and then have to rebuild the database again just to destruct it again to do "breakdown" tests, such snapshot feature can cut down your "setup" time by a magnitude.

There could be other things here that can be beneficial here. I think I will have to discuss this more with other folks.