pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Less intrusive ways to cluster?
2+ messages / 2 participants
[nested] [flat]

* Less intrusive ways to cluster?
@ 2008-01-08 16:44 Hans Guijt <hg@terma.com>
  2008-01-08 16:49 ` Re: Less intrusive ways to cluster? Joshua D. Drake <jd@commandprompt.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Hans Guijt @ 2008-01-08 16:44 UTC (permalink / raw)
  To: pgsql-admin

I have a fairly large table that keeps track of data measured by our
system. The data is in the form of BLOBs, and is only queried in order
of timestamp. Because we are measuring from multiple devices, the data
does not necessarily arrive in the database in correct temporal order,
and in fact it is very well possible for corrections to some BLOBs to be
entered after measurement. As a result, there is considerable churn in
the table - but only at the very end, in the last hour or so of data.
Older data is normally left alone.

I have noticed that clustering the table improves my query speed
considerably, so I would like to keep the table clustered (on the index
of the timestamp column, of course). However, I'm wondering what the
best approach is and would appreciate ideas from this list.

So far I have experimented with running some form of clustering every 24
hours. I have tried the following approaches:

* If I use the "cluster" command, the table is locked for a long time
(half an hour or more, on some data sets). This is unacceptable because
this also stops all reads from the table, and some output processing
relies on being able to get data in timely fashion. 

* ...but if I copy the entire table, as per the instructions in the
CLUSTER manual page, it appears the table is still locked? At least I
still cannot read from it during this operation. 

I don't really mind the clustering taking a long time, as long as I can
still read from the table (I can hold up writes for a long time, but
reads are problematic). Is there some way to cluster without blocking
reads?

Alternatively, is there some way to do partial clustering? Since 99% of
my data set will already be properly clustered, except for the last 24
hours or so of data, just clustering that last bit (which is trivial by
comparison) would already help a great deal. However, I'm not sure how
to achieve this. 

I have also considered a complex scheme, using two tables to represent
the data: one for long term storage that won't churn as much, and one
for short-term storage that has all the churn. I could then copy data
from the short-term table to the long-term table every 24 hours, and use
a view to still make it visible as a unified whole. However, this does
appear to be an overly complex solution for this problem. Any better
ideas?


Thanks,

Hans Guijt





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Less intrusive ways to cluster?
  2008-01-08 16:44 Less intrusive ways to cluster? Hans Guijt <hg@terma.com>
@ 2008-01-08 16:49 ` Joshua D. Drake <jd@commandprompt.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Joshua D. Drake @ 2008-01-08 16:49 UTC (permalink / raw)
  To: Hans Guijt <hg@terma.com>; +Cc: pgsql-admin

Hans Guijt wrote:
> I have a fairly large table that keeps track of data measured by our
> system. The data is in the form of BLOBs, and is only queried in order
> of timestamp. Because we are measuring from multiple devices, the data
> does not necessarily arrive in the database in correct temporal order,
> and in fact it is very well possible for corrections to some BLOBs to be
> entered after measurement. As a result, there is considerable churn in
> the table - but only at the very end, in the last hour or so of data.
> Older data is normally left alone.

> Alternatively, is there some way to do partial clustering? Since 99% of
> my data set will already be properly clustered, except for the last 24
> hours or so of data, just clustering that last bit (which is trivial by
> comparison) would already help a great deal. However, I'm not sure how
> to achieve this. 

Why not break it up into a partitioning scheme?

Joshua D. Drake

> 




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2008-01-08 16:49 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-08 16:44 Less intrusive ways to cluster? Hans Guijt <hg@terma.com>
2008-01-08 16:49 ` Joshua D. Drake <jd@commandprompt.com>

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