(1) Because SQLite is just a function call whereas PostgreSQL is a round-trip message to a separate server process, MRigger was able to run many more test cases per second on SQLite.
(2) We fixed bugs faster in SQLite, allowing MRigger to continue testing SQLite sooner.
(3) SQLite has much stronger backwards compatibility guarantees than PostgreSQL. We have to continue to support design errors made decades ago, whereas PostgreSQL gets to walk away from their poor design choices with each major release. For this reason, SQLite is rather more complicated than you might imagine.
(4) Many of the bugs found by MRigger had to do with the innovative (and controversial) decision by SQLite to use flexible typing rather than strict, rigid typing. SQLite allows you to put text into an INT column, for example. PostgreSQL has a more traditional design that simply does not allow that kind of thing, and hence many of the bugs found by MRigger are simply not applicable to PostgreSQL.
(5) The PostgreSQL developers are very clever people and write some of the best software around. When we were developing the cross-DBMS "sqllogictest" test suite for SQLite (https://www.sqlite.org/sqllogictest/doc/trunk/about.wiki) we were able to crash every DBMS we tried it on, except for PostgreSQL. To this day, when somebody has questions about whether or not the behavior of SQLite is correct, our reflexive reply is "What Does PostgreSQL Do?"
Yea, that made it harder in the past for other test tooling too. IIRC Greg Stark fuzzed our regex library and had to fight against the more complicated interaction due to client / server to make that work. Ended up finding quite a few things...
> (3) SQLite has much stronger backwards compatibility guarantees than PostgreSQL. We have to continue to support design errors made decades ago, whereas PostgreSQL gets to walk away from their poor design choices with each major release. For this reason, SQLite is rather more complicated than you might imagine.
FWIW, we (pg devs) are pretty hesitant to break backward compat. Sure, each release has a few things, but it's usually pretty corner case-y stuff. Check e.g. the list for the upcoming v13: https://www.postgresql.org/docs/13/release-13.html
There's a lot of significant design errors we're continuing to support just because it'd be too painful to break compat.
There's a few user visible one that need explicit options to be enabled, and there's documentation for those, of course.
There's other where there's plenty source code level comments explaining the issues.
And some others that "just" are mentioned in discussions.
I can come up with examples if you're interested.
Any plans for SQLite4 that will let you get a fresh start and break backwards compatibility so that you can simplify, correct design flaws, etc.? I saw this[0] so I'm guessing it'll just be SQLite3 for now.