4 ms·

Postgres doesn't require a _dedicated_ server. If your requirements allow for SQLite but it didn't exist, you could conceivably run postgres on your app server

by jayd16 4y ago

Postgres doesn't require a _dedicated_ server. If your requirements allow for SQLite but it didn't exist, you could conceivably run postgres on your app server to reduce the ops load if you really felt it was a significant effort. If you might want to use multiple app instances connected to a shared DB, I would say it's probably easier to just use a local postgres container for dev and a managed cloud DB. Really not that much effort and you get automatic backups. If you plan to never use a shared DB, SQLite is great though.

mikepurvis 4y ago
"never use a shared DB" I think this is what it comes down to for me too. Yes there might be some use cases that really benefit from Postgres features like GIS and the native JSON type, but ultimately, for most use-cases, it's going to hinge on whether or not you ever expect to need to scale any part of the system horizontally, whether that's the frontends or the DB itself.
- msbarnett 4y ago
  > Yes there might be some use cases that really benefit from Postgres features like GIS and the native JSON type Or a proper UUID type, materialized views, enforced data-constraints, lateral joins, queries that consider multiple indexes, etc, etc. It's not like there's just a couple of niches where PostgreSQL's features distinguish it from SQLite. SQLite is appropriate for very simple DB use-cases where you aren't too bothered by silent data inconsistency/corruption, but not a whole lot beyond that. It's a good little piece of software, but it hardly relegates PostgreSQL to "Just GIS things" or "Just JSON use-cases".
  - ElectricalUnion 4y ago
    > SQLite is appropriate for very simple DB use-cases where you aren't too bothered by silent data inconsistency/corruption, but not a whole lot beyond that. SQLite is also appropriate for use cases where you are bothered by silent data inconsistency/corruption, it's just that often the hardware running PostgreSQL (or any "serious" DBMS, relational or not) is usually less prone to random inconsistency/corruption (non-interruptible redundant power sources, well cooled environment, running lower but more stable clock speeds on everything, ECC RAM, raid disk array, corruption-resistant FS). If you run PostgreSQL on the cheapest consumer hardware, expect random corruption of the database when power runs out several times in a row.
    - msbarnett 4y ago
      Sorry, "corruption" was probably misleading there. By "silent data inconsistency/corruption" I mean, "a bug in my application led to me storing strings in an integer column, or too-long data in a too-short column, and SQLite just silently let me do that"-type errors – the data consistency is logically (but not physically) corrupted. The author encountered 2 separate occurrences of this just in their simple use-case, evidently.
      - 323 4y ago
        > led to me storing strings in an integer column Recent SQLite versions have STRICT mode where they forbid that. https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html > or too-long data in a too-short column You can use a constraint for that: CREATE TABLE test ( name TEXT NOT NULL CHECK(length(name) <= 20) ) STRICT;
        mikepurvis 4y ago
        Neat, will definitely give this a try. Definitely one of my minor peeves about SQLite is having to set a bunch of pragmas on every new connection. Like, especially stuff such as foreign_keys=1... really, enforcement of foreign key constraints is something each connection has to opt into rather than being set one on the whole DB file and never thought about again? Blah.
- twhitmore 4y ago
  Well, unless you care about disaster recovery or availability. For most business applications used by more than one user there are usually some expectations that data will be retained and the application will be available.
robonerd 4y ago
I read that as "dedicated server[process]" not "dedicated server[hardware]" While the latter would obviously imply the former, it's really the former that increases the complexity of managing whole affair. Desktop applications with a dependency on mysql/postgres running on the same machine are, in my experience without exception, a pain in the ass for users.
- ed25519FUUU 4y ago
  Managing a publicly accessible DB and user certs to connect to it securely is like someone’s full time job at some companies.
  - dx034 4y ago
    Why should the database ever be publicly accessible? I've only ever seen databases being accessible by applications and maybe users (but those only for analytics databases), and have never experienced anyone working full time on certs and user access for a single database. There's usually a team for that, but they do that for the whole server fleet.
riku_iki 4y ago
> you could conceivably run postgres on your app server to reduce the ops load if you really felt it was a significant effort. Still, PGSQL requires some level of setup/maintenance: create user, permissions, database, service with config/security effort. If there is a way to run PGSQL as lib and say to it: just store your data in this dir without other steps, I am very interested to learn.
- urda 4y ago
  You are likely wanting something like RDS [1], Azure [2], or even Linode (which I just discovered) [3]. Sure you have to pay a larger premium for it, but these products do allow you to focus on the app than the DBA stuff. - [1] https://aws.amazon.com/rds/ https://aws.amazon.com/rds/ - [2] https://azure.microsoft.com/en-us/services/postgresql/#overview https://azure.microsoft.com/en-us/services/postgresql/#overv... - [3] https://www.linode.com/products/postgresql/ https://www.linode.com/products/postgresql/
  - riku_iki 4y ago
    > pay a larger premium for it yeah, premium is very large if I want to do a lot of data crunching (lots of iops, cores and ram is needed), plus latency/throughput to those services will be much higher than to my local disk through PCIE bus.
- eropple 4y ago
  You can set up Postgres with three lines of bash and a Unix socket. It is more work; I wouldn't say it's appreciably more work, and if you need more rigorous data modeling (I frequently do) then it's worth considering if this is the mode in which you'd like to run your data stores (I wouldn't, but latency isn't the most critical thing for stuff I do).
  - riku_iki 4y ago
    I think it is more than that. Steps will be: - sudo apt install postgres (more steps are needed if you are not satisfied with OS default version) - sudo su postgres - psql -> create db, user, grant permissions - modify hba.conf file to change connection permissions - modify postgresql.conf to change settings to your liking because defaults are very out of touch with modern hardware Another options would be to build your docker image, but I am not good with this.

by jayd16 4y ago

mikepurvis 4y ago

"never use a shared DB" I think this is what it comes down to for me too. Yes there might be some use cases that really benefit from Postgres features like GIS and the native JSON type, but ultimately, for most use-cases, it's going to hinge on whether or not you ever expect to need to scale any part of the system horizontally, whether that's the frontends or the DB itself.

msbarnett 4y ago
> Yes there might be some use cases that really benefit from Postgres features like GIS and the native JSON type Or a proper UUID type, materialized views, enforced data-constraints, lateral joins, queries that consider multiple indexes, etc, etc. It's not like there's just a couple of niches where PostgreSQL's features distinguish it from SQLite. SQLite is appropriate for very simple DB use-cases where you aren't too bothered by silent data inconsistency/corruption, but not a whole lot beyond that. It's a good little piece of software, but it hardly relegates PostgreSQL to "Just GIS things" or "Just JSON use-cases".
- ElectricalUnion 4y ago
  > SQLite is appropriate for very simple DB use-cases where you aren't too bothered by silent data inconsistency/corruption, but not a whole lot beyond that. SQLite is also appropriate for use cases where you are bothered by silent data inconsistency/corruption, it's just that often the hardware running PostgreSQL (or any "serious" DBMS, relational or not) is usually less prone to random inconsistency/corruption (non-interruptible redundant power sources, well cooled environment, running lower but more stable clock speeds on everything, ECC RAM, raid disk array, corruption-resistant FS). If you run PostgreSQL on the cheapest consumer hardware, expect random corruption of the database when power runs out several times in a row.
  - msbarnett 4y ago
    Sorry, "corruption" was probably misleading there. By "silent data inconsistency/corruption" I mean, "a bug in my application led to me storing strings in an integer column, or too-long data in a too-short column, and SQLite just silently let me do that"-type errors – the data consistency is logically (but not physically) corrupted. The author encountered 2 separate occurrences of this just in their simple use-case, evidently.
    - 323 4y ago
      > led to me storing strings in an integer column Recent SQLite versions have STRICT mode where they forbid that. https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html > or too-long data in a too-short column You can use a constraint for that: CREATE TABLE test ( name TEXT NOT NULL CHECK(length(name) <= 20) ) STRICT;
      - mikepurvis 4y ago
        Neat, will definitely give this a try. Definitely one of my minor peeves about SQLite is having to set a bunch of pragmas on every new connection. Like, especially stuff such as foreign_keys=1... really, enforcement of foreign key constraints is something each connection has to opt into rather than being set one on the whole DB file and never thought about again? Blah.
twhitmore 4y ago
Well, unless you care about disaster recovery or availability. For most business applications used by more than one user there are usually some expectations that data will be retained and the application will be available.

robonerd 4y ago

I read that as "dedicated server[process]" not "dedicated server[hardware]" While the latter would obviously imply the former, it's really the former that increases the complexity of managing whole affair. Desktop applications with a dependency on mysql/postgres running on the same machine are, in my experience without exception, a pain in the ass for users.

ed25519FUUU 4y ago
Managing a publicly accessible DB and user certs to connect to it securely is like someone’s full time job at some companies.
- dx034 4y ago
  Why should the database ever be publicly accessible? I've only ever seen databases being accessible by applications and maybe users (but those only for analytics databases), and have never experienced anyone working full time on certs and user access for a single database. There's usually a team for that, but they do that for the whole server fleet.

riku_iki 4y ago

> you could conceivably run postgres on your app server to reduce the ops load if you really felt it was a significant effort. Still, PGSQL requires some level of setup/maintenance: create user, permissions, database, service with config/security effort. If there is a way to run PGSQL as lib and say to it: just store your data in this dir without other steps, I am very interested to learn.

urda 4y ago
You are likely wanting something like RDS [1], Azure [2], or even Linode (which I just discovered) [3]. Sure you have to pay a larger premium for it, but these products do allow you to focus on the app than the DBA stuff. - [1] https://aws.amazon.com/rds/ https://aws.amazon.com/rds/ - [2] https://azure.microsoft.com/en-us/services/postgresql/#overview https://azure.microsoft.com/en-us/services/postgresql/#overv... - [3] https://www.linode.com/products/postgresql/ https://www.linode.com/products/postgresql/
- riku_iki 4y ago
  > pay a larger premium for it yeah, premium is very large if I want to do a lot of data crunching (lots of iops, cores and ram is needed), plus latency/throughput to those services will be much higher than to my local disk through PCIE bus.
eropple 4y ago
You can set up Postgres with three lines of bash and a Unix socket. It is more work; I wouldn't say it's appreciably more work, and if you need more rigorous data modeling (I frequently do) then it's worth considering if this is the mode in which you'd like to run your data stores (I wouldn't, but latency isn't the most critical thing for stuff I do).
- riku_iki 4y ago
  I think it is more than that. Steps will be: - sudo apt install postgres (more steps are needed if you are not satisfied with OS default version) - sudo su postgres - psql -> create db, user, grant permissions - modify hba.conf file to change connection permissions - modify postgresql.conf to change settings to your liking because defaults are very out of touch with modern hardware Another options would be to build your docker image, but I am not good with this.