4 ms·

When people say "Just use SQLite. It's almost as good as Postgres and you won't need anything more" I'm trying to understand why I shouldn't just use Postgres.

by Kiro 2y ago

When people say "Just use SQLite. It's almost as good as Postgres and you won't need anything more" I'm trying to understand why I shouldn't just use Postgres. It's not like it's hard to install or has any significant overhead. Please enlighten me.

masklinn 2y ago
> It's not like it's hard to install or has any significant overhead. Depends on the environment or lack thereof, postgres is a pain in the ass on windows, and then you need support for software configuration so that it can talk to postgres, and then you have to take care of the features you're using. If you're deploying a complex server-side system with lots of moving parts, then yes postgres is basically free. But if you're deploying client-side, or want to run it in a VPS, or whatever, postgres might go from not available to extra cost to a huge chore. > Just use SQLite. It's almost as good as Postgres and you won't need anything more Can't say I agree with that sentiment in any way though, every time I use it sqlite frustrates me in its limitations compared to postgres, and how weak the defaults are from a safety and consistency perspective.
- CuriouslyC 2y ago
  > Depends on the environment or lack thereof, postgres is a pain in the ass on windows, and then you need support for software configuration so that it can talk to postgres, and then you have to take care of the features you're using. This is why everyone uses docker and .env files. The problem has already been solved and you can copy/paste starter files from project to project to make it a non issue.
  - 0dayz 2y ago
    Well running dbs in containers are generally not a good idea.
    - CuriouslyC 2y ago
      Works fine to enable developers, and most people use cloud database offerings like RDS in production.
ptrwis 2y ago
SQLite for embedded or desktop apps.
out_of_protocol 2y ago
Go with Postgres. SQLite is somewhat barebones, you are getting like 5 datatypes grand total with it (counting null as separate type), 1 type of indexes, somewhat unexplored tooling around it. Also column type is not strict and you can write strings into integer column. With Postgres you are getting very established and rich ecosystem, with various and VERY optimized data types, indexes, extensions (like postgis), tooling around it, lots of tutorials how to fix it if something goes wrong. P.S. but if your project don't need any of that, e.g. it's desktop audio player, just embed sqlite - remove another subsystem to care about
- aldonius 2y ago
  > Also column type is not strict and you can write strings into integer column. FYI, as of late 2021, you can opt into strictness. https://sqlite.org/stricttables.html https://sqlite.org/stricttables.html
jeroenhd 2y ago
Postgres isn't hard to use, but it requires maintenance. You need more scripts, more tooling, more knowledge of DBA, and that may not be necessary. When you're using a database to store a few thousand rows of data, Postgres quickly becomes overkill. Postgres is a V8 engine when all you may need to power is a lawn mower. I personally prefer to use abstractions like ORMs for most of my database interactions, and direct SQL when those abstractions get in the way (by generating expensive queries and not finding an easy fix without a large refactor). This way, starting out with sqlite (good enough for most websites I reckon, easy to backup) doesn't interfere with any necessary migration to postgres (like when the need for scaling arises). This also makes setting up tests easier (except for the manually written SQL) because starting an application with a temporary in-memory database is a lot faster than starting a full container. Unless I'm doing native apps, I'll probably always want to reserve the ability to use Postgres. Sometimes that means hooking up a Postgres account and such, but often that just means sticking with sqlite and leaving my options open for when sqlite doesn't work anymore.
- wonrax 2y ago
  > Postgres isn't hard to use, but it requires maintenance. You need more scripts, more tooling, more knowledge of DBA, and that may not be necessary. I don't think Postgres needs to be maintained at all for small databases, which is usually the use case for SQLite. Their default configurations would take care of most things for trivial applications. > Starting an application with a temporary in-memory database is a lot faster than starting a full container. Starting a container might be way slower than SQLite, but I would still consider it fast for most, if not all use cases. > hooking up a Postgres account You can configure Postgres to start up in trust mode, which doesn't require a password for any user. This is basically the same as the unencrypted SQLite database file but with a fixed connection string: `postgresql://postgres@localhost`
jononor 2y ago
I would use SQLite if the embeddable part makes things considerably easier. For example, in a desktop or mobile application where a single application process is easier, because it fits the deployment model better.
fulafel 2y ago
Using a networked service makes your app a distributed system with all the associated hard things.

by Kiro 2y ago

masklinn 2y ago

> It's not like it's hard to install or has any significant overhead. Depends on the environment or lack thereof, postgres is a pain in the ass on windows, and then you need support for software configuration so that it can talk to postgres, and then you have to take care of the features you're using. If you're deploying a complex server-side system with lots of moving parts, then yes postgres is basically free. But if you're deploying client-side, or want to run it in a VPS, or whatever, postgres might go from not available to extra cost to a huge chore. > Just use SQLite. It's almost as good as Postgres and you won't need anything more Can't say I agree with that sentiment in any way though, every time I use it sqlite frustrates me in its limitations compared to postgres, and how weak the defaults are from a safety and consistency perspective.

CuriouslyC 2y ago
> Depends on the environment or lack thereof, postgres is a pain in the ass on windows, and then you need support for software configuration so that it can talk to postgres, and then you have to take care of the features you're using. This is why everyone uses docker and .env files. The problem has already been solved and you can copy/paste starter files from project to project to make it a non issue.
- 0dayz 2y ago
  Well running dbs in containers are generally not a good idea.
  - CuriouslyC 2y ago
    Works fine to enable developers, and most people use cloud database offerings like RDS in production.

ptrwis 2y ago

SQLite for embedded or desktop apps.

out_of_protocol 2y ago

Go with Postgres. SQLite is somewhat barebones, you are getting like 5 datatypes grand total with it (counting null as separate type), 1 type of indexes, somewhat unexplored tooling around it. Also column type is not strict and you can write strings into integer column. With Postgres you are getting very established and rich ecosystem, with various and VERY optimized data types, indexes, extensions (like postgis), tooling around it, lots of tutorials how to fix it if something goes wrong. P.S. but if your project don't need any of that, e.g. it's desktop audio player, just embed sqlite - remove another subsystem to care about

aldonius 2y ago
> Also column type is not strict and you can write strings into integer column. FYI, as of late 2021, you can opt into strictness. https://sqlite.org/stricttables.html https://sqlite.org/stricttables.html

jeroenhd 2y ago

Postgres isn't hard to use, but it requires maintenance. You need more scripts, more tooling, more knowledge of DBA, and that may not be necessary. When you're using a database to store a few thousand rows of data, Postgres quickly becomes overkill. Postgres is a V8 engine when all you may need to power is a lawn mower. I personally prefer to use abstractions like ORMs for most of my database interactions, and direct SQL when those abstractions get in the way (by generating expensive queries and not finding an easy fix without a large refactor). This way, starting out with sqlite (good enough for most websites I reckon, easy to backup) doesn't interfere with any necessary migration to postgres (like when the need for scaling arises). This also makes setting up tests easier (except for the manually written SQL) because starting an application with a temporary in-memory database is a lot faster than starting a full container. Unless I'm doing native apps, I'll probably always want to reserve the ability to use Postgres. Sometimes that means hooking up a Postgres account and such, but often that just means sticking with sqlite and leaving my options open for when sqlite doesn't work anymore.

wonrax 2y ago
> Postgres isn't hard to use, but it requires maintenance. You need more scripts, more tooling, more knowledge of DBA, and that may not be necessary. I don't think Postgres needs to be maintained at all for small databases, which is usually the use case for SQLite. Their default configurations would take care of most things for trivial applications. > Starting an application with a temporary in-memory database is a lot faster than starting a full container. Starting a container might be way slower than SQLite, but I would still consider it fast for most, if not all use cases. > hooking up a Postgres account You can configure Postgres to start up in trust mode, which doesn't require a password for any user. This is basically the same as the unencrypted SQLite database file but with a fixed connection string: `postgresql://postgres@localhost`

jononor 2y ago

I would use SQLite if the embeddable part makes things considerably easier. For example, in a desktop or mobile application where a single application process is easier, because it fits the deployment model better.

fulafel 2y ago

Using a networked service makes your app a distributed system with all the associated hard things.