5 ms·

You don't need to maintain, secure and tweak postgres any more than you would with SQLite. Just install it and it'll work. Postgres backup is a single command.

You don't need to maintain, secure and tweak postgres any more than you would with SQLite. Just install it and it'll work. Postgres backup is a single command. And actually you're supposed to create sqlite backups with special command as well, if you're copying a file, you're doing it wrong. I really don't see any cons with Postgres over SQLite for server applications.

curtisblaine 2y ago
> You don't need to maintain, secure and tweak postgres any more than you would with SQLite. That's not true. Postgres is another standalone process, SQLite is a library. Even if you have your service and Postgres on the same box, you need to account for yet another process that can independently go down, that is competing for resources etc...
- jeremyjh 2y ago
  I've never had Postgres "go down". It might if you run out of disk space, but that is going to be a bad time with any database. It is not "competing for resources" when it is running the workload your app is sending it. You may as well say Sqlite is competing for resources in that case.
nh2 2y ago
> You don't need to maintain, secure Of course you need to maintain postgres. Major version upgrades are not automatic, you can't just install a newer binary/library version and start it as you can for SQLite. You need to shut down the DB and run `pg_upgrade`, or write manual full export-import scripts with `pg_dump`/`pg_dumpall`/`pg_restore`/`psql`. And good luck deciding between the different format options, as some of them are unsupported across some of these tools, some cannot export and reimport the full database cluster, there's no idempotent "just import this snapshot" operation (point-in-time restore), lack of progress reporting, etc. Here are some notes I on the topic: # Note on Postgres backups # # Unfortunately, postgres backup+restore is not straightforward. # # * Backups created with `pg_dumpall`, which create an .sql file, # cannot simply be used for point-in-time recovery. # They need to be restored with `psql` (not `pg_restore`), # which errors if the data already exists. # * You could probably tell it to ignore errors, but naturally it'll just # run through `INSERT ...`, so it's not a proper point-in-time recovery, # because it doesn't remove data newer than the backup as expected. # * To use `pg_restore` (which can ignore existing data, re-creating # everything with the `--clean` flag), you need to use `pg_dump` # (not `pg_dumpall`), which cannot backup *all* databases, # only a single given one. # * Further, `pg_restore` does not accept `--format=plain` SQL backups # (the default created by `pg_dump`). Only the non-plain backups are # accepted, which are less readable for a human to determine whether # a given backup is the one desired to restore based on the data. # # As a result, we aim for restoration using `pg_restore --clean`, # backing up only the `postgres` database using `pg_dump -d postgres`. # This works for us because we currently store all our tables in the # `postgres` database. # We use `--format=tar` because it is a plain text format, which # * deduplicates better than compressed formats, and # * allows a human to `grep` in plain text for desired contents. Why isn't there a mode with which I can just tell postgres to migrate my data automatically upon startup with a newer version? And why can't I just have postgres-as-a-library to link into my binary, like I can do with SQLite? You also can't just run postgres as root (e.g. in a container), and have to set up UNIX users to work around that, because postgres has it hardcoded to avoid running as root. This, too, you don't need to do with SQLite. Also, postgres is harder to secure. You need to either use TCP and ensure that other UNIX users on the same system can't just connect, or use UNIX Domain Sockets which have a 108 char path length restriction [1] (which is of course not documented in postgres's docs [2]), so it will suddenly break your CI when its path changes from /var/lib/jenkins/workspace/my-branch-name/postgres/sockets/.s.PGSQL.5432 to /var/lib/jenkins/workspace/my-longer-branch-name-for-additional-cool-feature-12456/postgres/sockets/.s.PGSQL.5432 And then you need to tell people to use shorter branch name "because otherwise the DB doens't work". Postgres is still my DB of choice, but it would be very misleading to say that it needs no maintenance and just works. [1]: https://serverfault.com/questions/641347/check-if-a-path-exceeds-maximum-for-unix-domain-socket https://serverfault.com/questions/641347/check-if-a-path-exc... [2]: https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-UNIX-SOCKET-DIRECTORIES https://www.postgresql.org/docs/current/runtime-config-conne...

curtisblaine 2y ago

> You don't need to maintain, secure and tweak postgres any more than you would with SQLite. That's not true. Postgres is another standalone process, SQLite is a library. Even if you have your service and Postgres on the same box, you need to account for yet another process that can independently go down, that is competing for resources etc...

jeremyjh 2y ago
I've never had Postgres "go down". It might if you run out of disk space, but that is going to be a bad time with any database. It is not "competing for resources" when it is running the workload your app is sending it. You may as well say Sqlite is competing for resources in that case.

nh2 2y ago

> You don't need to maintain, secure Of course you need to maintain postgres. Major version upgrades are not automatic, you can't just install a newer binary/library version and start it as you can for SQLite. You need to shut down the DB and run `pg_upgrade`, or write manual full export-import scripts with `pg_dump`/`pg_dumpall`/`pg_restore`/`psql`. And good luck deciding between the different format options, as some of them are unsupported across some of these tools, some cannot export and reimport the full database cluster, there's no idempotent "just import this snapshot" operation (point-in-time restore), lack of progress reporting, etc. Here are some notes I on the topic: # Note on Postgres backups # # Unfortunately, postgres backup+restore is not straightforward. # # * Backups created with `pg_dumpall`, which create an .sql file, # cannot simply be used for point-in-time recovery. # They need to be restored with `psql` (not `pg_restore`), # which errors if the data already exists. # * You could probably tell it to ignore errors, but naturally it'll just # run through `INSERT ...`, so it's not a proper point-in-time recovery, # because it doesn't remove data newer than the backup as expected. # * To use `pg_restore` (which can ignore existing data, re-creating # everything with the `--clean` flag), you need to use `pg_dump` # (not `pg_dumpall`), which cannot backup *all* databases, # only a single given one. # * Further, `pg_restore` does not accept `--format=plain` SQL backups # (the default created by `pg_dump`). Only the non-plain backups are # accepted, which are less readable for a human to determine whether # a given backup is the one desired to restore based on the data. # # As a result, we aim for restoration using `pg_restore --clean`, # backing up only the `postgres` database using `pg_dump -d postgres`. # This works for us because we currently store all our tables in the # `postgres` database. # We use `--format=tar` because it is a plain text format, which # * deduplicates better than compressed formats, and # * allows a human to `grep` in plain text for desired contents. Why isn't there a mode with which I can just tell postgres to migrate my data automatically upon startup with a newer version? And why can't I just have postgres-as-a-library to link into my binary, like I can do with SQLite? You also can't just run postgres as root (e.g. in a container), and have to set up UNIX users to work around that, because postgres has it hardcoded to avoid running as root. This, too, you don't need to do with SQLite. Also, postgres is harder to secure. You need to either use TCP and ensure that other UNIX users on the same system can't just connect, or use UNIX Domain Sockets which have a 108 char path length restriction [1] (which is of course not documented in postgres's docs [2]), so it will suddenly break your CI when its path changes from /var/lib/jenkins/workspace/my-branch-name/postgres/sockets/.s.PGSQL.5432 to /var/lib/jenkins/workspace/my-longer-branch-name-for-additional-cool-feature-12456/postgres/sockets/.s.PGSQL.5432 And then you need to tell people to use shorter branch name "because otherwise the DB doens't work". Postgres is still my DB of choice, but it would be very misleading to say that it needs no maintenance and just works. [1]: https://serverfault.com/questions/641347/check-if-a-path-exceeds-maximum-for-unix-domain-socket https://serverfault.com/questions/641347/check-if-a-path-exc... [2]: https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-UNIX-SOCKET-DIRECTORIES https://www.postgresql.org/docs/current/runtime-config-conne...