Thread: PostGreSQL for a small Desktop Application

PostGreSQL for a small Desktop Application

From
Gabriele
Date:
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.
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).

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).

Thank you!


Re: PostGreSQL for a small Desktop Application

From
Steve Atkins
Date:
On Jun 11, 2007, at 10:44 AM, 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.
> 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).
>
> 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).

Postgresql is reasonably well suited to such use, as long as
you're prepared to spend a little effort to preconfigure it. We
ship a client-server CRM system that bundles postgresql
as part of the installation, and is mostly run by people with
little database expertise. We have users who are handling
a quarter million tickets a day some days (I'm not sure what
that translates to as transactions, but it's a lot worse than 1:1 :) ).

That's on linux/solaris rather than windows, so the details
will be different, but PG is certainly capable of running, and
running well, in that sort of situation. I've seen discussion
of bundling the postgresql windows installer to do a "silent
install", so it's probably worth searching the list archives
for that. I think there's a decent .net provider for postgresql,
but I'm a C++/Qt guy so I know nothing beyond that.

(There can certainly be business reasons to support other
databases as well as PG, but PG can handle the range
from small single-user to medium sized multi-user quite
nicely).

Cheers,
   Steve



Re: PostGreSQL for a small Desktop Application

From
Magnus Hagander
Date:
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.

//Magnus

Re: PostGreSQL for a small Desktop Application

From
Owen Hartnett
Date:
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

Re: PostGreSQL for a small Desktop Application

From
David Gardner
Date:
I'm not much of a .Net guy, but the pgsql-ODBC driver works rather well for me. Take a look at:
http://psqlodbc.projects.postgresql.org/howto-csharp.html

As for Windows XP, isn't there some limit to the number of incoming network connections?

---
David Gardner, IT
The Yucaipa Companies
(310) 228-2855

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Gabriele
Sent: Monday, June 11, 2007 10:45 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] PostGreSQL for a small Desktop Application

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.
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).

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).

Thank you!


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings


Re: PostGreSQL for a small Desktop Application

From
Gabriele
Date:
I will probably try PostGreSQL for my needs as it seem powerful enough
and easy to ship to my potential customers.
For the data provider i will try npgsql as you and others suggested,
as it seem enough reliable and stable to be used efficiently.

I will probably use newsgroups again later if and when i will have
more specific issue, in the meanwhile i go back to links you have
provided me to read faqs for pgsql and npgsql.

Thanks everybody for your help and suggestions.





Re: PostGreSQL for a small Desktop Application

From
Oisin Glynn
Date:
David Gardner wrote:
> I'm not much of a .Net guy, but the pgsql-ODBC driver works rather well for me. Take a look at:
> http://psqlodbc.projects.postgresql.org/howto-csharp.html
>
> As for Windows XP, isn't there some limit to the number of incoming network connections?
>
> ---
> David Gardner, IT
> The Yucaipa Companies
> (310) 228-2855
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Gabriele
> Sent: Monday, June 11, 2007 10:45 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] PostGreSQL for a small Desktop Application
>
> 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.
> 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).
>
> 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).
>
> Thank you!
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
On the network connections there are limits but the only one that I have
hit is open file shares or named pipe connections. I have been able to
get the number of open sockets very high in testing (though I may be
breaking the license agreement!) to the point where I get an odd buffer
space error (I actually posted on one of the lists about it previously)
but that was under extreem load to breakpoint conditions.

FYI
We are running MS IIS, PostgreSQL, Oracle XE, all of "our" code(7-8 C++
Services) and a VM image of FC6 on a good XP Pro machine with no
complaints so far and the system is providing a real time service in
which delays/interruptions would be noticed.

For high end systems we use Windows 2003 Server

Oisin Glynn

Attachment

Re: PostGreSQL for a small Desktop Application

From
Marco Colombo
Date:
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.

Re: PostGreSQL for a small Desktop Application

From
Greg Smith
Date:
On Thu, 14 Jun 2007, Marco Colombo wrote:

> 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 you
> might even be able to switch between SQLite and PostgreSQL at any time
> w/o even noticing

The main thing to be wary of when trying this is the SQLite deals with
dates and times very differently than PostgreSQL does.  Even when
insulated with an ORM tool that can bite you.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Re: PostGreSQL for a small Desktop Application

From
Gabriele
Date:
On 14 Giu, 12:38, p...@esiway.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.