On Aug 26, 2007, at 9:02 AM, Dawid Kuroczko wrote:
On 8/26/07, Kevin Kempter <kevin@xxxxxxxxxxxxxxxxxxx> wrote:
On Saturday 25 August 2007 21:10:19 Ron Johnson wrote:
On 08/25/07 21:51, Kevin Kempter wrote:
Hi List;
I have a very large table (52million rows) - I'm creating a copy
of it to
rid it of 35G worth of dead space, then I'll do a sync, drop the
original
table and rename table2.
What is your definition of "dead space"?
Bad rows, duplicate rows, old rows? Something else?
deleted rows that should have been cleaned up with vacuum, problem
is the
client let it go so long that now I cant get a vacuum to finish
cause it
impacts the day2day operations too much. Long story, see my
recent questions
on the performance list for more info.
In your place I would do something like Slony-I does, when
it replicates the tables. Create on insert/update/delete triggers
on table1 which will log operations on table1 to some table1_log
table. Then copy table1 to table2. Then replay table1_log on
table2, then BEGIN;LOCK tablel1;finish replaying the lock;DROP table1;
alter table rename...;commit;
Or perhaps actually use Slony-I for the above steps? Should work
quite nicely... Or perhaps use SkyTools for it (I've never used it)?
Yeah, for trigger based replication it'd be simpler to just use Slony-
I or Skytools. However, if you're on 8.2, with row-wise comparisons,
you could do something like:
begin;
lock table1;
insert into table2
select *
from table1
where id not in (select id from test2);
drop table1;
alter table2 rename to table1;
commit;
Here id is your primary key. Note that if your ids are generated by
a sequence you'll need to use setval on the sequence to get it
"caught up" before that commit or you'll get duplicate key errors
immediately.
Erik Jones
Software Developer | Emma®
erik@xxxxxxxxxx
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
