Topp Engineering

The question has arisen on two different projects (one hypothetical, one already live) at TOPP in the past week:

Should we (continue to) use ZODB as the primary data store, or should we use … something else?

Well. I’ve been using ZODB in my daily work since at least 2001, so I’m pretty well familiar with it by now. By contrast, I’ve done a little bit of work with MySQL, a tiny bit with other relational systems (SQLite, SQL Server, and Oracle), none with PostgreSQL, and almost no work with any of the current crop of object-relational mappers … nor with any of the hip new kids on the block like CouchDB.

With that background, you’d expect me to be a fairly ignorant guy who always prefers ZODB just because he doesn’t know any better, right?

But there’s also the truism that familiarity breeds contempt. Consider yourself warned about the rest of this post

That said, I’ll start by listing some of the things I really do like about ZODB.

What’s Good about ZODB

• Persistence is very transparent (to your application) … Getting started with ZODB is really, really easy. If your app is not performance-sensitive, you can get away with very little attention to storing your data, it mostly Just Works. And you hardly have to do a damn thing. Neat. (When performance is a problem, you have to start paying a little more attention - or maybe a lot.)
• Schema, shmema. You just throw Python objects in there and they just stick. Neat.
• Undo. When you can use it, this is fantastic to have built in.
• Transactions that mostly just do what you want. Okay, this is really about the default transaction policy in Zope, not ZODB per se. But this is something that I really think Zope got right. (And thanks to repoze.tm, it’s something that any WSGI app can now do with any data store that supports transactions.)
• Text Indexing … there are some nice full-text indexes available for ZODB. On the other hand, these work at the application layer - i.e. you have to do the work of updating the index yourself, it doesn’t happen magically when you save data. And they don’t play nice with Undo. I gather there are now some that you can use without the whole Zope 2 ball of wax, which is great.
• Container hierarchies of arbitrary depth are trivial. This is really nice and something that’s easy for a ZODB user to take for granted. Doing the equivalent in an RDBMS is typically not so fun, I gather. (Haven’t had to do it yet myself, but some quick browsing suggests that it’s really pretty icky. I suspect though that traversing a ZODB object graph will have the same performance characteristics as the fairly simple “adjacency model” described in that article.)
• Scaling is pretty transparent. For a while at least. ZEO is trivial to set up, and to the application, it looks no different than running against a local storage. Multiple mount points (analogous to mounting different physical storage at different directories on a filesystem) also help you scale transparently.

What’s Bad

• Persistence is very opaque (to anything other than your application).

Let’s unpack that a little:

• No ad-hoc queries.

  This is more and more often the showstopper for me.

  With an RDBMS, or CouchDB for that matter, as long as the database server is running, you can poke around and see what’s there. With even a small bit of application knowledge, this can be enormously useful for troubleshooting, quickly repairing data problems, and some simple migrations, to say nothing of actual feature development. With ZODB, you have to know a lot more about the app just to look around and guess what you’re looking at.
  

• You can’t even load the data without exactly the right application software installed.

  This is closely related to the previous point. It doesn’t bite you as often, but when it does, it is NO FUN.

  It’s not just that non-Python applications can’t talk to the database at all, ever. It’s that your database depends on your code too much.

  If you botch an install, or are trying to resurrect a really old one for some reason, sometimes you’ll make a mistake like having a slightly incorrect version of some dependency, such that some container class can’t be loaded. Since the ZODB is strictly a tree structure, there is no way to access any of the children of a broken container instance. Which could, if you are unlucky, translate to all your data.

  Think about that for a second: if you ever lose the ability to perfectly reconstruct your code stack, you also lose your data. Well, you could maybe try to parse something out of raw pickles, but that sure doesn’t sound fun!

  Of course, normally everything’s fine because you have the right software installed. But what if you’re doing forensic work and you don’t have enough information to know what that is? Or what if the build scripts that used to work perfectly no longer work just because some third-party upstream release is no longer compatible?

  Here’s a little story. I once did a quick job for a non-technical nonprofit that was in a bind. Their initial email went something like: “Hi, we hired a contractor to build our Plone site on a shoestring, and now he’s gone, and our production server crashed, and all we have is a .zexp export of the site, and zipfile of the code but we’re not sure if it’s the same as the production version. Can you help us get Plone started or at least get the documents and images out so we can throw up some kind of temporary static site?”

  I gave it a go for a couple days, but I was thoroughly defeated. I felt so bad I only charged them for a couple hours and felt guilty for even doing that. I never want to put an employer or client in that position again, ever.

• No non-container relations. Expressing something like a many-to-many relation in ZODB means writing the code yourself, or installing something like http://pypi.python.org/pypi/zc.relation … which presumably works fine, but I really can’t get excited about it: I’d rather spend my time learning relational technology that might actually be portable to other systems.
• The ZEO server is still a single point of failure and potential bottleneck; No live replication. There’s no free workaround. There is an expensive solution from Zope Corp. RelStorage could theoretically solve this problem for free by deferring to the underlying RDBMS replication, but it’s apparently not been tested.

  It’s important to note that I have never actually run a site where the ZEO server was the bottleneck, but the sites I’ve worked on have relatively small user bases, and the largest-scale ZEO cluster I’ve heard of was a news site: very read-heavy with a relatively small user base doing relatively few writes. I’ve never heard of anybody doing a large site with lots of writes from lots of users. If your goal is to build the next Facebook or Wikipedia, I don’t think there are any relevantly large real-world ZODB case studies you can emulate.

  Most of us just make do without replication or failover of any kind, and hope we will never really need it.
  

• No fine-grained control over which (and how much) data you retrieve.
  
  In any SQL database, you can trivially do “select foo from bar” and get only the values in the foo column, regardless of what other gunk is in each row. In ZODB, you get a whole object - think of this as the equivalent of every query starting with “select *”, so you always get the entire row(s). Results tend to be fat and you have no ad-hoc control over that, short of reorganizing the database. Which leads me to…
  
• Migrations are inconvenient and expensive. Migrations with ZODB typically take the form of a script containing two functions: one which updates a single instance of a particular persistent class, and another function which finds all the instances to upgrade. The latter is the non-trivial part, because there’s actually no way to find all objects of a given type short of walking the entire object tree. If you’re using Zope 2, you may have a ZCatalog handy that you can use, if it knows about all the objects you want to upgrade; or you can use the old ZopeFind API which is just a convenient (and no less expensive) way to walk the entire tree.

  And you can’t really do a migration atomically on a live site, because you’re sure to get ConflictErrors if you try to do it in a single transaction. You can solve this by taking the site down for the duration of the migration. If that’s not an option, you have to try committing and starting a new transaction after every N objects touched, which practically speaking means you’re not going to want to undo your migration. AFAIK there is no existing infrastructure for the latter approach, which means you have to rewrite it in every migration script you ever write.

• Often, you can’t actually use undo. If a transaction touches a frequently-updated object (like oh, say, the catalog indexes), you probably won’t be able to undo that transaction for very long, because other transactions will have since touched the same object, so undoing it would cause a conflict. A transaction is not a database-wide savepoint, like a revision in Subversion; rather, a transaction only knows about the objects that were changed during its lifetime. There’s no way to revert to an arbitrary point in the past.
• Indexing is not transparent. I very often see code in Zope applications to ensure that some index is properly updated after some value changes. It gets tiresome. By contrast, indexes in an RDB typically require no attention from the developer… but they don’t serve the same purpose.
  

What’s Debatable

This section could grow endlessly, but I’ll just list a couple items off the top of my head:

Speed

For years, the accepted wisdom was that ZODB was pretty fast for reads, and slow for writes. Some people claim that it’s actually fast for writes too. I don’t care much about raw benchmarks except insofar as they translate to real applications. The ZODB application I actually get to use the most - Opencore, built on Plone 3 - feels quite slow at the storage layer (some of this is catalog stress, some of it is due to storing binary files in CMFEditions, which we now know was a terrible mistake.)

I have no hard numbers to offer, hence putting this in the “Debatable” category.

Partitioning

Object traversal in Zope encourages you to map your ZODB tree directly to your URL space. I actually quite like this as it’s really easy to understand. But it makes it harder to reorganize your data for scalability reasons (eg. horizontal partitioning aka sharding) without also reorganizing the URL space and breaking links.

This is another one of those problems that’s mostly theoretical to me so far - I haven’t actually needed to do sharding on a ZODB app yet. And most people never will, but if you’re building something very ambitious, it’s something to be aware of.

As noted above, the ZODB can do one kind of partitioning by “mounting” databases in the object graph, like filesystems mounted in one Unix file tree; this is great and easy, but it’s only transparent if the mount point can replace an existing folder; it doesn’t help you with flat but dense data. Also, mounting multiple storages can be problematic when objects under one mount point refer to objects outside that mount point; see eg. the notes at the bottom of http://apidoc.zope.org/++apidoc++/Book/zodb/crossref/show.html

This is another case where I wonder if creative use of RelStorage might help, although I’ve no idea how you’d know where to split the partitions.

Optimizing is Weird

When addressing bottlenecks in an app written against a given RDBMS, there are typically pretty decent docs available that help even a novice get started with tuning their queries and setting up the proper indexes. If I google “mysql query optimization”, I find a lot of useful results on the first page. With ZODB, there are some general strategies that typically are learned the hard way. Good luck googling for docs or tips. One of the few things I found was from a presentation (PDF) that Chris McDonough made about ZODB: “The most important optimization you can perform is to write efficient code. Unfortunately, this is also the hardest way to optimize, because you need to manage all the details.”

Finally: You Can’t Take It With You

You may have noticed a theme running through some of the above.

I’m tired of feeling like I’m in a programming ghetto, and frankly ZODB feels rather marginalized. Not because of any of the things that I think are wrong with it, but just because almost nobody uses it. This has a lot of implications - lost opportunities for re-using innovative work and so forth. This is the danger that Mark Ramm recently warned the Django world about (video link, sorry; there’s no transcript anywhere AFAICT).

But more personally, I just don’t feel that I’m young enough to waste much more of my career on dead-end tech. Python is a plenty big pond for me to swim in; but most of the fish in that pond wouldn’t touch ZODB with a ten-foot pole. That’s a shame, maybe, but perception is reality, and if this was going to change, it would have changed by now. It’s been over 10 years now.

Given that, the time that I spend using ZODB could be better spent learning skills that I can more realistically apply on more future projects. Maybe even (gasp) non-Python projects.

It may seem selfish to harp on how this affects an individual’s career, but sometimes the events in your life push you in that direction. Having ZODB and Zope on my resumé gives me a certain amount of hireability right now, I think because I’m a relatively experienced fish in this tiny (and, AFAICT, not growing) pond where demand (for now) seems to slightly outstrip supply. Will ZODB be even that relevant in, say, 10 years?

Let’s ask:

Okay, I’m putting my flame suit on now

(For a more generally enthusiastic take on ZODB, you should have a look at Chris McDonough’s blog post on ZODB compared to CouchDB. )

FeedBacker: An Introduction

For the last few weeks I’ve been working on FeedBacker, a small REST application that stores and serves up feed items.   FeedBacker also provides a rudimentary query interface to the stored feed items, serving up the result set as a feed.  The idea is that feed items will be stuffed into the FeedBacker database whenever activity happens on an Opencore site, allowing us to easily generate feeds of arbitrary activity history simply by constructing the appropriate FeedBacker queries.  Seeing David Turner’s update and debrief on his Henge efforts yesterday reminded me that I wanted to write a similar post about FeedBacker, so here I am.

Conceptually, FeedBacker is very simple.  It defines a simple FeedItem data model, based on the Atom syndication format, and lets you use POST requests to create them.  GET requests can return either a collection of stored FeedItems or a single FeedItem, in either HTML (using the hAtom microformat) or XML (using Atom)  RSS and/or other formats later may be added later.  There are, however, a few additional features that are worth a closer look.

Metadata

Our use cases require us to support fairly arbitrary data on our FeedItems.  These data points are needed for later retrieval and also for querying purposes.  Since our ultimate goal is to generate feeds of site activity, we need to be able to store such values as the type of action that happened (e.g. ‘created’, ‘edited’, ‘joined’), the type of object on which the action was performed (e.g. ‘wiki page’, ‘discussion’, ‘blog post’), and the project where the action took place, if any.  So FeedBacker allows you to define extra fields on your FeedItems, either through HTTP or by setting an ‘extra_fields’ value in the PasteDeploy ini file.  Each extra field is typed, either as int, string, unicode, datetime, or a binary BLOB.  Any non-BLOB extra field can be used in the queries.  Also, any non-BLOB extra field can be specified as ‘in_results’, i.e. the contents of that field will be included in the FeedItem’s rendering when you make queries or visit the FeedItem resource directly.  When results are returned in Atom format, the extra fields are included in the results as custom tags in the ‘feedbacker’ namespace, e.g:

 <feedbacker:project>StreetsBlogNet</feedbacker:project>

Filters

The querying facility will get us most of the way towards the feeds that we want, but there are some types of feed parameters that can’t be easily represented as a query against the result set.  For this reason ‘feed filters’ were introduced.  Feed filters are just callable functions that expect to receive the results of a feed query and will return a filtered result set on the other side.  Feed filters will help us handle cases like removing near-duplicates (e.g. multiple small page edits in rapid succession) and enforcing security policies so as not to expose a closed project’s activity to anyone not on the project.  Feed filters can be registered programmatically, or by use of a ‘feed_filters’ setting in the PasteDeploy ini file.  There is no support for using HTTP to register feed filters at this time.

Technical Debrief

FeedBacker was written in Python using the repoze.bfg web framework, with SQLAlchemy for persistence.  Since I’ve been working in Zope-land for so long, this was the first project in quite some time I’ve done using a relational database as a back end.  The expressiveness of SQL for a query language is very nice, and SQLAlchemy provides a surprisingly good abstraction layer, but you have to jump through so many hoops to model more complicated data structures in a queryable manner.  It’s quite nice to be able to just stick a dictionary (or a rich object, even) in a pickle and not have to worry about mapping all of the keys and values to relational tables.

Object-Relational Mapping

A significant amount of FeedBacker’s complexity right now is in the object-relational mapping.  To it’s credit, SQLAlchemy was able to do exactly what I wanted it to do, so the complexity of the mapping is absolutely hidden to the front end code.  All of the data points, even the arbitrarily defined extra fields, show up transparently as attributes on the FeedItem objects.  But it took me a few days to get it all dialed in correctly, and then a few later tweaks to make sure everything works correctly even when there are multiple values for a single field.  It remains to be seen how well this will scale with very large data sets or high traffic volumes.

HTML to SQL Query Translation

The other place where there is a bit of complexity is in the translation of the HTTP GET requests to the SQL queries that actually do the work.  FeedBacker provides a hand-rolled syntax to allow you express fairly arbitrary queries against the storage (nice HTML docs coming soon, for now see the docstring for details) which needs to be parsed and converted to a SQL query that can actually be run.  Again, I was very impressed w/ SQLAlchemy’s help here; the ability to construct query clauses piecemeal, passing them around and nesting them further within other clauses, made it relatively easy to pick apart the HTTP query arguments and iteratively construct a query in the process.  Again, we’ll see what the performance ends up looking like under high load, after the right indexes have been applied.

Current Status

For this week I’ve been working on tying everything together by actually implementing a FeedBacker-backed page in the Livable Streets stack.  Things have been coming quite well, although I of course had to revisit a few things when the truth of the requirements butted up against some of FeedBacker’s original assumptions.  The page I’ve been working on is the member account page, based on what is proposed in a mock-up Rollie put together a couple of months back.  Not everything you can see in the mock-up will be making it into the first deployment (there’s not yet support for being a ‘fan’ of a project, for instance), but it gives you a good idea what type of functionality we’re trying to support.  Ultimately, the hope is that the account page will become similar to a user’s home page on FaceBook, where all of the activity that the user cares about will roll by in one place.

The page works by making an HTTP query request of FeedBacker, where the query represents what should show up on the page.  It asks for the results in atom format and then uses Python’s universal feedparser to parse the results and construct the output.  The page is nearly usable; at this point I’m just working on getting all the links and formatting correct, getting the icons to show up, etc.  I’m also working on making sure that as much info as we need is embedded in the feed item data itself.  If we have to visit a large number of Zope objects in order to fill in the details of the page, then we’ve lost, the page will be very slow.  I haven’t hit anything that I’d call a roadblock, however, so I’m feeling pretty confident that this is going to be a good starting point.

My goal is to get the implementation of the page finished up today, and to deploy it to a dev server so we can start comprehensive testing tomorrow.  And, if I wrap this blog post up and get back to it, I just might.

I started working on a simple calendar for opencore called Henge. The idea is that projects could post a calendar of events. My initial version doesn’t even let you describe events — each event just has a link field that you can use.

I’ve decided to put the project on hiatus, because we’re considering rewriting opencore. I think we’re finally starting to figure out how to write applications in the dvhoster, openplans_hooks, transcluder, cookieauth, etc framework. But being able to do things doesn’t mean being able to do them well or efficiently. While debugging henge, I did run into various issues caused by the stack: deliverance hides error messages; I couldn’t figure out what file I had to change to map URLs to project/x/calendar; debugging the project member http call was hassle. And I didn’t even get to the opencore featurelet.

Also, I was starting to hit the limits of SQLObject pretty hard. I wasn’t doing anything especially complicated but I was trying to write an undo mode that made sense. The data model is:

Projects have many Events.

Events have many EventDates.

When an event is deleted, its associated dates should be deleted — this is trivial. But when an event is restored, its EventDates should be restored — and only those EventDates that it had when it was deleted should be restored (not any that might have been deleted earlier). I tried a bunch of strategies to make this work well, and ended up having to do both metaprogramming and hand-coding to make it work. I think a better programmer could have done it all through metaprogramming, but it would have taken me more time than it was worth. In particular, I wanted objects to have uuids as primary keys, and that was impossible. As it turned out, that wouldn’t have gotten me what I needed, but I still should have been able to do it.

What’s actually going on is that an Event is a composite object composed of some general data and a bunch of dates. But SQLObject doesn’t natively have a way to talk about a row and its associated rows from other tables. You can walk through the metadata and find one-to-many relationships, but that’s not the same thing as ownership. If there were a way to talk about this naturally, it would be easier to support things like undo and versioning of composite objects.

I think the lesson is that SQLObject is not really powerful enough for applications which want to support complex interactions with data. This isn’t a complaint about SQLObject — it’s not meant for these sorts of complex cases. I’m not sure if SQLAlchemy is the solution, but I’m certainly interested to find out.

Anyway, there’s a few weeks of work left before I could put Henge live, and I find myself without the time to do them.

• the aforementioned featurelet
• events should have descriptions
• there should be an agenda view
• a javascript date picker
• event permalinks which survive event deletion

Of course, there are a ton of ways to complexify things, many of which are obvious — presently, there is no interface to add multiple dates to an event. But really, the above is all that’s needed to launch.