Skip site navigation (1) Skip section navigation (2)

Re: Using PostGres general distribution

From: Robert Treat <robert(at)omniti(dot)com>
To: pgsql-general(at)postgresql(dot)org, mgould(at)allcoast(dot)net
Subject: Re: Using PostGres general distribution
Date: 2008-07-31 20:17:46
Message-ID: 200807311617.47180.robert@omniti.com (view raw or flat)
Thread:
Lists: pgsql-general
On Thursday 31 July 2008 15:28:14 Mike Gould wrote:
> We currently use SQL Anywhere 9.0.2 as our database in our current product.
>  The main reason is the low maintenance that is required and the
> installation is a breeze.  All we need to do is to ship 3 dll's and a db
> and log file. I understand that with PostGres that the installation will
> end up being much more complex, however that doesn't really worry me as
> much as how much "administration" of the database is needed.

IIRC, SQL Anywhere is designed to work as an embedded database, with 
particular points towards zero administrative overhead.  That's not the 
design goal of PostgreSQL, though it can (and is) used for the underlying 
piece of many products. 

> SQL Anywhere 
> has an event processor built in to make doing database backups while the
> system is online very easy. 

you can do backups of postgres under normal operations using pg_dump. you'll 
need to craft your own scheduler for this, as postgres doesn't include one. 

> We also are able to do certain types of 
> maintenance such as selective reorganize of tables, automatically adding
> additional free space at night so that it doesn't affect performance during
> processing hours and many other functions.
>

Most DML operations in postgres can be run inside a transaction, and postgres 
is not in the habit of creating artificial constraints for index/tablespace 
size as some other databases.  The main key for you will probably be to turn 
the autovacuum daemon on, which will do most of the cleanup work you would 
need to have done regularly automagically. 

> If we had 1500 customers running our system with PostGres and we have
> little control over the server hardware, the OS the customer would be
> running the db on, is Postgres the appropriate choice or is this going to
> be a maintenance nightmare?  How self sufficient is Postgres?
>

I know postgres is used in many kiosk type systems, where there is little to 
no ongoing maintenance for those machines which sound similar to what you 
might be looking at.  One of the clients my company works with is doing 
something similar to this, distributing a postgres backed application 
designed to be run with little/no postgres maintenance overhead. It's more 
like a data appliance than an embedded app (it deals with 100's GB of data), 
but seems so far has been very doable.  

Most of the trouble scenarios that are involved are when you have no control 
over usage patterns... Ie. someone has direct access to the database, and 
they do massive dumps and reloads of data you haven't designed into your 
original expectations; but if you have controlled access to the server, it's 
likely postgres can work in that scenario. (The other problem spots is server 
upgrades, but you can probably go years on a particular version before that 
becomes really problematic, it just depends on what your applications 
lifecycle looks like)

-- 
Robert Treat
Database Architect
http://www.omniti.com

In response to

pgsql-general by date

Next:From: Lennin CaroDate: 2008-07-31 20:42:00
Subject: Re: CAST(integer_field AS character) truncates trailing zeros
Previous:From: Richard BroersmaDate: 2008-07-31 20:17:24
Subject: Re: CAST(integer_field AS character) truncates trailing zeros

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group