8 ms·

> [...] sqlite is the 80% case and is also dead simple to get going and genuinely performant. I don't understand this. PostgreSQL is ALSO dead simple to get g

> [...] sqlite is the 80% case and is also dead simple to get going and genuinely performant. I don't understand this. PostgreSQL is ALSO dead simple to get going, either locally or in production. Why not just start off at 90%? I mean, I get there are a lot of use cases where sqlite is the better choice (and I've used sqlite multiple times over the years, including in my most recent gig), but why in general?

EasyMark 3y ago
it's much bigger and requires running a server, that's why I use sqlite3, and my needs are 99% modest most of the time.
- zilti 3y ago
  It requires running a separate process.
tvink 3y ago
I think "dead simple" is not doing anyone any favors when it is being used to try to equate the simplicity of things. It's obviously a lot simpler to just have a file, than to have a server that needs to be connected to, as long as we're still talking about running things on regular computers.
- Ensorceled 3y ago
  I guess that's really my point here. They difference in setup time is negligible so I'm not sure why people keep bringing it up as a reason to choose sqlite over PostgreSQL. For instance, "deployable inside a customer application" is an actual requirement that would make me loath to pick PostgreSQL. "Needs to be accessible, with redundancy, across multiple AWS zones" would make me very reluctant to pick sqlite. Neither of these decisions involve how easy it is to set up. It's like choosing between a sportbike and dump truck and focusing on how easy the sportbike is to haul around in the back of a pickup truck.
  - emseetech 3y ago
    > Needs to be accessible, with redundancy, across multiple AWS zones How many projects start with these requirements?
    - Ensorceled 3y ago
      Anything with real customer data in the cloud? Certainly you need replication.
      - emseetech 3y ago
        Litestream can handle realtime replication. But most projects don’t even have customers when they start, let alone large quantities of their data and legal requirements for guaranteed availability.
        int_19h 3y ago
        I think it's reasonable for a business, even a freshly starting one, to expect to grow to the point where it does have enough customers to outgrow SQLite fairly soon. Between that and PG offering more features in general (which often simplifies app code, because you can do more with queries), it's still not clear why not start with PG in the first place. PG, on the other hand, can scale enough to cover foreseeable future needs for most businesses, so aiming for something more complex than that is almost always premature.
        emseetech 3y ago
        > outgrow SQLite fairly soon That would be the result of either vastly overestimating their business plan or vastly underestimating SQLite.
    - rrr_oh_man 3y ago
      > How many projects start with these requirements? In a world fueled by cheap money and expensive dreams, you'd be surprised.
davidmurdoch 3y ago
But PostgreSQL is not dead simple when compared to SQLite.
benlivengood 3y ago
Practically, because sqlite is good enough for one machine and compatible-enough with postgresql that you can use either pretty easily. One thing I wrote was an exactly-once stream processor that fetched events from a lot of remote systems for processing. Transaction-based queue in the DB to achieve exactly-once with recovery (remote systems accepted time-stamp resyncing of the stream of events). It works fine at small scale on a single machine for design and testing (local integration tests with short startup time are very valuable) but trivially scales to hundreds of workers if pointed at a postgres instance. The work to allow sqlite vs postgres was a single factory that returned a DB connection in Go based on runtime configuration. It's also good practice for designing reasonably cross-database compatible schemas.
klibertp 3y ago
> PostgreSQL is ALSO dead simple to get going I'm not saying it's hard to set up Postgres locally, but sqlite is a single binary with almost no dependencies and no config, easily buildable from source for every platform you can think of. You can grab a single file from sqlite.org, and you're all set. Setting up Postgres is much more complicated in comparison (while still pretty simple in absolute terms - but starting with a relatively simpler tool doesn't seem like a bad strategy.)
- scaryclam 3y ago
  Except for when your data is in it. Migrating data on a running app is one of the worst things to deal with. I can understand using something simple and cut down for other things, but the DB is not the place I'd want to do that. Postgres isn't exactly hard to get going with, and will grow with you easily, so why trade that off for saving an hour or two at the start of the project?
  - klibertp 3y ago
    > Except for when your data is in it. Replacing the DB before it gets any actual data inserted into it solves this problem. You just switch to Postgres before you go anywhere beyond staging, at the latest - in practice, you need Postgres-exclusive functionality sooner than that in many cases, anyway. Even when that happens, you might still prefer having SQLite around as an in-memory DB in unit tests. The Postgres-specific methods are pretty rare, and you can mock them out, enjoying 100x faster setup and teardown in tests that don't need those methods (with big test suites, this quickly becomes important). Unless you really want to use Postgres for everything like the Gist here suggests, the DB is just a normal component, and some degree of flexibility in which kind of component you use for specific purposes is convenient.
randomdata 3y ago
Postgres complicates the application in several ways. In particular, Postgres suffers from the n+1 problem, while SQLite does not. That requires a significant amount of added complexity in the application to hack around. Why over engineer the application before it has proven itself as something anyone even wants to use? Let's face it, the large majority of software written gets thrown away soon after it is created. I already hear you saying that you know of a library that provides a perfect abstraction to hide all those details and complexities, making the choice between Postgres and SQLite just a flip of a switch away. Great! But then what does Postgres bring to the table for you to choose it over SQLite? If you truly prove a need for it in the future for whatever reason, all you need to do is update the configuration.
- tangjurine 3y ago
  > Postgres suffers from the n+1 problem, while SQLite does not. ?
  - deleted 3y ago
    [deleted]
  - randomdata 3y ago
    Indeed.
    - emseetech 3y ago
      It’s worth elaborating: N+1 Queries Are Not A Problem With SQLite https://www.sqlite.org/np1queryprob.html#:~:text=N%2B1%20Queries%20Are%20Not%20A%20Problem%20With%20SQLite&text=The%20SQLite%20database%20runs%20in,SQLite%20is%20not%20the%20problem https://www.sqlite.org/np1queryprob.html#:~:text=N%2B1%20Que....
  - javcasas 3y ago
    Meaning insertion sort does not suffer from the problems that made us construct quicksort because we optimized the insertion a lot (at a tradeoff that we will not specify).
runningamok 3y ago
One use case where SQLite is a good option is for embedding as a local database in an app. Starting local-only with SQLite allows you to defer a lot of the backend effort while testing an MVP.
- cpursley 3y ago
  You might find https://github.com/electric-sql/electric https://github.com/electric-sql/electric pretty cool.
mrbonner 3y ago
If dead simple involves me babysitting a service process then not it is not. SQLite has embedded version that requires no service out of process. That's what my definition of dead simple.

EasyMark 3y ago

it's much bigger and requires running a server, that's why I use sqlite3, and my needs are 99% modest most of the time.

zilti 3y ago
It requires running a separate process.

tvink 3y ago

I think "dead simple" is not doing anyone any favors when it is being used to try to equate the simplicity of things. It's obviously a lot simpler to just have a file, than to have a server that needs to be connected to, as long as we're still talking about running things on regular computers.

Ensorceled 3y ago
I guess that's really my point here. They difference in setup time is negligible so I'm not sure why people keep bringing it up as a reason to choose sqlite over PostgreSQL. For instance, "deployable inside a customer application" is an actual requirement that would make me loath to pick PostgreSQL. "Needs to be accessible, with redundancy, across multiple AWS zones" would make me very reluctant to pick sqlite. Neither of these decisions involve how easy it is to set up. It's like choosing between a sportbike and dump truck and focusing on how easy the sportbike is to haul around in the back of a pickup truck.
- emseetech 3y ago
  > Needs to be accessible, with redundancy, across multiple AWS zones How many projects start with these requirements?
  - Ensorceled 3y ago
    Anything with real customer data in the cloud? Certainly you need replication.
    - emseetech 3y ago
      Litestream can handle realtime replication. But most projects don’t even have customers when they start, let alone large quantities of their data and legal requirements for guaranteed availability.
      - int_19h 3y ago
        I think it's reasonable for a business, even a freshly starting one, to expect to grow to the point where it does have enough customers to outgrow SQLite fairly soon. Between that and PG offering more features in general (which often simplifies app code, because you can do more with queries), it's still not clear why not start with PG in the first place. PG, on the other hand, can scale enough to cover foreseeable future needs for most businesses, so aiming for something more complex than that is almost always premature.
        emseetech 3y ago
        > outgrow SQLite fairly soon That would be the result of either vastly overestimating their business plan or vastly underestimating SQLite.
  - rrr_oh_man 3y ago
    > How many projects start with these requirements? In a world fueled by cheap money and expensive dreams, you'd be surprised.

davidmurdoch 3y ago

But PostgreSQL is not dead simple when compared to SQLite.

benlivengood 3y ago

Practically, because sqlite is good enough for one machine and compatible-enough with postgresql that you can use either pretty easily. One thing I wrote was an exactly-once stream processor that fetched events from a lot of remote systems for processing. Transaction-based queue in the DB to achieve exactly-once with recovery (remote systems accepted time-stamp resyncing of the stream of events). It works fine at small scale on a single machine for design and testing (local integration tests with short startup time are very valuable) but trivially scales to hundreds of workers if pointed at a postgres instance. The work to allow sqlite vs postgres was a single factory that returned a DB connection in Go based on runtime configuration. It's also good practice for designing reasonably cross-database compatible schemas.

klibertp 3y ago

> PostgreSQL is ALSO dead simple to get going I'm not saying it's hard to set up Postgres locally, but sqlite is a single binary with almost no dependencies and no config, easily buildable from source for every platform you can think of. You can grab a single file from sqlite.org, and you're all set. Setting up Postgres is much more complicated in comparison (while still pretty simple in absolute terms - but starting with a relatively simpler tool doesn't seem like a bad strategy.)

scaryclam 3y ago
Except for when your data is in it. Migrating data on a running app is one of the worst things to deal with. I can understand using something simple and cut down for other things, but the DB is not the place I'd want to do that. Postgres isn't exactly hard to get going with, and will grow with you easily, so why trade that off for saving an hour or two at the start of the project?
- klibertp 3y ago
  > Except for when your data is in it. Replacing the DB before it gets any actual data inserted into it solves this problem. You just switch to Postgres before you go anywhere beyond staging, at the latest - in practice, you need Postgres-exclusive functionality sooner than that in many cases, anyway. Even when that happens, you might still prefer having SQLite around as an in-memory DB in unit tests. The Postgres-specific methods are pretty rare, and you can mock them out, enjoying 100x faster setup and teardown in tests that don't need those methods (with big test suites, this quickly becomes important). Unless you really want to use Postgres for everything like the Gist here suggests, the DB is just a normal component, and some degree of flexibility in which kind of component you use for specific purposes is convenient.

randomdata 3y ago

Postgres complicates the application in several ways. In particular, Postgres suffers from the n+1 problem, while SQLite does not. That requires a significant amount of added complexity in the application to hack around. Why over engineer the application before it has proven itself as something anyone even wants to use? Let's face it, the large majority of software written gets thrown away soon after it is created. I already hear you saying that you know of a library that provides a perfect abstraction to hide all those details and complexities, making the choice between Postgres and SQLite just a flip of a switch away. Great! But then what does Postgres bring to the table for you to choose it over SQLite? If you truly prove a need for it in the future for whatever reason, all you need to do is update the configuration.

tangjurine 3y ago
> Postgres suffers from the n+1 problem, while SQLite does not. ?
- deleted 3y ago
  [deleted]
- randomdata 3y ago
  Indeed.
  - emseetech 3y ago
    It’s worth elaborating: N+1 Queries Are Not A Problem With SQLite https://www.sqlite.org/np1queryprob.html#:~:text=N%2B1%20Queries%20Are%20Not%20A%20Problem%20With%20SQLite&text=The%20SQLite%20database%20runs%20in,SQLite%20is%20not%20the%20problem https://www.sqlite.org/np1queryprob.html#:~:text=N%2B1%20Que....
- javcasas 3y ago
  Meaning insertion sort does not suffer from the problems that made us construct quicksort because we optimized the insertion a lot (at a tradeoff that we will not specify).

runningamok 3y ago

One use case where SQLite is a good option is for embedding as a local database in an app. Starting local-only with SQLite allows you to defer a lot of the backend effort while testing an MVP.

cpursley 3y ago
You might find https://github.com/electric-sql/electric https://github.com/electric-sql/electric pretty cool.

mrbonner 3y ago

If dead simple involves me babysitting a service process then not it is not. SQLite has embedded version that requires no service out of process. That's what my definition of dead simple.