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

81–90 of 121 posts

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

#81

Post by zaphar »

cwyers wrote: post #38
pmontra wrote: post #34

Earlier quoted context omitted.

This and window functions and safety of data. I'm using MySQL and PostgreSQL in different projects (customers choice) and sometimes there are things I can't do in the MySQL one or would be much more complicated. Sometimes MySQL silently slips bad data in the db because it truncates strings that don't fit varchars or those impossible 0000-00-00 00:00:00 dates. To be fair, the latter are not much of a problem. I'd go P…

MySQL now has window functions: https://mysqlserverteam.com/mysql-8-0-2-introducing-window-f... And MySQL has strict mode, which I believe is enabled by default on newer versions but available in any 5.x version: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mo...

Strict mode is like 1/4'th of the safety that PostgreSQL gives you. Robust types for columns. Safe transaction semantics. Better tools for enforcing data consistency.

MySQL has a long way to go before it can give you the same security for you data that PostgreSQL does.

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

#82

Post by paulryanrogers »

marcosdumay wrote: post #69
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…

That. PG won't silently truncate your texts. It also won't accept 0000-00-00 dates. MySQL has enough data corruption bugs to last for a lifetime. It is also maintained by Oracle, what means the bugcount is likely going up instead of down.

FWIW, there are newer defaults and modes that help with old quirks.

Sadly MySQL still lacks TIMESTAMP WITH TIME ZONE.

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

#83

Post by yellowapple »

Not being at the mercy of Oracle's whims is enough justification to consider migrating to MariaDB at the very least. MySQL is (miraculously) still alive for now, but it wouldn't be the first case of Oracle letting one of its acquisitions wither and die a slow, painful death.

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

#84

Post by jerf »

rlpb wrote: post #61
jerf wrote: post #46

Earlier quoted context omitted.

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

Have you looked into sqlalchemy? I think it would meet some of your requirements; I'm not sure about the rest.

It is one of my favorites, yes, but alas, only works when I'm in Python, which for me personally is not very often. YMMV.

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

#85

Post by paulryanrogers »

Xamayon wrote: post #71

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

MySQL's InnoDB is MVCC as well, at least when used with transaction isolation.

That said, MySQL does have more mature replication options built-in.

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

#86

Post by e12e »

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?

Yes and no. The point of an actual ORM (object relational mapper) is to be able to the equivalent of lisp/smalltalk (runtime) image level perstiance: make objects permanent between restarts and changes transactional.

To really get there, you actually need a object db, like zoodb[z] for python (which incidentally can use pg as a storage back-end).

If you're using a db abstraction layer, and a typical active record like surrogate keys - then for simple use cases you can indeed switch back-ends; many rails projects run tests on sqlite and production on postgresql.

That's not a great idea, but it works for simple projects (first gotcha; most rdbms don't have a global namspace for indexes; sqlite does).

But there's no standard AFAIK for full text indexes, geodata, json/bson, materialized views... So it depends a lot on how much of the db you're actually using.

[z] http://www.zodb.org/en/latest/tutorial.html

Note that an object db does not free you from thinking about data structure migrations:

http://www.zodb.org/en/latest/guide/writing-persistent-objec...

https://pypi.org/project/zope.generations/

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

#87

Post by nathan_long »

I probably wouldn't migrate a project, but I switched my default db to PostgreSQL after seeing the many ways MySQL can silently screw up data. Truncating strings, truncating numbers, allowing NULLs in a NOT NULL, etc. Much (all?) of this can be fixed with settings these days: https://dev.mysql.com/doc/refman/5.7/en/constraint-invalid-d...

PostgreSQL has a hard bias toward correctness. If you give it invalid data, it will blow up loudly every time.

PostgreSQL also has a boatload of useful features. Need full-text search? It has it, and it's good enough for lots of stuff: http://rachbelaid.com/postgres-full-text-search-is-good-enou...

Need to search for locations with coordinates Need to store and query JSON? Partition tables by a date range? Index only the rows matching a WHERE condition? Use a CHECK constraint? Prevent race conditions from creating reservation rows with overlapping datetime ranges (exclusion constraints)? Run a query periodically and cache the results as a queryable table (materialized views)? The list goes on and on.

More correct data + fewer reasons to add another tool to the stack - especially when that would mean having to keep data in sync - is pretty compelling to me.

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

#88

Post by pmontra »

slig wrote: post #54
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…

> Example: Model.objects.get(), because nobody could understand Model.get(), right? /s That has a simple explanation: each Model can have multiple Managers [1], and the `objects` is the default one. Also, by convention, methods on the Model are usually relative to "one DB record", and methods on the manager are relative to N records. [1]: https://docs.djangoproject.com/en/2.2/topics/db/managers/

Thank you, I know now why Django inflicts that .objects method to everybody :-)

I'd rather design a less verbose API for the default manager

  Model.get()
  Model.filter()
  ...
and Model.manager.get() if one really needs custom managers.

The fact that I didn't know about managers after years of Django (and nobody told me) should be telling of me and my team, of course, but also how needed managers are. Having to go through that API without shortcuts is not nice to developers.

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

#89

Post by LoSboccacc »

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

Earlier quoted context omitted.

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

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.

Post reply on HN