-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?

| improve this 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.

| improve this answer | | | | |
  • 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.