31:T725,## What We Learned A lack of visibility into pending actions is a bad user experience: for example, if you click Fork and nothing happens, you might click Fork again and then create multiple unintended forks. We’ve identified places where we can better show the progress of pending actions. A handful of slow queries can have a disproportionate effect on overall performance. It’s not enough to look at median or P95 performance across all endpoints; if you have *any* slow endpoints, there’s a risk of saturating the connection pool if these endpoints get more traffic than expected. Per-endpoint performance analysis is essential. Heroku’s [technical characteristics](https://devcenter.heroku.com/articles/heroku-postgres-production-tier-technical-characterization) for premium-2 plans [have changed](https://web.archive.org/web/20180130093206/https://devcenter.heroku.com/articles/heroku-postgres-production-tier-technical-characterization), specifically regarding burstable disk I/O allowance. This can result in a “double” punishment during traffic spikes: if you have a lot of disk I/O, Heroku may make your database even slower. Augmenting `EXPLAIN` with `EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)` can provide better insight into underlying issues like poor disk I/O performance and query planner estimations. If a query is complicated by an edge case—we extended our collection query to fetch nested collections using a recursive [CTE](https://www.postgresql.org/docs/13/queries-with.html)—this can make it harder for the query planner in the common case. Writing a simplified, optimized query for the common case can help the query planner do its job well. While we identified that queries may get slower after upgrade, it took several days of real traffic to surface the severity of the problem.32:[{"id":"a6564a4dfb3fd730b19672c8050de467f05f199138f9dbcd8233c82dc6ff484340fa899baad305054008e6c3899389e1098d8c638fdaa143edfbd689ee67eae2","href":"https://static.observableusercontent.com/files/a6564a4dfb3fd730b19672c8050de467f05f199138f9dbcd8233c82dc6ff484340fa899baad305054008e6c3899389e1098d8c638fdaa143edfbd689ee67eae2","name":"Screen Shot 2021-05-21 at 2.57.23 PM.png","type":"image/png","lastModified":1621645749131.706,"size":778759},{"id":"5193a5fc1f44ce0ca73630c2ce655fb1bd4dd9498bc361f0fd4efb583cfb6657bdc2593d411afaa21778df4a2baac9b027cebf1e821f80a908cd04a3d8a76869","href":"https://static.observableusercontent.com/files/5193a5fc1f44ce0ca73630c2ce655fb1bd4dd9498bc361f0fd4efb583cfb6657bdc2593d411afaa21778df4a2baac9b027cebf1e821f80a908cd04a3d8a76869","name":"untitled-79.png","type":"image/png","lastModified":1621645749131.706,"size":65150},{"id":"4f3b365b87968bad5969bbff6138c19e627b8c40df3217a13a5b6a4a04c21114e73d15a3df924cd7f2d8b620b07d89bdfcbefcc1a848f2a6ea83ad783f257219","href":"https://static.observableusercontent.com/files/4f3b365b87968bad5969bbff6138c19e627b8c40df3217a13a5b6a4a04c21114e73d15a3df924cd7f2d8b620b07d89bdfcbefcc1a848f2a6ea83ad783f257219","name":"untitled-80.png","type":"image/png","lastModified":1621645749131.706,"size":64444}] 2f:{"title":"Database outage on May 18, 2021","cells":[{"id":0,"value":"# Database outage on May 18, 2021","pinned":false,"hidden":false,"mode":"md"},{"id":4,"value":"Upgrading our primary database led to degraded performance of search and collection queries, impacting users during our live Twitch stream workshop on Tuesday. We have resolved the issue and improved Observable’s performance.\n\nWe are sharing this timeline of the outage, along with additional details and lessons learned, to provide visibility into how we handle production issues.","pinned":false,"hidden":false,"mode":"md"},{"id":175,"value":"toc(\"h2, h3\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":6,"value":"## Timeline (PDT)\n\n05/15/21 5PM–6PM: We upgraded our primary database on Heroku from PostgreSQL 9 to 13. Testing showed slower search but nothing immediately prohibitive. Analyzing tables did not identify any significant issues.\n\n05/18/21 9AM–11AM: We hosted an Observable Plot workshop on Twitch. 200+ people started searching for, opening, and forking the workshop notebook per instructions on the stream. This traffic spike to the search and collection endpoints increased database load and significantly increased latency across all endpoints.","pinned":false,"hidden":false,"mode":"md"},{"id":186,"value":"

A scatterplot of request latencies for Observable’s Plot Explorations collection. The vertical spike corresponds to the start of the Observable Plot workshop. Time along the x-axis is in UTC.

","pinned":false,"hidden":false,"mode":"html"},{"id":375,"value":"Shortly thereafter, the API servers crashed when encountering out of memory and connection errors from the database. We believe the database errors were due to the simultaneous increase in traffic and latency.","pinned":false,"hidden":false,"mode":"md"},{"id":155,"value":"We identified the collection and search endpoints as being the primary cause of slow requests. We added more caching to public collection endpoints, alleviating some load, and then began separate investigations into improving the performance of these endpoints.\n\nOur investigation identified significantly slower disk I/O as compared to the previous database instance; `heroku pg:diagnose` reinforced this hypothesis. We decided to upgrade our primary database tier for better disk I/O performance. We also rewrote our collection query to be 100× faster by avoiding a recursive CTE.","pinned":false,"hidden":false,"mode":"md"},{"id":402,"value":"05/19/21 1–2PM: We added a missing index on the collections table and deployed an improved collection query; see below.\n\n05/19/21 2–2:30PM: We announced read-only downtime via the Observable forum and Twitter, and initiated the database tier upgrade.\n\n05/19/21 2–2:35PM: Performance returned to within expected latency ranges.","pinned":false,"hidden":false,"mode":"md"},{"id":125,"value":"

A heatmap of request latencies for Observable’s Visualization collection, with the median latency line drawn on top. The requests above 30 seconds represent timeouts. After optimizing the collection query, the median latency went down from more than five seconds to about fifty milliseconds. Time along the x-axis is in UTC.

","pinned":false,"hidden":false,"mode":"html"},{"id":203,"value":"

A scatterplot of search latencies. The requests at 30 seconds represent timeouts. After the main database tier upgrade, most searches complete rather than time out. Further optimization is still needed. Time along the x-axis is in UTC.
\n

","pinned":false,"hidden":false,"mode":"html"},{"id":10,"value":"$31","pinned":false,"hidden":false,"mode":"md"},{"id":167,"value":"## Gratitude\n\nThank you to all the Observable Plot workshop participants, and everyone else impacted by this outage, for your patience. We are striving to deliver new features and improvements to Observable quickly, and we will redouble our efforts to improve Observable’s performance and minimize downtime.","pinned":false,"hidden":false,"mode":"md"},{"id":428,"value":"blogSubscribeFooter","pinned":false,"hidden":false,"mode":"ojs"},{"id":172,"value":"import {toc} from \"@nebrius/indented-toc\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":414,"value":"import {blogSubscribeFooter} from \"615860c65e5663a6\"","pinned":true,"hidden":false,"mode":"ojs"}],"files":"$W32","stdlib":"1","titling":"auto"}