Why I'm Walking Away From CouchDB

The ratio of write/read does not seem to be the problem. After a certain amount of write traffic the view indexing and storage problems appear - regardless of the amount of read traffic. More writing means more of 1. and 2. and the more likely the database enters the dreaded more-than-half full phase. I have cron job compact the database every 6 hours. (I'm the blog post author.)

After reading the article, I'm convinced that the author has something really, really, REALLY wrong with their data format, or their view code.

20,000 new documents per _day_ (from the article) is absolutely nothing for CouchDB. Our dataset in Couch is 28 million records, takes up 13GB of data, and we can create a brand new view against those records and have it run the first time in less than ten minutes. Adding 20k records to this dataset might cause the view to take a couple of seconds to update. One update to the view, which is what the article complains about, takes one or two milliseconds to be added.

But let's hypothetically say that the author has the most complex data format in history, and the most complex view code in history, and ten writes to the database actually cause minutes worth of view index updating. Adding the "&stale=update_after" request parameter to your view query returns instantly, with the last-generated view. After the query is issued, the view is automatically updated. No cron jobs necessary-- it is (and should be) a drop-in addition for all of your view queries.

Now, about the append-only database file. We've been playing with autocompaction from CouchDB 1.2, and it seems to be working quite well, but again, at the scale the author is talking about, 20k records per day is a very predictable delta in disk space. Even running CouchDB off a 32GB compact flash card (and it is possible!) will give ample room for the size of his dataset. We run off a 120GB fibre channel mount and haven't needed to increase the partition size yet.

From the article:

"The system in question does a lot of writes of temporary data that is followed up by deletes a few days later. There is also a lot of permanent storage that hardly gets used."

That, to me, speaks to two separate databases, one for the temporary data, one for long-term storage. The temporary database can be compacted once per day, and the permanent storage could be compacted once per week/month/never.

You have something extremely wrong. Maybe it's at the hardware level. Maybe you have a crap SSD. Something. I just created a test database on my workstation, with a spinning disk, with the same type of data, and a temporary view against 50k documents takes 10 seconds to establish. Saving the view, re-running, then adding 10k documents and re-running the view takes 4 seconds to update. The 60k records take up 110MB.

"What the site needs is view updating after every write."

So why not build that into your application? For every write that you want to be consistently shown in the very next client view, ensure that you spawn a thread to immediately issue a query to the view after the write. Remember, CouchDB does not try to be an ACID-level database, substituting the C for raw speed. In fact, you can't be guaranteed that a client won't get the view while a new record is being added, and with no blocking on write, your client now has out of date data. With such a small amount of database traffic, you don't really notice this side-effect of Couch.

"I was excited about auto-compaction in 1.2 but the core problem remains that rewriting every single record to a new spot on disk at every compaction is a tremendous waste of I/O and write cycles on a (SSD) disk."

With the number of IOPS that we're talking about here, your SSD will last for years and years and years, even if you ran compaction once every minute. A 256GB SSD costs $200 at Amazon, and with your Couch instance taking 1.8GB, I'd be more worried about the heat death of the Universe before worrying about running out of disk space or SSD write operations.

Given that it's only 20k new documents a day my immediate thoughts were of PostgreSQL. In particular thinking of use of things like the XML storage capabilities (XQuery is not yet supported but that's something folks are working on, but you can do a lot of processing with XML parsing/SQL if you like) or the new JSON type in 9.2 beta, particularly with Javascript as a stored procedure language....

Thanks for doing a quick benchmark. In my frustration I may have misremembered the amount of time the indexing takes, but the number of seconds really a distraction from the post. Its the principle that the next read operation time is so wildly unpredictable. Slowing down based on the size of the database makes some sense but slowing down based on the number of inserts since the last read is downright irrational to someone used to rdbms.

The number of write cycles on an SSD is also a distraction. Its the principle that gigs and gigs of records that are essentially read only are physically moved to another spot on the disk every six hours (thats how often a cron job kicks off compaction) is still a tremendous waste of effort/bandwidth/whathave-you.