Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

old.reddit.com

Post reply on HN
Print view View original

71–80 of 121 posts

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

#71

Post by Xamayon »

As much as I like PostgreSQL's features, MySQL does have advantages in some situations.

The copy on write/mvcc type stuff in particular has bit me hard in the past. It's great in most cases but can cause some serious issues with huge tables where every row is updated many times. Even with frequent table cleanup/analyze/etc, the tables bloat up and can become completely unworkable whereas in MySQL there are no problems at all. Symptoms are things like the table sizes growing hundreds of GB larger than the data, queries which should use indexes becoming table scans, etc. The existence of multiple versions of a row seems to really upset the query planner in some cases. I have a table which takes several hours to do a basic conditional count in PostgreSQL, which will finish in milliseconds on MySQL.

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

#72

Post by LoSboccacc »

munk-a wrote: post #52
Finnucane wrote: post #44

Earlier quoted context omitted.

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

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.

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

#73

Post by laurencerowe »

bobviolier wrote: post #22
bhouston wrote: post #2

We use PostreSQL. We ran into the issue with Google Cloud SQL (PostreSQL-flavored) that we can not make it multi-region, only multi-zone. With Google Cloud SQL (MySQL-flavored) you can do multi-region. I would feel much better if we had multi-region failover with PostreSQL just in case a Google Data Center region (which contains the multi-zones) goes completely down (as it did in early June 2019.) We have some manual…

You also can't do multi-region on Google Cloud SQL (MySQL). The only thing you can do is run your replica on your own VM and configure it to read from the Master in Google Cloud SQL. (Or I am completely missing something)

> Cloud SQL for PostgreSQL does not yet support replication from an external master or external replicas for Cloud SQL instances.

https://cloud.google.com/sql/docs/postgres/replication/

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

#75

Post by nsxwolf »

imhoguy wrote: post #53

If you need specific PostgreSQL features then yes, otherwise switch to MariaDB instead.

I was a bit bummed when I started my new gig at a MySQL shop after spending a few years enjoying all the new recent features of PostgreSQL. But then we switched to MariaDB and it basically has everything.

Definitely give it a look before considering switching.

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

#76

Post by munk-a »

LoSboccacc wrote: post #72
munk-a wrote: post #52

Earlier quoted context omitted.

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

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.

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

#77

Post by deusex_ »

jsilence wrote: post #74

Isn't that about just switching the db driver in the ORM? Sorry for being sarcastic, but was that not exactly the point of countless database abstraction layer projects?

Uh, no? The idea is to have a full featured object mapping layer that can work with multiple database backends.

Doing mapping of db result sets to object is non trivial, no need to do develop it multiple times for each RDMBS when 90% of the code would overlap.

I'd work on the sarcastic attitude though

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

#78

Post by paulryanrogers »

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?

Possibly JSON operators

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

#79

Post by pmontra »

status_quo69 wrote: post #48
pmontra wrote: post #41

Earlier quoted context omitted.

I'm working at three projects with three languages and three different ORMs so you have my sympathy. They're the ORMs of Django, Rails, Phoenix. Rails' got the easiest ORM to use by far, maybe because IMHO it's now the closest to SQL. Phoenix's Ecto is needlessly complicated and Django's is as verbose as Python's libraries can get. Example: Model.objects.get(), because nobody could understand Model.get(), right? /s S…

Sounds like you don't want an ORM but a DB driver like psycopg2 http://initd.org/psycopg/docs/cursor.html#fetch

I know psycopg2 and other drivers for several languages. They're ok to make queries but they lack everything else ORMs are good at.

I want an ORM, but a smarter one and as similar as possible across languages. Instead everybody reinvented the wheel in every language, with different approaches, more or less over engineering their solution and making the polyglot experience as difficult as learning English and Russian instead of Italian, French, Portuguese, Spanish (learn one, you'll get the others quickly.)

My background is that I learned SQL almost 10 years before the first Java ORMs went mainstream. I've been using ORMs since the 90s but for writing queries they're a kind of waste: the query in the example in Rails is

  Employee.joins(:departments).
    limit(10).
    order_by("employees.id desc")
at the cost of

1) defining some relationships between the tables in the models

2) learning how to translate SQL in Ruby

ActiveRecord is magically convenient most of the time. Sometimes it's quicker to write that SQL, sometimes is not. Django (I can't remember the name of the ORM) and Ecto are nearly always slower to write than SQL, especially Ecto.

For complex queries SQL is the only way to go in any framework and the ORM is there only to deserialize the results, so why not using it for simple ones too?

  Employee.find(params["id"])
is about the limit.

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

#80

Post by grogenaut »

oarabbus_ wrote: post #63
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…

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.
Post reply on HN