Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ask HN: It's 2023, how do you choose between MySQL and Postgres?

news.ycombinator.com

Post reply on HN
Print view View original

271–280 of 366 posts

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#271

Post by pyuser583 »

dijit wrote: post #97
pyuser583 wrote: post #95

Earlier quoted context omitted.

Does Postgres have an archive mode?

If you say what you’re trying to actually achieve I can help with a solution, but asking if it supports an arbitrary feature is not going to get the answer you want because depending on what you’re actually using an archive table for, Postgres might have something already built in but it will almost assuredly not be exactly like an archive table storage type.

Sorry, ARCHIVE is a MySQL storage engine. It supports only non destructive transactions: INSERT, REPLACE, and SELECT, but not DELETE and UPDATE.

It’s an excellent alternative to use a WORM drive when you’re trying to preserve everything (say, a list of financial transaction).

I’ve looked for something like this in Postgres (which I love!), but sadly it doesn’t seem supported.

https://dev.mysql.com/doc/refman/8.0/en/archive-storage-engi...

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#272

Post by arp242 »

evanelias wrote: post #267
arp242 wrote: post #265

Earlier quoted context omitted.

There is no way to see from the query itself if it's case-sensitive or insensitive; that is what I meant.

Eh, just from a SQL query alone, there's no way to see that (lower(email)) is indexed, or see column data types etc. That metadata lives in the table definition, which is a normal place for it, so it seems reasonable for the collation / case-insensitivity to not be explicit in the query text. Besides, MySQL also supports functional indexes, so you could do the (lower(email)) approach in MySQL too if you really want!

No, you can't see everything, but you can see the exact comparison it's making. Is that useful? You can decide that for yourself but I like being able to see as much of the logic (and thus verify the correctness) in the query itself. Also helps with copy/paste and some other things.

I never said you can't use functional indexes in MySQL. Someone said "you can't do this in PostgreSQL" and I just commented "here's (one way) to do this, maybe that's helpful some day". That's it.

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#273

Post by arp242 »

jhas78asd wrote: post #252
herpderperator wrote: post #123

I find PostgreSQL permission management quite convoluted. In MySQL it is simple to query for what grants a user has, but in PostgresSQL you need to write 100 lines of SQL to do the same... and you can't run \du and other commands without psql. Why couldn't they just come up with `SHOW` shortcuts that work in any SQL client?

You can likely get the SQL for a meta command, and you could run the SQL from your preferred client if you don’t use psql. Here is one example: https://dba.stackexchange.com/a/131031 I also highly recommend investing in psql skills though if you are a Postgres user.

Yes, that's the "100 lines of SQL to do the same" the previous poster mentioned (obviously they were exaggerating a bit and it's less than literally 100 lines, but it's pretty complex).

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#274

Post by jesterson »

gmac wrote: post #83

Postgres. Fast, full-featured, rock-solid, and a great community. I think many of us can’t be bothered to go over (again) the issues we’ve had with MySQL in the past. The last straw for me was about ten years ago, when I caught MySQL merrily making up nonsense results for a query I’d issued that accidentally didn’t make any sense. Very likely this particular issue, and others like it, have been fixed in the meantime.…

Working with MySQL (MariaDB, but doesn't make much difference). Never get any issues that couldn't be explained by architectural or development mistakes.

Just as example - how do you create read-only user (SELECT only) in Postgres? In MySQL it's extremely simple and it works, while in Postgres it's a nightmare to create and maintain

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#276

Post by 0xf8 »

evanelias wrote: post #268
stouset wrote: post #262

Earlier quoted context omitted.

Fair. I picked two instances that had long been personally painful with MySQL and didn’t exist when I finally fled to greener pastures. There are far more examples than just those two though. :)

Sure, and there are also plenty of examples of things MySQL can do that Postgres can't. There's no silver bullets in software or databases, no "one size fits all". Just various trade-offs between different design decisions.

I mean, for most mature ecosystems / technology stacks I think the “no one size fits all, just strategic design trade-offs” theory largely holds true as an objectively accurate account of the “choosing the best tool“ situation ….

but IMO, I think it’s worth noting that occasionally, some rare unique and functionally superlative technology comes along that in practice transcends every alternative from the onset and indefinitely going forwards, sometimes even at a more prominent scale than the MySQL / Postgres projects topic of discussion (which are not small by any means).

something maybe like Git, most immediately comes to mind, as an example of the de-facto standard for distributed VCS basically since … 2005* when Linus decided to create it?

edit: not 1995

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#277

Post by robertlagrant »

TehShrike wrote: post #137
robertlagrant wrote: post #107

Earlier quoted context omitted.

> when I started working on a postgres project, I was shocked at some of the ways it was lacking (how am I supposed to store email addresses in a database without collations?) How long ago was this? :)

3 years ago. From this comment thread https://news.ycombinator.com/item?id=35908169 I infer that Postgres still doesn't support collations.

Oh - is this ci collations, not collations in general?

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#278

Post by erhaetherth »

Easy. I keep using MySQL because that's all I know.

Kinda j/k. I'm intrigued my Postgres but I don't start that many new projects. It has at least 1 feature that MySQL doesn't -- deferred referential integrity which there's no good workaround for that I'm aware of. Arises extremely rarely when you need to have 2 tables have a FK to eachother.

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#279

Post by sjamaan »

dijit wrote: post #91
LammyL wrote: post #87

Earlier quoted context omitted.

Is there a good way to do case-insensitive accent-insensitive collations yet in postgresql? It’s been a holdup for using that for some use cases like searching for data, like a person’s name, in pgsql when the casing or accents don’t match perfectly. Mssql has had this for ever, and I’m pretty sure MySQL has it as well.

Maybe this helps: https://stackoverflow.com/posts/11007216/revisions ? My gut tells me that I would do it in the query itself though, and not rely on the collation. Maybe I am misunderstanding.

Look under "Update for Postgres 12 or later", there they create a collation, an index to make use of it and then a query to make use of it:

   SELECT \* FROM users WHERE name = 'João' COLLATE ignore_accent;

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#280

Post by erhaetherth »

0xf8 wrote: post #276
evanelias wrote: post #268

Earlier quoted context omitted.

Sure, and there are also plenty of examples of things MySQL can do that Postgres can't. There's no silver bullets in software or databases, no "one size fits all". Just various trade-offs between different design decisions.

I mean, for most mature ecosystems / technology stacks I think the “no one size fits all, just strategic design trade-offs” theory largely holds true as an objectively accurate account of the “choosing the best tool“ situation …. but IMO, I think it’s worth noting that occasionally, some rare unique and functionally superlative technology comes along that in practice transcends every alternative from the onset and in…

> something maybe like Git, most immediately comes to mind, as an example of the de-facto standard for distributed VCS basically since … 1995 when Linus decided to create it?

hot take. might be the most popular, maybe even by a large margin, but I think you'll find a good chunk of people who have actually tried different VCSs don't think it's the best.

Post reply on HN