4 ms·
Many reasons for this: (1) Because SQLite is just a function call whereas PostgreSQL is a round-trip message to a separate server process, MRigger was able to
by SQLite 6y ago
Many reasons for this:
(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 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?"
- anarazel 6y ago> (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. 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 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.
- juped 6y agoDo you list the unfortunate design baggage somewhere?
- anarazel 6y agoNot centrally anywhere, as far as I am aware of. 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.
- umvi 6y ago> SQLite has much stronger backwards compatibility guarantees than PostgreSQL. We have to continue to support design errors made decades ago 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. [0] https://sqlite.org/src4/doc/trunk/www/index.wiki https://sqlite.org/src4/doc/trunk/www/index.wiki