Обсуждение: 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!
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
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
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
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
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.
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
Вложения
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.
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
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.