6 ms·

> The enterprise mindset dictates that you need an out-of-process database server. But the truth is, a local SQLite file communicating over the C-interface or m

> The enterprise mindset dictates that you need an out-of-process database server. But the truth is, a local SQLite file communicating over the C-interface or memory is orders of magnitude faster than making a TCP network hop to a remote Postgres server. I don't want to diss SQLite because it is awesome and more than adequate for many/most web apps but you can connect to Postgres (or any DB really) on localhost over a Unix domain socket and avoid nearly all of the overhead. It's not much harder to use than SQLite, you get all of the Postgres features, it's easier to run reports or whatever on the live db from a different box, and much easier if it comes time to setup a read replica, HA, or run the DB on a different box from the app. I don't think running Postgres on the same box as your app is the same class of optimistic over provisioning as setting up a kubernetes cluster.

jampekka 5mo ago
> It's not much harder to use than SQLite, you get all of the Postgres features, it's easier to run reports or whatever on the live db from a different box, and much easier if it comes time to setup a read replica, HA, or run the DB on a different box from the app. Isn't this idea to spend a bit more effort and overhead to get YAGNI features exactly what TFA argues against?
Jolter 5mo ago
I mean, you’re not wrong about the facts, but it’s also pretty trivial to migrate the data from SQLite into a separate Postgres server later, if it turns out you do need those features after all. But most of the time, you don’t.
- pdhborges 5mo ago
  I bet that takes more time than the 5 extra minutes you take to setup Postgres in the same box upfront.
  - SpaceNoodled 5mo ago
    To export a database? Probably even faster. And that's ignoring the difference in performance.
    - pdhborges 5mo ago
      So you are migrating from Sqlite to Postgres because you need it. What is the state of your product when you need to do this migration? Is your product non trivial? Are you now dependent on particular performance characteristics of Sqlite? Do you now need to keep your service running 24/7? Accounting for all of that takes way more than 5 minutes. The only way to beat that is if you still have a toy product and you can just export the database and import it and pray that it all works as a migration strategy.
eurleif 5mo ago
Looks like the overhead is not insignificant: Running 100,000 `SELECT 1` queries: PostgreSQL (localhost): 2.77 seconds SQLite (in-memory): 0.07 seconds (https://gist.github.com/leifkb/1ad16a741fd061216f074aedf1ecaf62 https://gist.github.com/leifkb/1ad16a741fd061216f074aedf1eca...)
- bob1029 5mo ago
  This is mostly about thread communication. With SQLite you can guarantee no context switching. Postgres running on the same box gets you close but not all the way. It's still in a different process.
  - andersmurphy 5mo ago
    This. Run an app on the same box as PG and you can easily be plagued by out of memory etc (as there's memory contention between the two processes).
- piker 5mo ago
  I love them both too but that might not be the best metric unless you’re planning to run lots of little read queries. If you’re doing CRUD, simulating that workflow may favor Postgres given the transactional read/write work that needs to take place across multiple concurrent connections.
  - locknitpicker 5mo ago
    > I love them both too but that might not be the best metric unless you’re planning to run lots of little read queries. Exactly. Back in the real world,anyone who is faced with that sort of usecase will simply add memory cache and not bother with the persistence layer.
    - piker 5mo ago
      Not sure that’s always right either though. For example Mapbox used to use an SQLite database as the disk cache for map tile info. You cannot possibly store that amount of data in memory, so it’s a great use case.
dizhn 5mo ago
Author's own 'auth' project works with sqlite and postgres.
usernametaken29 5mo ago
I have used SQLite with extensions in extreme throughput scenarios. We’re talking running through it millions of documents per second in order to do disambiguation. I won’t say this wouldn’t have been possible with a remote server, but it would have been a significant technical challenge. Instead we packed up the database on S3, and each instance got a fresh copy and hammered away at the task. SQLite is the time tested alternative for when you need performance, not features
jbverschoor 5mo ago
I've been doing that for decades.. People seem to simply not know about unix architecture. What I like about sqlite is that it's simply one file
- dxxvi 5mo ago
  But ... when you use the WAL mode, you have 3 files :-)
andersmurphy 5mo ago
Sqlite smokes postgres on the same machine even with domain sockets [1]. This is before you get into using multiple sqlite database. What features postgres offers over sqlite in the context of running on a single machine with a monolithic app? Application functions [2] means you can extend it however you need with the same language you use to build your application. It also has a much better backup and replication story thanks to litestream [3]. - [1] https://andersmurphy.com/2025/12/02/100000-tps-over-a-billion-rows-the-unreasonable-effectiveness-of-sqlite.html https://andersmurphy.com/2025/12/02/100000-tps-over-a-billio... - [2] https://sqlite.org/appfunc.html https://sqlite.org/appfunc.html - [3] https://litestream.io/ https://litestream.io/ The main problem with sqlite is the defaults are not great and you should really use it with separate read and write connections where the application manages the write queue rather than letting sqlite handle it.
- locknitpicker 5mo ago
  > Sqlite smokes postgres on the same machine even with domain sockets [1]. SQLite on the same machine is akin to calling fwrite. That's fine. This is also a system constraint as it forces a one-database-per-instance design, with no data shared across nodes. This is fine if you're putting together a site for your neighborhood's mom and pop shop, but once you need to handle a request baseline beyond a few hundreds TPS and you need to serve traffic beyond your local region then you have no alternative other than to have more than one instance of your service running in parallel. You can continue to shoehorn your one-database-per-service pattern onto the design, but you're now compelled to find "clever" strategies to sync state across nodes. Those who know better to not do "clever" simply slap a Postgres node and call it a day.
  - rpdillon 5mo ago
    I wonder what percentage of services run on the Internet exceed a few hundred transactions per second.
    - egwor 5mo ago
      I think the better question to ask is what services peak at a few hundred transactions per second?
direwolf20 5mo ago
IIRC TCP/IP through localhost actually benchmarked faster than Unix sockets because it was optimized harder. Might've been fixed now. Unix sockets gives you the advantage of authentication based on the user ID of who's connecting. My experience with sqlite for server-based apps has been that as your app grows, you almost always eventually need something bigger than sqlite and need to migrate anyway. For a server-based app, where minimizing deployment complexity isn't an extremely important concern, and with mixed reads and writes, it's rarely a bad idea to use Postgres or MariaDB from the start. Yes there are niche scenarios where sqlite on the server might be better, but they're niche.
- deleted 5mo ago
  [deleted]
weego 5mo ago
Thats just swapping another enterprise focused concern into the mix. Your database connection latency is absolutely not a concerning part of your system.
- 9rx 5mo ago
  Its not a significant concern because we've learned the hacks to work around it, but it is pretty freeing to not have to put hacks into your app.
lichenwarp 5mo ago
ORDERS OF MAGNITUDE NEWS
winrid 5mo ago
you also get a much better query execution engine, so if you need to run reports or analytics they will be faster
himata4113 5mo ago
As someone who sets up a k3s cluster for a single user project I feel called out. The thing is one you learn the technology, everything else seems more work than the "easy way".
eikenberry 5mo ago
> It's not much harder to use than SQLite, you get all of the Postgres features [..] More features is a net negative if you don't need those features. Ideally you want your DB to support exactly what you need and nothing more. Not typically realistic but the closer you can get the better.
- leptons 5mo ago
  A feature you don't think you need today, might be one you actually need tomorrow. It would be short-sighted to choose some tech based only on what you need today. If the extra features don't cost you anything, I can't see that as a "net negative".
  - eikenberry 5mo ago
    It is better to keep it simple and rework as needed than to try to anticipate everything ahead of time.
    - leptons 5mo ago
      I'm not anticipating everything ahead of time, I'm choosing mature tech that does what I need and then some. There's no misstep there. The real problem I've seen with choosing software tech is "new, shiny".
      - bigiain 5mo ago
        That's fine, so long as your aware of the costs of that decision. That choice is what tends towards a"minimum prod deployment" consisting of something like a pair of app servers behind a load balance with a pair of redundant databases behind them and usually some sort of object store as well. Assuming you engineer you app and db schemas sanely, you can reliably get four nine out of a setup like that. On AWS that looks like an aELB with two EC2 instances and a multiAZ RDS with a few S3 buckets. At on demand rates with .medium ec2 and rds instances that runs about $150/month - or maybe as low as $100 with reserved instances. You could probably deploy that for $60 or so on something like Linode or Digital ocean. That's usually fine when you're spending someone else's money, and they're fully-but-incorrectly expecting their idea to need Netflix or Facebook scale within six months. |||||If it's _my_ money, something "less scalable" that runs closer to $20/month that will easily support enough traffic to prove product market fit and generate enough sales/profit to suppot both otself and a team to rebuikd it when/if it ever needs it is a really sensibly approach.
pipeninja 5mo ago
You can't simply copy/paste a Postgres database though...also you'd be surprised how fast SQLite can be...I've used SQLite for projects where I just couldn't get the performance elsewhere. For example, I had a names database with over 100 million rows in it for converting names to diminutives (e.g. David to Dave) and the inverse...after I precomputed a metric ton of indices it went like a rocket. Sure the file was quite big but oh boy was it quick.
dzonga 5mo ago
mysql is really easy to run too. Postgres too (though it might come with a few operational headaches) all these things are awesome. however the complex over-engineering comes from people putting a k8 cluster or some cloud services when self hosting will do.
rtalk 5mo ago
At the end of the day, revenue matter, whatever techstack is 't it
deleted 5mo ago
[deleted]

jampekka 5mo ago

> It's not much harder to use than SQLite, you get all of the Postgres features, it's easier to run reports or whatever on the live db from a different box, and much easier if it comes time to setup a read replica, HA, or run the DB on a different box from the app. Isn't this idea to spend a bit more effort and overhead to get YAGNI features exactly what TFA argues against?

Jolter 5mo ago

I mean, you’re not wrong about the facts, but it’s also pretty trivial to migrate the data from SQLite into a separate Postgres server later, if it turns out you do need those features after all. But most of the time, you don’t.

pdhborges 5mo ago
I bet that takes more time than the 5 extra minutes you take to setup Postgres in the same box upfront.
- SpaceNoodled 5mo ago
  To export a database? Probably even faster. And that's ignoring the difference in performance.
  - pdhborges 5mo ago
    So you are migrating from Sqlite to Postgres because you need it. What is the state of your product when you need to do this migration? Is your product non trivial? Are you now dependent on particular performance characteristics of Sqlite? Do you now need to keep your service running 24/7? Accounting for all of that takes way more than 5 minutes. The only way to beat that is if you still have a toy product and you can just export the database and import it and pray that it all works as a migration strategy.

eurleif 5mo ago

Looks like the overhead is not insignificant: Running 100,000 `SELECT 1` queries: PostgreSQL (localhost): 2.77 seconds SQLite (in-memory): 0.07 seconds (https://gist.github.com/leifkb/1ad16a741fd061216f074aedf1ecaf62 https://gist.github.com/leifkb/1ad16a741fd061216f074aedf1eca...)

bob1029 5mo ago
This is mostly about thread communication. With SQLite you can guarantee no context switching. Postgres running on the same box gets you close but not all the way. It's still in a different process.
- andersmurphy 5mo ago
  This. Run an app on the same box as PG and you can easily be plagued by out of memory etc (as there's memory contention between the two processes).
piker 5mo ago
I love them both too but that might not be the best metric unless you’re planning to run lots of little read queries. If you’re doing CRUD, simulating that workflow may favor Postgres given the transactional read/write work that needs to take place across multiple concurrent connections.
- locknitpicker 5mo ago
  > I love them both too but that might not be the best metric unless you’re planning to run lots of little read queries. Exactly. Back in the real world,anyone who is faced with that sort of usecase will simply add memory cache and not bother with the persistence layer.
  - piker 5mo ago
    Not sure that’s always right either though. For example Mapbox used to use an SQLite database as the disk cache for map tile info. You cannot possibly store that amount of data in memory, so it’s a great use case.

dizhn 5mo ago

Author's own 'auth' project works with sqlite and postgres.

usernametaken29 5mo ago

I have used SQLite with extensions in extreme throughput scenarios. We’re talking running through it millions of documents per second in order to do disambiguation. I won’t say this wouldn’t have been possible with a remote server, but it would have been a significant technical challenge. Instead we packed up the database on S3, and each instance got a fresh copy and hammered away at the task. SQLite is the time tested alternative for when you need performance, not features

jbverschoor 5mo ago

I've been doing that for decades.. People seem to simply not know about unix architecture. What I like about sqlite is that it's simply one file

dxxvi 5mo ago
But ... when you use the WAL mode, you have 3 files :-)

andersmurphy 5mo ago

Sqlite smokes postgres on the same machine even with domain sockets [1]. This is before you get into using multiple sqlite database. What features postgres offers over sqlite in the context of running on a single machine with a monolithic app? Application functions [2] means you can extend it however you need with the same language you use to build your application. It also has a much better backup and replication story thanks to litestream [3]. - [1] https://andersmurphy.com/2025/12/02/100000-tps-over-a-billion-rows-the-unreasonable-effectiveness-of-sqlite.html https://andersmurphy.com/2025/12/02/100000-tps-over-a-billio... - [2] https://sqlite.org/appfunc.html https://sqlite.org/appfunc.html - [3] https://litestream.io/ https://litestream.io/ The main problem with sqlite is the defaults are not great and you should really use it with separate read and write connections where the application manages the write queue rather than letting sqlite handle it.

locknitpicker 5mo ago
> Sqlite smokes postgres on the same machine even with domain sockets [1]. SQLite on the same machine is akin to calling fwrite. That's fine. This is also a system constraint as it forces a one-database-per-instance design, with no data shared across nodes. This is fine if you're putting together a site for your neighborhood's mom and pop shop, but once you need to handle a request baseline beyond a few hundreds TPS and you need to serve traffic beyond your local region then you have no alternative other than to have more than one instance of your service running in parallel. You can continue to shoehorn your one-database-per-service pattern onto the design, but you're now compelled to find "clever" strategies to sync state across nodes. Those who know better to not do "clever" simply slap a Postgres node and call it a day.
- rpdillon 5mo ago
  I wonder what percentage of services run on the Internet exceed a few hundred transactions per second.
  - egwor 5mo ago
    I think the better question to ask is what services peak at a few hundred transactions per second?

direwolf20 5mo ago

IIRC TCP/IP through localhost actually benchmarked faster than Unix sockets because it was optimized harder. Might've been fixed now. Unix sockets gives you the advantage of authentication based on the user ID of who's connecting. My experience with sqlite for server-based apps has been that as your app grows, you almost always eventually need something bigger than sqlite and need to migrate anyway. For a server-based app, where minimizing deployment complexity isn't an extremely important concern, and with mixed reads and writes, it's rarely a bad idea to use Postgres or MariaDB from the start. Yes there are niche scenarios where sqlite on the server might be better, but they're niche.

deleted 5mo ago
[deleted]

weego 5mo ago

Thats just swapping another enterprise focused concern into the mix. Your database connection latency is absolutely not a concerning part of your system.

9rx 5mo ago
Its not a significant concern because we've learned the hacks to work around it, but it is pretty freeing to not have to put hacks into your app.

lichenwarp 5mo ago

ORDERS OF MAGNITUDE NEWS

winrid 5mo ago

you also get a much better query execution engine, so if you need to run reports or analytics they will be faster

himata4113 5mo ago

As someone who sets up a k3s cluster for a single user project I feel called out. The thing is one you learn the technology, everything else seems more work than the "easy way".

eikenberry 5mo ago

> It's not much harder to use than SQLite, you get all of the Postgres features [..] More features is a net negative if you don't need those features. Ideally you want your DB to support exactly what you need and nothing more. Not typically realistic but the closer you can get the better.

leptons 5mo ago
A feature you don't think you need today, might be one you actually need tomorrow. It would be short-sighted to choose some tech based only on what you need today. If the extra features don't cost you anything, I can't see that as a "net negative".
- eikenberry 5mo ago
  It is better to keep it simple and rework as needed than to try to anticipate everything ahead of time.
  - leptons 5mo ago
    I'm not anticipating everything ahead of time, I'm choosing mature tech that does what I need and then some. There's no misstep there. The real problem I've seen with choosing software tech is "new, shiny".
    - bigiain 5mo ago
      That's fine, so long as your aware of the costs of that decision. That choice is what tends towards a"minimum prod deployment" consisting of something like a pair of app servers behind a load balance with a pair of redundant databases behind them and usually some sort of object store as well. Assuming you engineer you app and db schemas sanely, you can reliably get four nine out of a setup like that. On AWS that looks like an aELB with two EC2 instances and a multiAZ RDS with a few S3 buckets. At on demand rates with .medium ec2 and rds instances that runs about $150/month - or maybe as low as $100 with reserved instances. You could probably deploy that for $60 or so on something like Linode or Digital ocean. That's usually fine when you're spending someone else's money, and they're fully-but-incorrectly expecting their idea to need Netflix or Facebook scale within six months. |||||If it's _my_ money, something "less scalable" that runs closer to $20/month that will easily support enough traffic to prove product market fit and generate enough sales/profit to suppot both otself and a team to rebuikd it when/if it ever needs it is a really sensibly approach.

pipeninja 5mo ago

You can't simply copy/paste a Postgres database though...also you'd be surprised how fast SQLite can be...I've used SQLite for projects where I just couldn't get the performance elsewhere. For example, I had a names database with over 100 million rows in it for converting names to diminutives (e.g. David to Dave) and the inverse...after I precomputed a metric ton of indices it went like a rocket. Sure the file was quite big but oh boy was it quick.

dzonga 5mo ago

mysql is really easy to run too. Postgres too (though it might come with a few operational headaches) all these things are awesome. however the complex over-engineering comes from people putting a k8 cluster or some cloud services when self hosting will do.

rtalk 5mo ago

At the end of the day, revenue matter, whatever techstack is 't it

deleted 5mo ago

[deleted]