8 ms·

> For a start, Postgres is simply an incredibly powerful, robust, and reliable piece of software, providing the best implementation of SQL. Why is it better th

> For a start, Postgres is simply an incredibly powerful, robust, and reliable piece of software, providing the best implementation of SQL. Why is it better than MySQL? This article wasted my time. Is there a better article which explains why Postgres is better than other free and opensource SQL implementations?

CabSauce 5y ago
Better yet, there's a website that allows you to find other websites that answer your questions.
- flowerlad 5y ago
  Sadly the first page of results is all similar to this site, in terms of quality. HN readers are more knowledgeable than websites that can find other websites.
indymike 5y ago
MySQL and MariaDB are REALLY great software. The real difference is not one of quality (both are proven performers, and both have survived the test of time, both are used at scale by some pretty giant customers), it is one of quantity. Postgres simply has more features by a pretty wide margin than does MySQL. For many DBAs and developers, Postgres is a more flexible choice due to the additional capability.
- Tostino 5y ago
  I love me some partial indexes, exclusion constraints, range types, etc... Not even mentioning the GIS capability.
  - sam_goody 5y ago
    And trigrams! Both have "Full Text Search", but Postgre's is more complete. In general, Postgres also seems to have better plugins and extensions
- threeseed 5y ago
  > Postgres simply has more features And yet lacks the most important one. A natively supported clustering and HA solution. Especially important with CitusData being acquired by Microsoft and its future now at risk.
  - whitepoplar 5y ago
    pg_auto_failover is pretty great.
    - threeseed 5y ago
      a) It's maintained by CitusData which was acquired by Microsoft and so is now both unsupported and based on history at risk of being unmaintained. b) Is not part of the core PostgreSQL codebase and so you are forever having to worry about compatibility. c) PostgreSQL still lacks multi-master HA which is needed when you want to HA but continue to scale out.
      - whitepoplar 5y ago
        FWIW, pg_auto_failover was released post-acquisition and is most certainly not unsupported; it's actively developed and new feature releases have been solid. It would be nice for this functionality to be built into Postgres, though.
  - Twisell 5y ago
    Ahem there is also https://repmgr.org https://repmgr.org An actively developed FOSS solution backed by EnterpriseDB also one of the main company supporting PostgreSQL. But err... And ahem ahem... there are more https://www.postgresql.org/download/products/3-clusteringreplication/ https://www.postgresql.org/download/products/3-clusteringrep... It's a choice of the PostgreSQL development team to only include low level api for HA in core project. But it's fairly harsh to say there is no active support for HA. Many maintainers of the core are also invested in HA related project build around PostgreSQL.
    - 5y ago
johncalvinyoung 5y ago
Transactional DDL changes is a huge one. Postgres allows me to modify the table schema in a transaction, and rollback changes as if it was a mere data transaction, if something fails mid-migration. Makes maintaining production databases through upgrades and schema changes much less stressful.
- stepbeek 5y ago
  Transactional DDL changes cannot be understated - it’s the most outrageously useful database feature out there. I have to work with oracle for a client right now and I’m gobsmacked that they don’t offer it at the pricepoint.
- WJW 5y ago
  I've always wondered about this in the scope of REALLY big tables. Migrating a busy table with a few billion rows will take 10+ hours, doesn't the redo log fill up? The amount of work that needs to be undone could easily take another 10+ hours, is there enough storage space for that? Every busy production db I've ever seen has had big problems when dealing with 10-hour transactions, and I don't see how Postgres could deal with these kind of problems as they're fundamental in the data model.
  - andruby 5y ago
    What kind of DDL migration are you thinking about that would take 10+ hours? Adding a column is instant, even if your table is multiple TB's large. Same for dropping and renaming columns.
    - funcDropShadow 5y ago
      Adding a column can be instant on multi TiB tables, but that depends on the details. If is a non-null column with a non-stable default value, like the current time, it will require a full rewrite of all rows of the table.
      - WJW 5y ago
threeseed 5y ago
One is not better they are just different. If you value more expressive data types and constructs to read/write your data then PostgreSQL is better. If you don't need those but do need to be able to scale to very large data volumes then MySQL is better. I don't understand why people keep wanting to make this a competition. It's been going for decades and it was stupid back then as it is today. The databases are both popular, both high quality but serve different needs.
reilly3000 5y ago
For me it’s their extensive selection of foreign data wrappers and extensions which allow for it to be incredibly integrated and work with novel paradigms. It was the first by several years to support JSON fields and window functions, but I was pleased to see those in MySQL 8. For extremely write-heavy use cases I think MySQL wins as Postgres tends to want every materialized view current before completing an insert. It’s license has never been in question, where MySQL, Percona, and MariaDb have been a source of concern/confusion over the years. https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers IMAP, GitHub, BigQuery, Faker and CSV have been awesome for me, but don’t tend to be 100% supported by public cloud managed DB services. I’ve heard some can be flaky. INSERT INTO public.people SELECT ssn, name, phone_number FROM faker.people; That is a way to generate a ton of fake records for integration and load testing. Very elegant!

CabSauce 5y ago

Better yet, there's a website that allows you to find other websites that answer your questions.

flowerlad 5y ago
Sadly the first page of results is all similar to this site, in terms of quality. HN readers are more knowledgeable than websites that can find other websites.

indymike 5y ago

MySQL and MariaDB are REALLY great software. The real difference is not one of quality (both are proven performers, and both have survived the test of time, both are used at scale by some pretty giant customers), it is one of quantity. Postgres simply has more features by a pretty wide margin than does MySQL. For many DBAs and developers, Postgres is a more flexible choice due to the additional capability.

Tostino 5y ago
I love me some partial indexes, exclusion constraints, range types, etc... Not even mentioning the GIS capability.
- sam_goody 5y ago
  And trigrams! Both have "Full Text Search", but Postgre's is more complete. In general, Postgres also seems to have better plugins and extensions
threeseed 5y ago
> Postgres simply has more features And yet lacks the most important one. A natively supported clustering and HA solution. Especially important with CitusData being acquired by Microsoft and its future now at risk.
- whitepoplar 5y ago
  pg_auto_failover is pretty great.
  - threeseed 5y ago
    a) It's maintained by CitusData which was acquired by Microsoft and so is now both unsupported and based on history at risk of being unmaintained. b) Is not part of the core PostgreSQL codebase and so you are forever having to worry about compatibility. c) PostgreSQL still lacks multi-master HA which is needed when you want to HA but continue to scale out.
    - whitepoplar 5y ago
      FWIW, pg_auto_failover was released post-acquisition and is most certainly not unsupported; it's actively developed and new feature releases have been solid. It would be nice for this functionality to be built into Postgres, though.
- Twisell 5y ago
  Ahem there is also https://repmgr.org https://repmgr.org An actively developed FOSS solution backed by EnterpriseDB also one of the main company supporting PostgreSQL. But err... And ahem ahem... there are more https://www.postgresql.org/download/products/3-clusteringreplication/ https://www.postgresql.org/download/products/3-clusteringrep... It's a choice of the PostgreSQL development team to only include low level api for HA in core project. But it's fairly harsh to say there is no active support for HA. Many maintainers of the core are also invested in HA related project build around PostgreSQL.
  - 5y ago

johncalvinyoung 5y ago

Transactional DDL changes is a huge one. Postgres allows me to modify the table schema in a transaction, and rollback changes as if it was a mere data transaction, if something fails mid-migration. Makes maintaining production databases through upgrades and schema changes much less stressful.

stepbeek 5y ago
Transactional DDL changes cannot be understated - it’s the most outrageously useful database feature out there. I have to work with oracle for a client right now and I’m gobsmacked that they don’t offer it at the pricepoint.
WJW 5y ago
I've always wondered about this in the scope of REALLY big tables. Migrating a busy table with a few billion rows will take 10+ hours, doesn't the redo log fill up? The amount of work that needs to be undone could easily take another 10+ hours, is there enough storage space for that? Every busy production db I've ever seen has had big problems when dealing with 10-hour transactions, and I don't see how Postgres could deal with these kind of problems as they're fundamental in the data model.
- andruby 5y ago
  What kind of DDL migration are you thinking about that would take 10+ hours? Adding a column is instant, even if your table is multiple TB's large. Same for dropping and renaming columns.
  - funcDropShadow 5y ago
    Adding a column can be instant on multi TiB tables, but that depends on the details. If is a non-null column with a non-stable default value, like the current time, it will require a full rewrite of all rows of the table.
    - WJW 5y ago

threeseed 5y ago

One is not better they are just different. If you value more expressive data types and constructs to read/write your data then PostgreSQL is better. If you don't need those but do need to be able to scale to very large data volumes then MySQL is better. I don't understand why people keep wanting to make this a competition. It's been going for decades and it was stupid back then as it is today. The databases are both popular, both high quality but serve different needs.

reilly3000 5y ago

For me it’s their extensive selection of foreign data wrappers and extensions which allow for it to be incredibly integrated and work with novel paradigms. It was the first by several years to support JSON fields and window functions, but I was pleased to see those in MySQL 8. For extremely write-heavy use cases I think MySQL wins as Postgres tends to want every materialized view current before completing an insert. It’s license has never been in question, where MySQL, Percona, and MariaDb have been a source of concern/confusion over the years. https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers IMAP, GitHub, BigQuery, Faker and CSV have been awesome for me, but don’t tend to be 100% supported by public cloud managed DB services. I’ve heard some can be flaky. INSERT INTO public.people SELECT ssn, name, phone_number FROM faker.people; That is a way to generate a ton of fake records for integration and load testing. Very elegant!