Hacker News Reader

Comment by IMTDb | Hacker News Reader

If your project is already on MySQL and you have no issue => Don't change anything

If your project is already on MySQL and you have issues => understand the issues you are facing and make sure that moving to Postgres would fix them (99% chance it won't)

If you have a new project and have very precise informations about the constraint you will face (pretty rare) => Do your research and choose what's best for your use case

If you have a new project and have only got a vague idea of what you are going to need/face 5 years down the road => Postgres is a slightly better choice than MySQL due to the feature set. Unless you have some other reason to go for MySQL (expertise, ...)

View on HN

Maybe I've had the misfortune to work at companies that were especially bad at MySQL, or maybe I'm just a Bad Programmer (actually that's probably it), but I've never worked on a project using MySQL that didn't have issues which could have been resolved by switching to Postgres.

The first time you need to run a DDL statement on live data (usually: "2.sql"), you'll be thankful for transactions. The places where Postgres shines aren't exactly uncommon situations.

MySQL has transactions. MyISAM doesn’t, but the apples to apples comparison would be InnoDB, right?

MySQL doesn't have transactions for DDL (i.e. alter table ), which Postgres does. This is very handy for migrations, as you can modify columns and update data in the same transaction and rollback if things go awry.

MySQL has lots of features, which don't work in specific cases you will likely encounter some day. This is the case very frequently (and of course this is also the case with transactions). If nothing quite works as you expect, I'd say it falls in the category of a "newb trap". Maybe it would behove us to pick the options that have the lowest amount of those.

Your database is one place where the old rule of "if it ain't broke, don't fix it" should apply.

I agree, for a greenfield project PostgreSQL is quite a nice option - if you're already using MySQL then probably stick with that until performance forces a change.

All that said I actually did lead a DB switch at my current gig - though it was quite a few years back when PostgreSQL had a really clear feature lead, one of the biggest quick wins was moving a terrible string field like... `1-2-3-4` over to a slightly less terrible int[] field.

also when you have performance issue in a db it's likely you'd get performance issue on any dB, at which point other solutions than a db migration can get more bang for buck - memcached sessions, solr searches, caching proxies, materialized pages etc.

Having had to discern performance issues (poorly align/absent indexes, optimization fences, etc...) I much prefer postgreSQL over mySQL especially since postgreSQL's query planner is much more advanced than mySQL's.

For highly patterned data access some of those options are quite good to investigate and invest in before getting serious about DB tuning (since, from my experience, once a company starts being serious about DB tuning it is a constant maintenance cost) but a lot of usage - especially for younger companies - are not such that caching will buy you much on the expensive components.

This comment is really conditional on a bunch of things so I wanted to clarify that I'm not disagreeing with your statement on performance directly, but I am disagreeing with it being a generalization. Performance is complicated and there is a lot to keep in mind.

true, I should have gone more in depth with it, the missing bit is that, assuming the db schema is not completely borked, cores and ram and fast disk on a db get you a long long way, so the tipping point for performance is often when you hit a real scaling problem as a mature company while younger company can get by just purchasing more iops from a vendor, given the current total comp of a full time dba.

of course if one want to get serious on tuning itself all the kind of analyze toolings available in and around postgres are phenomenal, I think on par with those of oracle, albeit my exp there is stuck at 12i of the old times, which makes pg my default choice for any project.

But, if your database silently changes your data then the database should be considered broken.

If YOUR life is good but you want to continue to make your analysts' life a living hell => Don't change anything

Otherwise, switch to Postgres. At the very least, for the love of god, make sure your MySQL is >=v8.0

Care to expand on why MySQL < 8 will be problematic for analysts? Anything else than the lack of window function?

Possibly JSON operators

Window functions.

IMO lack of window functions alone are a death knell.

CTEs/WITH clause is the other massive one - almost as important as window functions.

Additionally, EXCEPT/INTERSECT support, better EXPLAIN (it's still shit though), much improved REGEXP and JSON support.

Or you know just export your data to a data lake like redshift spectrum and let the analysts pay for their usage by querying from s3 and take them out of the equation on how you run the website. There is no need for a one size fits all solution here nor a centralized approach.

Except postgres is a terrific backend for a web app, so not sure where you're going with that.

And using Spectrum or Athena and reading directly from the data lake has it's own pros and cons and overhead costs. I wouldn't agree with your approach in most cases. Much better would be to ETL from postgres into a vanilla redshift DW or just set up a replica postgres to query from. If you're using Redshift Spectrum you should probably take a look at Snowflake as an alternative.

This is in comparison to rewriting your web application from MySQL to pgsql so your analysts can work.

And I'm using spectrum as a data lake solution generically. If you want to run snowflake or some other thing is dependent on if you are in aws, gke, bare metal, etc and if you want to host or not. I prefer not to have to manage hosting and I run in AWS.

The important part is you don't need to tie your web app database to all of your needs. Separation of concerns is the important part and independent scaling for independent needs.

Exporting the data analysts want to a jurisdiction that has very bad privacy safeguards is unethical. Even if you're not exporting it between jurisdictions, exporting it to a separate company should not be a step that is easy to take for you.

There's some caveats there.

If you are just running a web app, then this is the correct answer. If your business is in your database, you employ data analysts and want to gleam some additional insights from your database, then it might be worth a look, though most people who chose MySQL did so because they were building a web applications and don't employ analysts.

I think MySQL smells like poor engineering, and PostgreSQL like good engineering. That’s why my gut says PostgreSQL is the better choice, for the myriad issues you won’t face. It’s like Python vs PHP, in some ways.

> ...It’s like Python vs PHP, in some ways.

Uh, did you notice where this was posted? I don't exactly disagree, but it's kinda ironic to raise that point.

How is that ironic? Because HN was written in Arc, denigrating PHP is ironic? I don't get it.

Edit: Oh, because the article is on the PHP subreddit. To be honest I file PHP developers using MySQL under expected, not ironic.

> I think MySQL smells like poor engineering, and PostgreSQL like good engineering

> It’s like Python vs PHP, in some ways.

Not just the ordering of the characterization, which would paint PHP in the positive light you clearly think is undeserving. SMH

It seems far more likely that the GP was imprecise in their analogy's ordering, rather than slamming python and praising PHP.

And like PHP an extraordinary amount of good engineering has poured in after bad, resulting in a quite workable even good products with technical debt. I might also make the comparison to MS-DOS / Windows.

Not sure why this is downvoted.

With sufficient thrust pigs fly, but that doesn't mean you should totally ignore aerodynamics when making a plane.

Reply on news.ycombinator.com