Thread: 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

On Wed, Aug 6, 2008 at 1:04 PM, Mike Gould <mgould@allcoast.net> 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?  What has the stability factor been once installed if the
> user has no administrative / superuser access to the database?  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).

it can work -- autovacuum makes things a lot easier particularly if
the database isn't *too* busy.

your main areas of concern should be:
*) user account problems on windows (win32 changing password of
postgres account for example)
*) upgrades
*) security

for various reasons, it will be impossible to hide that you are using
postgresql.  not that that's a bad thing, but you have to understand
that.

merlin