4 ms·

>also that since Postgresql 15, you can use `NULLS NOT DISTINCT` when creating a unique index [0]. I'm less familiar with other databases. Why would anyone wan

>also that since Postgresql 15, you can use `NULLS NOT DISTINCT` when creating a unique index [0]. I'm less familiar with other databases. Why would anyone want to use another database?

stronglikedan 2y ago
Simplicity. PG is often overkill for simple apps, where MySQL/Maria/et al is easier to maintain, or even SQLite for very simple apps where zero DB maintenance is preferable.
- lcnPylGDnU4H9OF 2y ago
  Why would you say MySQL/Maria/et al are easier to maintain for simple apps than PG?
  - demurgos 2y ago
    The main pain point for smaller apps is that every major Postgres version requires an explicit migration of the underlying data representation. I get why it's there, but for simpler apps I would appreciate a flag to do it transparently.
    - throwaway10235 2y ago
      I'm not sure what you mean. I have migrated versions without having to update any applications that connects to it? Maybe it is a driver specific issue? I have used Python/Java, and haven't updated any of my code or dependencies because of a major Postgre update
      - homebrewer 2y ago
        It requires manual interventions because the upgrade process is basically dump + restore. MySQL and MariaDB upgrade between major versions automatically — you simply install the next version (or change the version tag in your container definition) and restart the server. Usually it takes almost no time, altought might be just as slow as PG when major changes to the data format are introduced. The only example I can remember is 8.0 when oracle completely rewrote the data format (making things like atomic ddl possible).
      - panzi 2y ago
        You need to dump the database on the old PostgreSQL version and then load the dump on the new PostgreSQL version. Some other database servers can just use the old table data or migrate the data files on the fly transparently.
      - Volundr 2y ago
        It's not client side, it's server side. The binary format between Postgres major versions is generally not compatible so you need to do a pg_dump from the old database and do a pg_restore into the new one. pg_upgrade [1] can let you bypass this by migrating the binary data directly, but still requires having both the new and old postgres versions installed. There's also things you can do with replication, but since we're talking simplicity I don't think that really applies. Personally I think upgrades are the one thing MySQL has on Postgres at this point. [1] https://www.postgresql.org/docs/current/pgupgrade.html https://www.postgresql.org/docs/current/pgupgrade.html
      - marcosdumay 2y ago
        The GP is complaining about the need to run the upgrade cluster command, and reconfigure your server when you want to use a different version of postgres itself. MySql does it transparently if you just open the database in the new server. Of course, lots of people also think it's a feature. But those aren't very vocal. Anyway, this is a main reason why people keep using old postgres versions, they have to think about upgrading, and they often don't.
    - wruza 2y ago
      Do you even have to upgrade to the latest major version? What does that do, on average?
  - phplovesong 2y ago
    Usually its a skill issue
  - badlibrarian 2y ago
    VACUUM
    - kstrauser 2y ago
      If your data's large and changing enough that you have to care about vacuuming, any reasonable database is going to require some tuning, tending and management. I'd posit that only a tiny fraction of PostgreSQL uses have to know or care that vacuuming is a thing because the autovacuum default handle it for them.
      - mardifoufs 2y ago
        Sure, it's never going to be plug and play, but it doesn't mean that all the issues will be equivalent. Vacuuming doesn't really have an equivalent in say, MySQL. It's something you don't have to worry about if you use the latter. For example, HA and clustering will always be challenging to deploy/maintain, but you will still have a harder time doing that with postgres than with MySQL. Postgres also has a lot of benefits obviously, though.
  - deleted 2y ago
    [deleted]
- homebrewer 2y ago
  Also the reverse — MySQL et al support much more complex replication topologies out of the box, including multi master for the very rare use case when you need it. It's also much easier to tune, most database instances require setting innodb_buffer_pool_size, and that's basically it. Newer versions can even set it automatically if you're fine with consuming all memory on that machine, thus requiring no tuning at all.
password4321 2y ago
Replication
poincaredisk 2y ago
* legacy applications or vendor lock-in * use of a cloud provider that favours another database (like SQL server) * some people claim mysql/maria is faster for them, simpler, or has a better support for replication * use of sqlite for lightweight or portable apps * domain specific databases that still use sql as their query language * someone may want to use another database for fun or to learn something new
hot_gril 2y ago
SQLite has its own use cases. And MySQL was all-around better than Postgres in the past, so it still has more usage in some areas. Nowadays I'll take Postgres over MySQL by default, but it's not a huge difference.

>also that since Postgresql 15, you can use `NULLS NOT DISTINCT` when creating a unique index [0]. I'm less familiar with other databases. Why would anyone want to use another database?

stronglikedan 2y ago

Simplicity. PG is often overkill for simple apps, where MySQL/Maria/et al is easier to maintain, or even SQLite for very simple apps where zero DB maintenance is preferable.

lcnPylGDnU4H9OF 2y ago
Why would you say MySQL/Maria/et al are easier to maintain for simple apps than PG?
- demurgos 2y ago
  The main pain point for smaller apps is that every major Postgres version requires an explicit migration of the underlying data representation. I get why it's there, but for simpler apps I would appreciate a flag to do it transparently.
  - throwaway10235 2y ago
    I'm not sure what you mean. I have migrated versions without having to update any applications that connects to it? Maybe it is a driver specific issue? I have used Python/Java, and haven't updated any of my code or dependencies because of a major Postgre update
    - homebrewer 2y ago
      It requires manual interventions because the upgrade process is basically dump + restore. MySQL and MariaDB upgrade between major versions automatically — you simply install the next version (or change the version tag in your container definition) and restart the server. Usually it takes almost no time, altought might be just as slow as PG when major changes to the data format are introduced. The only example I can remember is 8.0 when oracle completely rewrote the data format (making things like atomic ddl possible).
    - panzi 2y ago
      You need to dump the database on the old PostgreSQL version and then load the dump on the new PostgreSQL version. Some other database servers can just use the old table data or migrate the data files on the fly transparently.
    - Volundr 2y ago
      It's not client side, it's server side. The binary format between Postgres major versions is generally not compatible so you need to do a pg_dump from the old database and do a pg_restore into the new one. pg_upgrade [1] can let you bypass this by migrating the binary data directly, but still requires having both the new and old postgres versions installed. There's also things you can do with replication, but since we're talking simplicity I don't think that really applies. Personally I think upgrades are the one thing MySQL has on Postgres at this point. [1] https://www.postgresql.org/docs/current/pgupgrade.html https://www.postgresql.org/docs/current/pgupgrade.html
    - marcosdumay 2y ago
      The GP is complaining about the need to run the upgrade cluster command, and reconfigure your server when you want to use a different version of postgres itself. MySql does it transparently if you just open the database in the new server. Of course, lots of people also think it's a feature. But those aren't very vocal. Anyway, this is a main reason why people keep using old postgres versions, they have to think about upgrading, and they often don't.
  - wruza 2y ago
    Do you even have to upgrade to the latest major version? What does that do, on average?
- phplovesong 2y ago
  Usually its a skill issue
- badlibrarian 2y ago
  VACUUM
  - kstrauser 2y ago
    If your data's large and changing enough that you have to care about vacuuming, any reasonable database is going to require some tuning, tending and management. I'd posit that only a tiny fraction of PostgreSQL uses have to know or care that vacuuming is a thing because the autovacuum default handle it for them.
    - mardifoufs 2y ago
      Sure, it's never going to be plug and play, but it doesn't mean that all the issues will be equivalent. Vacuuming doesn't really have an equivalent in say, MySQL. It's something you don't have to worry about if you use the latter. For example, HA and clustering will always be challenging to deploy/maintain, but you will still have a harder time doing that with postgres than with MySQL. Postgres also has a lot of benefits obviously, though.
- deleted 2y ago
  [deleted]
homebrewer 2y ago
Also the reverse — MySQL et al support much more complex replication topologies out of the box, including multi master for the very rare use case when you need it. It's also much easier to tune, most database instances require setting innodb_buffer_pool_size, and that's basically it. Newer versions can even set it automatically if you're fine with consuming all memory on that machine, thus requiring no tuning at all.

password4321 2y ago

Replication

poincaredisk 2y ago

* legacy applications or vendor lock-in * use of a cloud provider that favours another database (like SQL server) * some people claim mysql/maria is faster for them, simpler, or has a better support for replication * use of sqlite for lightweight or portable apps * domain specific databases that still use sql as their query language * someone may want to use another database for fun or to learn something new

hot_gril 2y ago

SQLite has its own use cases. And MySQL was all-around better than Postgres in the past, so it still has more usage in some areas. Nowadays I'll take Postgres over MySQL by default, but it's not a huge difference.