Stored procedures do not scale

Stored procedures failed at Etsy:

After starting off with just a single web server and database in 2005, the company’s IT architecture evolved over the next two years to rely heavily on business logic written as stored procedures in a back-end Postgres database. The presentation side was driven mostly by PHP on Lighttpd web servers, chosen at the time because the Etsy team felt Lighttpd was less common and less likely to be hacked.

And the organization mirrored the architecture: Etsy’s engineering organization was siloed into developers, database administrators, and operations, with little cohesion between them. Site software deployments happened infrequently and in large chunks, so they ended up being clumsy—with some site features disappearing in the process.

Scaling nowhere

While some of the database was partitioned by feature, it was largely monolithic. And, Snyder said, the reliance on stored procedures didn’t lend the database to scaling up well. The site’s uptime was “not that great,” he said, and “regular maintenance windows and site deploys often dissolved into outages.” After weighing options that included re-writing the whole site’s code base, he says that in the fall of 2007, the decision was made to help scale the site up by writing some middleware—a software stack that Etsy called “Sprouter,” a portmanteau of “stored procedure router.”

The Sprouter middleware was written largely in Python, and sat between the front-end PHP and the Postgres database. Snyder said it mapped requests to stored procedures in the database, returned results to the front end and did some caching of them. There was some thought of sharding the database to help scale it up, but those ideas were never implemented. The idea was that “the dev team writes code, the DBAs write SQL, and they meet in the middle,” Snyder explained. The middleware, it was hoped, would help scale up the performance of the site, since it couldn’t be easily scaled on the database end. And it would, in theory, prevent developers from having to write SQL calls. There were even hopes of turning Sprouter into an open-source project to help support it in the long term.