Database Performance
SQLite on Rails: Supercharging the One-Person Framework
Edit
#sqlite
#ruby-on-rails
#database-performance
#deployment
#single-page-applications-spa
Summarized using AI

SQLite on Rails: Supercharging the One-Person Framework

Stephen Margheim • September 26, 2024 • Toronto, Canada

The video titled "SQLite on Rails: Supercharging the One-Person Framework" features Stephen Margheim speaking at Rails World 2024. The focus of the talk is on illustrating how Rails 8 integrates seamlessly with SQLite to create efficient, production-ready applications designed for single developers.

Key Points Discussed in the Video:

  • Complexity of Current Web Applications: Margheim highlights the increasing complexity of web applications compared to earlier models, suggesting that simplification can lead to more powerful and operationally efficient systems.
  • Rails 8 and SQLite Integration: He discusses how the new features in Rails 8 complement SQLite, enabling lean application architectures that can support significant user loads.
  • SQL Features in SQLite: Margheim explains SQLite’s capabilities as a robust SQL engine, emphasizing its design as an embedded database which offers unique advantages for lightweight applications.
  • Myth of SQLite in Production: He addresses the outdated belief that SQLite is unsuitable for production use, detailing how recent developments in Rails 8 have validated SQLite's role in production environments.
  • Load Testing and Real-World Applications: Examples include the Campfire application, where he showcased that switching to SQLite did not compromise performance, handling up to 50,000 concurrent users effectively. Additionally, he cites the Ruby video app as an example that runs efficiently on a minimal server setup while supporting millions of requests.
  • Cautions and Trade-offs: Margheim warns about specific challenges such as limited write scalability and the necessity for proper backup systems when using SQLite in production.
  • Simplicity and Accessibility: He advocates for starting small with a simple stack that scales up, challenging the notion that complexity is always necessary for web applications.

Conclusions and Takeaways:

  • With Rails 8, developers can now confidently use SQLite in production, reaping the benefits of an embedded database without falling prey to prior limitations.
  • SQLite and Rails together create a powerful toolkit for developers looking to build robust applications easily.
  • Margheim urges developers to take advantage of the simplicity derived from using these tools effectively, positioning them for future success in the digital landscape.

SQLite on Rails: Supercharging the One-Person Framework
Stephen Margheim • September 26, 2024 • Toronto, Canada

The Rails 8 feature set perfectly complements SQLite's power in creating resilient, high-performance production apps, but still the question lingers: Can I really go all-in on #SQLite? Stephen Margheim illustrates how to leverage Rails and SQLite's full potential in your next venture, and when SQLite does and doesn't make sense for your application.

Find the slides here: https://fractaledmind.github.io/2024/10/16/sqlite-supercharges-rails/

#Rails #Rails8 #rubyonrails #sqlite

Thank you Shopify for sponsoring the editing and post-production of these videos. Check out insights from the Engineering team at: https://shopify.engineering/

Stay tuned: all 2024 Rails World videos will be subtitled in Japanese and Brazilian Portuguese soon thanks to our sponsor Happy Scribe, a transcription service built on Rails. https://www.happyscribe.com/

Rails World 2024

00:00:13.679 So the vision that David presented yesterday for Rails was exciting, wasn't it? This vision of Rails as a kind of rocket engine that can launch your idea from 'Hello World' to IPO is bold and beautiful. However, for those of us who have already embraced this new no-PaaS mindset, I expect that you, like me, have probably felt like you needed to be a kind of rocket scientist to deploy and run your application in production.
00:00:22.160 This makes sense because, much like rocket engines, the standard web application architecture has grown more complicated and larger over time. When you look at what we might call the typical Heroku application from 15 years ago, it was a distributed system, but it was understandable and manageable. Today, however, the typical Heroku application has tripled the number of services and servers. Many of you, like I did when I first saw this, might think, 'This must simply be the cost of running production-grade systems.' It's easy to believe that complexity is the price we have to pay for progress.
00:00:40.880 But that's not always true. Sometimes progress is simplification; it involves removing moving parts and shrinking the surface area of the system while still somehow expanding its functionality and power. My name is Stephen; you might know me as fractalmind on Twitter. Today, I want to show you my vision for how Rails 8 and SQLite together supercharge Rails as the one-person framework. I want to present the possibility of building applications that harness all of this power while remaining lean and simple.
00:01:05.320 Believe it or not, this is a viable application architecture today—one that can serve tens of thousands of users. To extend the metaphor, like liquid methane and liquid oxygen in a rocket engine, SQLite and Rails, when mixed, form a potent combination. On one hand, we have Rails with its two decades of battle-tested solutions extracted from real production systems. On the other, we have SQLite, which has undergone three decades of refinement.
00:01:17.200 The vision behind SQLite emphasizes having an operationally minimal embedded database with just a single file on disk and an executable that runs in your application process. When you combine this conceptual compression with operational compression, you get powerful and explosive results, resulting in a remarkably lean and simple application. What I want to show you today is how Rails 8 specifically makes Rails new command production-ready.
00:01:37.360 Rails has always been a full-featured framework that comes with all the components needed for a complete web application. Many of those components require some form of persistent data, which we can refer to as the databound components of Rails. A full-featured application is going to need a data store to back each of these components. Rails is a modular framework, so each component has various adapters to work with different data stores.
00:02:05.680 With version seven of Rails, when you run 'rails new,' these are the production defaults you would see. While there’s nothing inherently wrong with these defaults, they are not production-ready plug-and-play solutions. For instance, the async adapter for jobs is fast and simple locally, but in production, you may lose jobs during deploys or restarts.
00:02:19.560 The file system for caching works well if you have a persistent file system, but on Heroku, it's less effective when your entire cache can be randomly wiped. While the Redis adapter for Action Cable is production-grade software, it isn't plug-and-play—you must have a Redis instance configured and running in your application in production. In Rails 8, you get an out-of-the-box experience that is both production-ready and plug-and-play.
00:02:42.880 With version 8, when you run 'rails new,' you will see notably different defaults for the central databound components of Rails. This suite of solid gems provides production-ready, flexible, and scalable defaults for these components, allowing you to use them with Postgres or MySQL. I hope by the end of this talk you'll be interested in trying them with SQLite because they offer a solid foundation for Active Job, Active Support Cache, and Action Cable.
00:03:06.760 I hope that you were here in this room yesterday at this time to see and hear Rosa's talk, where she detailed what makes Solid Q truly production-grade software. Last year at Rails World, Donald introduced Solid Cache, detailing its thoughtful and production-oriented design. Unfortunately, Nick isn’t able to be here today, but the work he has done on Solid Cable allows you to have a simple, scalable, agnostic default experience for your Rails applications.
00:03:30.520 Rails 8 being production-ready isn’t just about these new defaults. It also includes an improved out-of-the-box solution for production deployments with Kamal. Kamal offers everything you need to deploy and manage your web application in production with Docker. Yesterday, Donal introduced the new version of Kamal, Kamal 2.0, and Kevin introduced the new Kamal Proxy. I hope you were here to see what the future of deploying and running Rails applications looks like.
00:03:56.399 If you weren't, you can catch it on YouTube. All in all, Rails 8 provides the tools you need to go from 'Hello World' to 'Hello Web' just with Rails new. These features and details are what make Rails the one-person framework, as David outlined in his keynote last year and reiterated with his talk this year. Rails aims to be a bridge over complexity that allows the smallest possible team—just you and your laptop—to build full, rich, valuable web applications.
00:04:25.360 SQLite aligns perfectly with this vision. I like to say that SQLite supercharges Rails because it enables you to go from the moderate complexity of a minimally distributed system to the radical simplicity of an application where all operational dependencies reside on a single machine. This is possible because of SQLite's unique architecture.
00:04:57.360 Most client-server databases run in a separate process, often on a separate machine. However, SQLite runs embedded within your Ruby process. Legally speaking, it’s just a file on disk and an executable running in your application process. Nonetheless, it is a fully featured SQL engine, with support for CTE, window functions, aggregations, and other elements from the SQL standard.
00:05:16.920 And this is stable software. SQLite's current major version, version 3, was released two decades ago in 2004. Today, there are an estimated one trillion SQLite databases in active use around the globe, making SQLite the single most used database in the world.
00:05:36.360 You might presume that an embedded database could only manage a small fraction of the data that a proper client-server database can handle, but you would be wrong. SQLite can handle databases up to 280 terabytes—so you won't hit its computational limits. When you pair the power and simplicity of SQLite with modern hardware, you're starting to see how SQLite enhances the vision of Rails as the one-person framework.
00:05:57.640 This allows us to radically simplify our application operational needs. However, I understand that we have had years of people consistently saying that SQLite simply doesn't work for web applications. Indeed, for the past few years, if you ran SQLite in production, Rails would tell you it doesn’t work—go elsewhere. The Rails guides explicitly warned against using SQLite in production.
00:06:16.480 But this outdated point of view has changed. The reality today is that this sentiment is a myth, and more and more people are starting to recognize it. This myth persists for a reason; it has foundations. For many, SQLite in the context of web applications is easily misunderstood and misused.
00:06:43.160 Unlike much of modern software, SQLite cares more about backward compatibility than about enabling newer and more powerful features. By default, the maintainers ensure that a SQLite database created two decades ago can be opened by SQLite two decades from now. This is one of the key reasons why SQLite is the archival format for digital data by the US Library of Congress.
00:07:05.600 But this backward compatibility means that when you run SQLite out of the box today, it's as if you're running SQLite as it was created twenty years ago. It's true that SQLite wasn't well suited for web applications then.
00:07:23.520 SQLite has needed fine-tuning, and over the last two decades, many features have been added to make it suitable for web applications. All that’s required is to fine-tune its configuration and usage, and that is precisely what I, the Rails core team, and the Rails community have been working on for the last year and a half.
00:07:37.880 This work culminates in Rails 8. Many pull requests have been released with various previous versions of Rails, but two in particular coming out with Rails 8 warrant special consideration. These changes make Rails 8 the first version of Rails, and as far as I know, the first version of any web application framework that provides a fully production-ready SQLite experience out of the box.
00:07:58.560 The two issues that hindered the default experience of SQLite on Rails until now were, firstly, that as your application was put under more concurrent load, an increasing percentage of your requests would simply error out. As you can see here, even at just four concurrent requests, nearly half of the responses are errors, which is utterly unacceptable for production.
00:08:15.760 The second issue related to your application’s tail latency under increasing concurrent load is that you’d see your P99 latency, or even P95 latency, skyrocket. When some requests take five-plus seconds to respond, it’s fair to say this is not production-ready software.
00:08:41.360 Both issues arise from the nuances of using an embedded database in a multi-threaded web application. Rails spins up multiple threads to process incoming requests, and each thread has its own embedded connection to the SQLite database. This means Rails must ensure that those connections don’t conflict and that they don’t block each other.
00:09:01.600 To ensure those embedded connections don’t conflict, we made changes to how Rails manages transactions for SQLite databases. Moreover, to ensure that these connections don't block each other and create latency problems, we had to work on a pair of pull requests—one in the lower-level SQLite gem and the other in Rails to utilize this feature.
00:09:27.000 Unfortunately, I don’t have the time today to delve into the technical details, but if you're interested in learning more, I have an in-depth post on my blog that walks through every detail of these changes, the nature of the problems, the reasoning behind the solutions, and the specifics of the implementation.
00:09:46.200 But the results speak for themselves. Not only do we no longer see errored responses, but our P99 latency has improved a thousand times under heavier concurrent load. In fact, we can observe that these improvements hold steady even for the slowest requests in our benchmark.
00:10:08.280 These configuration and usage changes require nothing of you in your application code, unlocking the full power and speed of SQLite, making it a viable production option. You can now back four out of the five databound components of Rails with SQLite without compromise. This architecture is a viable option for a fully featured Rails application with no compromises on features or performance, all running on a single machine.
00:10:25.720 Yet, I recognize that this is a somewhat radical suggestion, and I expect many of you are thinking right now that this architecture couldn’t serve production-level workloads. Let’s quickly investigate this, starting with the Campfire app released earlier this year by 37 Signals. It’s a full production-grade, full-featured application. The team at 37 Signals provided a load-testing tool in the source code itself.
00:10:47.920 Now, Campfire doesn’t go all-in on SQLite; it was built before all of these solutions were implemented in the framework. However, Rails is a modular framework, so I simply swapped the adapters. This took me literally 45 minutes. Once I had the solid gems in place, I ran the load tests, and again, the results speak for themselves.
00:11:08.480 Whether you're looking at the number of connections spawned or the number of messages received, the Campfire on SQLite fork performs just as well as the standard Campfire build. The team at 37 Signals ran load tests on big servers and were able to handle 50,000 concurrent users without any issues.
00:11:25.360 But we don't only have to rely on speculative load tests. There are applications today running on this architecture. This is the Ruby video app built by my friend Adrienne Polly. It runs on a single $4-a-month Hetzner box with SQLite backing all its I/O needs.
00:11:50.800 It has served millions of requests with an average response time of less than 100 milliseconds and boasts four nines of uptime. I have also been running applications in production with SQLite. Unfortunately, they are all proprietary and associated with NDAs with big corporations, but I can tell you these applications have run smoothly for three years, driven millions of dollars in revenue, and garnered no complaints from customers or the business.
00:12:07.280 Recently, Tech Twitter has been abuzz about Peter Levels' setup, where he runs multiple successful applications all backed by SQLite on a single beefy VPS. It's important to note that single server deployments have been serving Rails applications quite well since day one. So yes, it is indeed possible to run a fully featured Rails application in production without compromises on a single machine.
00:12:31.440 I hope that you trust me or at least begin to trust me when I say that this idea is a myth. Next year, I hope to hear stories from some of you about applications you have created and have run successfully for months. Together, we can add more evidence to show that this belief is no longer valid.
00:12:49.440 However, I do not want to suggest that this is a silver bullet; it isn’t. There are use cases where this architecture excels and others where it doesn’t. When running SQLite in production, you need a solid backup mechanism set up. I know this from experience because I once deleted my production database, and it was not a fun weekend.
00:13:07.680 I believe the best tool for the job is Lightstream, a utility that streams every change to your database to an S3-compatible bucket or storage system you configure. You could even stream it to an FTS server if that suits your needs, resulting in point-in-time backups with low storage costs. As it’s a single Go executable, I’ve created a pre-compiled binary gem for easy installation.
00:13:31.280 This gem provides a Puma plugin, enabling you to manage the replication process easily within your configuration. It also includes a verification job that you can run daily at midnight to ensure your backup system is working.
00:13:52.240 Aside from data resilience, the most common concern I encounter revolves around the fact that SQLite only currently supports linear writes. People worry that having only one operation at a time might prevent their application from scaling. However, this worry is often exaggerated. Most applications are read-heavy, not write-heavy; likely, only about 20% of your traffic consists of writes.
00:14:12.760 Moreover, even if you are running Postgres on the same machine as your application, the simple inter-process communication overhead means you could execute 10 SQLite queries in the time it takes to run one Postgres query. As applications increasingly move away from self-hosting and database management, opting for cloud databases instead, the disparity grows.
00:14:33.400 If utilizing a cloud database, even in a neighboring region, you could run 600 SQLite queries in the same time it takes to execute a single Postgres query. In essence, when you transition from a client-server database architecture that introduces network latency to an embedded database, you shift from queries being measured in milliseconds to microseconds. This largely negates concerns about the limitations imposed by linear writes.
00:14:54.520 Though, should you find yourself ingesting large data volumes—say 50,000 writes per second—SQLite might not be the optimal choice for you. In such cases, consider using Postgres or MySQL; there’s no issue with utilizing the best tool for your problem. However, if you operate below that threshold, SQLite will perform adequately without meaningfully impacting your application’s performance.
00:15:16.640 Linear writes do necessitate taking care with migrations. If you have a lengthy, write-intensive migration, such as adding a new index to a table with millions of rows, it may impact your application’s performance. Currently, no well-tested solutions exist to mitigate this limitation, so be mindful that such migrations are best handled with scheduled downtime.
00:15:35.760 Generally speaking, getting comfortable with scheduled downtime becomes essential if you want to embrace SQLite. SQLite operates best on a single machine, which means scaling often involves expanding the machine’s resources.
00:15:57.560 Many may assume that available machines are limited in size, but you can rent exceptionally large servers. For instance, any new user at Hetzner can get a 48-core, 192-gig RAM server with 1 terabyte of NVMe SSD space for $350 a month. It's a powerful machine and accessible for everyone.
00:16:19.040 Therefore, do not impose artificial limitations on the extent of vertical scaling for an application. For years, we have been told that building web applications correctly demands redundancy, high availability, automatic failover, and infinite autoscaling. However, these solutions target issues faced by merely a minority of web applications and incur genuine tradeoffs regarding operational complexity.
00:16:39.520 We must remember that larger system surfaces increase the chances of failures. So ask yourself, do you truly need all that complexity, especially on day one? Or should you begin with a stack that's simple enough to keep in your head, yet powerful enough to satisfy all customer needs?
00:17:02.280 Like any tool, SQLite has trade-offs, and you must familiarize yourself with its quirks to maximize its utility. Choosing high-leverage tools—those that are well-built and well-known—enables you to harness the power of simplicity.
00:17:20.760 Truly, you can create an application capable of elevating your next idea to IPO without needing to be a rocket scientist to manage it. Rails 8 and SQLite strip away all incidental complexity, leaving you with the leanest, simplest, yet most powerful application stack imaginable.
00:17:42.480 There are few things as powerful as tools that have gained their simplicity through years of evolution and consideration. These engines empower individuals to build production-grade, full-featured, valuable applications faster, simpler, and more affordably than ever before. I genuinely believe that we are on the cusp of a new wave of opportunities for Rails developers to establish their presence in the digital marketplace.
00:18:01.760 This journey allows you to pursue your dream of crafting solutions that can sustain your livelihood while nurturing your interests. SQLite and Rails form a unique, powerful pairing.
00:18:06.960 I hope that after this exploration, you will better understand and appreciate how well SQLite pairs with Rails as an engine for creativity and development. I also hope you feel confident that you can—if not even that you might want to—build and run your next Rails application with an all-in commitment to SQLite.
00:18:25.360 Thank you.
See Slides on fractaledmind.github.io
Stephen Margheim
@fractaledmind
Explore all talks recorded at Rails World 2024
+13