Comment by nmpennypacker | Hacker News Reader

Comment by nmpennypacker | Hacker News Reader

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.

> 1. Enforces data types natively.

Quite a while ago MySQL changed the default to be strict on types and not doing truncations anymore. Recently it also (finally) got check constraints.

> 2. JSONB support makes it easy to use a 'hybrid' schema

MySQL has a JSON data type with validation, binary storage, efficient updates and efficient replication.

My experiences are with a pretty old mysql version (5.5, iirc), but that version allowed inserts without supplying a value to a column that is NOT NULL and has no default value.

If that's the strict mode, I shudder to think what the lax mode must be... :(

And upgrade to MariaDB 10.3 caught those cases, so at least there's some progress in the mysql/mariadb ecosystem.

> MySQL has a JSON data type with validation, binary storage, efficient updates and efficient replication.

Postgres has, in addition, indexes over expression, which allows you to build indexes for specific queries into JSONB.

(I'd argue that if you need indexes, it's typically better to extract that data into the regular table and not keep it in the json, but it can still save your butt if you need to react quickly to a requirement change and no time for a data migration).

> My experiences are with a pretty old mysql version (5.5, iirc), but that version allowed inserts without supplying a value to a column that is NOT NULL and has no default value.

With recent MySQL:

create table ttt (col int not null); insert into ttt () values(); ERROR: 1364: Field 'col' doesn't have a default value

> Postgres has, in addition, indexes over expression, which allows you to build indexes for specific queries into JSONB.

MySQL has that, too. I consider this quite useful, especially since MySQL also supports indexing an array of values from a JSON document. (Given `{"colors": ["red", "green", "blue" ]}` a search for `JSON_CONTAINS('$.colors', 'red')` benefits from the index.

Reply on news.ycombinator.com