On Thu, Feb 28, 2013 at 12:13 PM, Carlo Stonebanks < stonec.regis...@sympatico.ca> wrote:
> <<Could you use CLUSTER on the table after it had been closed off? If > appropriate, that should make the queries run much faster, as elated > entries will be in the same or nearby blocks on disk.**** > > >>** ** > > ** ** > > Technically, yes. That would really help, but the issue is scheduling. > Although the logs are closed off for writes, they aren’t closed off for > reads, ref PG documentation: “When a table is being clustered, an ACCESS > EXCLUSIVE lock is acquired on it. This prevents any other database > operations (both reads and writes) from operating on the table until the > CLUSTER is finished.”**** > > ** ** > > Not ideal, but a lot better than doing nothing at all! > Since it is read only, you could make a copy of the table, cluster the copy (or just do the sorting while you make the copy), and then atomically swap the two tables by renaming them inside a single transaction. The swap process will take an exclusive lock, but it will only last for a fraction of second rather than the duration of the clustering operation. Cheers, Jeff
