Re: PostGreSQL for a small Desktop Application

On 14 Giu, 12:38, p(dot)(dot)(dot)(at)esiway(dot)net (Marco Colombo) wrote:

> While PG has tons more features than SQLite, the major question here is:
> do you really need a database _server_? One thing that PG is designed
> for is handling many (as in 100) concurrent users. Database users, that
> is, meaning processes (running on different computers) opening a
> connection and issueing queries.
>
> Of course, it handles it very well also when those processes all run on
> a single server (and all connections are local connections), such as an
> HTTP server running, say, PHP. That model is very similar to the
> distributed one, since there's no state shared by the httpd/PHP
> processes. All shared state is inside the database server. It also
> happens to be persistant.
>
> [.....]
>
> But you mentioned using C#/.Net. AFAIK (but I'm no expert) that's yet a
> different model. You have a single process (although very likely
> multithreaded) which is able to hold a shared state while serving
> concurrent clients. Here, a database is "just" a backend for persistent
> state (that it, across reboots or crashes). Any good (thread-safe)
> library that writes to files would do. If you need/want SQL, SQLite
> comes into play. Actually, this is what it was designed for. It's much
> easier to install (it's all in a .dll) and administer (close to zero
> administration I think) than PostgreSQL (or any RDBMS). For such an use,
> PG would surely do, but may be just overkill.

Good advices.

My issue here is that i will surely need to access to the same
database from different computer, not many, maybe just a couple but i
can't size my scope to a single machine.
Reading inside SQLite documentation i found this:
"People who have a lot of experience with Windows tell me that file
locking of network files is very buggy and is not dependable. If what
they say is true, sharing an SQLite database between two or more
Windows machines might cause unexpected problems."
http://www.sqlite.org/faq.html#q7

I do prefer to avoid this kind of problem, as mostly my user base will
run on Windows machine. I may probably use SQLite to synchronize
server data with notebooks to allow offline work, but for the main
system even if i do not need the performance (i don't need scalability
and i'm not going high volume) of a real data server, i do need its
reliability handling concurrency.

If i do have two or three users using same data at the same time
(typical use) i need to trust the data system to correctly handle
requests.

Lastly, i will surely take an inner look on ORM i may enjoy the
possibility to switch database system, this way i may not only easily
switch between online and offline mode but even allow my users to
easily install my app in a "single computer" mode (not unusual) as
opposed to normal configuration.