Re: Using PostGres general distribution

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