When using SQLite for offline client, better use MySQL or PostgreSQL for server? [closed]

Question

Closed. This question is opinion-based. It is not currently accepting answers.

---

Want to improve this question? Because this question may lead to opinionated discussion, debate, and answers, it has been closed. You may edit the question if you feel you can improve it so that it requires answers that include facts and citations or a detailed explanation of the proposed solution. If edited, the question will be reviewed and might be reopened.

Closed 8 years ago.

Improve this question

I'm developing an application which uses embedded SQLite for local storage.

My application will replicate changes to internet server (via REST/HTTP). There I can choose between MySQL/MariaDB or PostgreSQL.

My db is pretty "normal", many tables (~80), mostly with few columns (max. ~30), many M:N relationships, some of the tables carry text data (only a couple of paragraphs in length). I use some kind of compressed UUIDs as primary key because they have to be globally unique.

Being able to do case-insensitive full-text searches is almost critical.

I won't use JSON, triggers and stored functions/procedures.

From a feature and compatibility point of view (datatypes, behavior of NULL, LIKE, indexes, BLOB-handling), which db-server should I use?

• hgoebl: Why downvote? This is not a marketing or political question expecting opinionated answers. Very concrete, named features and facts drive the decision.
• Vérace: To show that there's no anti-commercial opnion-based (:-)) bias, you might also wish to take a look at Zumero here - it's a commerical SQLite to RDBMS server replication and synchronisation solution - it is MS SQL Server, but I've read Sink's book on the business of software and he appears to know what he's talking about - disclaimer - I have no connection with Zumero!

Answer 1

I am literally screaming at my screen "Use Postgresql!"

SQLite is the protégé of PostgreSQL. That link is to a talk given by D. Richard Hipp (the inventor, and still primary developer, of SQLite) to PGCon 2014.

Furthermore, if you look at the SQLite wiki, you will find the lines:

SQLite uses PostgreSQL as a reference platform. "What would PostgreSQL do" is used to make sense of the SQL standard.[9][10] One major deviation is that, with the exception of primary keys, SQLite does not enforce type checking; the type of a value is dynamic and not strictly constrained by the schema (although the schema will trigger a conversion when storing, if such a conversion is potentially reversible). SQLite strives to follow Postel's Rule.

PostgreSQL supports CHECK constraints whereas MySQL (astoundingly) does not! MySQL's support for Window/Analytic functions and Common Table Expressions (CTEs - also known as the WITH clause) is only in Beta - PostgreSQL has had them for years!

Overall, technically, PostgreSQL is a vastly superior database.

MySQL is as widespread as it is due largely to luck and good marketing - it ran natively on Windows (PostgreSQL does now also) back in the mid-nineties, and went GPL before the first internet boom.

Even if this question were not specifically about SQLite, I would unhesitatingly (99.9% of circumstances) recommend PostgreSQL over MySQL - PostgreSQL also has advanced full-text capabilities [1, 2] and would also be a much better fit for this sort of functionality - out of the box (thanks to @a_horse_with_no_name) - no need for secondary tools like Sphinx or Lucene.

If you are making (ab)use of the "well known quirk" of SQLite that it "does not enforce type checking" - you will have problems when replicating - although you will have the same problems with MySQL also.

• hgoebl: Many thanks for this detailed answer. I'm convinced and there is no doubt about the decision.
• Vérace: Thanks! I'm just doing another answer - no REGEXP_REPLACE in MySQL either! See here!
• Rick James: REGEXP_REPLACE is in MariaDB, along with PCRE.