Hacker News new | past | comments | ask | show | jobs | submit login
Why I Built Litestream (litestream.io)
703 points by benbjohnson 22 days ago | hide | past | favorite | 176 comments



Regarding SQLite's performance, some things I've found very useful:

Use WAL mode (writers don't block readers):

  PRAGMA journal_mode = 'WAL'
Use memory as temporary storage:

  PRAGMA temp_store = 2
Faster synchronization that still keeps the data safe:

  PRAGMA synchronous = 1
Increase cache size (in this case to 64MB), the default is 2MB

  PRAGMA cache_size = -64000
Lastly, use a modern version of SQLite. Many default installations come with versions from a few years ago. In Python for example, you can use pysqlite3[0] to get the latest SQLite without worrying about compiling it (and it also comes with excellent compilation defaults).

[0] https://github.com/coleifer/pysqlite3


The negative value in the cache size pragma seems a mistake, but it isn’t. Negative values are used to specify a cache size that is a multiple of the page size (more or less) and the sign is discarded.

Not a great choice of API IMHO, but it’s a database, I’ve seen much worse.

https://www.sqlite.org/pragma.html#pragma_cache_size


I agree it's a very weird API, but you get used to it. In python, when initializing the DB I always use:

  conn.execute(f"PRAGMA cache_size = {-1 * 64_000}")
That way I never forget about the minus.


In case you don't control which SQLite version you use (for example, on iOS or on Android, iOS normally comes with sane defaults), SQLite's default page size only recently changed to 4KiB, and update that can help with performance as well: https://www.sqlite.org/pgszchng2016.html


Here are the pros and cons of WAL mode: https://sqlite.org/wal.html

For us, the biggest thing would be that it can't be used if the database is on a network drive relative to the host process.


> “But nobody writes production applications with SQLite, right?"

We've been doing it for 5 years now. Basic tricks we employ are:

Use PRAGMA user_version for purposes of managing automatic migrations, a. la. Entity Framework. This means you can actually do one better than Microsoft's approach, because you don't need a special unicorn table to store migration info. A simple integer compared with your latest integer and executing SQL in the range is all it takes.

Use PRAGMA synchronous=NORMAL alongside PRAGMA journal_mode=WAL for maximum throughput while supporting most reasonable IT recovery concerns. If you are running your SQLite application on a VM somewhere and have RTO which is satisfied by periodic hot snapshots (which WAL is quite friendly to), this is a more than ideal way to manage recovery of all business data while also giving good throughput to writers. If you are more paranoid than we are, then you can do FULL synchronous for a moderate performance penalty. This would be more for situations where your RTO requires the exact state of the system be recoverable the moment it lost power. We can afford to lose the last few minutes of work without anyone getting yelled at. Some modern virtualization technologies do help a lot in this regard. Running bare metal you need to be a little more careful.

For development & troubleshooting, being able to copy a .db file (even while its in use) is tremendously powerful. I can easily patch up a QA database I mangled with a bad migrator in 5 minutes by stopping the service, pulling the .db local, editing, and pushing it back up. We can also ask our customers to zip up their entire db folder so we can troubleshoot the entire system state.

Being able to use SQLite as our exclusive data store also meant that our software delivery process could be trivialized. We use zero external hosts, even localhost, for our application to be installed or started. We don't even require a runtime exist on the base operating system. Unzip our latest release build to a blank Win2019 server, sc.exe the binary path, net start the service, and it just works. Anyone can deploy our software because it's literally that simple. We didn't even bother to write a script because its a bigger pain in the ass to set powershell execution mode.

So, its not just about the core data storage, but also about the higher-order implications of choosing to use a database solution that can be wholly embedded within your application. Because of decisions like these, we don't have to screw around with things like Docker or Kubernetes.


Those are awesome tips. I didn't even think of using "user_version" for storing a migration version. I'm definitely stealing that trick.


This blog post gives a nice example of how that could be implemented:

https://levlaz.org/sqlite-db-migrations-with-pragma-user_ver...


This is how we manage SQLite migrations in Notion’s native apps.


>We can afford to lose the last few minutes of work without anyone getting yelled at. Some modern virtualization technologies do help a lot in this regard. Running bare metal you need to be a little more careful.

How does virtualization help with data loss? I would expect that a VM can't have guarantees better than the underlying physical hardware provides.


VMs can be migrated to other nodes, so for example you can mitigate failures that don't occur out of the blue


> How does virtualization help with data loss? I would expect that a VM can't have guarantees better than the underlying physical hardware provides.

E.g. storage virtualisation.


It sounds likely that it can. Software solutions to hardware problems have been a common pattern for years now.


Out of curiosity, what kind of read and write concurrency is your application dealing with?

In my experience, sqlite performance becomes problematic quite quickly, even with settings you mentioned (WAL etc).


We are able to get reads on the order of 10k/s+, and writes on the order of 5k/s+ using NVMe drives and practical serialized business object sizes (0.1~5 megabytes). I can easily saturate an NVMe drive using SQLite. In fact, it is substantially easier to max out storage devices with SQLite and carefully-tuned code than it is with something like SQL Server.

I should amend my original post, because I know a lot of developers fall into the trap of thinking that you should always do the open/close connection pattern with these databases. That is a huge trap with SQLite. If you want to add some extra zeroes to your benchmark figures, only use a single connection for accessing SQLite databases. Use application-level locking primitives, rather than relying on the database for purposes of getting consistent output from things like LastInsertRowId and in cases where transactional scopes are otherwise required. This alone can take you from 100 inserts/second to 10k without changing anything else.


> Use application-level locking primitives, rather than relying on the database for purposes of getting consistent output from things like LastInsertRowId

You mean for generating unique primary keys? Why would last insert row id be slow?

> and in cases where transactional scopes are required

Could you elaborate on what you mean by this?


LastInsertRowId is not slow, but if you are inserting on the same connection from multiple threads, you will require a mutex or you will be getting other threads' row ids.

Transactional scopes meaning scenarios like debiting one account and crediting another. This is something you can also manage with locking with application-level primitives.


So the mutex in sqlite (for multiple connections) is worse than the one you implement in your own application?

I’d assume the DB would be most efficient at handling it’s own. At least to the extend that it wouldn’t garner a 100x speedup to do it in app.


Yes it is substantially worse to use multiple connections vs a single connection. This is fairly easy to test in a few lines of code.

We need to remember that opening a connection to SQLite is like opening a file on disk. Creating/destroying file handles requires far more resources and ceremony than taking out a mutex on a file that is never closed.


That doesn't sound right. SQLite's lock for writes is not the best, but it is still pthread mutex under the hood. Are you sure your compilation options for SQLite is right? One common pitfall is compiling without `-DHAVE_USLEEP`. In absence of that flag, SQLite will use sleep in case of conflict, and that will have time resolution of 1 second, causing 1s delay on every lock contention. That flag tells SQLite to use usleep instead, and it is substantially faster on busy timeout.

Here is my SQLite compilation flags: https://github.com/liuliu/dflat/blob/unstable/external/sqlit...

Here is where the flag used: https://github.com/sqlite/sqlite/blob/d46beb06aab941bf165a9d...


Great writeup, thank you.

I'm used to leaning on SQLite for desktop apps, but now I'm keen to think about it WRT to web apps using these tips.


> We can afford to lose the last few minutes of work without anyone getting yelled at. Some modern virtualization technologies do help a lot in this regard. Running bare metal you need to be a little more careful.

Can you say more about how (and which) modern virtualization technologies help? RTO is something I've never found a happy to, since piecing together any missing data is painful, but avoiding a clustered DB setup (or the cost of Aurora) is always welcome.


Thanks for details.

I yet wait to see how somebody serve 1M users on a web service using sqlite. Sounds like you can do all that since you create desktop app, you almost never need anything more then sqlite for that.



Surely that doesn't qualify as an answer being a read only test. Also, it looks like there is not much interaction between users in their core domain.


"Solutions such as Kubernetes tout the benefits of zero-downtime deployments but ignore that their inherent complexity causes availability issues."

This is completely accurate. I've seen several teams do kubernetes, only to both spend 50% of their dev time on ops, AND cause outages due to kubernetes complexity. They do this all while boasting about zero downtime deployments. It's comical really.


Well then you just need multiple Kubernetes clusters for redundancy :-) :-)

From another thread on the home page right now:

The current trend goes to multi-cluster environments, because it's way too easy to destroy a single k8s cluster due to bugs, updates or human mistake. Just like it's not an very unlikely event to kill a single host in the network e.g. due to updates/maintenance.

For instance, we had several outages when upgrading the kubernetes version in our clusters. If you have many small cluster it's much easier and more save to apply cluster wide updates, one cluster at a time.

https://news.ycombinator.com/item?id=26106353

:-( :-(


This reads like a bad joke.

Where did the KISS principle go?


Web developers buried it under a mountain of trash


I'm a web dev and I agree. Complexity justifies paychecks.


k8 is about as anti "keep it simple" as it can get.


I call these "aspirational features".

The tech aspires to provide that feature, and _technically_ can, but in practice you're always chasing the goose. (or spending so much on ops time/people that it becomes an invalid option, of course this becomes clear after you've fully invested in it)


It truly is. I see the complexity stems from replication of the many OS services applications require. As a result, the containerized ecosystem ends up full circle but reinvented with a leaky abstraction that generates complexity. To me the comical part is how the IT team fails to acknowledge this.

Naturally it spreads like cancer. Non k8s native infrastructure is now abandon-ware. All that tech built over the last ten years is no longer seeing investment. Unfortunately, it solves real problems and rather well. Now a candidate to be reinvented and under the guise of reducing complexity it instead throws the users under the bus and actually does the opposite while costing a fortune.

When you step back and see: bare metals, vms, docker, k8s, stack of k8s plugins and tools, on prem and multi cloud all running concurrently... the IT team is really great at creating work and justifying their existence. Management needs to stop padding them on their back and hold them accountable for the mess they're generating.

It easy for me to complain, I guess, I'm not smart enough to understand how to kill this hydra. But I care about users and their experience and so maybe that's what's missing from this new frontier.


> the IT team is really great at creating work and justifying their existence.

Once you said this, the is no longer a neccesity to say more. I could not agree more.


I have always wondered if PostgreSQL for the data layer + HAProxy(multiple instances of web services) would be enough in most cases where Kubernetes is used. HAProxy provides blue-green deployment strategies for web applications for continuous deployments. Database is best kept outside of a distributed system anyway..

A good weekend project and some learning ahead.


It was a few years ago now, but my team at Amazon just deployed to a bunch of servers behind a load balancer and very rarely experienced downtime due to infrastructure. I’m working on a system that isn’t live right now and the infrastructure is so much more complex than that without a single customer. I spend a huge amount of dev time debugging issues that have their root cause in a flakey kubernetes cluster.


Awesome! I built a side-business that runs completely on Crystal + SQLite. Very light, fast service and makes ~$200k/mo.

I just cp my sqlite file to S3 every 2 hours.

From my app, i have a page[1] where i can load any snapshot database saved on S3. I can backup at anytime too with a click, which i do before deployment.

[1]: https://i.imgur.com/Ls1Tnxc.png


$200k/mo is remarkable for a "side" business! Can you share more of your story?


Sounds like it's https://cravecookie.com/ – so my guess is it's $200k/mo in revenue, not profit... but that's still seriously impressive, assuming at least decent margins!


They mention margins of 35-40% percent here: https://www.indiehackers.com/podcast/166-sam-eaton-of-crave-...

So, $70k - $80k a month in profits


That’s about 60k cookies per month, or about 1 cookie every 50 seconds.


This guy cookies


and he doesn't use cookies on his site


I would very much like a cookie


As a non-native speaker I struggle with "make". To me "make" for a side-business means "personal income" but it looks like you mean "revenue"?


Most people use "make" w/ a salary, so like "I make $80k/year" is your salary before any taxes, benefit costs, etc. It's ambiguous when it comes to a business but, on an internet forum that's fine. If you want to be specific you can use ARR and the like.

Edit: It's also totally cool to ask for a clarification, e.g. "huh do you mean total annual revenue or this is your annual salary from your business?"


If the side business makes $200k/mo, that usually means the revenue of the side business is $200k/mo.


Another non-native speaker here. I was also confused by this wording.


I'm a native speaker and it confused me to. If I make $50k, I mean I am taking home $50k If I've revenue of $50k, I am taking home less than $50k


> If I make $50k, I mean I am taking home $50k If I've revenue of $50k, I am taking home less than $50k

That is not normal native usage. If you make $50k, you're salaried at $50k, but you take home considerably less than that.


Another native speaker here: I think the fact that we can debate this is evidence enough that it's confusing :) Yes with salary I usually say I "make" my pre-tax income. But I don't know if that's really what "make" means per se, or if that's just a side effect of how most jobs advertise salaries in pre-tax terms. (Also I assume this usage is actually older than the income tax.) In my mind, if someone tells me they "make $X / month" from a business, it sounds to me like they're trying to draw a comparison with "making a salary of $X / year", which is a lot closer to profit than revenue.

I guess in the end, it's just uncommon to say something like "Microsoft made $X billion last year" by itself, because it's just not clear what it means. Business news articles will almost always phrase something like that as "made $X billion in profits" etc.


I think the fact we can debate this is evidence that it's not confusing!


Also got this gem from the Crave Cookies website:

"The software is built "from scratch" like the cookies and is part of Crave's success story. No other food company has the software Crave has for managing deliveries."

You should definitely do a write-up on Not Invented Here syndrom. And we sometimes "reinventing" the wheel, in moderation and for core components, really is the best solution.


Most of the time it's a matter of distilling to simplicity. No so much building a better mousetrap, as much as needing only a mousetrap instead of an Animal Processor 5000.


Good observation.


Honestly, that's a great solution that I've used before too. It seems like a potential 2-hour data loss window isn't catastrophic for your side business so something like Litestream could be overkill.


OT: Love the name! I've leaned into my name and make all my side hustles Crave {product} (ie, "Crave Training"), so I guess I'll cross cookies off my potential list lol.


Sounds fantastic, proves you don't need an overcomplicated tech stack to make bank.

Congratulations my dude!


I've legit fallen in love with sqlite. i save my global-scale infra chops for the dayjerb


Such a nice little mini-dashboard. Nice work. I'm copying it!

P.s. Crave Cookie looks neat.


Hey Devmunchies,

I'm too building a site on Crystal. This is my first production site(static comment). Would love to hear some story about how you run it.

Especially how you manage migration with Crystal+SQlite?


Is this a public business? Would you mind sharing, if so?


We do local cookie delivery[1]. We run our own mapping software and have our own drivers so most of the software is internal facing.

Talked about it on Indie Hackers podcast[2], where i briefly mention SQLite as well.

[1]: https://cravecookie.com

[2]: https://www.indiehackers.com/podcast/166-sam-eaton-of-crave-...


You make $200k/m delivering cookies in two cities? That’s incredible!

Always amazed by these niche businesses that make bank


we only have a single kitchen and it seems just above 200k is our ceiling for a single location. Expanding soon.

we took down our revenue from indiehackers but somebody took a screenshot and posted on twitter of the stripe verified revenue. https://pbs.twimg.com/media/EXbNBVUX0AAotOo?format=jpg&name=...


Is that gross?


I'm sure it's delicious! :)


Nice!


No the cookies are good apparently


Looks to be. Still very impressive!


That growth curve is impressive. If you keep that rate it won't be a side business for long.


Dammit man, now I want cookies.


Looks like it is Crave Cookies: https://www.indiehackers.com/product/crave-cookie


What crystal web framework do you use?


none. I had built a Crystal web framework that i retired[1]. I created something else that I use that's faster than the popular ones. Not open sourced.

Funnily, been into F# the past few months and built a web framework for that too. Still in progress[2].

[1]: https://github.com/samueleaton/raze

[2]: https://wiz.run/


Would you be willing to opensource that "something else" or if not then perhaps write a blog post about how it works?


Things are not quite that simple. You can't say "because my Go app serves a single request under no load in 50us, it will serve 20'000 per core under 100% load" you'd be surprised it will not.

Modern machines are like a networked cluster themselves. You need to do a ton of work to tune both kernel and "hardware" parameters to identify bottlenecks with near-non-existing debugging tools.

There is one truth here: we used to get more performance by scaling "horizontally". Maybe it's time to "scale within"?


That's a fair point. I've updated the post to read, "That translates to thousands of requests per second per core" instead of saying it's linear scaling. Thanks for the feedback!


That’s nice. But I didn’t really take it literally.

Do you have some benchmark results by any chance? Although it’s a bit of a can of worms, and I would understand if you didn’t want to get into it at this time.


I've only done some light benchmarking so far. I had it running on a two-core DigitalOcean machine with sustained write load to test for race bugs and it was replicating 1K+ writes per second. But honestly I haven't even tried optimizing the code yet. I'm mainly focused on correctness right now. I would bet it could get a lot faster.

https://twitter.com/benbjohnson/status/1351590920664313856


Sharding is not really scaling for many, and I think despite your good intentions you may be misleading others. I’m glad you like the setup but people flocking to SQLite scares me.


Immutable data are so much easier to operate on.

This is why a database like SQLite has its miraculous read performance: "competing not with Postgres but with fopen()", as somebody said.

But any mutation locks the entire database. This, again, simplifies the implementation a lot, and guarantees serialized DML execution.

Rather few web apps need high write concurrency and low write latency. Great many web apps serve 99.9% of hits with SELECTs only, and when some new data needs to be persisted, the user can very well wait for a second or two, so rarely it happens. But this is often forgotten.

Same realization brought a wave of static site generators: updates are so rare that serving pages from a database makes no sense, and caching them makes little sense: just produce the "pre-cached" pages and serve them as is.

Maybe a similar wave can come to lighter-weight web apps: updates are so rare that you don't need Postgres to handle them. You can use SQLite, or Redis, or flat files as the source of your data, with massively less headache. Horizontal scaling becomes trivial. Updates are still possible, of course, you just pay a much lower complexity price for them, while paying a higher latency price.

It's easy to notice that horizontally scaled local databases are already known: it's called "sharding". Unless you need to run arbitrary analytical queries across shards, eventual replication of changes, where needed, is sufficient. (BTW this is how many very large distributed databases operate anyway.) Litestream already seems to support creation of read-only replicas. This can allow each shard have a copy of all the data of a cluster, while only being able to update its own partition. This is a very reasonable setup even for some rather high-load and data-packed sites.


SQLite should be fine if your workload biased towards reads. But its design does prevent many-writers and these, even operates on completely different tables, has to be serialized between each other. This can be more problematic since many SQLite articles recommend big transactions to improve performance. And write transactions have to be serialized between each other. If you are a write-heavy workload, at some point, you may need to shard and may even need to implement custom transaction support on top of that yourself (or is there open-source libraries does that? rqlite from my understanding only handles consensus / replication, doesn't do sharding).


Yes, SQLite does serialize writes and write-heavy workloads and long-running transactions are not a good fit. Small batching of writes can help but SQLite provides options to tradeoff throughput for durability if that works for your situation:

PRAGMA synchronous = NORMAL;

That avoids fsync() calls on the WAL until checkpointing. I don't know of any libraries that implement transaction coalescing as it can be different depending on if you need to batch sequential writes versus writes coming from multiple parallel threads

re: rqlite, yes it handles consensus/replication through Raft but IIRC it doesn't do sharding.


Yeah, rqlite doesn't do sharding. There is a full copy of the data -- as an in-memory or on-disk SQLite database -- under each node.


Embedded databases where the db is a single file are the bomb.

We build several products in Java using H2 as our database. What a pleasure. Full SQL support, zero configuration/installation/etc.

Just copy data-files/dbs around using normal file tools.

Just start up one process and your app is ready.


This is my first time hearing about H2, what's the benefit of H2 over sqlite?


I think the big one is that it's written in Java so it runs seamlessly on the JVM. While JNI allows you to wrap native libraries for the JVM, it tends to mix poorly with the common artifact management and deployment pipelines.

The main use I've put it to over the years is as an in-memory database for integration tests.


Derby is/was the other alternative for an in JVM database.

For a long time, it was possible/practical to use SQLite from Java. Now, it is, but not if you want to keep things as pure Java (and another commenter mentioned). But really, in my mind, that’s the only real benefit for H2, the fact that’s it’s pure Java. So if you need that, you’re good.

But otherwise, I try to stick to SQLite.


I mean H2 is only really useful in Java, but the perf compared to sqlite in Java is equal or better due to better JDBC integration and not having to deal with JNI.


It integrates better with JVM


I've been building a web service on a cheapo DigitalOcean box lately, so I'm excited to see explorations in this space, especially with an eye towards staying cheap! I'd probably only use this particular tool if it could hook up to Backblaze B2 instead of S3, since life's too short to ever have to engage with the hell that is AWS for a hobby project, but since B2's API-compatible it seems like a feature that could be added in the future.

That said, I've always been a little worried about trying SQLite since I'm so used to Postgres. I've currently got Postgres running alongside my app in a Docker container, which isn't too hard to manage. I'm curious whether anyone has switched from Postgres to SQLite in a web app context (whether in the same project, or when making a new project) and if they've found themselves missing any of the features Postgres offers. I've tried to research this before but always found just googling "sqlite vs postgres" just results in surface-level differences that mostly focus on performance, whereas I'm more curious about e.g. the differences in their JSON extensions.


I haven't tried Backblaze B2 but I agree with you on engaging in AWS hell. Litestream should work with any S3-compatible API. I've added an issue to add guides for B2 & Minio[1].

Regarding Postgres vs SQLite, I've found that I can use much simpler SQL calls with embedded databases when I don't need to worry about N+1 query performance issues. That makes many of the query features moot. That being said, there is a JSON extension for SQLite[2] although I haven't tried it.

[1] https://github.com/benbjohnson/litestream/issues/41

[2] https://www.sqlite.org/json1.html


A bit more literature on the N+1 situation in SQLite: https://www.sqlite.org/np1queryprob.html


Thanks for linking this! It intuitively made some sense to me (no IPC/network overhead) but neat to see them point this out as an intentional advantage.


I've used json1 in SQLite for a bunch of different things and it's great - super-fast, has all of the features that I need. Since it's part of SQLite core I trust it as having the same level of reliability as the rest of SQLite.


I have always found the JSON functionality in SQLite fantastic. I'm using SQLite in production with Python for a project right now. It does not have much traffic yet, though.

I'm using multiples DBs and one of them is a simulation of key-value store. It's like a (Python) dictionary that is really an SQLite database, then I use keys like:

    db["users:1000:email"] = "email@email.com"
The feature I think I miss is being able to connect to the production database from my laptop to do a quick check. With SQLite, I have to ssh into the server and run the SQLite CLI (or copy the whole file).

Many people also mention concurrency, but I think that if you make your INSERT/UPDATE/DELETE statements fast and short + use WAL mode + use PRAGMA synchronous = 1, and some other optimizations, you can get quite far.


B2 seems to have an S3 Compatible API now.

https://www.backblaze.com/b2/docs/s3_compatible_api.html


Also used to PostgreSQL, and was considering SQLite for a web service. The biggest issue is how to run database migrations without downtime (while still accepting writes). There is no CREATE INDEX CONCURRENTLY. No ALTER TABLE DROP COLUMN. If the database is small, then it should be fast and it's probably fine. The clients can just retry. But the if the database is bigger, and migration takes a few minutes, this is an issue. Not sure how others here are handling this.


I'm appreciating this because I'm seeing the value of single process and single machine workflows. And this is after spending a decade on massive scale distributed systems, so I know that 98% of all people don't need scale.

My bet is on a single document server, and I'm writing a programming language for board games : http://www.adama-lang.org/docs/what-the-living-document

While I do see a future of the concepts I espouse as I'm making the language feel like an excel-reactive environment, my focus is on having fun. This year looks to be a slow year for the language as I built up some UIs for games.

Litestreem looks very interesting because it may be the missing piece where I could leverage it to get durability of a single database.


My work over the last decade has also been in distributed systems and I completely agree most people don't need that scale. These new massive scale solutions are exciting for the few large companies that need them but it feels like everyone is being pushed onto these platforms as well—whether it makes sense or not.

Let me know if you end up giving Litestream a try and how it works for you. I'm really working to make the developer experience as easy as possible and I'd love to hear feedback.


I've wanted to move a small, Postgres-backed web app (written in Go) to SQLite, but one of the hurdles is that we deploy to Heroku (which famously has an ephemeral filesystem).

Since the web app runs on a single, always-on dyno, seems like it may work to use Litestream to (1) continuously replicate and (2) restore when we restart the dyno.

For folks who have dug into Litestream further than me, any thoughts on this use case?

(Of course, need to handle starting and supervising Litestream and the web app from one process, per Heroku's 1:1 process-dyno model.)

Coincidentally, I stumbled across an old tweet by Ben (OP/Litestream author) about Heroku and SQLite[1] a month ago, when first thinking of getting the app off Postgres.

[1]: https://twitter.com/benbjohnson/status/1186666174467039233

Edit: typos


I’m also interested in trying this out


> Anecdotally, I’ve run several VPS servers over the years which all have well over 99.9% uptime and have suffered no catastrophic failures.

Without advocating for Kubernetes, there's a pretty big difference between running a single application like WordPress or whathaveyou that receives only occasional updates and a SaaS application that is actively developed by hundreds or thousands of engineers deploying dozens of times per day. Yes, Kubernetes is complex and that complexity can introduce its own downtime issues, but that risk is a large constant whereas without it the risk increases with the number of deployments (and deploying larger deltas less frequently carries its own penalties). It's important to understand and acknowledge these dynamics in order to optimize for uptime and velocity.


Yes, I agree there's definitely a place for Kubernetes. If you have thousands of engineers working on an application then workflow management becomes a very real concern. I've seen a lot of organizations adopt Kubernetes well before then though and I feel like it's a mistake. I didn't mean to imply that all Kubernetes applications should move to SQLite but that SQLite is a viable option for most small to medium sized applications with moderate uptime requirements.


Right, I didn't interpret you as trying to gloss over this nuance, but I've seen a lot of "always k8s vs never k8s" debates which suggests to me that people need the explicit disclaimer.


Ben, regardless of the product (I have not checked it out yet), kudos on the pitch. At least for me it sounded genuine, and hit a few sore spots right in the stomach as it should.

Best of luck.


On the GitHub repo ( https://github.com/benbjohnson/litestream ), it saids: "Litestream only communicates with SQLite through the SQLite API so it will not corrupt your database."

Does that mean I can use an in memory SQLite DB and have it replicated too? That would have some amazing potentials.


Unfortunately, no. Litestream uses the SQLite API for locking and state but reads the WAL file from disk.


On GCP I do something similar with regional disks:

- Disks are replicated to another zone on every write.

- Incremental disk snapshots can run once per hour and are stored in Cloud Storage.

This means when the OS fsync's it is actually copying those bytes to another zone.

https://cloud.google.com/compute/docs/disks#repds


Refreshing to see Ben and good luck with the new project. I'm a huge fan of boltdb and use it in micro. With a background in distributed systems also I share this frustration with the complexity of software and the direction cloud is taking in general. While my personal take is running things on a VPS, my work is all around platforms and I think the end user experience needs to be drastically simpler.


> If you exceed the capacity of a single node, sharding your data can allow you to scale horizontally to multiple nodes.

It's so simple, just don't have a lot of data that doesn't need to interact with other data and your problem is solved!

/s


This is take seems a little intellectually dishonest. The very next sentence is, "this works particularly well for SaaS applications where each customer is isolated from one another." Sharding doesn't work for all applications. Social networks are one example where all data can potentially interact with all other data.

Sarcasm noted though. :)


Awesome product, btw. Definitely see where it fits some niches.

Actual concerns about running a real application with real uptime requirements.

1. Say your EC2 or docker container that's hosting this goes down. Is that left up to the user to deal with? RDS handles this for you

2. No ACID transactions if you ever outgrow a DB. You talk about in your pitch that the vertical scaling, so you have to just keep bumping the VPS/container memory.

3. Sure a SaaS application where a customer specific DB is isolated, but as soon as you hit any _real_ scaling limits you immediately are back to the entire problem statement you are aiming to (at least you hint at that in your pitch) solve which is the crazy n-tier architectures we have.

While I was being sarcastic, I was not being intellectually dishonest. There are entire hosts of problems that you call out you are trying to solve without providing any real solution.


> Awesome product, btw. Definitely see where it fits some niches.

Thanks! I appreciate it.

> Say your EC2 or docker container that's hosting this goes down. Is that left up to the user to deal with? RDS handles this for you

Yes, that's out of scope for Litestream since there are a lot of ways to manage that depending on your application. I agree that RDS wins here for simplicity.

> No ACID transactions if you ever outgrow a DB. You talk about in your pitch that the vertical scaling, so you have to just keep bumping the VPS/container memory.

You still have ACID transactions but they are just per-shard. For a SaaS application, that seems reasonable since they're localized to the customer (assuming you're sharding by customer).

> Sure a SaaS application where a customer specific DB is isolated, but as soon as you hit any _real_ scaling limits you immediately are back to the entire problem statement you are aiming to (at least you hint at that in your pitch) solve which is the crazy n-tier architectures we have. [...] There are entire hosts of problems that you call out you are trying to solve without providing any real solution.

I'm not trying to solve an infinite scaling problem. If you're seeing sustained 100K request/sec on your application then you'll need specific solutions. But I'd argue that 98% of applications never come near that threshold and those are the applications that could benefit from simpler architecture.

Thanks for all the feedback. I hope I'm not coming off as argumentative.


Social networks are also write-heavy so probably not a good fit for SQLite anyways, right?


Social networks have a lot of lurkers who mostly read so I'm not sure about the balance of reads vs writes.


I was getting enthusiastic about Fediverse (ActivityPub) use case, for small-ish federated instances. But these probably are still rather write-heavy even with lurker users due to the synching of federation msgs with other instances.


With this approach, people have to start sharding their data as soon as one server can't handle their application and sqlite usage.

Why not advocate instead for people to start out running their application and postgresql on the same server, with wal-e for backups to S3?

That has almost the same benefits while everything fits on one server, but opens up alternative approaches to scaling if one day things no longer fit.


because sqlite is super simple to use and most things won't ever need to scale beyond one server.


> most things won't ever need to scale beyond one server.

Yep. But don't tell anyone. This is a secret weapon/super power most juniors (and many mid/seniors) have been conditioned by the GOOG/FB/AMZN/MSFT approved project literature to believe is simply untrue. The number of experienced, highly skilled founder devs I know that reach for shiny tech stacks that solve issues they don't have but can "scale" is a 100:1. That choice comes with staggering costs.


Well, as soon the solution doesn't scale - which will likely be within a couple of years - one needs to rearchitect and recode.

I suppose single node+db works fine for hobby SAAS and expert programmers who can squeeze every bit of performance from the machine. But for the average in-efficient programmer or teams, standard PostGres as DB and HA services fronted by a load-balancer work better.


> Well, as soon the solution doesn't scale - which will likely be within a couple of years - one needs to rearchitect and recode.

Your project is far more likely to die within a couple of years from having too few users than from having too many. If you find you're having scaling issues then that's a good time to solve them - your project is popular enough to justify the investment.

You'll likely be recoding your project quite a lot in the early phase of the development anyway as you learn more about the problem and (most importantly) more about what your users want/need. Anything that can reduce the cost of this early iteration will pay dividends later because it makes it more likely that you'll need to scale - and by the time you do need to scale you'll be scaling something that you understand and other people want.


> Solutions such as Kubernetes tout the benefits of zero-downtime deployments

To be fair, these features of k8s are nice to have. Is there a similar tool for running single node (i.e. ec2) instances that offers zero-downtime deploys? One way you could do it is run a EKS cluster with a single ec2 node running your single-node setup. You could get the benefits of k8s while still running the simple single-node arch


You can do zero-downtime deploys on a single node with tools like seamless[1]. That works for Go but I'm sure there are other options for other languages. I'm not sure if/how you can do it with a containerized workload on a single node though. I haven't tried that.

[1] https://github.com/rs/seamless


Cool, hadn't heard of seamless. Something similar to this for other langs wrapped in a CLI with an integration with Digital Ocean/AWS/etc. could be valuable


> From the kernel point of view, there is a only one socket with a unique listen queue. This socket is associated to several file descriptors: one in systemd and one in the current process. The socket stays alive as long as there is at least one file descriptor. An incoming connection is put by the kernel in the listen queue and can be dequeued from any file descriptor with the accept() syscall. Therefore, this approach actually achieves zero-downtime deployment: no incoming connection is rejected. [0]

systemd can be sufficient for a zero-downtime deployment.

[0] https://vincent.bernat.ch/en/blog/2018-systemd-golang-socket...


Random thought--what about storing SQLite replicas on a OCI image store (docker registry) as an option? Then your service code (as container images) and data (as SQLite replicas from litestream) all live in one central store, under the same auth strategy, the same versioning, the same auditing, etc. You can tightly lock down your servers to only talk to the image registry and that's it.


Ben, this is impressive work, and I will almost certainly use it. (Already believe in sqlite vs mysql/PG/Redis) But as a backend developer I am always trying to learn simpleR ways to build beautiful open source websites. Would you mind sharing how you built the site? I see Bootstrap. Would you share the tools you use? Thank you, Cameron


Thanks! The site is a Hugo[1] site with a modified version of the Doks template[2]. I changed some colors and the font and I hired a designer on Fiverr[3] to do a logo for $99.

[1]: https://gohugo.io/

[2]: https://getdoks.org/

[3]: https://www.fiverr.com/dieseelle


You are my hero! Thank you! Front end for a back-end guy like me is a learning journey. (I suppose I should stop labeling myself!)


I'm mostly backend myself so I feel your pain. Writing Litestream was the easy part; setting up litestream.io was the real challenge! ;)


I think the simplicity of different thinking of this project is brilliant! Great work!

Having dealt with complicated db replication this sounds like a good fresh idea.


This is fantastic. I have hemmed and hawed in doing similar things to build a serverless database. (both in the embedded sense and the FaaS sense) I could have a file or more per client and pay nothing until they load the app. There are a lot of use-cases as in mine that have low read frequency and low or no updates yet high write throughput. I would love to not have to pay for keeping all my indexes in hot ram If at all possible by sacrificing a cold-start and maybe some latency. I could see doing that with Litestream.


Another aspect of this db file per client configuration that is fantastic is the ability to offer data ownership and portability by allowing users to download the database file for themselves. No sql dump or service needed just a signed download url.


This is neat! I have a tool blacklite[1] that logs to sqlite, so using this I can export those logs automatically to S3 and keep persistent logs even if the instance goes away. I can see this being really useful for k8s and docker containers.

[1]: https://tersesystems.com/blog/2020/11/26/queryable-logging-w...


One notable site which runs on SQLite is SQLite's own source code version tracking system called Fossil.

See https://sqlite.org/src/doc/trunk/README.md and https://www.fossil-scm.org


I haven't used SQLite in production, but I've used a single installation of Postgres for some surprisingly high traffic websites, and it works amazingly well with essentially no tweaking. I agree that the vast majority of people using anything more complex very likely don't need to be.


Hi Ben, thank you so much for building this!

I'm currently working on implementing a set of data structures (and more things later) on top of SQLite (https://github.com/litements/) for some of the same reasons you mention in the article.

I probably don't even have 1/10 of your experience, but your work is really motivating me to keep working on it, thanks!


I like where you're going with Litements. There are a lot of servers that could just be libraries running on SQLite. Job queues are one of those I find myself reimplementing sometimes.


This is an interesting idea... It'd be really cool to have these as SQLite extensions. I could see that being powerful (in part because of language agnosticism). Granted, a bit harder to build/install. https://sqlite.org/loadext.html


This is off-topic, but when I was a Mac user, I always wishes Apple built a barebones graphical tool into the OS to view and edit SQlite databases. They pop up now and then, including via Apple applications, and it would be convenient to view their contents in the GUI without third party tools.


During development, database tables often start out as spreadsheets. The Pages app has an excellent GUI. It's not difficult to import and export SQLite table data in either CSV or TSV format. Using pipes and scripting, I bet the transfer between SQLite and Pages could be implemented with one-line shell commands.


The article mentions saas. So instead of a single multi tenant db, you might have a sqlite for each customer? Maybe even copy it down to the client? I have a idea for a media cms, wondering if this design would fit.

One drawback I can think of is it would be difficult to create reports, cross customer.


I think the point was that saas data is easily sharded as it's all just a bucket of data per user/customer so you have a lot of freedom in distributing customers across different database instances. The most specialized version of that architecture would be an entire DB per customer, but that's likely a lot of potentially unnecessary file overhead (and a giant pain for upgrades and migrations). IMHO you'd probably be fine to just start scaling horizontally distributing users across more DB instances (but really... don't miss the main point that you might not even need more than one DB instance in the first place).

Reports are nice because they can generally be done in an async job that's effectively a big map-reduce run across your DBs. If you need faster real-time reports you're going to want some kind of pipeline for events and stream processing that's outside the scope of this anyways.


So I create database on VPS, than set up Litestream on s3. Than what? Should I also setup the copied database and use it from my production application? It seems like I don't understand the workflow here in a real case scenario. It my be because I didn't work with such setups


Litestream runs on the VPS with your application & database and then streams changes to S3. If your VPS dies and you lose your data, you can run "litestream restore" to restore your database to the state it was just before your VPS died.


This is great. I love all of the work being done around SQLite.

If bedrockdb ( https://bedrockdb.com/ ) replicated to object storage like litestream, I’d be in heaven.


What is this doing, I mean, cannot append files in s3, so how does it store the objects?


My love affair with sqlite is hard to describe. I'm glad I'm not crazy.


This is a awesome Project!

Is there a way to backup encrtypted? This would be a killier feature!


How do you plan to monetize this given your experience with BoltDB and opensource monetization stance?

Is making the project closed to contributing part of that plan?

How did you decide on GPLv3 liscense?


But what's the endgame of this approach regarding scaling? If I ever need horizontal scaling, am I screwed? Genuinely curious :)


> If you exceed the capacity of a single node, sharding your data can allow you to scale horizontally to multiple nodes. This works particularly well for SaaS applications where each customer is isolated from one another. Because SQLite and Litestream simplify deployment, managing a cluster of several isolated nodes is easy to maintain.

Basically the argument is: do it yourself, and time spent learning how to do it effectively is compensated by the time not spent fighting over complexity caused by operating (and understanding how to use properly) stuff that allegedly "does it for you".

The argument sounds plausible in principle, but I'm not entirely sure if this argument resist the harsh clash with reality (where you end up with both doing it yourself and dealing with the effects of your own complexity but this time with no community to consult)


This isn't really a new approach to scaling--even if you're using a server DBMS like postgres/mysql/etc. you still have to figure out scaling based on your data. If that data is easily isolated into customer/user units then it's straightforward to shard them across more instances, whether they're more postgres boxes or SQLite processes. If the data isn't easily sharded... well you're no worse off whether you're using SQLite or any other database.

The main point here is that instead of reaching for a costly to maintain, to configure, to secure, etc. DBMS first, many folks would likely be better served by the vastly simplified management of a single SQLite process.


I don’t think the comparison is fair with regard to scaling. Imagine this situation: you’d like to have 2 instances of your application running on two different machines, and you’d like to rollout updates with no downtime. It’s trivial with MySQL/Postgres, but it’s tricky with sqlite.

A lot of solutions don’t take into consideration “day 2” operations: mostly around no downtime upgrades.


Sounds similar to CouchDB's approach, but I've never used that either.


Seems like something I want to play with. Max size of an object in S3 is 5TB so that’s plenty of head room.


Is there anything like temporal tables (select as of) in SQLite? The one thing keeping me with MariaDB


This looks like exactly what I need for my small projects. Looking forward to using it. Thanks.


Ben - What are some practical examples of use cases you see here over the long term?


Hi Ethan, I see two primary use cases currently:

1. Applications which are read-heavy & serve fewer than 10,000 requests per second.

2. SaaS applications which can be sharded so that the largest customer uses 10,000 requests per second or less.

Once read-only replicas functionality is added, I'm also excited about globally-distributed applications where local servers can serve users with low-latency read requests. For example, you could run a high-traffic e-commerce site with 20 PoPs spread across the world for only $100/month. Customers in India could get the same fast response times as someone in the US. I think that's pretty compelling.


I would love to be able to read sqlite from s3 without loading the whole file.


I use boltdb for all my personal projects. Thank you so much for writing it.


>The biggest problem with using SQLite in production is disaster recovery.

No, SQLite has WAL and can recover from failures. The biggest problem with SQLite is concurrency. You can't write from multiple processes in parallel.


That only solves a narrow range of disasters. Your disk could die or the file system becomes corrupted or your ec2 instance is accidentally destroyed or etc.


We need this, right now. Thank you for building it!


Is Litestream as well-tested as SQLite?


No, not yet! :)

Litestream runs continuously on a test server with generated load and streams backups to S3. It uses physical replication so it'll actually restore the data from S3 periodically and compare the checksum byte-for-byte with the current database.

I made sure Litestream would be safe with the primary database so it only communicates through the SQLite API for locking and state. It should be completely safe to run unless there is a bug in SQLite itself. You can also continue to use a separate periodic backup strategy if you're not comfortable running Litestream alone for disaster recovery.


Very awesome project!!!


"Litestream is a tool that runs in a separate process and continuously replicates a SQLite database to Amazon S3."

Why Amazon S3? Was it a huge invention by people at Amazon, and even if so, does it assign credit where it is deserved? Is Amazon helping the open web? I don't think so.


"Why Amazon S3?"

Because it's extremely cheap, durable and easy to read/write?


I mean, why not just call it "cloud storage"? That would leave it open for the bajillion of them, of which S3 is no longer special.

https://rclone.org/


They did invent it and name it like this. Is there an issue with calling it Amazon S3? Can't they get credit for it?

Even software such as Ceph calls themselves compatible with "Amazon S3 API".


They did not invent object storage so much as popularize it and standardize the terminology we now use. S3 was announced in 2006. By then, many other distributed object stores had already existed for years, mostly for purely archival use and often a bit enterprise-ish (e.g. FilePool/Centera in 2002). Depending on how much similarity you require, you could even trace lineage back to NASD in 1995. There's also a closely related space of P2P applications which had different goals but similar APIs and implementation details. For example, Freenet and Gnutella both started in 2000. It's practically certain that some techniques worked out in that space informed the design of S3.

Unfortunately Amazon has not done a very good job crediting predecessors, but they do deserve credit for bringing what had previously been rather niche ideas (I know because I was there throughout) to the masses.


Great explanation, thanks. While I knew several object storage techs came before S3, I was not aware that there were also similar APIs existing before.

It's bad from Amazon to not credit predecessors. If previous APIs are that close with S3, adding their branding is problematic.




Applications are open for YC Summer 2021

Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: