Re: PostGreSQL for a small Desktop Application

At 12:34 PM +0200 6/13/07, Magnus Hagander wrote:
>On Mon, Jun 11, 2007 at 10:44:38AM -0700, 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.
>>
>> Knowledge base of my users is very low and "servers" will be standard
>> class desktop computers most probably ran on Windows XP (and Vista
>> later on, i suspect).
>> The service should be enough lightweight to be ran on such "server"
>> and I need silent installation and configuration because i can't
>> expect my user to be able to configure a DBMS.
>
>Silent installation is available and works fine. There will be a bit of
>overhead, especially in disk usage, compared to an embedded database. But
>it's much better than most commercial ones, like MSDE.
>See http://pginstaller.projects.postgresql.org/silent.html.
>
>
>> Additionally i need a passable to good data provider to interface
>> PostGreSQL with .Net which possibly provide better performance than
>> ODBC (don't know if it exists and i hope it is free).
>
>npgsql (http://pgfoundry.org/projects/npgsql) works very well for .net.
>
>
>> Anyway performance shoudn't be a big issue, i expect low concurrency
>> level (less than 10 users) and low to medium volume of rows and
>> queries. If more users and more data are needed for especially big
>> customer i can simply suggest bigger and dedicated server. (different
>> problems will arise for the aggregated data which will feed the web
>> application, but for these we will have a real server).
>>
>> Is PostGreSQL suited for such use? If not which alternatives are there
>> to be used? When using PostGreSQL in such a way is there any
>> suggestion to be followed? Links to sources which i may find
>> interesting (how to make a silent install, basic hardware
>> requirements, so on).
>
>Your other option would be to use an embedded database like SQLite. It has
>a much smaller footprint, but is of course also a lot less capable. But it
>could be worthwhile to check it out.
>
>As for suggestions, well, don't forget to run autovacuum and to schedule
>your backups properly. You can certainly not expect your users to do that
>:) Also, plan and test in advance a method for upgrading the installations
>- you'll always want to be at the latest release in a branch as soon as
>possible after the release.

I'm in almost exactly the original poster's boat. I introduced
PostgreSQL into a rewrite of an old application that ran with an
Access backend, and I'm certainly glad I moved up.

There are a couple of .Net interfaces. The OLE one is OK, but seemed
a lot slower than Npgsql, which really is pretty good performance, at
least for my usage. I've used the ODBC interface for using Crystal
Reports, but not enough so I can measure its performance. I'd
recommend Npgsql.

Right now my backend is running on a Macintosh Xserve, which does a
backup cron task every night, and rsyncs the backups over to another
offsite location. Since my database isn't that big (~30MB), this
isn't a big deal.

I've run Postgres as a server on both Windows and Mac, and it seems
to make a bigger strain on Windows performance than on Mac
performance (probably because the scheduler for multi-tasking on the
Mac suits it better). I'll probably use windows as the installed
backend, but I might just drop in a Mac Mini if that's problematic
(not from Windows, but trying to get space on a client's server).

-Owen