Re: Postgres as the embedded database

On Aug 6, 2008, at 10:04 AM, Mike Gould wrote:

> I am looking to hear from people that are using PostGres as their
> backend in a commerically available product and how much work would
> be needed to ensure that our configurations are optimized for each
> platform along with normal database routines?

You need to ensure that it's vacuumed and analysed regularly.
Depending on your data update patterns autovacuum in 8.3 and later may
be adequate for that, but it may not be.

> What has the stability factor been once installed if the user has
> no administrative / superuser access to the database?

I've not tried that, because I want my users to back up the database
using pg_dump. But I have many users who never touch the database, and
they're perfectly stable after several years of heavy (tens or
hundreds of thousands of transactions a day) 24x7 use. I've had one
case of having to do a manual recovery after a system died badly
enough that RAID10 couldn't save the data, but that's it.

> In our application, we never allow for deletes to happen, the
> records get marked as "deleted", but until a archival process is run
> (normally every 2-3 years) the records cannot be removed.
>
> We have used SQL Anywhere in the past and while I know that the
> PostGreSQL installation will be more complex than what we currently
> have, there are many aspects of the actual database that have made
> us look at it as a replacement to SQL Anywhere in our new product
> line.
>
> I can say that with SQL Anywhere it is pretty much install and
> forget it. I'd like to have it pretty much the same with
> PostGreSQL. With SQL Anywhere we use their internal event processor
> to manage the database when it is needed. We fully expect that we
> will need to write our own external event processor to manage some
> of the database maintenance processes so that isn't really a issue.
>
> We will have several hundred installations and what I don't want to
> do is significantly increase our maintenance costs with PostGreSQL
> to gain some of the functionality that it has (partitioned tables
> being one).

Plan ahead for version upgrades. I have many customers still on 7.4,
because the pain of dumping and restoring to a newer version, along
with the related downtime, is too great. There isn't currently any
viable way to upgrade in place (despite the suggestions of "Use
Slony!" that will inevitably appear).

Cheers,
Steve