zillablog

We're coming up on conference season again. While hectic, I always enjoy (or at least try to enjoy) conference season as a chance to not only share the things we've learned whilst abusing Postgres at OmniTI, but also as a chance to meet-up with fellow community members and see what new and interesting ways they are making use of Postgres in their worlds. First up this year, I'll be speaking at the PostgreSQL East Conference, taking place Aril 3rd-5th, in Philadelphia, PA. This year I'm involved in two talks:

The first session will be on Friday, and will take shape as a 3 hour wander through the upcoming PostgreSQL 8.4 release, now nearing beta. We'll discuss some of my favorite new features, discuss some other 8.4 related talks during the conference you'll want to see, and provide some hands on examples of 8.4 goodness.

On Sunday, I'll also be taking part in a round-table discussion on Postgres Performance, along side Greg Smith, Andrew Dunstan, and Joshua Drake. I speak with all three of these guys regularly, so I'm excited to be serving on a panel with them. I'm certain the audience will also have several notable Postgres experts, so it's likely to be a lively discussion.

In between all of that is a solid line-up of talks for DBA's, developers, and newbies as well, so if you're into Postgres, take this opportunity to get out, learn some new things, and meet some of your fellow Postgres Community members.

I'm starting to see more and more chatter about a report from MarketWatch about IBM's apparent move to purchase Sun Microsystems; a lot of it centered around people's $favorite_open_source_project and how the ramifications of the deal might affect things in the long term. Personally I see a lot of downsides for open source as a whole if this deal goes through, based on the contrast of Sun's tight control of many of todays most popular open source offerings, and IBM's more general hands off approach.

Yesterday the posterous guys put up a very good article on scaling rails applications from the ground up (the ground up bit is key; we're focusing on what you need to do as a start-up, not once you are Facebook). It's a must read for any Rails shop, and a good read regardless of the application stack you work with. One of the comments to the post was from jonathanwallace asked about stories for folks using a Postgres back-end. I started to work up a response for this, but before long decided it needed to live as a full post. I should mention that much of what was posted here applies just as well to Postgres (or even Oracle); mostly I just wanted to point out some differences for those trying to scale on a Postgres backend instead, so this blog post is essentially a "diff" to the original article. Read that first, then read this.

"You're not going to run full text search out of your DB."

If you're using Postgres, this probably isn't true. Postgres has a built in full text search implementation that runs very fast, gives multiple indexing options, and has all the flexibility of multiple languages, custom stop words, and custom dictionaries, that you would expect from a solid full text search implementation. It might not beat a solution built on Sphinx/Lucene/Solr on straight performance numbers, but the ability to maintain your full text data in a transactional manner, all using one piece of technology rather than having to bolt on an additional application, make it worth it to start with the built-in stuff. For many people, this will be all you ever need.

"Storage engine matters, and you should probably use InnoDB"

Ok, this isn't exactly about Postgres, but the reasons he gives for InnoDB (crash resistence, non-locking) also apply to Postgres. (And yes, there's a certain irony that all the reasons why you want to switch to InnoDB are the same reasons you should have just picked Postgres in the first place.) This ability to handle higher concurrency rates is actually even more important with Postgres because...

"if you can start with some replication in place, do it. You'll want at least one slave for backups anyway. "

You don't need a slave for backups (pg_dump will give you online backups just find thank you), but you should create a PITR slave for failover. This wont allow you to scale reads or writes; you'll need something like Slony for that, but since Postgres is going to scale way up, you don't need to worry about that for now, just keep buying bigger hardware.

"Fix your DB bottlenecks"

No matter what you code in, having tools like the ones laid out for Ruby/Rails can be very helpful. If you use any kind of ORM, you need to watch for excessive queries and dumb queries. PostgreSQL provides a slow query logging option, so that's one place to start. You can also do profiling of queries using pgfouine or PGSI. Oh, and Postgres's EXPLAIN tool kicks the MySQL EXPLAIN's butt. Learn it. Use it.

And yes, you should start here before you move on to memcached and other types of query caching, because in a lot of cases you may not ever need to go there (honestly memcached use isn't very widespread in the Postgres world, and a lot of the reason is it's not needed. fwiw, it's even less so in the Oracle world.) Oh, it's not (just) that those databases are just full of more magic data grabbing juice, it's that you'll also likely want to implement materialized views inside the database before you start bolting on external solutions like memcached. Note that this doesn't apply to static content caching (images/js libraries/etc...), you'll probably want to move into caching that stuff much sooner.

"Offline Job Queues... I don't know why people don't talk about this more, because if you run a site that basically does anything, you need something like this."

In case you're not sure if it needs to be part of Rails, postgres has a notification service built in, and there are also plenty of external queuing systems available. The interesting thing is that it probably doesn't matter which technology you pick; the pain points will be obvious (resize an image 5 different ways and cache them on 3 different servers upon web form submission?) and moving those out of the critical path will be an obvious win.

"If you don't monitor it, it will probably go down, and you will never know."

Again a universal truth. In the Postgres world, check_postgres is hands down the best off the shelf monitoring you can use. You should also think about trending; In MySQL land, Cacti with Baron's templates are very solid, but there isn't a clear cut winner on the Postgres side. If you hired us for operations support and don't have an existing data trending solution, we'd likely use Cacti or Noit, although options like Staplr and I suppse evenMRTG are ok too; point here being you have options, use them.

The rest of the post is mostly Rails focused, and I'll leave it be at that, but hopefully this gives you a better picture of how things will scale if you're doing Rails/Postgres. The picture is mostly the same, just the walls are moved further away.

Before I go too far, let me disclose that I submitted a talk for CommunityOne designed to cover some of the tools we've open-sourced that combine Postgres with various parts of Solaris (like dtrace and zfs), which was turned down.

Now, I'm willing to accept that my talk didn't fit the theme of the event or that Sun didn't find me to be a compelling speaker, or maybe they would just rather forget about Postgres in general; it happens, no big deal. But what I do have to take issue with is roster of talks that is chock full of Sun employees at an event called "Community One". Looking at the released list of sessions, I get 24 of the 31 talks being given by Sun employees; that's over 75%! If you want your conference to be about community, you need to have some actual community members involved.

I guess I shouldn't be surprised by this from a company that thought it could buy itself an open source database community, but last years line up wasn't nearly this Sun heavy. Not to mention the recent flack from the MySQL conference being so weighted toward MySQL employees. Is anyone at Sun paying attention? I suspect they have met the internet, but they seem to be two steps back on meeting the open source community.

So, not sure I will be attending CommunityOne this year. It is just a train ride up north, so there is still an outside chance, but that lack of community in this community conference is uninspiring (I suppose if I want community inspiration, I should be looking at Open Source Bridge), but I'd love to hear from others if they are going.

Wanted to give a hat tip to MindGrub, tonight's speakers for the Columbia PHP Meetup. Due to some scheduling issues, their designated speaker Todd Marks couldn't make it, but they more than made up with it, by sending Patrick Wilson, their top software engineer, who helped host Todd put a virtual talk, by way of speakerphone and remote desktop. Very nice! MindGrub does custom development of social networking oriented websites, primarily using technologies like drupal and kickapps. They discussed several implementations, some of the tech behind the scenes, and other practices they've come to embrace (yay svn!). All in all it was a good evening with lots of technical discussion. I also want to say thanks to Chris Stone, our organizer, for pulling things together, and OmniTI for donating space/refreshments. Hope we can do it again next month!

Last Friday's news of Monty officially resigning was important news, though not really earth shattering. The news of Martin leaving was actually bigger to me, since I didn't see it coming, and the exit strategy seem less clear for him. That said, I think that the general discussion on these events are just rehashes of the same arguments that come up whenever the next big drama hits in the mysql world. It happened when they sued NuSphere, happened when they GPL'd their libraries, happened when PHP removed the MySQL libraries, happened when they split the development tree, happened when Sun bought them out, happened when Axmark left, happened when they released 5.1, happened when they released 5.0, happened when they talked about proprietary only features, happened when they forked drizzle, happened when Oracle bought InnoDB, and maybe even a little when Oracle bought BerklyDB . Drama Drama Drama! Every time, people claim the next "drama" is what will help MySQL get to the next level; other people claim this will be the downfall. Some people who were going to switch to MySQL don't. Some people who are using MySQL switch to Postgres. And yet, the new users keep coming, and the machine rolls on. And so I think it will be with these changes. MySQL is like Windows now; it can afford to stumble, it can even afford to do harm to it's users; it doesn't matter. It has reached a level of ubiquity that means it will be around for quite some time. That is not going to change, and that is not what this post is about.

So, having wasted a paragraph on that topic, what is this post about? Mostly I am wondering if someone can answer the question of "who owns Maria now?". Monty said that he plans to start his own company that will do custom development and also continue developing the Maria table egine. But it seems odd that he would do this if that code is still owned by Sun, since they would control the release cycle, have final say on all patches, and also require some kind of proprietary licensing rights to keep it in the main product. And I want to know if it will be in the main product. I like several of the ideas in Maria, but right now there is no way to get it in a format that I would run on my customers production machines. That's fine, it is still under development, I shouldn't run it in production now anyways. But what is that ultimate path that gets it into my servers going to be? Drizzle is not ready, and I don't think it's end game involves Maria (though I think it would be a better fit overall). The OurDelta builds are not quite there yet either, though this might just be a product of my own comfort level rather than the code itself. As much as I don't trust Sun/MySQL code, I know there is a good user base to turn to for FLOSS level support, and I don't think OurDelta has that yet. Also I don't have Baron on IM, so no free advice from him . I think if Sun owns Maria, it means that Maria development will stagnate, with Monty being unhappy working within those confines. OTOH, if Monty Program AB owns it, then I see issues with it being accepted by large numbers (much like SolidDB never was) in the Sun/MySQL ecosystem, but probably more chance for adoption within OurDelta; meaning I should start paying more attention to OurDelta. So, does anyone know the answer?

One thing I haven't really seen discussed is how best to handle controlled failover scenarios when using PITR warm standby. Typically when failover is discussed, it is done with the typical notion that your primary database has actually failed, and you now need to bring up the secondary database and let it "take over". Traditionally this is controlled using some kind of trigger file; pg_standby waits for the creation of the file, I've also seen scripts that look for removal of a file; but you could have any number of mechanisms for when you "flip the switch" from warm standby machine to active database. Now, when your primary has gone down, the choice is simple... make sure you don't have any unprocessed xlog files, and the flip the switch.

But what if your primary hasn't gone down? There are two scenarios where this seems likely (though I would guess there may be more). The first would be in testing your failover mechanism, where you would like to bring up the slave to verify things are working OK on the test machine (We like to do this using zone promotion via zfs, with our db clone script, but it's not always an option). The second would be where you have a problem on the primary that spells pending doom, but not immediate doom (failed cpu, failed disk, need for security patch, whatever). Yes, you need to being up the secondary, but you have some amount of time to prepare for a smooth transition. In both of these cases, your primary goal is not simply to get the slave up and running, but also to minimize any potential data loss that could occur from the asynchronous process.