Re: Is my database now too big?

On 10/7/07, Darren Reed <darrenr+postgres(at)fastmail(dot)net> wrote:
> On Sat, 06 Oct 2007 23:05:23 -0700, "Joshua D. Drake"
> <jd(at)commandprompt(dot)com> said:
> > Darren Reed wrote:
> > > I'm starting to wonder if my database has finally grown too big for my
> > > computer.
> > >
> > > But, rather than peform badly, I see a number of "bad things" happen:
> > >
> > > * pg_dump fails to run, causing an "out of memory" error (I don't
> > > understand
> > > why this happens, but it does);
> > >
> > > * restarting postgres across a reboot results in tables disappearing
> > > without any error messages being given out (this is *really
> > > disturbing*.)
> >
> > Are you vacuuming?
>
> No. I'm just using the standard config
>
> If you're implying I should?
>
> The problems I've come across have become evident with as little work as
> this:
> - run "initdb"
> - start postgres
> - run "psql" to import the dumped data
> - run for a short period of time (less than an hour)
> - shutdown postgres
> - reboot
> - postgres starts
> - the large tables are now missing
>
> With the above, I wouldn't have thought there is enough time to do
> vacuums,
> which seem to take quite a while on large tables. The activity is a
> mixture
> of inserts and updates (from triggers and plsql functions.)

Any reasonably modern version of pgsql should simply stop accepting
requests rather than suffering loss due to txid wraparound. So,I can
think of two possibilities here. Bad hardware or operator error.

Assuming you've checked out your machine thoroughly for bad hardware,
I can see a scenario where one does something like:

begin;
create table xyz;
load 10,000,000 rows
manipulate rows
shutdown db without committing
start database
voila, table xyz is gone, and rightly so.

Got more detailed info on what you're doing?

There's no problem with a few million rows. That's actually a pretty
small pgsql db. I have a reporting db that grows by about 1-2 million
rows a month and takes up 40 gigs, and that's pretty small in the
pgsql universe.