From: Josh Berkus <josh@agliodbs.com>
To: Karim Nassar <karim.nassar@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Delete query takes exorbitant amount of time
Date: Thu, 24 Mar 2005 21:24:39 -0800
Message-ID: <200503242124.39558.josh@agliodbs.com> (raw)
In-Reply-To: <1111715304.9089.179.camel@k2.cet.nau.edu>
References: <1111709457.9085.127.camel@k2.cet.nau.edu>
<2630.1111711927@sss.pgh.pa.us>
<1111715304.9089.179.camel@k2.cet.nau.edu>
Karim,
> Problem now is: this referencing table I expect to grow to about 110
> million rows in the next 2 months, then by 4 million rows per month
> thereafter. I expect that the time for recreating the foreign key will
> grow linearly with size.
>
> Is this just the kind of thing I need to watch out for? Any other
> suggestions for dealing with tables of this size? What can I do to my
> indexes to make them mo' betta?
How about getting some decent disk support? A single 10K SCSI disk is a bit
sub-par for a database with 100's of millions of records. Too bad you didn't
get a v40z ...
Beyond that, you'll want to do the same thing whenever you purge the
referencing table; drop keys, delete, re-create keys. Or think about why it
is you need to delete batches of records from this FKed table at all.
--
Josh Berkus
Aglio Database Solutions
San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com, karim.nassar@acm.org
Subject: Re: Delete query takes exorbitant amount of time
In-Reply-To: <200503242124.39558.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox