Ask HN: PostgreSQL or MySQL?

#1

Post by ReD_CoDE » Sun, Sep 22, 2019, 10:27 AM UTC

I have four questions related to SQL

1. PostgreSQL or MySQL? And why?

2. Is it possible to build a hybrid database schema? For example, SQLite+JSON?

3. Is it possible to convert XML(XMI) schema to SQLite schema automatically?

4. Is it possible to build a custom file format based on SQLite or hybrid one based on SQLite+JSON?

Re: Ask HN: PostgreSQL or MySQL?

#2

Post by jstewartmobile » Sun, Sep 22, 2019, 10:56 AM UTC

#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 databases. SQLite competes with fopen()."

Re: Ask HN: PostgreSQL or MySQL?

#3

Post by fouc » Sun, Sep 22, 2019, 11:13 AM UTC

1. Both are great, they're both absolutely fantastic products!

PostgreSQL does have slightly more popularity nowadays, especially since they came out with JSONB support first and have more advanced features built-in.

Re: Ask HN: PostgreSQL or MySQL?

#4

Post by matthewmacleod » Sun, Sep 22, 2019, 11:17 AM UTC

On the first point, default to using Postgres unless you have a particular reason not to. In general, it offers more and better-executed features. There are some scenarios where MySQL offers a benefit and this advice doesn’t apply, but it’s unlikely you will need to worry about that if you’re in a “what database do I use” position.

Your other questions are a bit too vague to be answerable. It’s possible to build anything you want, including custom file formats or schema converters, if you want to invest the time. You might have more useful responses if you explain the use case instead of the solution you already have in mind.

Re: Ask HN: PostgreSQL or MySQL?

#5

Post by viraptor » Sun, Sep 22, 2019, 11:26 AM UTC

What does your app/framework support as a default store (what do the devs use and test against).

What features do you expect from the db? What versions of each database can you run?

Have you got an environment where one option is better supported than the other? (For example AWS aurora cluster and MySQL?)

Do you have an ops team with existing experience with either db?

Re: Ask HN: PostgreSQL or MySQL?

#6

Post by nbouscal » Sun, Sep 22, 2019, 11:28 AM UTC

The following isn’t the top reason I recommend Postgres, but is the reason I think least likely to be echoed in a dozen other comments:

Postgres has some of the best documentation of any software product I’ve ever used. If someone wants to learn about SQL or databases, I always have to restrain myself from recommending that they just read the Postgres manual front to back. It’s comprehensive, it’s well written, it’s easy to navigate, and almost every explanation comes with several clear examples. It’s hard to overstate how valuable a property that is for a product as central to your architecture as your primary transactional database.

Re: Ask HN: PostgreSQL or MySQL?

#7

Post by gtsteve » Sun, Sep 22, 2019, 11:28 AM UTC

When considering between PostgreSQL and MySQL bear in mind that client-side drivers are licensed differently. I believe the MySQL connector is still GPL licensed; it used to be LGPL and then the license was changed with little fanfare. Commentators called this "weaponising the GPL". The goal is to get you to buy the enterprise version of MySQL.

I know MySQL way better but at a brief look I can see Postgres drivers are licensed more liberally.

Re: Ask HN: PostgreSQL or MySQL?

#8

Post by Arkanosis » Sun, Sep 22, 2019, 11:30 AM UTC

Regarding 1): choosing a software stack (or any part thereof) without a clear use case is a recipe for failure. What's the use case?

Re: Ask HN: PostgreSQL or MySQL?

#9

Post by steffann » Sun, Sep 22, 2019, 11:37 AM UTC

I have used PostgreSQL+JSONB and it works great. Doing indexes on JSON fields adds a lot of value.

Re: Ask HN: PostgreSQL or MySQL?

#10

Post by nmpennypacker » Sun, Sep 22, 2019, 11:38 AM UTC

Personally,PostgreSQL. Both have their advantages and disadvantages, but my standard arguments in favor of Postgres are that it: 1. Enforces data types natively. I've shot myself in the foot before with MySQL and 'polluting' data. 2. JSONB support makes it easy to use a 'hybrid' schema if you want NoSQL-like behavior or a junk drawer to shove JSON into.

Regarding your conversion questions, it's common to have an app that communicates with several databases, but I don't know of any architecture that would allow ACID transactions in two engines simultaneously.

As far as converting data, it all depends on what your development preferences are. I work mostly in the Ruby ecosystem, and I can tell you that I've used ActiveRecord to a Postgres database, and exported the results into a SQLite file. I'm sure it would be possible to use an XML parser like Nokogiri to convert XML into a SQLite file as well. If you prefer another major programming language, the tools likely exist to accomplish the same thing.