7 ms·

Besides what's already mentioned by siblings: - Forwards (and usually also backwards) compatible disk format, meaning version updates don't require more than a

Besides what's already mentioned by siblings: - Forwards (and usually also backwards) compatible disk format, meaning version updates don't require more than a few minutes of downtime. On large datasets, postgresql can require days or weeks. - Replication works across version differences, making upgrades without any downtime at all easier. - No need for a vacuum process that can run into trouble with a sustained high write load. - Page-level compression, reducing storage needs for some types of data quite a bit. - Clustered primary keys. Without these, some types of queries can become rather slow when your table doesn't fit in memory. (Example: a chat log containing many billions of messages, which you're querying by conversion-id.) - xtrabackup (innobackupex) is awesome.

darkr 5y ago
> Replication works across version differences, making upgrades without any downtime at all easier. Postgres has logical replication these days, which can be used to achieve this
- funny_falcon 5y ago
  Except logical replication doesn't handle DDL yet. And 2 phase commit (prepared transactions) as well.
  - aeyes 5y ago
    Two phase commit for logical replication is now available in Postgres 14. The biggest issue I see at the moment is that sequences are not replicated.
mattashii 5y ago
> - Forwards (and usually also backwards) compatible disk format, meaning version updates don't require more than a few minutes of downtime. How so? Postgres' data formats are forwards (and mostly backwards) compatible with release 8.4 in 2009; where effectively only the catalogs need upgrading. Sure, that can be a lot of data, but no different from MySQL or any other RDBMS with transactional DDL. > - Replication works across version differences Logical replecation is available since at least 9.6
- boxed 5y ago
  Not the dump format. The actual data on disk of the database itself. If you try upgrading even one major postgres version and you're not aware of this you lose all your data (you don't really as you can roll back to the previous version, but it doesn't even tell you that!)
  - mattashii 5y ago
    That is what I'm talking about, yes. Page format for tables hasn't changed since 8.4, tuple format hasn't changed in a backwards-incompatible manner since 8.4 (probably earlier). BTree indexes have changed a bit, but ones created in 9.0.x can still be loaded and used in 14.x. GIN/GiST/SP-GiST/BRIN all don't seem to have had backwards-incompatible changes, looking at https://why-upgrade.depesz.com/show?from=8.4&to=14.2 https://why-upgrade.depesz.com/show?from=8.4&to=14.2 The only thing that changed is the catalogs, upgradable through pg_upgrade.
    - justinclift 5y ago
      Pretty much every major PG release clearly states the on-disk format isn't fixed between major versions, and is subject to change: • https://www.postgresql.org/docs/14/upgrading.html https://www.postgresql.org/docs/14/upgrading.html • https://www.postgresql.org/docs/13/upgrading.html https://www.postgresql.org/docs/13/upgrading.html • https://www.postgresql.org/docs/12/upgrading.html https://www.postgresql.org/docs/12/upgrading.html • https://www.postgresql.org/docs/11/upgrading.html https://www.postgresql.org/docs/11/upgrading.html • https://www.postgresql.org/docs/10/upgrading.html https://www.postgresql.org/docs/10/upgrading.html For major releases of PostgreSQL, the internal data storage format is subject to change, thus complicating upgrades.
      - hans_castorp 5y ago
        pg_upgrade will take care of that. And you can still run upgrade from 8.4 to 14 using the --link mode which means no data will be copied - only the system catalogs need to be re-created.
Gibheer 5y ago
> - No need for a vacuum process that can run into trouble with a sustained high write load. MySQL has a similar mechanism called the purge job. It can lead to similar problems, for example DML slow down. https://mariadb.com/kb/en/innodb-purge/ https://mariadb.com/kb/en/innodb-purge/
hans_castorp 5y ago
> Clustered primary keys. Without these, some types of queries can become rather slow when your table doesn't fit in memory. (Example: a chat log containing many billions of messages, which you're querying by conversion-id.) https://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key https://use-the-index-luke.com/blog/2014-01/unreasonable-def...
- SigmundA 5y ago
  Clustered indexes can be overused but they are sorely missing from PG, having had them forever in SQL server it was another surprise to see they don't exist in PG and there is even a confusing CLUSTER command to reorder the heap table but does not actually make a clustered index. Clustered index are great for storage space and IO for common write and retrieval patterns. If your table is accessed in one way most of the time and that way needs to be fast and therefore requires an index a clustered index saves write IO (only writing the table no secondary index) disk space (no secondary index redundantly storing the indexed columns) and retrieval time (no indirection when querying the clustered index). This is great for certain kinds of tables, for instance log tables that need to written to quickly but queried quickly (usually by time range) and grow quite large and are append only. GIS tables which can also get quite large and are by points can be packed really tight and row found quickly. Entity tables that are mostly retrieved via primary key ID during app usage, you trade a slight performance penalty when using secondary index for maximum performance when retrieving by ID which effect everything in including foreign key checks this again saves space on writing the ID twice to disk. Tables that actually are an index for something else such that the table is kept up to date with a trigger etc and usually exist for specific access pattern and performance.
  - castorp 5y ago
    I have been working with Oracle for more than 20 years now. I think I only had very few situations where an "index organized table" (=clustered index) was useful or actually provided a major performance benefit over a "heap table". So I never really miss them in Postgres.

darkr 5y ago

> Replication works across version differences, making upgrades without any downtime at all easier. Postgres has logical replication these days, which can be used to achieve this

funny_falcon 5y ago
Except logical replication doesn't handle DDL yet. And 2 phase commit (prepared transactions) as well.
- aeyes 5y ago
  Two phase commit for logical replication is now available in Postgres 14. The biggest issue I see at the moment is that sequences are not replicated.

mattashii 5y ago

> - Forwards (and usually also backwards) compatible disk format, meaning version updates don't require more than a few minutes of downtime. How so? Postgres' data formats are forwards (and mostly backwards) compatible with release 8.4 in 2009; where effectively only the catalogs need upgrading. Sure, that can be a lot of data, but no different from MySQL or any other RDBMS with transactional DDL. > - Replication works across version differences Logical replecation is available since at least 9.6

boxed 5y ago
Not the dump format. The actual data on disk of the database itself. If you try upgrading even one major postgres version and you're not aware of this you lose all your data (you don't really as you can roll back to the previous version, but it doesn't even tell you that!)
- mattashii 5y ago
  That is what I'm talking about, yes. Page format for tables hasn't changed since 8.4, tuple format hasn't changed in a backwards-incompatible manner since 8.4 (probably earlier). BTree indexes have changed a bit, but ones created in 9.0.x can still be loaded and used in 14.x. GIN/GiST/SP-GiST/BRIN all don't seem to have had backwards-incompatible changes, looking at https://why-upgrade.depesz.com/show?from=8.4&to=14.2 https://why-upgrade.depesz.com/show?from=8.4&to=14.2 The only thing that changed is the catalogs, upgradable through pg_upgrade.
  - justinclift 5y ago
    Pretty much every major PG release clearly states the on-disk format isn't fixed between major versions, and is subject to change: • https://www.postgresql.org/docs/14/upgrading.html https://www.postgresql.org/docs/14/upgrading.html • https://www.postgresql.org/docs/13/upgrading.html https://www.postgresql.org/docs/13/upgrading.html • https://www.postgresql.org/docs/12/upgrading.html https://www.postgresql.org/docs/12/upgrading.html • https://www.postgresql.org/docs/11/upgrading.html https://www.postgresql.org/docs/11/upgrading.html • https://www.postgresql.org/docs/10/upgrading.html https://www.postgresql.org/docs/10/upgrading.html For major releases of PostgreSQL, the internal data storage format is subject to change, thus complicating upgrades.
    - hans_castorp 5y ago
      pg_upgrade will take care of that. And you can still run upgrade from 8.4 to 14 using the --link mode which means no data will be copied - only the system catalogs need to be re-created.

Gibheer 5y ago

> - No need for a vacuum process that can run into trouble with a sustained high write load. MySQL has a similar mechanism called the purge job. It can lead to similar problems, for example DML slow down. https://mariadb.com/kb/en/innodb-purge/ https://mariadb.com/kb/en/innodb-purge/

hans_castorp 5y ago

> Clustered primary keys. Without these, some types of queries can become rather slow when your table doesn't fit in memory. (Example: a chat log containing many billions of messages, which you're querying by conversion-id.) https://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key https://use-the-index-luke.com/blog/2014-01/unreasonable-def...

SigmundA 5y ago
Clustered indexes can be overused but they are sorely missing from PG, having had them forever in SQL server it was another surprise to see they don't exist in PG and there is even a confusing CLUSTER command to reorder the heap table but does not actually make a clustered index. Clustered index are great for storage space and IO for common write and retrieval patterns. If your table is accessed in one way most of the time and that way needs to be fast and therefore requires an index a clustered index saves write IO (only writing the table no secondary index) disk space (no secondary index redundantly storing the indexed columns) and retrieval time (no indirection when querying the clustered index). This is great for certain kinds of tables, for instance log tables that need to written to quickly but queried quickly (usually by time range) and grow quite large and are append only. GIS tables which can also get quite large and are by points can be packed really tight and row found quickly. Entity tables that are mostly retrieved via primary key ID during app usage, you trade a slight performance penalty when using secondary index for maximum performance when retrieving by ID which effect everything in including foreign key checks this again saves space on writing the ID twice to disk. Tables that actually are an index for something else such that the table is kept up to date with a trigger etc and usually exist for specific access pattern and performance.
- castorp 5y ago
  I have been working with Oracle for more than 20 years now. I think I only had very few situations where an "index organized table" (=clustered index) was useful or actually provided a major performance benefit over a "heap table". So I never really miss them in Postgres.