Message-ID: <27ef17e6-7fe2-fe5e-5338-776207cd17e0@net-c.cat>
Date: Wed, 14 Dec 2022 13:50:02 +0000
MIME-Version: 1.0
Reply-To: github-siiky@net-c.cat
Subject: Re: SQLite as DB alternative
To: Jiri Vlasak <jiri.hubacek@gmail.com>
Cc: ~qeef/damn-project@lists.sr.ht
References: <bd4eeb77-f669-9aa4-8e20-58e67003bb22@net-c.cat>
 <Y5i8UQLaHmpKQ7Lx@gmail.com>
From: siiky <github-siiky@net-c.cat>
In-Reply-To: <Y5i8UQLaHmpKQ7Lx@gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Thanks for the reply!


There's at least SpatiaLite[0] as a GIS extension for SQLite. How good
it is, and how it compares to PostGIS (feature-wise and language-wise) I
don't know, I haven't used either.


RE loads: that makes sense, though it's possible to tweak SQLite for
better performance in concurrent contexts (such as enbaling WAL
mode[1][2]).  It's also possible to serialize SQLite access (e.g. with a
"proxy class"), and with more SQLite tweaks (allowing it to assume
single-process and single-thread[3], plus [4]), I doubt it would be much
slower than Postgres.

(Aside: I had a big performance improvement by enabling WAL mode in a
two-writers scenario, where one of them rarely writes and the other
mostly reads. But I can't comment much more than that, I would need to
try things out in context.)


For context, what prompted the email was that I'd like to use DAMN for
small (1 person) tasks and but feel it's overkill to use your public
instance. So I'd like to run DAMN on my desktop/laptop but I'm not too
keen on having to setup Postgres+PostGIS (it's just overkill for this
usecase).


[0]: https://www.gaia-gis.it/fossil/libspatialite/index
[1]: https://www.sqlite.org/wal.html
[2]: https://www.sqlite.org/pragma.html#pragma_journal_mode
[3]: https://www.sqlite.org/faq.html#q6
[4]: https://www.sqlite.org/pragma.html#pragma_locking_mode