5 ms·
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 is
by IMTDb 7y ago
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, ...)
- toxik 7y agoI 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.
- deleted 7y ago[deleted]
- 0815test 7y ago> ...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.
- darkpuma 7y agoHow 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.
- reallydude 7y ago> 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
- darkpuma 7y agoIt seems far more likely that the GP was imprecise in their analogy's ordering, rather than slamming python and praising PHP.
- crystaln 7y agoAnd 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.
- wbl 7y agoWith sufficient thrust pigs fly, but that doesn't mean you should totally ignore aerodynamics when making a plane.
- deleted 7y ago[deleted]
- Finnucane 7y agoYour database is one place where the old rule of "if it ain't broke, don't fix it" should apply.
- deleted 7y ago[deleted]
- munk-a 7y agoI 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.
- LoSboccacc 7y agoalso 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.
- munk-a 7y agoHaving 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.
- oarabbus_ 7y agoIf 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
- sslalready 7y agoCare to expand on why MySQL < 8 will be problematic for analysts? Anything else than the lack of window function?
- paulryanrogers 7y agoPossibly JSON operators
- jimktrains2 7y agoWindow functions.
- oarabbus_ 7y agoIMO 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.
- grogenaut 7y agoOr 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.
- oarabbus_ 7y agoExcept 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.
- batbomb 7y agoThere'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.
- ken 7y agoMaybe 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.
- closeparen 7y agoMySQL has transactions. MyISAM doesn’t, but the apples to apples comparison would be InnoDB, right?
- 33degrees 7y agoMySQL 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.
- krageon 7y agoMySQL 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.