Re: PostGreSQL for a small Desktop Application - Mailing list pgsql-general

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.