Re:Ask HN: Is PostgreSQL better than MySQL?
by voganmother42 on Monday April 17, 2023 @08:37AM
(#35599791)
As far as making/performing modifications to the schema/data, as others have stated, transactional ddl is a huge feature for Postgres and makes migrations much more straightforward in my experience.
Re:Ask HN: Is PostgreSQL better than MySQL?
by barrkel on Monday April 17, 2023 @09:12AM
(#35600118)
Any DDL worthy of the name (table rewrite needed) in MySQL needs to be done with pt-online-schema-change or gh-ost. With DDL, you want to e.g. have it tune the I/O load of the migration so it doesn't interfere too much with production load. In practice, pt-osc / gh-ost means you have a kind of transaction, because the rewritten table doesn't take the place of the old one until the rename at the end. That's essentially an atomic commit.
Re:Ask HN: Is PostgreSQL better than MySQL?
by anonzzzies on Monday April 17, 2023 @09:08AM
(#35600076)
I think this is a good answer. Our case is weird; millions of tables with billions (not sure how many in total even) of rows and no joins. Mysql handles this far better in our testing.
May I ask what kind of data you are storing that you need millions of tables? If you have such varied data, what are the tradeoffs compared to only a few tables with json columns?
I find it useful to remember that MySQL has various storage engines (that make different trade offs, and may themselves have different options such as various row formats), when using for instance the default of InnoDB knowing that it uses index organized (meaning stores the data in a btree as opposed to heap) tables can influence what workloads perform well (is the primary key used heavily/load bearing).
Postgres often excels when queries are more complex, for instance being able to use partial indexes (include a subset of a table, a “where” clause in the index) can be very powerful.
One other point of differentiation that has been useful for me to consider for various projects is also the programming within the database(stored programs): specifically doing things like writing stored procedures/functions, Postgres has many high quality procedural languages(some are shipped in core like PL/Perl, or others like PL/R that are external) that can useful to express these with.