The DX for deploying apps to Fly.io is rough.

Every time I deploy something, it spins up 2 instances that are in some suspended state. I have to restart them like 3 times before they actually boot? And why can I never just pick one instance when launching an app.

Apps will randomly go into a suspended state, without explaining why. Contacting support says they ran out of capacity, but didn't automatically start them back when capacity was available?! That's the whole point of Apps (vs Machines), you keep my app running...

Fly is set up to be the best compute provider, but there are too many reliability and ergonomics issues.

Please stop updating flyctl every time i go to deploy an app

I setup a fresh rails 8 app on Fly last year, using PG for the main data store but using SQLite for the ancillary solid stack dbs.

Only fuss I remember encountering was with fighting with rails migrating solid queue properly, but this seemed like a rails unit issue and don’t remember it being a Fly issue.

I’ve been contemplating migrating my pg primary to SQLite too. Anyways don’t have much else to offer other than an anecdote that I’m happily using fly with partial SQLite.

I use in production but it's a console app that lives on a server. The database sits on a file share.

I self host on a vps Litestream works for that quite well

That makes sense to me. LiteFS used FUSE, which meant figuring out how to run and mount a custom filesystem. Litestream is a single compiled Go binary that you point at the SQLite database file (and accompanying WAL file).

>The DX for deploying SQLite apps to Fly.io is rough. I'm a few hours into trying to get a production Rails app booting, but running into issues getting the database to initialize, migrate, and become writable. The root of my problem was the eager loading of a gem I wrote, but there were several layers of runners above it that made it hard to diagnose.

What's the Fly.io issue here? Aren't the issues you're describing in Rails not Fly.io?

I run several Go apps in production on Fly.io[0, 1, 2] and I've never had an issue with the Fly.io + SQLite part of it.

SQLite + Litestream makes things slightly more complicated, but again, I've never had issues with Fly.io specifically making that harder. I use a custom launch script in my Dockerfile that starts litestream with the -exec flag to start my app as a child process.[3]

[0] https://github.com/mtlynch/logpaste

[1] https://github.com/mtlynch/picoshare

[2] https://github.com/mtlynch/screenjournal

[3] https://github.com/mtlynch/logpaste/blob/0.3.1/docker-entryp...

I tried to go the litestream route on Fly.io, but there is too much that needs to be done to get it working. Specifically I was hoping scaling would be a lot easier, but master election kept breaking for me causing the whole app to not be able to come online. I just moved to Fly's managed postgres and called it a day.

Their managed postgres has gotten better, but its still a little sparse, so after about 6 months using it I am going to just take my DB to either Supabase or Planetscale.

> I bet you could get very very far on a single box,

With single instances topping out at 20+ TBs of RAM and hundreds of cores, I think this is likely very under-explored as an option

Even more if you combine this with cell-based architecture, splitting on users / tenants instead of splitting the service itself.

I used to work on a product where the app server and database were in the same rack - so similar low latency. But the product was successful, so our N+1 would generate thousands of queries and 1ms would become >500ms or more easily. Every other month we would look at New Relic and find some slow spot.

It was a Rails app, therefore easy to get into the N+1 but also somewhat easy to fix.

Bad query practices are always going to bite you eventually. I would not call that a shortcoming of this approach

Jepsen is primarily for testing distributed systems and consensus systems. SQLite has neither of those.

Litestream is pretty boring for the testing Kyle does. It's not a distributed consensus system, but something more like a storage engine.

Yeah, something that’s messed up that they don’t think is messed up is running `fly console` fires up another instance, which isn’t attached to the same volume, so you have to run `fly ssh console —pty`

It’s certainly not intuitive. It would be awesome if they sweat these details, but their deal is “here’s a bag of sharp knives”, which is good for some use cases.

It's not a bad query practice in SQLite! https://www.sqlite.org/np1queryprob.html

I mean, that's not much of a trade off given that it seems that what you're saying is that using such a service might just show you how shit your code actually is.

Its not its fault. :)

this is infra for a single-user app. SQLite is THE replacement for file databases like MSAccess, but the box goes down and your database dies with all your data.

So this fills that gap by giving you a database as a service level of QOL without needing to provision a database as a service backend. Otherwise you're dicking about maintaining a service with all that comes with that (provisioning, updating, etc) when really all you need is a file that is automagically backed up or placed somewhere on the web to avoid the drawbacks of the local file system.

litestream makes very few consistency guarantees compared to other datastores, and so I would expect most any issues found would be "working as intended".

at the end of the day with litestream, when you respond back to a client with a successful write you are only guaranteeing a replication factor of 1.

Thanks, what'd you like about it?

Thx. The only problem I have with litestream binary is ~31Mb !?

This why I prefer to take backup stuff in a side container, eg: https://github.com/atrakic/gin-sqlite/blob/main/compose.yml

As a side note, you might consider revisiting your dockerfiles and skip litestream build steps, eg. in your final stage just add line like this:

COPY --from=litestream/litestream /usr/local/bin/litestream /bin/litestream

For our rails app we actually added tests asserting no N+1s in our controller tests. Think a test setup with 1 post vs 10 posts (via factorybot) and you could do an assertion that the DB query count was not different between the two. A useful technique for any Railsheads reading this!

Single instance is underappreciated in general. There's a used server reseller near me, and sometimes I check their online catalogue out of curiosity. For only $1000ish I could have some few generations old box with dual socket 32-core chips and 1TB of RAM. I don't have any purpose for which I'd need that, but it's surprisingly cheap if I did. And things can scale up from there. AWS will charge you the same per month that it costs to get one of your own forever - not counting electricity or hard drives.

What is N+1?

It's significantly faster and incurs less ops overhead. That's it.

But most apps should just use a classic n-tier database architecture like Postgres. We mostly do too (though Litestream does back some stuff here like our token system).

To avoid operating a database by yourself and dealing with incidents, backups, replicas, failovers, etc... You can use cheap commoditised S3-like storage and run your application statelessly.

If you have access to a database that is well managed on your behalf I would definitely still go with that for many usecases.

I'm not sure, I've never done it, but I think the idea is to have many tiny customer-specific databases and move them to be powered by sqlite very close to the customer.

But I'd love to hear more from someone more well-versed in the use cases for reliable sql-lite

I find myself mostly in this camp now.

In every case where I had a SQLite vertical that required resilience, the customer simply configured the block storage device for periodic snapshots. Litestream is approximately the same idea, except you get block device snapshots implicitly as part of being in the cloud. There is no extra machinery to worry about and you won't forget about a path/file/etc.

Also, streaming replication to S3 is not that valuable an idea to me when we consider the recovery story. All other solutions support hot & ready replicas within seconds.

All the public benchmarks (and my own applications) indicate that there is only a small (often unnoticeable) performance penalty associated with modernc.org/sqlite and this is far outweighed by the ability to eliminate CGO. I'd use it on future projects without hesitation.

One of the big advantages people enjoy is the elimination of the network latency between the application server and the DB. With SQLite your DB is right there often directly attached over NVME. This improves all access latencies and even enables patterns like N+1 queries which would typically be considered anti-patterns in other DBs.

Why use Postgres if all you need is sqlite? Postgres is way overkill for a simple app with few users and no advanced database functionality.

The common answer (especially from Fly.io) is "at-the-edge" computing/querying. There is network latency involved in sending a query to MySQL or Postgres and getting the data returned, whereas with Litestream you could put a read replica of the entire SQLite DB at every edge. Queries become fast and efficient only to the local read replica. There's still network latency associated with updating that read replica over time, but it is amortized based on the number of overall writes rather than the number of queries, is more fault tolerant in "eventually consistent" workflows (you can answer queries from the read replica at the edge in the state that you have it while you wait for the network to reconnect and replay the writes you missed during the fault), and with SQLite backing it still has much of the same full relational DB query power of SQL you would expect from a larger (or "proper") database like MySQL or Postgres.

I have a branch office in boondocks with limited internet connection. The branch office cannot manage a RDBMS or access cloud services. They can use sqlite app on LAN and we could do reconciliation at end of the business day.

It's a good question, and I don't think answered sufficiently in the recent sqlite hype.

In my opinion if you have an easy way to run postgres,MySQL,... - just run that.

There's usually a lot of quirks in the details of DB usage (even when it doesn't immediately seem like it - got bitten by it a few times). Features not supported, different semantics, ...

IMO every project has an "experimental stuff" budget and if you go over it it's too broken to recover, and for most projects there's just not that much to win by spending them on a new database thing

I’m curious what you don’t like about fly.io. Also what’s the connection to svelte?

Interesting! I really like your wasm compiled SQLite more than the pure Go one, so it is what I use most of the time.

What exactly are you trying to port?

Law Theorem[1] fits perfectly for this scenario

1- https://law-theorem.com/

they can also run the entire application in these scenarios on the resources of a 10-yr-old phone.

they hired some of the svelte people on top of the react people they hired... "embrace, extend, extinguish", for the cynically-bent. i personally wouldn't go anywhere near that far-- i just haven't "dug" the platform the times i have considered it, personally. i wish them nothing but success.

For me, the introspection and meeting (us) where we're at:

>But the market has spoken! Users prefer Litestream.

>Yeah, we didn’t like those movies much either.

>Thx. The only problem I have with litestream binary is ~31Mb !?

Where are you seeing a 31 MB binary? Latest releases are 10-11 MB.[0]

>This why I prefer to take backup stuff in a side container, eg: https://github.com/atrakic/gin-sqlite/blob/main/compose.yml

Yeah, I agree that's cleaner, but once you're dealing with multiple containers, it's a big step up in complexity, and you can't do the simple install on places like Fly.io and Lightsail.

>As a side note, you might consider revisiting your dockerfiles and skip litestream build steps, eg. in your final stage just add line like this:

>COPY --from=litestream/litestream /usr/local/bin/litestream /bin/litestream

Ah, thanks! The litestream Docker image is new as of 0.5.0.

[0] https://github.com/benbjohnson/litestream/releases

That's a good trick. In Django world I like pytest-django's django_assert_max_num_queries fixture: https://pytest-django.readthedocs.io/en/latest/helpers.html#...

  def test_homepage_queries(django_assert_max_num_queries, client):
      with django_assert_max_num_queries(10):
          assert client.get("/").status_code == 200

Or django_assert_num_queries to assert an exact number.

Way back in the prehistoric era of Rails I just wrote a like 5 line monkey punch to ActiveRecord that would kill mongrel if queries per request went above a limit.

Probably some of the most valuable code I've ever written on a per LOC basis lol.

But anyhow, merging that into a new project was always a fun day. But on the other side of the cleanup the app stops falling down due to memory leaks.

I guess you got cheap power. Me too, but not 24/7 and not a whole lot (solar). So old enterprise hardware is a no-go for me. I do like ECC, but DDR5 is a step in the right direction.

> One of the big advantages people enjoy is the elimination of the network latency between the application server and the DB. With SQLite your DB is right there often directly attached over NVME.

You can install MySQL/PostgreSQL on the application server, connect over a unix socket and get the same benefits as if you'd used SQLite on the application server (no network latency, fast queries). Plus the other benefits that come from using these database servers (Postgres extensions, remote connections, standard tooling etc). I'm guessing more RAM is required on the application server than if you used SQLite but I haven't benchmarked it.

Real talk, how do you actually avoid N+1? I realize you can do complicated JOINs, but isn't that almost as bad from a performance perspective? What are you really supposed to do if you need to, e.g. fetch a list of posts along with the number of comments on each post?

For a cloud service, I think it comes down to whether you’ll ever want more than one app server.

If you’re building something as a hobby project and you know it will always fit on one server, sqlite is perfect.

If it’s meant to be a startup and grow quickly, you don’t want to have to change your database to horizontally scale.

Deploying without downtime is also much easier with multiple servers. So again, it depends whether you’re doing something serious enough that you can’t tolerate dropping any requests during deploys.

Because Postgres is mature, works, and has a version number above v1.0?

I'd argue that anything larger than a desktop app should not use SQLite. If you need Litestream for replication and backup it is probably better to just use Postgres. There are a ton of one-click deployment offerings for proper databases, Fly.io actually offers managed Postgres.

Let's say I'm building a small app that I'm hosting on some shared vps, if I think about the effort involved in setting up sqlite with litestream and just getting a $5 (or free) postgres provider I don't think sqlite makes my life easier.

Now if I'm building a local app then absolutely sqlite makes the most sense but I don't see it otherwise.

>> the recent sqlite hype.

This is an interesting take; why do you see recent hype around the most boring and stone-age of technologies, SQLite?

Just so we're all clear: Litestream is just an open source project, not a feature of our platform. This isn't about Fly.io.

A VFS that allows you to directly open a Litestream replication target (e.g. S3) as a read-only database and run queries against it without ever having to download the entire database (e.g. to an ephemeral instance that doesn't even have the disk space for it).

But aren't many single-user apps still multi-platform? For example as an Android application but also as a web app the user might access from his desktop device?

Sqlite and msaccess can and have often been used with multiple users. I have experience with the latter in the 2000s with Access on a network share.

By "replication factor of 1" you mean your data is stored on local disk only, right? That matches my understanding: Litestream replication is asynchronous, so there's usually a gap of a seconds or two between your write being accepted and the resulting updated page being pushed off to S3 or similar.

I run my entire business on a single OVH box that costs roughly $45/month. It has plenty of headroom for growth. The hardest part is getting comfortable with k8s (still worth it for a single node!) but I’ve never had more uptime and resiliency than I do now. I was spending upwards of $800/mo on AWS a few years ago with way less stability and speed. I could set up two nodes for availability, but it wouldn’t really gain me much. Downtime in my industry is expected, and my downtime is rarely related to my web services (externalities). In a worst case scenario, I could have the whole platform back up in under 6 hours on a new box. Maybe even faster.

Sure. But you'll be transferring the entire file everytime.

The advantage of sqlite3_rsync is that, if on the other end is an SSH server with sqlite3_rsync, you only transfer the changed pages, and still get a “perfect” copy of the file on the other end.

The advantage of Litestream is that on the other end does not need to live a “server,” and still only changes are uploaded. If you do it continuously, you get many points to recover from, and many of those cross reference each other, saving storage space too. On the flip side, you need the tool to restore.

Often you can use joins to get the data in a single complex SQL query. Number of comments for a post is relatively straight-forward, but you can also do increasingly complex associated data fetches with modern databases.

In particular, JSON aggregations mean you can have a single query that does things like fetch a blog entry and the earliest 10 comments in a single go. I wrote up some patterns for doing that in SQLite and PostgreSQL here: https://github.com/simonw/til/blob/main/sqlite/related-rows-...

Here's an example PostgreSQL query that does this to fetch tags for posts: https://simonwillison.net/dashboard/json-agg-example

  select
    blog_entry.id,
    title,
    slug,
    created,
    coalesce(json_agg(json_build_object(blog_tag.id, blog_tag.tag)) filter (
      where
        blog_tag.tag is not null
    ), json_build_array()) as tags
  from
    blog_entry
    left join blog_entry_tags on blog_entry.id = blog_entry_tags.entry_id
    left join blog_tag on blog_entry_tags.tag_id = blog_tag.id
  group by
    blog_entry.id
  order by
    blog_entry.id desc

The alternative, more common path is the pattern that Django calls "prefetch_related". Effectively looks like this:

  select id, title, created from posts order by created desc limit 20

  -- Now extract the id values from that and run:

  select
    blog_entry.id,
    blog_tag.tag
  from
    blog_entry
    join blog_entry_tags on blog_entry.id = blog_entry_tags.entry_id
    join blog_tag on blog_entry_tags.tag_id = blog_tag.id
  where
    blog_entry.id in (?, ?, ?, ...)

  -- Now you can re-assemble the list of tags for
  -- each entry in your application logic

Once you have a list of e.g. 20 IDs you can run a bunch of cheap additional queries to fetch extra data about all 20 of those items.

Avoiding N+1 doesn't have to mean limiting yourself to 1 query. You can still fetch the posts in one query and the comments of _all_ posts in a separate query, just don't issue a query for _each_ post.

More formally, the number of queries should be constant and not linearly scaling with the number of rows you're processing.

Unix sockets don't actually give you the same benefit. You're still doing IPC which can incur substantial memory subsystem utilization. SQLite is on the same thread/core as whatever is using it.

This plagues (plagued?) pretty much everything to do with WordPress, from core to every theme and plugin developed.

TIL. Thanks!

A proper join is the right answer. But, it's not always possible to make those run well. [1] A "client side join" in the right situation can be much better, but then you probably want to do a 1+1 rathet than N+1. Do the first query to get the ids for the second query, and then construct the second query with IN or UNION depending on what works best for you database. UNION likely bloats your query string, but I've seen plenty of situations where UNION is gobs faster than IN.

Alternately, if you can separate query issuance from result parsing, you can make N+1 palletable. Ex, do your query to get the ids, wait for the results, loop and issue the N queries, then loop and wait for results in order. That will be two-ish round trips rather than N+1 round trips. But you have to search to find database apis that allow that kind of separation.

[1] You can almost always express the query you want in SQL, but that doesn't mean it will have a reasonable runtime. Sometimes server side join and client side join have about the same runtime... if it's significant and you have the usual case where clients are easier to scale than database servers, it might be worthwhile to have the join run on the client to reduce server load.

A JOIN is fast, fetching the whole list in one extra query with "WHERE id IN (...)" is also pretty fast and results in less complex queries if you have serval of these. Doing all queries separate is slow because of the network round-trip for each query.

The performance problem in N+1 is (mostly) not in fetching the N rows from disk, but rather from multiplying the network latency by a factor of N. Joins solve this; so do stored procedures.

In general, you want to ask the remote server once for all the data you need, then read all the results. It applies to databases as well as APIs.

Pipelined requests also solve the problem and can be more flexible.

Also, joins can be optimised in different ways. Sometimes the optimal way to do a join isn't to query each row one-by-one, but to do something like (when the rows you want are a large fraction of the rows that exist) making a bloom filter of the rows you want and then sequentially reading all the rows in the table.

No, JOINs are pretty much always faster than performing N+1 queries.

You do indeed use JOINS. The goal is to retrieve exactly the data you require in a single query. Then you get the DB to `EXPLAIN VERBOSE` or similar and ensure that full table scans aren't happening and that you have indexed the columns the query is being filtered on.

Either joins for a fat query, or aggregate the subqueries.

For the latter, it's along the lines of `select * from posts where ...` and `select * from authors where id in {posts.map(author_id)}`. And then once it's in memory you manually work out the associations (or rely on your ORM to do it).

Version numbers dont mean anything as the whole Elixir ecosystem shows:D

Effort of setting up litestream and sqlite is less time than you spend signing up for supabase. And you can have 100 apps with their own databases for almost free (just a few cents of storage) vs 5*100 for postgres.

I love postgres but in no way is it as simple to run as sqlite (pretty sure even postgres core team would agree that postgres is more complex than sqlite).

i hope that came across in what i was saying too. it was intended purely in a "i am not even a fanboy but this thing they do is really cool" sense.

Oh yeah, the ORM thing (common side-effect with DB query abstractions) - I must not have been fully awake. Cheers and thank you for humoring me, @cbm-vic-20!

I dislike ORMs as much as the next ORM disliker, but people who are more comfortable in whatever the GP programming language is than SQL will write N+1 queries with or without an ORM.

> I realize you can do complicated JOINs, but isn't that almost as bad from a performance perspective?

No, JOINs should be orders of magnitude faster.

> What are you really supposed to do if you need to, e.g. fetch a list of posts along with the number of comments on each post?

You're really supposed to do a JOIN, together with a GROUP BY and a COUNT(). This is elementary SQL.

AFAIK the problem of N+1 isn't necessarily one more DB query, but one more network roundtrip. So if for each page of your app you have an API endpoint that provides exactly all of the data required for that page, it doesn't matter how many DB queries your API server makes to fulfill that request (provided that the API server and the DB are on the same machine).

This is essentially what GraphQL does instead of crafting each of these super tailored API endpoints for each of your screens, you use their query language to ask for the data you want, it queries the DB for you and get you the data back in a single network roundtrip from the user perspective.

(Not an expert, so I trust comments to correct what I got wrong)

This is the idea behind LiteFS --- to transparently scale out SQLite (in some very common configurations and workloads) to support multiple app servers. It's still there and it works! It's just a little ahead of its time. :)

99,99% of apps dont need more than one app server. You can serve a lot of traffic on the larges instances.

For sure downtime is easier with kubernete etc but again overkill for 99,99% of apps.

If v1.0 is your North Star, you should re-evaluate a whole lot of software in your stack: https://0ver.org/#notable-zerover-projects

this is essentially the "no one ever got fired for buying IBM" statement. One counter is why buy & manage a rack-mounted server when all you need is a raspberry Pi?

I'm guessing this is a joke?

Why would you argue that? Do you have some benchmarks backing it up or is it more a personal preference?

Litestream is dead simple to setup. You make an S3 bucket (or any compatible storage bucket), paste the access keys and the path to your db file in /etc/litestream, and then run

  dpkg -i litestream.deb
  systemctl enable litestream
  systemctl start litestream

The fact it's so simple is my favourite thing about it.

The rails creator dhh has been hyping it up a lot in the first 6 month of this year, and quite a few followed of the "Dev influencers" scene. Fly's litestream came out around that time, and there's been more sqlite in the cloud companies/discussions, in particular with the AI agent use-case.

Not super sure who followed who but there was all of a sudden a lot of excitement

Interesting. Any plans for a cache? Or would that just be a litestream thing

With orms, it can be easy, but also often fixed with eager fetching too.

Very true. But ORMs did make it particularly easy to trigger N+1 selects.

It used to be a very common pitfall - and often not at all obvious. You’d grab a collection of objects from the ORM, process them in a loop, and everything looked fine because the objects were already rehydrated in memory.

Then later, someone would access a property on a child object inside that loop. What looked like a simple property access would silently trigger a database query. The kicker was that this could be far removed from any obvious database access, so the person causing the issue often had no idea they were generating dozens (or hundreds) of extra queries.

I defense of the application developer, it is very difficult to adopt set theory thinking which helps with SQL when you've never had any real education in this area, and it's tough to switch between it and the loop-oriented processing you're likely using in your application code for almost everyone. ORMs bridge this divide which is why they fall in the trap consistently. Often it's an acceptable trade-off for the value you get from the abstraction, but then you pay the price when you need to address the leak!

Yep, people who think OOP is all you need will just "abstract away the database".

any notes or pointers on how to get comfortable with k8? For a simple nodejs app I was looking down the pm2 route but I wonder of learning k8 is just more future proof.

Is this vanilla k8 or any flavor?

I'd list these as the real-world advantages

  * Very flexible, but rigid deployments (can build anywhere, deploy from anywhere, and roll out deployments safely with zero downtime)
  * Images don't randomly disappear (ran into this all the time with dokku and caprover)
  * If something goes wrong, it heals itself as best it can
  * Structured observability (i.e. logs, metrics, etc. are easy to capture, unify, and ship to places)
  * Very easy to setup replicas to reduce load on services or have safe failovers 
  * Custom resource usage (I can give some pods use more/less CPU/memory limits depending on scale and priority)
  * Easy to self-host FOSS services (queues, dbs, observability, apps, etc.)
  * Total flexibility when customizing ingress/routing. I can keep private services private and only expose public services
  * Certbot can issue ssl certs instantly (always ran into issues with other self-hosting platforms)
  * Tailscale Operator makes accessing services a breeze (can opt-in services one by one)
  * Everything is yaml, so easy to manipulate
  * Adding new services is a cake-walk - as easy as creating a new yaml file, building an image and pushing it. I'm no longer disincentivized to spin up a new codebase for something small but worthwhile, because it's easy to ship it.

All-in-all I spent many years trying "lightweight" deployment solutions (dokku, elastic beanstalk, caprover, coolify, etc.) that all came with the promise of "simple" but ended up being infinitely more of a headache to manage when things went wrong. Even something like heroku falls short because it's harder to just spin up "anything" like a stateful service or random FOSS application. Dokku was probably the best, but it always felt somewhat brittle. Caprover was okay. And coolify never got off the ground for me. Don't even get me started on elastic beanstalk.

I would say the biggest downside is that managing databases is less rigid than using something like RDS, but the flip side is that my DB is far more performant and far cheaper (I own the CPU cycles! no noisy neighbors.), and I still run daily backups to external object storage.

Once you get k8s running, it kind of just works. And when I want to do something funky or experimental (like splitting AI bots to separate pods), I can go ahead and do that with ease.

I run two separate k8s "clusters" (both single node) and I kind of love it. k9s (obs. tool) is amazing. I built my own logging platform because I hated all the other ones, might release that into its own product one day (email in my profile if you're interested).

Deployments are easy. You define a bunch of yamls for what things are running, who mounts what, and what secrets they have access to etc.

If you need to deploy it elsewhere, you just install k3s/k8s or whatever and apply the yamls (except for stateful things like db).

IT also handles name resolution with service names, restarts etc.

IT's amazing.

This problem is associated with ORMs but the moment there's a get_user(id) function which does a select and you need to display a list of users someone will run it in a loop to generate the list and it will look like it's working until the user list gets long.

I really wish there was a way to compose SQL so you can actually write the dumb/obvious thing and it will run a single query. I talked with a dev once who seemed to have the beginnings of a system that could do this. It leveraged async and put composable queryish objects into a queue and kept track of what what callers needed what results, merged and executed the single query, and then returned the results. Obviously far from generalizable for arbitrary queries but it did seem to work.

Use K3s in cluster mode, start doing. Cluster mode uses etcd instead of kine, kine is not good.

Configure the init flags to disable all controllers and other doodads, deploy them yourself with Helm. Helm sucks to work with but someone has already gone through the pain for you.

AI is GREAT at K8s since K8s has GREAT docs which has been trained on.

A good mental model is good: It's an API with a bunch of control loops

I'd say rent a hetzner vps and use hetzner-k3s https://github.com/vitobotta/hetzner-k3s

Then you are off to races. you can add more nodes etc later to give it a try.

Definitely a big barrier to entry, my way was watching a friend spin up a cluster from scratch using yaml files and then copying his work. Nowadays you have claude next to you to guide you along, and you can even manage the entire cluster via claude code (risky, but not _that_ risky if you're careful). Get a VPS or dedicated box and spin up microk8s and give it a whirl! The effort you put in will pay off in the long run, in my humble opinion.

Use k9s (not a misspelling) and headlamp to observe your cluster if you need a gui.

I use microk8s

Also running a few single node clusters - perfect balance for small orgs that don't need HA. Been running small clusters since ~2016 and loving it.

I think many ORMs can solve (some of) this these days.

e.g. for ActiveRecord there's ar_lazy_preloader[0] or goldiloader[1] which fix many N+1s by keeping track of a context: you load a set of User in one go, and when you do user.posts it will do a single query for all, and when you then access post.likes it will load all likes for those and so on. Or, if you get the records some other way, you add them to a shared context and then it works.

Doesn't solve everything, but helps quite a bit.

[0] https://github.com/DmitryTsepelev/ar_lazy_preload

[1] https://github.com/salsify/goldiloader

I think that's very fair. But the use case for Litestream is much simpler and you can get your head around it immediately. It also doesn't ask you to do anything that would commit you to SQLite rather than switching to Postgres later. It's just a way of very easily getting a prod caliber backend up for an app without needing a database server.

Both are mature. There are way more sqlite databases running than postgres. The code base is smaller and has less new features added to it every year. What is unstable with sqlite?

Here's their docs on running in a Docker container: https://litestream.io/guides/docker/

You'd probably want to put the sqlite db in a volume & run litestream in a separate container that restarts automatically on failure.

Systemctl's only in there to restart it if it crashes; litestream itself is (iirc) a single cli binary.

This is documented on the Litestream website.

Maybe it's a local bump, but it sure seems like SQLite has become a fair more popular topic in the Rails world. I wouldn't expect to find it in a HN search tool. SQLite has gone from the little database you might use to boostrap or simplify local development to something products are shipping with in production. Functionality like solid_cable, solid_cache, and solid_queue allow SQLite to be used in more areas of Rails applications and is pitched as a way to simplify the stack.

While I don't have stats about every conference talk for the last decade, my experience has been that SQLite has been featured more in Rails conference talks. There's a new book titled "SQLite on Rails: The Workbook" that I don't think would have had an audience five years ago. And I've noticed more blog posts and more discussion in Rails-related discussion platforms. Moreover, I expect we'll see SQLite gain even more in popularity as it simplifies multi-agent development with multiple git worktrees.

That's like saying it gets more complicated when you have to use loops with break statements in programming. It's just what programming is.

The filtering you describe is trivial with COUNT(flag IN (...) AND state=...) etc.

If you want to retrieve data on the last comment, as opposed to an aggregate function of all comments, you can do that with window functions (or with JOIN LATERAL for something idiomatic specifically to Postgres).

Learning how to do JOIN's in SQL is like learning pointers in C -- in that it's a basic building block of the language. Learning window functions is like learning loops. These are just programming basics.

For analytics the solution is very simple. Just copy the database and run the queries on the copy.

If your goal is to serve billions of users you should probably use cassandra etc. Why limit yourself to postgres if your goal is to have a billion users online at the same time?

Litestream is just a backup solution. Should probably be compared to a backup solution for postgres that does automated backups over the network etc. That isnt part of postgres.

Besides the question wasnt litestream vs postgres backup apps. It was sqlite vs postgres.

SQLite is probably more mature than Postgres in terms of service hours.

That's not entirely true. SQLite is designed to support many processes reading the same file on disk at once. It only allows one process to write at a time, using locks - but since most writes finish in less than a ms in most cases having a process wait until another process finishes their write isn't actually a problem.

If you have lots of concurrent writes SQLite isn't the right solution. For concurrent reads it's fine.

SQLite also isn't a network database out-of-the-box. If you want to be able to access it over the network you need to solve that separately.

(Don't try and use NFS. https://sqlite.org/howtocorrupt.html#_filesystems_with_broke... )

Not true. Can you back up your claim that the developers of Sqlite says they dont recommend it for webservers? (hint they recommend it).

If you have a read-heavy app (99% of saas) that runs on one server and dont have millions of users then sqlite is a great option.

My bad, I remember vaguely that fly io had a "litefs released" post but I seem to have confused timelines

agree - with SQLite and DuckDB I've really switched my mindset from one perfect, pristine DB to multiple copies and a medallion architecture that looks more like participation ribbons for everyone! The resources required are so cheap & lightweight when you avoid the big "BI focused" tech stacks.

Because cassandra isn't easy to set up and has all kinds of tradeoffs on consistency, transactions, et al compared to an SQL db.

On the other side, why not just store everything in memory and flush to a local json file if you won't have any users? sqlite is overkill!

your goal should be to solve this problem when you have a million or 10M concurrent users. YAGNI

The original response at least concerned litestream because the not-1.0 comment only applies to that.

I didn't say that. I said one local client at a time. If you're running on one server then your webserver is the one local client.

Usually you want to be able to run multiple webservers against a single database though, since that's the first thing you'll usually need to scale.

Yes, all that’s possible. But it’s not straightforward in the majority of ORMs.

I understood the comparison to be with Litestream, not SQLite in general.

the reality is very few workloads have access patterns that SQLite can't support. I would much rather start with a strategy like 1. use sqlite for my beta / single client, 2. duplicate the entire environment for the next n clients, 3. solve the "my application is wildly successful" and SQLite is no longer appropriate problem at a future date. Spoiler: you're never going to get to step #3.

Almost sounds like ORMs are a bad idea

Right, and ORMs are the main cause of the N+1 problem, and atrocious database performance in general.

For anything that isn't just a basic row lookup from a single table, you should really just be writing the SQL yourself.

> 2. duplicate the entire environment for the next n clients

That becomes an instant problem if users ever write to your database. You can't duplicate the environment unless it's read-only.

And even if the database is read-only for users, the fact that every time you update it you need to redeploy the database to every client, is pretty annoying.

That's why it's usually better to start with Postgres or MySQL. A single source of truth for data makes everything vastly easier.

Yeah LiteFS was more recent - September 2022, https://fly.io/blog/introducing-litefs/ - and the Cloud hosted version was July 2023 https://fly.io/blog/litefs-cloud/

Why would we compare Litestream (a backup solution) with Postgres (a database)? Would be like comparing Linux with NGINX.

Oh right the cloud version is what I remember - thanks for clarifying

Bit more stretched out than I thought it had been

Postgres has a similar replication solution built-in. It's like comparing Samba with Windows Server if your purpose is to run a SMB file server.