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

Ask HN: PostgreSQL or MySQL?

news.ycombinator.com

Post reply on HN
Print view View original

51–60 of 181 posts

Re: Ask HN: PostgreSQL or MySQL?

#51

Post by evanelias »

rayrrr wrote: post #33

MySQL: Wait a sec, what do I use for this table, InnoDB or MyISAM? What are the tradeoffs again? Oh, right, do I want ACID transactions or fulltext search? Man, this tradeoff sucks. PostgreSQL: Wait a sec, which schema (namespace) should I put this table in? Man, it's nice to have namespaces within a DB.

MyISAM is considered deprecated at this point. It isn't crash-safe and basically should not be used at all, let alone for a new project.

InnoDB has supported fulltext indexes since 5.6 (GA release back in feb 2013). However, to be fair, fulltext indexes in InnoDB are not widely used and have a mixed reputation at best. Large MySQL-based companies tend to lean on external search services e.g. Solr.

I'm not familiar enough with pg's fulltext to compare. I would expect the functionality is superior to InnoDB's, but I am genuinely curious whether large pg-based companies rely on it solely vs using an external search index service.

Re: Ask HN: PostgreSQL or MySQL?

#52

Post by AdrianB1 »

Thaxll wrote: post #35
rayrrr wrote: post #33

MySQL: Wait a sec, what do I use for this table, InnoDB or MyISAM? What are the tradeoffs again? Oh, right, do I want ACID transactions or fulltext search? Man, this tradeoff sucks. PostgreSQL: Wait a sec, which schema (namespace) should I put this table in? Man, it's nice to have namespaces within a DB.

No one has been using MyISAM for the last 10 years.

Unfortunately this is not true, I found it used in the most unexpected places, even new developments as old as 2-3 years.

Re: Ask HN: PostgreSQL or MySQL?

#53

Post by AdrianB1 »

1. For small databases (up to a few GB) my personal preference in the past 5 years was MS SQL (Express Edition, free), then PostgreSQL and last is MySQL. That means the answer to your specific question, PostgreSQL. Why? That is the order of features, ease of use and performance in my experience, other people may have seen different.

For larger databases I have no experience with either PostgreSQL or MySQL, I was spoiled to use MS SQL for up to Terabyte size and that's the largest I've ever worked with.

Re: Ask HN: PostgreSQL or MySQL?

#54

Post by evanelias »

jimktrains2 wrote: post #14
jstewartmobile wrote: post #2

#1: depends on your scenario. Postgres is more like Oracle--there are types of query it can do natively that would have to be externally assisted with MySQL. MySQL is simpler, has all kinds of warts, but scales decently. Here's why uber moved to MySQL: https://eng.uber.com/mysql-migration/ #2-4: My use of SQLite has always been very basic, so no idea. Per SQLite's author: " SQLite does not compete with client/server…

Re: Uber it's not like they didn't cause some of their own issues by keeping very long running transactions. Also, they don't use MySQL directly, my understanding is thqt they wrote their own database and use MySQL as a kv store.

Based on what I have read, "kv store" is a major over-simplification.

A number of companies have built special-case storage services/APIs on top of MySQL. This is not the same thing as writing your own database. In any case, it shows the strength and stability of MySQL for high-volume OLTP use-cases.

Also I don't think "very long running transactions" were the singular core of Uber's problem. InnoDB MVCC doesn't handle those well either; a long-running tx blocks the purge thread and causes a pile-up of old row versions. While the impact of that is less severe in InnoDB than in Postgres, it's still very bad and will lead to performance degradation and undo-log size bloat.

Re: Ask HN: PostgreSQL or MySQL?

#55

Post by evanelias »

dezzeus wrote: post #27

I'm going to write a couple of things about the first point. Several years ago, a knowledgable guy told me that the most compelling reason for choosing between PostgreSQL and MySQL was the expected I/O: "for read-intensive workloads (e.g. blogs), choose MySQL; for mixed workloads (e.g. forums), choose PostgreSQL". But I honestly don't know if that may still be valid as of today. Nowadays, I think that for basic thing…

> Several years ago, a knowledgable guy told me that the most compelling reason for choosing between PostgreSQL and MySQL was the expected I/O: "for read-intensive workloads (e.g. blogs), choose MySQL; for mixed workloads (e.g. forums), choose PostgreSQL".

This seems a bit backwards. MySQL's main strength is OLTP workloads, including mixed read-write workloads / high write volumes. A majority of the giant social networks are using MySQL (or previously used MySQL before moving to in-house custom databases) and have insane write volumes.

For OLAP workloads, or mixed OLTP/OLAP workloads, Postgres tends to be a better choice. OLAP is inherently read-heavy, which is why I would disagree with the advice above.

Re: Ask HN: PostgreSQL or MySQL?

#56

Post by donatj »

MySQL or better yet AuroraDB if you want scalability. MySQL still has a sizable speed advantage, is easier to learn, maintain, and has a more vibrant world of tooling. MySQL is also easier to find experienced developers for. This has been my experience anyway.

Re: Ask HN: PostgreSQL or MySQL?

#57

Post by evanelias »

quicksilver03 wrote: post #22
dfsegoat wrote: post #12

Earlier quoted context omitted.

As someone who has transitioned from MySQL->pg, this makes total sense to me. Partitioning is still a bit better in MySQL IMO (many types / options) - but pg is nearly there.

Did you mean replication rather than partitioning perhaps? I've found MySQL replication quite easy to use, whereas PostgreSQL has been rather clunkier in my experience. However, I've had the exact opposite experience with respect to partitioning.

MySQL partitioning shines with one specific use-case: time-based data retention. e.g., by using RANGE partitioning over a temporal value, you can very efficiently drop large amounts of old data when no longer needed, and can automate this fairly easily.

For anything else, MySQL partitioning rule-of-thumb is you'll have a bad time, mainly for performance reasons.

Re: Ask HN: PostgreSQL or MySQL?

#58

Post by markuman123 »

Use what you're most comfort with, not what others are.

Postgres and MySQL (MariaDB/Percona) are both big players with a huge community.

Maybe consider to use Mariadb or Percona instead of MySQL to avoid oracle vendor lock in.

Post reply on HN