Anyone made the jump from MySQL to PostreSQL? It is worth it? • Hacker News Board

Re: Anyone made the jump from MySQL to PostreSQL? It is worth it?

#101

Post by xemdetia » Tue, Jul 16, 2019, 11:16 PM UTC

HugThem wrote: post #36

If you don't need transactions, then MySQL with the MyISAM engine has several aspects I love: 1: Blazingly fast. As it simply hands DB writes to the OS and benefits from all the caching and optimization the OS does. Depending on your storage medium, insert/updates/deletes often are 100x faster then with an ACID compliant engine. 2: Simple data structure. Want to see the sizes of your tables? "ls /var/lib/mysql/your_d…

As long as your application understands https://dev.mysql.com/doc/refman/8.0/en/internal-locking.htm... then MyISAM may work for you, it just should be relegated to the second tier option especially for new development. I feel like I have seen so many people overestimate the benefits of MyISAM when they later realize that they can't cleanly write all of the information in a single append unit and have to touch values after the first insert.

I generally hope people stop using it as a starting point and instead relegate it to a particular optimization crutch like in-memory tables.

Re: Anyone made the jump from MySQL to PostreSQL? It is worth it?

#102

Post by oarabbus_ » Tue, Jul 16, 2019, 11:47 PM UTC

sslalready wrote: post #66

oarabbus_ wrote: post #63

Earlier quoted context omitted.

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?

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.

Re: Anyone made the jump from MySQL to PostreSQL? It is worth it?

#103

Post by xemdetia » Tue, Jul 16, 2019, 11:48 PM UTC

dijit wrote: post #7

I've done it twice; the context is completely different on why though. Job #1, we were a heavy PHP/MySQL shop with a smattering of Ruby. I had come from a PG/MySQL shop and knew the merits and pitfalls of both databases (PGs auto vacuum being a particular point of contention at my job before this one), and informed the CIO that going to PG instead of following the upgrade path to MySQL $latest had a number of minor i…

Both of these cases are the exact same reasons why I end up finding MySQL to be concerning especially going forward in time. There are just some use cases that in the current MySQL design with regards to locking that I feel like will never be fixed now that MySQL feels like it's headed further and further towards splintering as an ecosystem. These are the kind of extremely hard, change the major version number level issues, and with the MariaDB/MySQL/cloud MySQL-compatible frontend ecosystem I'm not entirely sure these things will ever get fixed.

Re: Anyone made the jump from MySQL to PostreSQL? It is worth it?

#104

Post by oarabbus_ » Tue, Jul 16, 2019, 11:50 PM UTC

grogenaut wrote: post #80

oarabbus_ wrote: post #63

Earlier quoted context omitted.

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

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.

Re: Anyone made the jump from MySQL to PostreSQL? It is worth it?

#105

Post by grogenaut » Tue, Jul 16, 2019, 11:52 PM UTC

oarabbus_ wrote: post #104

grogenaut wrote: post #80

Earlier quoted context omitted.

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 sh…

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.

Re: Anyone made the jump from MySQL to PostreSQL? It is worth it?

#106

Post by reallydude » Tue, Jul 16, 2019, 11:52 PM UTC

darkpuma wrote: post #67

0815test wrote: post #32

Earlier quoted context omitted.

> ...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

Re: Anyone made the jump from MySQL to PostreSQL? It is worth it?

#107

Post by martin_marques » Wed, Jul 17, 2019, 12:16 AM UTC

wgjordan wrote: post #35

leandrod wrote: post #21

Earlier quoted context omitted.

PostgreSQL underlies at least a plurality of SQL DBMS developments nowadays, specially for scale-out (distributed) projects.

Any sources on that assertion? A quick bit of Google searching [1] shows me only ~10% market share for PostgreSQL, compared to ~46% for MySQL. [1] https://www.datanyze.com/market-share/databases/postgresql-v...

That's a joke right? I mean, you don't measure market share by looking at how many "websites" use one or the other database.

Re: Anyone made the jump from MySQL to PostreSQL? It is worth it?

#108

Post by wbl » Wed, Jul 17, 2019, 1:00 AM UTC

crystaln wrote: post #47

toxik wrote: post #28

Earlier quoted context omitted.

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.

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.

Re: Anyone made the jump from MySQL to PostreSQL? It is worth it?

#109

Post by closeparen » Wed, Jul 17, 2019, 1:19 AM UTC

ken wrote: post #95

IMTDb wrote: post #9

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…

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 Postgr…

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

Re: Anyone made the jump from MySQL to PostreSQL? It is worth it?

#110

Post by beatgammit » Wed, Jul 17, 2019, 2:04 AM UTC

h1d wrote: post #20

Cymen wrote: post #13

I have on all my personal projects but I wouldn't bother with an existing project unless I had a specific need that PostgreSQL met (and MySQL did not). I'm actually all in on PostgreSQL at this point -- I'm not bothering with trying to use vanilla SQL and worrying about switching databases but I have that luxury on my side projects. PostgreSQL has a lot of interesting features that I am happy to use and I frankly nev…

Are you talking about ORM? ORM is such a waste of time to learn as you switch to another language at one point, your time spent on ORM is gone and you don't even learn the underlying tech that is SQL. Stick with SQL and you can keep that skill mostly the same across different SQL products. And obviously you will sooner or later hit some performance penalty when you start writing complex ORM and ORM starts spitting ou…

I used to hate ORMs, but then I found Diesel for Rust, which checks that structs match the database schema, which is really nice when building basic CRUD apps. I ditch it when doing more complex work, but for simple tasks, it saves a lot of time and catches some errors at compile time that might not be caught as quickly at runtime.

Nothing beats knowing the underlying database, but an ORM certainly has its place.