[HN Gopher] Why I Built Litestream
___________________________________________________________________
Why I Built Litestream
Author : benbjohnson
Score : 266 points
Date : 2021-02-11 16:46 UTC (6 hours ago)
(HTM) web link (litestream.io)
(TXT) w3m dump (litestream.io)
| asim wrote:
| 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.
| avolcano wrote:
| 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.
| misframer wrote:
| B2 seems to have an S3 Compatible API now.
|
| https://www.backblaze.com/b2/docs/s3_compatible_api.html
| polyrand wrote:
| 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.
| benbjohnson wrote:
| 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
| simonw wrote:
| 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.
| benatkin wrote:
| "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.
| moonlighter wrote:
| "Why Amazon S3?"
|
| Because it's extremely cheap, durable and easy to read/write?
| benatkin wrote:
| 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/
| Sayrus wrote:
| 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".
| notacoward wrote:
| 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.
| camkego wrote:
| 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
| benbjohnson wrote:
| 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
| camkego wrote:
| 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!)
| benbjohnson wrote:
| I'm mostly backend myself so I feel your pain. Writing
| Litestream was the easy part; setting up litestream.io was
| the real challenge! ;)
| throwaway894345 wrote:
| > 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.
| benbjohnson wrote:
| 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.
| throwaway894345 wrote:
| 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.
| nine_k wrote:
| 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.
| mathgladiator wrote:
| 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.
| benbjohnson wrote:
| 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.
| devmunchies wrote:
| 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
| truetraveller wrote:
| 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.
| bdcravens wrote:
| 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.
| truetraveller wrote:
| Good observation.
| RobRivera wrote:
| I've legit fallen in love with sqlite. i save my global-scale
| infra chops for the dayjerb
| kureikain wrote:
| 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?
| ibraheemdev wrote:
| What crystal web framework do you use?
| devmunchies wrote:
| 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/
| yannoninator wrote:
| Sounds fantastic, proves you don't need an overcomplicated tech
| stack to make bank.
|
| Congratulations my dude!
| truetraveller wrote:
| Such a nice little mini-dashboard. Nice work. I'm copying it!
|
| P.s. Crave Cookie looks neat.
| renewiltord wrote:
| Is this a public business? Would you mind sharing, if so?
| devmunchies wrote:
| 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-...
| vxNsr wrote:
| You make $200k/m delivering cookies in two cities? That's
| incredible!
|
| Always amazed by these niche businesses that make bank
| devmunchies wrote:
| 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/EXbNBVUX0AA
| otOo?format=jpg&name=...
| lostmsu wrote:
| Is that gross?
| indigodaddy wrote:
| No the cookies are good apparently
| vxNsr wrote:
| Looks to be. Still very impressive!
| growt wrote:
| I'm sure it's delicious! :)
| truetraveller wrote:
| Nice!
| reggieband wrote:
| That growth curve is impressive. If you keep that rate it
| won't be a side business for long.
| andag wrote:
| Dammit man, now I want cookies.
| hobofan wrote:
| Looks like it is Crave Cookies:
| https://www.indiehackers.com/product/crave-cookie
| KingOfCoders wrote:
| 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"?
| mplewis wrote:
| If the side business makes $200k/mo, that usually means the
| revenue of the side business is $200k/mo.
| cpach wrote:
| Another non-native speaker here. I was also confused by
| this wording.
| pbowyer wrote:
| 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
| thaumasiotes wrote:
| > 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.
| camgunz wrote:
| 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?"
| benbjohnson wrote:
| 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.
| chrisweekly wrote:
| $200k/mo is remarkable for a "side" business! Can you share
| more of your story?
| tnorthcutt wrote:
| 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!
| bdcravens wrote:
| 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.
| mplewis wrote:
| This looks like exactly what I need for my small projects.
| Looking forward to using it. Thanks.
| ethanpil wrote:
| Ben - What are some practical examples of use cases you see here
| over the long term?
| benbjohnson wrote:
| 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.
| hangonhn wrote:
| 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.
| benbjohnson wrote:
| Unfortunately, no. Litestream uses the SQLite API for locking
| and state but reads the WAL file from disk.
| cfors wrote:
| > 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
| benbjohnson wrote:
| 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. :)
| cfors wrote:
| 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.
| benbjohnson wrote:
| > 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.
| aidenn0 wrote:
| Social networks are also write-heavy so probably not a good
| fit for SQLite anyways, right?
| benbjohnson wrote:
| Social networks have a lot of lurkers who mostly read so
| I'm not sure about the balance of reads vs writes.
| mixmastamyk wrote:
| 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.
| grandrew wrote:
| 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"?
| benbjohnson wrote:
| 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!
| wsargent wrote:
| 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...
| polyrand wrote:
| 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!
| benbjohnson wrote:
| 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.
| polyrand wrote:
| 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
| liuliu wrote:
| 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).
| benbjohnson wrote:
| 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.
| otoolep wrote:
| 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.
| acd wrote:
| 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.
| lootsauce wrote:
| 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.
| lootsauce wrote:
| 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.
| lancehasson wrote:
| > 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
| benbjohnson wrote:
| 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
| lancehasson wrote:
| 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
| knowhy wrote:
| > 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...
| PeterStuer wrote:
| 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.
| qbasic_forever wrote:
| 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.
| skybrian wrote:
| Is Litestream as well-tested as SQLite?
| benbjohnson wrote:
| 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.
| grandinj wrote:
| 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.
| xrendan wrote:
| This is my first time hearing about H2, what's the benefit of
| H2 over sqlite?
| mmcdermott wrote:
| 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.
| mbreese wrote:
| 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.
| bob1029 wrote:
| > "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.
| pbowyer wrote:
| > 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.
| breischl wrote:
| >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.
| throwaway3699 wrote:
| It sounds likely that it can. Software solutions to hardware
| problems have been a common pattern for years now.
| mattgreenrocks wrote:
| 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.
| the_duke wrote:
| 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).
| bob1029 wrote:
| 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.
| majkinetor wrote:
| 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.
| motiejus wrote:
| https://blog.expensify.com/2018/01/08/scaling-sqlite-
| to-4m-q...
| benbjohnson wrote:
| Those are awesome tips. I didn't even think of using
| "user_version" for storing a migration version. I'm definitely
| stealing that trick.
| jitl wrote:
| This is how we manage SQLite migrations in Notion's native
| apps.
| tomaszs wrote:
| 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
| benbjohnson wrote:
| 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.
___________________________________________________________________
(page generated 2021-02-11 23:00 UTC)