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.

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.

Geez, I can't imagine running migrations without this.

You'd just run your migration statements one after the other and pray it doesn't fail midway? Or have a contingency plan for every possible point of failure?

Postgres has given me a sheltered life.

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.

>doesn't the redo log fill up?

It's not a redo/undo log. It's more like (simplifying things here) having multiple copies of things and different sessions/connections "see" different sets of data. That's why you need to vacuum the database periodically, to garbage collect old rows that are no long accessible to any sessions/transactions.

See https://devcenter.heroku.com/articles/postgresql-concurrency

>is there enough storage space for that?

You do need to make sure you aren't too close to filling your storage, postgres needs some breathing room for this reason.

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.

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.

Indeed, i was talking about multi-TB tables. From the postgres v13 docs (https://www.postgresql.org/docs/current/sql-altertable.html):

> Adding a column with a volatile DEFAULT or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception, when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt. Table and/or index rebuilds may take a significant amount of time for a large table; and will temporarily require as much as double the disk space.

Clearly there are workarounds where you make a new empty column that you then slowly backfill with the correct value and eventually transactionally rename the new column to the old one, but the magic "oh postgres does DDL in a transaction so it's magic and we never have to worry" is pretty much gone at that point.

Yes, but generally that could be handled in a multi-stage process. First you would create the new column with no constraint, then periodically start updating the rows, once nearly all the rows are updated with the new default, in a transaction you update the rest of the rows, update the table with the constraint, and set the default. Not very much downtime is required with this approach (although of course it is more involved).

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.

I love me some partial indexes, exclusion constraints, range types, etc... Not even mentioning the GIS capability.

And trigrams! Both have "Full Text Search", but Postgre's is more complete.

In general, Postgres also seems to have better plugins and extensions

> 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.

Ahem there is also 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-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.

Natively supported means part of the core product.

Dealing with third party solutions means having to worry about incompatibility, enterprise support, ongoing maintenance etc.

Not being in part of core mean multiple solutions to fit different needs. Also a lot of people don't really need HA so it keep core feature more focused. (Again HA is supported at low level, you could totally achieve HA without third party, it just require more internal ressources)

There are a lot of companies out there which provide enterprise support for PostgreSQL + HA.

pg_auto_failover is pretty great.

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.

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.

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 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!

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.

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

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.