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

41–50 of 121 posts

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

#41

Post by pmontra »

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

So, along the years I felt sometimes like I have to relearn how to do SQL in arbitrary library X, for the sake of it.

However ORMs have an advantage: no need to change queries when adding/removing fields to the database. No need to deserialize data from resultsets. The usually migrate the database either by applying migrations to the database and infer models (Rails) or syncing the db with the model (Django). Weirdly Ecto forces the developer to both write the migration and the model.

My ideal ORM would be something that lets me write

  sql("select * from employee join department
    on employee.department_id = department.id
    order by employee.id desc limit 10").each do |record|
   
    puts("#{record.employee.id}, #{record.department.name}")
I don't know how that would play with static typed languages but if all ORMs would be like that we could know only SQL and be able to perform arbitrarily complex queries.

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

#42

Post by Beltiras »

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've used the Django ORM professionally for 8 years. Back when I started migrations came in the form of the South app. I can remember one time where the ORM spat out SQL and that was a migration where I botched something in the index of a column. I realized immediately, fixed the error and was done with it. I never once saw anything on Sentry with an SQL error. This is anecdotal and may speak to the genius of Andrew Godwin (and others on the Django team) but it is a data point.

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

#43

Post by [deleted] »

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…

[deleted]

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

#44

Post by Finnucane »

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…

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

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

#45

Post by hrktb »

cwyers wrote: post #10

This strikes me as bike shedding. If you have a specific problem that you believe will be solved by the migration, do it. Otherwise, users will benefit more if you focus on changes based on their value, not what part of your tech stack looks better on Reddit.

It is bike-shedding in a way, but I think it’s also a direction PHP is taking (becoming more strict, more rigid, less “hacky”).

Switching to Postgres follows the same philosophy I think. Migrating existing services is usually a stupid thing to do, but anecdotally I know a number of PHP shops adding pg to their infra and building the new services there.

I’d compare it to the switch to unicode everywhere or normalizing dates on UTC.

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

#46

Post by jerf »

matthewmacleod wrote: post #30
h1d wrote: post #20

Earlier quoted context omitted.

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…

This is bad advice. An ORM is a useful tool for smoothing over the generation of queries and their conversion into object graphs, and make sense for many use cases. It is true that using them effectively still requires knowing the underlying database technology, but that is a given - they are not meant to supplant that knowledge. I will say though that every time I have encountered a codebase developed by a fanatical…

"An ORM is a useful tool for smoothing over the generation of queries and their conversion into object graphs, and make sense for many use cases."

I'm finding it's more powerful to have some language convenient query generator + query -> basic data structure + basic data structure -> final data structure broken into three separate parts, instead of bound together monolithically as most ORMs do it. Those steps are all useful on their own merits, but it's really quite frequent that for some specific task the prepackaged monolithic ORM is not what I need; either I need to tweak the query, or I want to get a basic data structure from some other source (JSON or something) and want to be able to reuse the logic, or I need a tweak to how I'm processing it down to an object (e.g., processing it into a class that is standalone vs. one that is integrated with the rest of the world, the "banana vs. a jungle containing a gorilla holding a banana" sort of thing Joe Armstrong talked about), or I want to create data structures from a complex query involving joins, aggregates, etc. that I can't necessarily express as "a class that represents a table" or something.

It's not the three tools that are the problem; it's the inflexibility of jamming them all together in one shot, along with the semantic contradictions encountered while trying to make one class be the query generator, the basic data structure representing the query, and the "final" data structure, all at once, even before we consider the affordance of the ORM that really, really encourages you to make DB structures to OO classes instead of queries.

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

#47

Post by crystaln »

toxik wrote: post #28
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…

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.

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

#48

Post by status_quo69 »

pmontra wrote: post #41
h1d wrote: post #20

Earlier quoted context omitted.

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

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

#49

Post by cwyers »

hrktb wrote: post #45
cwyers wrote: post #10

This strikes me as bike shedding. If you have a specific problem that you believe will be solved by the migration, do it. Otherwise, users will benefit more if you focus on changes based on their value, not what part of your tech stack looks better on Reddit.

It is bike-shedding in a way, but I think it’s also a direction PHP is taking (becoming more strict, more rigid, less “hacky”). Switching to Postgres follows the same philosophy I think. Migrating existing services is usually a stupid thing to do, but anecdotally I know a number of PHP shops adding pg to their infra and building the new services there. I’d compare it to the switch to unicode everywhere or normalizing…

I think a lot of people have a mental model of MySQL that's stuck in the 4.x days. You can get most of these proposed benefits by "just" upgrading to the latest MySQL and making sure that you're running it in strict mode.

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

#50

Post by barrkel »

brightball wrote: post #31

I'm a huge PG fan but switching your database on an existing project is a non-small undertaking. If you're starting from scratch, then yes, you take PG every time. There's a lot...a lot that goes into saying that and if you haven't made the jump, a lot of the reasons aren't going to seem important because you currently "don't do" the things that PG lets you do with your data. Like this: https://www.brightball.com/art…

Text column type is not where I'd advertise pg differences. Rather a much richer SQL query feature set and a smarter (though sometimes smartass) query optimizer.

MySQL is dumb (though predictably dumb) and queries are single threaded, and the feature set is limited. Some queries have no efficient representation, and you need to trade off cost of materialising derived table vs repeated correlated queries.

Post reply on HN