Can it(the database) do simple pub/sub without third-party software? :)
Ask HN: It's 2023, how do you choose between MySQL and Postgres?
341–350 of 366 posts
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#342There is nothing to argue about.
Postgres is better in almost every possible aspect you can come up with.
So you choose mysql in 2023 only if you have very very specific requirements and constraints to justify the sacrifice.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#343Earlier quoted context omitted.
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.
> bulk data loads that make table stats more stale
This is the usual culprit (cure: "ANALYZE ((tablename))").
Collecting more samples (ALTER TABLE SET STATISTICS...) may be useful.
"Extended Statistics" covers most(?) other cases: https://www.postgresql.org/docs/current/planner-stats.html#P... https://www.postgresql.org/docs/current/multivariate-statist...
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#344Actually I would like to know just ONE advantage MySQL has over PostgreSQL... are there actually any?
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#345Earlier quoted context omitted.
> avoid Oracle as if it has herpes herpes isn't that bad. most people will get it in their lifetime. 1 in 6 people have hsv-2, the less common variant. trying to avoid herpes is like trying to avoid chickenpox (although herpes isn't nearly as harmful as chickenpox). you should avoid Oracle like it's a blood pathogen.
As a person who has herpes firmly in his nerves, I would say don't underestimate herpes.
Suddenly the analogy got very accurate.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#346Earlier quoted context omitted.
At a certain scale, you'll want replication or replication, which SQLite doesn't really do AFAIK. At a scale below that, you'll probably want to be able to have multiple web servers talking to one database server, which SQLite doesn't really do either. I also think SQLite's performance during heavy write workloads is worse than PostgreSQL's? Basically, AFAIK, SQLite becomes problematic once you need more than one com…
Just to point out, there are now SQLite replication and various "distributed database" projects which seem to work fairly well. They're probably not as battle tested as the PostgreSQL ones, but they are around, have users, and are actively developed. The ones I remember off the top of my head: * https://litestream.io * https://github.com/rqlite/rqlite * https://github.com/canonical/dqlite
Yeah, there are SQLite forks or products built on SQLite which have these sorts of features, but SQLite doesn't. They remains reasons why someone may want to use another database (such as PostgreSQL, or an SQLite fork, or a database which uses SQLite as a storage engine) instead of SQLite.
Honestly though, if I need these sorts of distribution features, I would probably prefer the database to have them built in. I don't really see the point in using SQLite at that scale.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#347Earlier quoted context omitted.
FWIW while I use Postgres for my own development I've had to administer a number of MySQL servers for other devs. Upgrades have always been updating the MySQL package, restarting MySQL, then running `mysql_upgrade`, and restart the server again. I'm pretty sure the mysql_upgrade has even been missed a number of times and it's worked fine. I won't say it's impossible you ran into issues doing this, but it is the docum…
as long as you upgrade with a minor version, you will have the same experience with postgres. 11.0->11.2 will work totally fine, with no command needed.
Right, but now go from 11->12, which is the equivalent of the upgrade path I was describing for MySQL. I either need to install both versions and use pg_upgrade to convert the binary files, then remove 11 (and extensions may break this flow) or do pg_dump/restore.
Minor versions on both Postgres and MySQL are painless, just install and restart the server. Major upgrades on MySQL are significantly less painful.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#348Postgres for almost anything. Robustness, ecosystem, accuracy, all top notch. One exception: I did migrate two very large tables (15B+ rows) from Postgres to MySQL for performance reasons. InnodB (MySQL storage engine) can arrange the records on page by primary key, and if you have a multi-value PK (user_id, uuid) it means all records from a user are in the same set of pages. Huuuuge improvement over having your data…
Did you implement table partitioning with Postgres or consider that before moving?
Been a while. IIRC it didn’t work with millions of users, and partitioning with many users per partition would have same issues. Also looked at CLUSTER but there was no workable online version.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#349Easy. You don't ever pick MySQL because it's essentially garbage.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#350Postgres. 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.…
FWIW, it hasn't changed in ten years. Here is an 18-year-old bug, that DELETE triggers don't work for foreign key cascades: https://bugs.mysql.com/bug.php?id=11472 That makes the entire feature mostly worthless. Reported in 2005, last updated in 2008. --- While I would choose PostgreSQL every time, MySQL has the following advantages: 1. Write-performance, due to fundamental design tradeoffs. [1] 2. Per-connection res…
Good you shouldn't be using them. You shouldn't be using foreign keys either. It just makes working with data harder and doesn't help with constraining it if your data modifications are inside transactions and properly written statements.