Esoteric Curio - PostgreSQL

Sexified fonts for the web.

nospam@example.com (Theo Schlossnagle) — Mon, 11 Jan 2010 15:08:05 -0500

OmniTI has been working with Clearleft for a while now on Fontdeck. The super Greg Chiasson has been pushing our beta live and I've been provisioning machines and setting up infrastructure just so you can read this web page in a nice sexy font... Nice... Sexy.

And yes, of course, fontdeck is powered by our friends: Apache, PostgreSQL, OpenSolaris, a bit of Linux here and there, a sprinkling of both asymmetric and symmetric cryptography and an ancient dialect of computer-speak called Perl.

Asynchronous PostgreSQL Candy

nospam@example.com (Theo Schlossnagle) — Sun, 27 Dec 2009 13:49:32 -0500

I've put the first (intended) use of the pg_amqp setup to the test. So far I'm very pleased. While none of the code for the usage is open source the real "magic sauce" is open and has had a few bug fixes and bits of robustness added since I last posted. I can, however, describe the use and let your imagination run while.

The parts: PostgreSQL, RabbitMQ, and a bit of Java around Lucene. Our java program actually uses jetty to expose a tiny servlet that accepts search queries and returns json results making AJAX-style searches easy and fast.

The obvious question we all face in these sort of configurations is "how do keep our search indexes up-to-date?" Usually, we relax constraints (wisely so) and determine exactly "how up-to-date does out index needs to be." If you can answer this question with a number sufficiently far from zero, you've won. In our situation, the user experience must be able to search and find the updated items in database via search immediately after insert or update (within a 100ms or so).

Enter message queueing. The first thing we do is make our Java process connect to RabbitMQ, bind a queue and consume. The messages it consumes from the queue have instructions on the precise element that has changed which then causes an query against the database retrieving all the new data to be reindexed (in our case, it is much more than can be easily witnessed from a trigger on update) and updates the Lucene indexes.

The second updated we made is to PostgreSQL by installing pg_amqp. On the tables whose changes should induce reindexing, we add a (or augment the existing) trigger to call: amqp.publish('amq.direct', 'searchstuff', E'reindex\t' || NEW.rowid). Assuming we have a column called rowid, this will queue a message that looks like "reindex<tab>328432"

The neat part is that if the update is rolled back in the database, the message is never sent. Otherwise, we see the message from the our indexing and search app and viola.

I think this technique is really useful for people that are currently leveraging Lucene-based systems to provide powerful search functionality yet keep their data safe and secure in PostgreSQL.

AMQP for PostgreSQL

nospam@example.com (Theo Schlossnagle) — Sat, 19 Dec 2009 18:00:27 -0500

So, it turns out that being stranded in an airport can lead to some productive output after all.

I've hacked together an AMQP extension for PostgreSQL. If you don't know what AMQP or PostgreSQL are, stop reading.

One thing I've needed to do for a while is be able to submit a message to a message queue from within a PostgreSQL transaction. However, obviously (because we run a real database here), if the transaction aborts I'd rather not have those messages sent. Enter pg_amqp.

pg_amqp allows:

Multiple connections to different AMQP brokers (configured in the amqp.broker table).
Allows for the declaration of an exchange.
Tries to be smart about bring up a connection to a broker on demand and leaving it connected to accelerate the next publication request.
Allows AMQP publishing from within SQL that is transactionally aware (only publishes on commit).
Allows AMQP publishing from within SQL that is autonomous (happens outside the current transaction).

This is completely alpha. I've tested it quite a bit by hand, but put no load on it whatsoever. Monday of next week, I'm going to beat the ever-living @#$% out of it on one of our systems that desperately needs on-commit AMQP notifications.

I'd love some more eyes on this. It has some flaws, specifically related to processing asynch events from within a PosgreSQL backend (which has no concept of asynchronous even notification). As such, if you do stupid stuff, stupid things happen. The easiest solution is to add a thread to each postgres backend to process the backqueue of events from AMQP; however that so blatantly violates postgres' process model and design that I've no intention to do that. With all the issues aside — it works pretty well for me.

I didn't add queue declaration or the ability to consume from queues. That was intentional because PostgreSQL can't run SQL outside of a transaction block making a while(1) { consume, do, commit } impossible from within SQL itself. Without the ability to do that it seems really useless (and pretty dangerous) to allow someone to do a blocking AMQP frame receive from within SQL. If you think it would be useful for you, let me know — it would be a trivial addition.

Oh yeah: pg_amqp

OmniTI seeks data management experts; a new type of DBA.

nospam@example.com (Theo Schlossnagle) — Thu, 17 Dec 2009 11:49:25 -0500

What's a DBA? Database administrator? Database architect? Whatever it is, we need one (or two or three) at OmniTI. The problem is that the Jurassic variant of the DBA simply can't cut it in today's world of the web giants (OmniTI services the web giants). What is it about the modern use of database in the web world that make traditionals DBAs so inadequate? It's actually a variety of things. If you think tackling them is a challenge you're cut out for, send me your resume!

Scale

One might say "Scale? Scale? Are you kidding me? Traditional database problems come in various flavors of gigantic." This is true, but the unique dynamics of the web mean that not only is the data flowing in at uncontrollable rates, but tomorrow there is always a new data source with a new representation. Due to the scale of external systems and your own it makes for an interesting sandbox in which we play. A multi-terabyte database is quite common just about anywhere these days; however, ten of them running four different data management platforms is rather common place within large web infrastructures.

Continuity

This is the real game changer. This is the mental paradigm shift that classical DBAs struggle with most. Try this on for size: "no maintenance windows." Okay, admittedly that is a stretch, but only a small stretch. How about 5 minutes of database downtime allowed every 4 weeks or, worse, every 52 weeks.

Coupled with this is daily (or more frequent) code rollout often times requiring database schema change, new triggers, and different replication dynamics.

The amount of seemingly ass-backwards engineering required to accomplish this feat is usually described as either: "excruciating pit of hell" or "fascinating enigma." (I think of it as both) What I do know is that I have seen some of the most profound data engineering creativity emerge from this insipid constraint.

Innovation

While noSQL is a fanatical movement that needs to be kept in check, the problems it addresses are real and [some of] the solutions promoted are good. Understanding when data better fits in a key-value store is something that the classically trained DBA struggles with. It's all about data management: Dynomite, CouchDB, Cassandra, Oracle, PostgreSQL, MySQL… they all have a sweet spot &emdash; know it.

Scope

The other interesting dynamic has less to do with data management and has more to due with the pace and culture of web companies. In traditional companies, roles are more disjointed; DBAs have a set of responsibilities that software engineers and system administrators do not have and those two groups have a set of responsibilities with which DBAs need not concern themselves. This "silo effect" spells absolute disaster for web companies.

The pace on the web is simply too fast (I often think it is a bit reckless, but that's another rant) to keep all the parts separate. An insufficient amount of time exists to define how one layer of an architecture will communicate with another layer. And while the layers meet at reasonable places most of the time, there are other places where the lines are blurred. These are the points in the architecture that stand to have the highest efficiency, but also are the hardest to troubleshoot; they require an organization of engineers that have no boundaries. In the case of a DBA… one that wants to look at the ORM code that builds the insanely awful SQL queries that beat the stuffing out of their database every second; one that is actually aware of the storage configuration and layer-2 fabric that sits behind the magical "remembering device" that the database commits things to all day long.

Beat you with a stick?

Around here, Robert Treat runs the show. The stick wielding and beatings are left to him (he a father of three, so I'm sure he's more than capable).

I guess the question is… are you up for the challenge? Are you a DBA who thinks their job is about more than just ACID? Are you a developer or an SA that has decided to make a shift and tackle the data management layer with a unique perspective? Are you interested in mind bending problems that arrive on your plate a bit too quick for anyone's comfort level? I hope so, because we need people like that!

Itch scratched - right between the rabbit's ears.

nospam@example.com (Theo Schlossnagle) — Fri, 13 Nov 2009 17:18:32 -0500

Net::RabbitMQ, get it while it's hot.

Extending and Embedding

nospam@example.com (Theo Schlossnagle) — Tue, 06 Oct 2009 22:21:48 -0400

If you’re like me, you’ve read a handful of really useful articles about extending and embedding things like perl, Python, Java, lisp, scheme or lua. The slant there is a technical one: “So you need Java, here’s how to embed it.” I’ve embedded Python once, perl and Java countless times and, most recently, lua. Throughout this I realized that I don’t need the languages as much as I need a convenient extension out of C. In other words, if your foundation app is feature-rich, embedding isn't as much about adding the features of a specific language as it is about exposing the features of your system.

Given this context, there is a lack of articles about how to choose the appropriate embedding technology for a given project. Each language has its own advantages and people religiously argue about them seemingly to no end. Things get interesting when you look to embedding languages, as their internal implementation decisions start to influence what is and isn't possible. I’ll take you on a brief whirlwind tour of my personal experience embedding.

Perl

Many of you know I program in Perl quite often. Many people hate Perl and believe it to be an abomination. I love Perl for its flexibility, expressiveness and its terseness — aligning it with myself. However, the perl (5.10 and earlier) interpreter is a horrid, catastrophic example of how not to write a virtual machine for an interpreted language; spaghetti does not begin to describe that mess that is perl internals. The API is insufficiently documented, the internal data representations are indecently exposed and the the way thread support was backed into the code (like a garbage truck through a toy shop) make it more than a challenge to embed. Now, I’ve been writing XS code for years (the C-like language for extending perl itself) and I know and love the language itself, but I’d argue that it is rarely a good idea to embed perl in any application.

You may have noted that I claimed to have embedded perl several times in applications. Some of those turned out to be a good idea. Both (yes only two) of those times, the reason it ended up being a good idea was that to make my application more useful I needed the wealth of CPAN (the comprehensive Perl archive network that contains a wealth of easy-to-use Perl wrappers around common needs). While in the end I won, embedding perl was an awful experience and I wouldn't recommend it to anyone. My specific problems came down to debugging memory usage as it transcends from C into Perl heap and the painful problem that is threading within perl. Each time, integrating into perl’s threading system proved a waste of time and I ended up starting an interpreter for each thread (or had a resource pool of interpreters from which each thread would reserve). So many useful CPAN modules aren’t thread-safe and were never developed with a mindset that they would be loaded into a perl that might be embedded in another system.

While I love the language, I hate the implementation.

Python

While I like Python as a language, I personally find that if my job doesn’t fit in perl and that Python would be a better choice, I usually think a bit harder and realize that C would be even better. I realize this isn’t the same mental leap made by most people, but I love C. C is my favorite language. In fact, I wouldn’t be writing this article if I didn’t write so many applications in C that can benefit from an embedded interpreter to ease configuration and processing tasks.

In fact, I would argue that Python is a great language to embed because the of the interpreter. It's implementation is cleaner than perl’s — as I’ve mentioned the bar could not be set much lower. The fact is that embedding Python in a complicated multi-threaded and/or even driven application really shows the inadequacies of its embedding design. That combined with the fact that I’d rather be coding directly in C means: "I wouldn’t embed that again."

Now, all the applications in which I typically embed interpreters or virtual machines are complicated. If your application is single-threaded and doesn’t do extensive co-routines (e.g. closure-oriented programming or event driven systems) then Python could really be an ideal embedding language.

Java

Java is a complicated discussion. I have a lot of mixed feelings about Java. No language should have functions in its core distribution deprecated at the rate that Java boasts — it’s dysfunctional. Java as a language itself is quite nice. It is powerful, fast, and expressive. It has some annoyances like its verboseness and sprawling code bases. It has one true, deep negative: Java programmers. Now, not all Java programmers are bad, but I've seen far too much Java code produced by programmers far and wide that is of a quality that is flat-out unacceptably low.

When you’re embedding a language, some of those negatives don’t matter so much. We can instead concentrate on ease of embedding. Java has to be, hands-down, the easiest system to embed. The API is crystal clear and absolutely complete. It is all held in a single, small header file called jni.h. Every time I’ve embedded Java is has been simple and painless. JNI team, pat yourself on the back — I’ll happily buy a round of beers if we ever run into each other.

On top of the ease of embedding, the native support for mapping application (C) threads into JVM threads is concise and demonstrates pre-meditated good engineering.

One reason I don’t like Java for this is that it isn’t really “interpreted.” I have to compile (or auto-compile) my code to get it to run in my app. Now, perhaps I could use Java to pull in something like Jython or JRuby to make a rather obtuse, but convenient, system. Success via indirection has never been high on my list — if I ever try it, I’ll be sure to let you know how it went.

Tcl, lisp, ruby, PHP

Too old, too arcane, AYFKM, leave it on the web guys, respectively.

Okay, in all fairness, I didn’t give Tcl its due. It’s likely the most widely embedded language. I just don’t like Tcl and it shows.

Javascript

Javascript deserves its own section for arguments against embedding a language. If you have a single-threaded program that is not event driven, Javascript is a brilliant choice. I don’t remember the last time I wrote an application that was both that simple and was in need of an embedded language. So, the fact that Javascript has no support for threading makes it utterly painful and obtuse to embed in most systems. This saddens me. Of all the languages I program in, Javascript is likely my favorite. I know this must be on the merits of the syntax and expressiveness of the language because I loathe DOM and anything that touches it. Oh! how I wish Javascript embraced threading. Oh! how I wish Javascript had the embeddability of Lua.

Lua

Lua is my new love. It is the reason I decided to write this article. I actually don’t like the lua language. I don’t particularly like the internal implementation (garbage collected instead of ref counted). It doesn’t have particularly useful extensions to add feature value to your system. So, what is it about lua?

I᾿ve been working a lot on a system called Reconnoiter. noitd, the agent responsible for performing active checks against other systems, has a high-performance hybrid thread/event core. Writing code for a hybrid thread/event system can be quite mind bending to write and challenging to debug. Reconnoiter allows writing checks as modules that are dynamically loaded into the system at run-time. The hybrid thread/event core means that the core itself is capable of handing hundreds of thousands of open sockets (think network connections). Writing complex event-driven C code severely limits the audience that can contribute check code to this open source project. This frames the need.

Managing hundreds of thousands of concurrent threads in the system is a recipe for suboptimal performance. In order to remove the complexity of event-driven programming, I need to extend Reconnoiter’s core to a language that could provide the feel of a procedural (or OO) programming atmosphere while maintaining a non-blocking, continuation based implementation for high single-system concurrency.

Now, this is possible (sort of) with many of the embedding choices. When I get to a point in the interpreted code where I need to suspend what I’m doing and complete it later when actionable data is available (say date from a network read) to take some action. The issue is that in order to resume all these languages where they left off, I need to maintain their C stacks. This can be accomplished with a user-space threading implementation strategy using setjmp, longjmp and a healthy sprinkling of black magic. In a lot of ways, this approach doesn’t solve the problem if requiring a interpreter or VM thread for each concurrent operation which would voraciously consume resources.

Enter lua, champion of concurrency. lua is a stack based virtual machine specifically designed for integration with larger C systems. Within lua, assuming you don’t use any third-party extensions (of which there are few), I can leverage the lua_yield and lua_resume API calls to suspend the execution of a lua co-routine (lua-space thread) without the need of capturing a C stack. The state of lua’s execution is represented in the lua stack and simply pick up later right were I left off. This is a technical nuance, but one that provides a considerable amount of freedom. In a single thread, I can open 100k network sockets and have them all independently driven by lua programs without maintaining 100k C stacks. Furthermore, the lua programs give the feel of blocking reads and writes making network programming once again bearable.

After wrapping Reconnoiter’s libeventer and check systems with lua, I was able to replace the previous incarnations of our HTTP checker (based on libserf, then on libcurl) with a 170 line lua HttpClient that consumes less memory and less CPU! If you’re wondering, yes, the client supports chunked transfer encoding, gzip/deflate content encoding, arbitrary headers, client payloads and methods.

So, despite not really liking lua as a language (I find its syntax a bit painful), the simplicity of embedding it my application was on par with Java, the fact that it is truly interpreted (no compiling lua before it will run) and the absolutely brilliant exposure of its continuations as first-class embedding APIs puts me in bed with lua.

Summation

Never embed a language just to embed a language; always have a purpose in mind. Understand the host architecture in which you need to embed and attempt to match the strong points of a particular interpreter or VM to your needs. For example, if you have a heavily threaded host architecture, don’t embed something that doesn’t support threading well (or claims to but takes global locks).

Next time I embed, I will start with three preferences: Java, Javascript and Lua. Sadly, I predict Javascript will be disqualified quickly due to its deficiencies. Java and Lua both have that pros and cons. I just hope the next project finds Lua the best bedmate, I’m already looking forward to working with it again.

A look at rubyrep

nospam@example.com (Theo Schlossnagle) — Thu, 20 Aug 2009 15:27:12 -0400

rubyrep looks neat. I suppose our next round of tests will have to use it on some billion row tables and see how it fairs.

highscalability.com talks about OmniTI's Reconnoiter

nospam@example.com (Theo Schlossnagle) — Sun, 16 Aug 2009 15:13:07 -0400

It’s exciting to see things starting to take off. The momentum and excitement around Reconnoiter are at an all time high here at OmniTI. I really appreciate Todd Herr’s extensive write up about Reconnoiter over at highscalability.com — the review was spot on.

It has ample criticism (all deserved) and we’re continuing to work on the “consumability” aspects of the product. One of the comments he made was the interesting choice of Lua as a language to extend the product. I really wanted to use Perl or Python to extend Reconnoiter, but Lua has some pretty magical properties that made it meld well with the noit internals. In my opinion it is perhaps one of the more technically interesting parts of the product as the search was long and the choice reluctant. When I have more time, I’ll write an article about embedding lua in noit and the deeper reasoning behind it.

Also, don’t be scared by Lua, you can always write extensions in C! Although it doesn’t meld well with its high performance design, there is an extproc module that allows writing checks as standalone scripts and operates fine with existing Nagios checks — I say I wrote it to make adoption a bit easier, but in truth Eric and Mark made me do it.

Big Bad Postgres Indeed

nospam@example.com (Theo Schlossnagle) — Mon, 10 Aug 2009 21:45:39 -0400

I gave a talk at the Percona Performance conference (same time as MySQL, in the same facility... can we say awkward?) about running large PostgreSQL installs. I referred to a few instances in the presentation that are a handful of terabytes in size. In today's world, these aren't that large, however we do pretty deep analytics on these installs. It is most definitely not a case of store and forget.

A few people came up and said: "I thought you were going to talk about big... a terabyte is not big." I would rebut that with it's not how big it is, it is what you do with it, but then I would be on the defensive. The truth of the matter is that it is a combination of things. Size matters: below a certain threshold, you simple can't call it large. Usage matters: if you don't do something interesting with the data, you might as well be throwing it away. While most of the PostgreSQL instances we deal with were considered larger by 2005 standards, a terabyte simply no longer meets the bare minimum for "large."

I'm excited that we're looking to launch a new service that will turn the tables on large for PostgreSQL. We very well could have 10 petabytes in postgres in no time if things go as planned. Not only will we meet the size requirements, we'll also be doing lots of interesting things with the data. Big Bad PostgreSQL indeed.

Make the web a faster place. Pretty please.

nospam@example.com (Theo Schlossnagle) — Tue, 07 Jul 2009 10:36:59 -0400

Call to action! Make the web a faster place! Here's a short article on how I spent 45 minutes to improve user-perceived performance on our website. This is the low-hanging fruit of front-end web performance optimizations.

Most of you who read my blog are scalability or performance nuts. Most of you also cast the majority of your focus (like me) on the back-end infrastructure problems. Don't ignore the front-end when just a tiny bit of work can remove a huge amount of suck.

If everyone takes these steps, the web will be a more enjoyable place to visit.

Web: you can now not suck.

nospam@example.com (Theo Schlossnagle) — Thu, 25 Jun 2009 23:33:33 -0400

In perhaps a new trend, I’m blogging from 39011 feet (or so says the seatback in front of me). I’m traveling back home to the east coast from San Jose, CA where I attended (and spoke) at this year’s O’Reilly Velocity Conference.

I participated (and blogged) about the Velocity Summit in which I’ve participated for the past two years. The summit is the unconference preceding the real conference that help the organizers digest current hot topics and better define the conference track for the actual conference. The summit itself is filled with enough brain power to warp space-time, so I drop everything to go to that.

Ironically, despite being a well respected authority in web site (and general internet) scalability and performance, my talk proposals for Velocity 2008 were not accepted — I clearly need to write better proposals. This year, I managed to work my way into the workshop track on Monday. Despite having a bad headache and feeling "off" the day before, I managed to get my act together and put on an A-game for my workshop. For those of you interested, here is my scalable09 slide stack.

I thought I’d take a moment to talk about what I liked about the conference and what I think could use some improvement. I realize this is a down economy and that might be a legitimate justification for some the actions that resulted in some of my disappointment.

First, the negative. I usually start with positive and end with negative because I’m a pessimist. However, all in all the conference was awesome, so I thought I’d get my short list of gripes out of the way early.

O’Reilly is infamous for throwing good conferences for geeks. In my opinion, the field of web operations has been so severely neglected and applies so broadly to the world today that this conference needs to be for everyone.

In the next conference, I’d love to see a technical business track. Several of the talks I went to spoke to the dollars and cents lost or earned by paying the right amount of attention to web site performance and better operational paradigms. I thought a lot of the topics would be very useful to business managers.
The first day was not video taped and the second and third day were only half video taped. Come on guys, ante up. The attendance fee was substantial, you can afford to give your attendees the value of watching what they had to choose not to attend. I like the option when I go to a conference to choose a session that seems interesting so that I have the opportunity to participate, but often times I find that another session was top notch and I expect to be able to later review a recording of that.
Lastly, and this is the most significant. While I thought the conference was extremely well executed (excellent job Jesse, Steve, all your support, and most definitely O’Reilly), it lacked sufficient PR and marketing outreach. I talked with several journalists (as a part of my normal day job) while I was at that conference and not one of them was aware of Velocity — simply embarrassing. Given that the Structure conference was in town that same week, O’Reilly should have invested more in their PR and marketing outreach. It would have resulted in a substantially increased audience and a better venue for teaching the world to run a faster web.

Now that I’ve griped and aired my disappointment. I can focus on the gobs of awesomeness that was Velocity.

The conference was put on quite well from an operational perspective. Things started on time, A/V problems were non-existent. Like an idiot, I managed to lose my MacBook Air power adapter and the A/V crew managed to recover it for me. Conferences just plain suck when they have technical difficulties; this one had none.
The two tracks at the conference were extremely well articulated and while I wanted to be in both all the time (as OmniTI is a full-stack company, we care about both equally) it was an excellent split.
One track was performance which focused intently on user-perceived performance. This was largely front-end (HTML,CSS,JS,etc.) but also had a healthy amount of deep stack performance discussion as well including the often ignored, but much deserving networking aspect of delivering the web to users.
The second track was operations and I feared it would be bunch of blind “The Cloud Solves All Our Problems” sessions. Much to the contrary, it focused heavily on operational strategy and and what it takes to execute tactically. There was a bit of cloud here and there (okay everywhere), but there was very little blind and ignorant mentality that launching in the cloud was a solution to a hard scaling problem.
I had the opportunity to see a lot of old faces, but spent most of my time meeting new ones. The opportunity to learn more about other people’s problems is what completes me as an engineer. The people I met at this conference were both honest and open and provided a fabulous and refreshing perspective on what today’s performance and scalability problems really are.

In my workshop, I spent about 20% of the time discussing the philosophy of being a good engineer and 80% discussing practice (non-cookbook) with examples and advice. The basic message is that systems are complex and you must think of all the parts holistically or its a recipe for disaster — or failure.

Two of my favorite talks were Nicole Sullivan's “The Fast and the Fabulous: 9 ways engineering and design come together to make your site slow” and “ 10+ Deploys Per Day: Dev and Ops Cooperation at Flickr” by John Allspaw and Paul Hammond. While Nicole's presentation, like mine, was not recorded, the other was and if you want to break down the divide between operations and development, it is a must see.

All in all, I would encourage everyone reading this to attend next year's Velocity conference. I am certain you will walk away with knowledge that is both valuable and applicable.

What time is it?

nospam@example.com (Theo Schlossnagle) — Tue, 16 Jun 2009 15:28:40 -0400

PostgreSQL has pretty awesome date/time functionality. I've used a lot of database and the functionality and thoroughness of the treatment of dates and times (and particularly timezones) is unparalleled. As much as I'm impressed with it, I knew there would come a time where the outcome of all that cleverness would backfire.

Recently, I was doing some data partitioning. I split a couple of largish (approximately billion row) tables up into month segments. I wrote a tiny little pl/pgsql function that takes a parent table, and creates an inherited child with the right indexes, triggers, check constraints (for constraint exclusion) and permissions. I renamed the big table something transient, created a new parent table with the old table's name, made the old unwieldy table a child of that table and then created a whole bunch of new partitions. This allowed me to pretty much ignore my shenanigans from the application side. Once I created the partitions, I need to back populate them. To do this, I did the following:

ALTER TABLE newchildtable_200903 DISABLE TRIGGER ALL;
INSERT INTO newchildtable_200903
  SELECT * FROM oldcrappytable
  WHERE whence >= '2009-03-01 00:00:00-00'::timestamptz
      AND whence < '2009-03-01 00:00:00-00'::timstamptz + '1 month'::interval;
DELETE FROM oldcrappytable
  WHERE whence >= '2009-03-01 00:00:00-00'::timestamptz
      AND whence < '2009-03-01 00:00:00-00'::timstamptz + '1 month'::interval;
ALTER TABLE newchildtable_200903 ENABLE TRIGGER ALL;

Suffice it to say, this did not do what I wanted at all.

PostgreSQL's interval type is one of its more clever features. The idea that a month isn't always equal to a month is a Gregorian truism. So, PostgreSQL is design to "do the right thing" and consider a month in the context of another argument. A month in the above example is a month with respect to March. Right? No.

The lacking part here is the timezone. I do the partitioning in UTC (because I'm not insane). So, I need the month of March in UTC. Although I explicitly stated '2009-03-01 00:00:00-00' in UTC, PostgreSQL interprets that in the client's timezone... and then adds a month. I'm in US/Eastern which is trailing UTC by four or five hours and thus the reference starting point is actually in February of 2009, which only had 28 days. So, the latter inequality up there does not do through the end of March!

I would argue that this behavior is invalid, because of the extremely unexpected results of the following simple test case:

postgres=# select ('2009-03-01 00:00:00-00'::timestamptz + '1 month'::interval);
2009-03-28 19:00:00-04

postgres=# set timezone = 'utc';
SET

postgres=# select ('2009-03-01 00:00:00-00'::timestamptz + '1 month'::interval);
2009-04-01 00:00:00+00

Here I get a completely different date/time if I ask what appears to be a very unambiguous question depending on whether I'm left or right of the Prime Meridian.

Cloud storage primer

nospam@example.com (Theo Schlossnagle) — Thu, 11 Jun 2009 16:41:27 -0400

I post here, I post there... I certainly don't post everywhere. Anyway, I wrote an article that discusses the basic technologies that power large cloud-like storage systems. Thought you might be interested.

Reconnoiter Update

nospam@example.com (Theo Schlossnagle) — Thu, 04 Jun 2009 08:04:34 -0400

A little over a year ago, I started in on a project that was of significant scope. Not a few scripts hacked together, nor a conglomeration of pre-existing tools, but rather a carefully engineered product. What product is this? Reconnoiter.

About 10 years ago, we were neck deep in large scale e-mail architecture. We felt pain, we were up at 3am every night attempting to make systems work. Finally, we decided enough was enough and started a skunkworks project to build a better e-mail server. Well, that turned out pretty well. It's got considerable momentum at this stage and is leading the industry as the most advanced digital messaging platform on the planet.

Over the past several (12) years, we have run operations for small and large sites alike. We're responsible for waking up and fixing things at 3am when they are not working. We're responsible for not only designing highly scalable architectures, we're responsible sticking around and seeing them through to the finish. Many people are writing tools for management; two in the recent spotlight are Puppet and Chef. We had very little pain in the arena of provisioning and maintaining systems. I have a theory as to why that is, but that is a topic for another monologue. One of the distinct pains we have suffered since we began revolves around monitoring.

The first issue is that monitoring is two things:

trend analysis for long-term capacity planning and postmortem analysis,
fault detection.

There are many tools today that are hard to use and fail to address our needs for managing thousands of very different machines. Worse, the tools do only one or the other. This means that we must invest time checking disk space in the fault detection tool to alert us when it is "too full" and configure a similar check in a trending tool to show us historical information. Some patchwork was introduced into fault detection tools like Nagios to add trending features... and when I use it, it is clear it was not central to the design.

I have a lot of gripes, but I won't go into all of them. Suffice to say I have them and I think they are the true fuel for developing a next-gen tool to make operations folk suffer less. Combine that with the combustive talent of the engineering group at OmniTI (and now a few outside it) and the oxygen that the open source community provides, and we'll be having a barbecue in no time.

So what's new?

A lot has happened in recent months on the Reconnoiter front. Here's a set of highlights:

OmniTI labs got its IANA enterprise number. On that we build SNMP trap support into noitd.
Reconnoiter got its IANA assigned port number, no more picking random ports. 43919 or bust!
We support durable streaming (for collection and storage of metrics)
We support temporal streaming and check cloning for real-time, high-granularity metric collection. This means cross-check (even cross-datacenter) real-time graphs for pretty awesome event correlation.
We integrated Esper as a streaming database (complex event processor) as the foundation on which to build fault detection. There's a long way to go here, but the plumbing is in place.
We build a custodial daemon capable of running nagios-type checks to ease adoption -- even though it made me cry.
We started sitting on #noit on freenode, though it is still eerily quiet in there.
We got some new users with the brilliant patience for alpha software and excellent feedback!
I'll be talking about Reconnoiter at the 2009 O'Reilly Open Source Convention.
It's starting to get some serious attention, so it's time we add some polish!

We've been slowly introducing our managed clients to Reconnoiter and we have, at this point, about a terabyte of metric data. In Reconnoiter, there is no default action to discard data. Yes, that's right. Go buy more disk. It's cheap. You'll thank me next time you have an anomaly today that you think reminds you of one seven months ago... and when you go look at the graph you actually find all the data at its original granularity.

Robert, spreading the wealth as usual.

nospam@example.com (Theo Schlossnagle) — Thu, 02 Apr 2009 22:36:55 -0400

I'm pretty excited that Robert Treat is talking at PostgreSQL Conference East 2009. He'll be talking about the upcoming release of PostgreSQL 8.4. The postgres product is pretty complete and has been pretty complete for quite some time. I've been noted for saying I wouldn't use a database feature in production that hasn't been available (and tested) for several years. As such, it is a bit ironic for me to say I'm excited for a new release of a database product. Let me explain myself.

I'm excited by several of the new features coming in PostgreSQL 8.4 because the sooner they are released, the sooner I can start my count down to when I'd trust them in production. Okay, that's not entirely fair. When you engineer things to embrace failure it is easy to launch newly released features with less risk. However, we run people's online businesses. Some things (e.g. those that must comply with these specifications), even if engineered to allow for failures, you simply don't take the chance. For other things, you can embrace failure and improve the product through feedback by running them in a globally trafficked, high impact environment.

Whatever the circumstances, I'm excited by the pending PostgreSQL 8.4 release. Perhaps Robert will give the same presentation to OmniTI at one of our weekly development sessions so that we know what the hell is going on.

Make sure you check out Robert's talk:

Another year, another PostgreSQL release, and once again this release packed full of new features that will help you whether you develop apps inside or outside the database, or you just need to keep them running. This talk will give an overview of the new features available in 8.4, and give you pointers to talks during the rest of the conference you'll want to focus on to get the most out of 8.4.