Skip site navigation (1) Skip section navigation (2)

Re: selects from large tables

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Nikk Anderson <Nikk(dot)Anderson(at)parallel(dot)ltd(dot)uk>
Cc: "'Charles H(dot) Woloszynski'" <chw(at)clearmetrix(dot)com>,pgsql-performance(at)postgresql(dot)org
Subject: Re: selects from large tables
Date: 2002-11-20 15:16:42
Message-ID: 1453.1037805402@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Nikk Anderson <Nikk(dot)Anderson(at)parallel(dot)ltd(dot)uk> writes:
> As our backend systems are writing hundreds of rows of data in per minute
> into the table that needs clustering - will cluster handle locking the
> tables when dropping the old, and renaming the clustered data?  What happens
> to the data being added to the table while cluster is running?

Nothing, because there won't be any: cluster acquires exclusive lock on
the table while it runs.  Any would-be inserter will block till it's done.

If you are clustering by timestamp of insertion, and you never update or
delete rows, then I think it's a one-time-and-you're-done kind of task
anyway --- newly inserted rows will always get added at the end, and so
will be in timestamp order anyway.  But if you need to update the table
then things aren't so nice :-(

			regards, tom lane

PS: it's not really necessary to quote the entire thread in every
message, and it's definitely not nice to do so twice in both plain
text and HTML :-(.  Please have some consideration for the size of
your emails that Marc is archiving for posterity ...

In response to

pgsql-performance by date

Next:From: Bruce MomjianDate: 2002-11-20 15:18:16
Subject: Re: selects from large tables
Previous:From: Nikk AndersonDate: 2002-11-20 15:08:11
Subject: Re: selects from large tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group