Re:SQLite or PostgreSQL? It's Complicated
by msbarnett on Tuesday June 28, 2022 @12:39PM
(#31909965)
Sorry, "corruption" was probably misleading there. By "silent data inconsistency/corruption" I mean, "a bug in my application led to me storing strings in an integer column, or too-long data in a too-short column, and SQLite just silently let me do that"-type errors – the data consistency is logically (but not physically) corrupted. The author encountered 2 separate occurrences of this just in their simple use-case, evidently.
> led to me storing strings in an integer column
Recent SQLite versions have STRICT mode where they forbid that.
https://www.sqlite.org/stricttables.html
> or too-long data in a too-short column
You can use a constraint for that:
CREATE TABLE test (
name TEXT NOT NULL CHECK(length(name) <= 20)
) STRICT;
Re:SQLite or PostgreSQL? It's Complicated
by mikepurvis on Tuesday June 28, 2022 @06:12PM
(#31913753)
Neat, will definitely give this a try. Definitely one of my minor peeves about SQLite is having to set a bunch of pragmas on every new connection. Like, especially stuff such as foreign_keys=1... really, enforcement of foreign key constraints is something each connection has to opt into rather than being set one on the whole DB file and never thought about again? Blah.
SQLite is also appropriate for use cases where you are bothered by silent data inconsistency/corruption, it's just that often the hardware running PostgreSQL (or any "serious" DBMS, relational or not) is usually less prone to random inconsistency/corruption (non-interruptible redundant power sources, well cooled environment, running lower but more stable clock speeds on everything, ECC RAM, raid disk array, corruption-resistant FS).
If you run PostgreSQL on the cheapest consumer hardware, expect random corruption of the database when power runs out several times in a row.