-1

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?

closed as primarily opinion-based by Marco, hot2use, mustaccio, John Eisbrener, Erik Darling Sep 7 '17 at 14:40

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Why downvote? This is not a marketing or political question expecting opinionated answers. Very concrete, named features and facts drive the decision. – hgoebl Sep 7 '17 at 13:21
  • 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! – Vérace Sep 7 '17 at 15:05
4

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.

  • My understanding is that Postgres' full text search is also more flexible and feature richt then MySQL's (as hgoebl mentions needing to do "case insensitive full text search) - but I have never (serieously) used MySQL and I have never used the full text search in Postgres – a_horse_with_no_name Sep 7 '17 at 11:37
  • Many thanks for this detailed answer. I'm convinced and there is no doubt about the decision. – hgoebl Sep 7 '17 at 13:16
  • Thanks! I'm just doing another answer - no REGEXP_REPLACE in MySQL either! See here! – Vérace Sep 7 '17 at 14:27
  • REGEXP_REPLACE is in MariaDB, along with PCRE. – Rick James Sep 7 '17 at 14:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.