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

251–260 of 366 posts

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

#251

Post by jhas78asd »

skunkworker wrote: post #45

Postgres for anything with a single database size But if you need something that can handle 100TB+, go Vitess(mysql compatible).

What’s the reason though for Vitess? Postgres supports tables up to 32TB but hopefully you’re splitting them up using declarative partitioning in one or more ways before that. If you have tables that are smaller than a TB and a large memory DB (>1 TB RAM) Postgres should run ok right? I’d also imagine you’re splitting up your database into multiple databases and multiple instances (the writers) well before that as well right?

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

#252

Post by jhas78asd »

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.

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

#253

Post by jhas78asd »

arp242 wrote: post #229
Izkata wrote: post #160

Earlier quoted context omitted.

I forget if there's an equivalent for the first one, but from psql there is a translation of mysql's "DESC table" as "\d table", and the rest are: \dt \l SELECT * FROM pg_stat_activity;

> I forget if there's an equivalent for the first one Not really; PostgreSQL doesn't store the original query, so you'll need to re-create it from pg_class, pg_attribute, and all of that (which is really what \d and such in psql do). The easiest way is probably pg_dump, but it's best to just get used to \-commands because it's really just the same thing.

Find the SQL from meta commands. Example: https://dba.stackexchange.com/a/131031

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

#254

Post by jhas78asd »

spacedcowboy wrote: post #176
tbarbugli wrote: post #156

Earlier quoted context omitted.

I highly suggest investing time learning psql, autocomplete works great and it has a ton of useful slash commands. \d for instance shows you the list of tables. Awesome tool

I don't need it often enough to invest the time. I generally set up a database as backing store to some project, fiddle with it until I'm happy it's working at the scale/performance I want, and then move on to something else. During those few weeks I'm actively using the database on the project, I can either get frustrated beyond belief with the CLI for Postgres, or just use what's at hand with MySQL. In fact, these…

Each of the bullets you listed have very straightforward and memorable meta commands that I use on a regular basis with psql. It may be worth learning them just for when you use Postgres. There is also a built in help. These can also be saved into your dot files so you don’t need to memorize them. Happy to show you if you’re interested!

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

#255

Post by jhas78asd »

spudlyo wrote: post #85
trympet wrote: post #46

The Postgres query optimizer is more powerful than the MySQL query optimizers [1]. It generally scales better for OLTP. Also tons of extensions that can accelerate your workload. [1] - https://ieeexplore.ieee.org/document/9537400

It's also more opinionated than the MySQL query optimizer, in that you can't give it hints to prevent it from making a horrible mistake.

There is a way to provide some type of planner hints https://pghintplan.osdn.jp/pg_hint_plan.html

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

#256

Post by jhas78asd »

craigkerstiens wrote: post #171

Having answered this a ton over the years, don't want to really take shots at MySQL. But Postgres stands in pretty unique ground. 1. It's solid as a really reach data platform (more than just a relational database). It's extension framework is quite unique compared to others. It's JSONB support was the first among other relational databases and is feature rich and performant. Multiple index types. Transactional DDL.…

Hi Craig! :) https://twitter.com/andatki

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

#257

Post by evanelias »

tapoxi wrote: post #247
connormcd wrote: post #243

Earlier quoted context omitted.

(Full disclosure, I work for Oracle) Anyone who says no investment has been into MySQL I suspect never took the time to read the features/release notes for MySQL 8 https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html

Didn't 8 ship 5 years ago?

Their release model changed with MySQL 8 -- they do rolling point releases every quarter with new features sprinkled in as they're ready. Quite a few new major features have been released that way, including INSTANT alters, parallel index builds, CLONE plugin, major changes to how UNDO logs are sized... it's more like Windows 10's release model.

Very recently they've mentioned they'll be changing this again to have separate LTS releases, which is a positive change stability-wise.

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

#259

Post by jhas78asd »

manv1 wrote: post #248

The main reason I prefer mysql over PostgreSQL is that mysql is just more consistent - in its commands, quirks, etc. Postgres - is it pg, pgsql, psql, postgres, postgresQL? The answer is "yes." Plus the case behavior for tables and column names drives me crazy. It's like some leftover VMS shit. I mean seriously fix it. Can you or can you not use a capital letter for a table/column name? I can never remember. Or you c…

If you’re talking about the command line client that’s built in, it’s psql. If you can’t remember the command name to launch it or regularly type those other commands when you meant to type psql, you could add aliases to your shell that point to psql. :)

Learning any new CLI client is a bit daunting at first. With repetition and intention, I think the commands become very memorable. Eg “describe table” is “dt”.

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

#260

Post by jhas78asd »

jitl wrote: post #224
pawelduda wrote: post #175

Earlier quoted context omitted.

What's the ratio of solving DB perf issues by optimizing it and letting the planner do its work, to telling it what to do? For me it's like 1000:1. And that one case I remember was perfectly solvable the regular way, with a little more time.

Postgres query planner suddenly deciding to do something silly in the middle of the night has taken Notion down a few times. It's quite annoying, and it's very frustrating to have no recourse.

Any posts on this? Are there bulk data loads that make table stats more stale and affect plans? I’m wondering what would suddenly make a plan selection change a lot that might be a contributing factor.
Post reply on HN