Re: PostGreSQL for a small Desktop Application

Gabriele wrote:
> I'm going to develop a medium sized business desktop client server
> application which will be deployed mostly on small sized networks and
> later eventually, hopefully, on medium sized networks.
> It will probably be developed using C#.
> 
> I do need a solid DBMS wich can work with .Net framework. I do know
> PostGreSQL is a good DBMS in general (it sports most of the advanced
> DBMS features, transactions and stored procedure included) but i
> wonder if it is suited for my application. 

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.

Technically, that's not simply client/server, it's 3-tier, with 
httpd/PHP processes being multiple instances of a middle layer. As far 
the database server (PG) is concerned, those are (multiple) clients.

In this scenario PostgreSQL is at home, being that what it's designed 
for. To tell the truth, *any* serious RDBMS out there would do. SQLite 
won't, tho, since it's not a server at all - it's just a library.

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.

PG still has advantages vs. SQLite, being more featured (do you need 
stored-procedures?). But if you plan to use an ORM tool for .Net
(see: http://www.google.com/search?q=ORM+.Net) you might even be able to 
switch between SQLite and PostgreSQL at any time w/o even noticing (be 
sure of choosing one that supports both backends, of course).

I'm a big fan of both PG and SQLite, and happily use them. When I design 
an application, I ask myself: is this going to be a strongly database 
oriented app, with potentially different implementations of the 
middlelayer, or just a server that happens to need a solid and nice way 
to access data on disk? If you can answer to that, the choice is 
natural: use different tools for different purposes. But also remember 
that PG can functionally replace SQLite anywhere, but not the other way 
around. If you have room enough in your toolbox for just one tool, go 
PostgreSQL. I think the best thing about PG is that it's a terrific 
general purpose tool: a full RDBMS, extremely reliable, with no 
compromises, almost covering anything you might need in the features 
area (even more if you consider how easy is to extend it), yet light 
enough to be easily embeddable.

.TM.