in reply to Re^2: Architecture design for full stack development.
in thread Architecture design for full stack development.

CLUSTER dependent on the contiguity of /values/? I have to say, I find this a bit hard to believe. Do you perhaps have any recent postgres-based evidence that explains or supports this idea? Or, if you could perhaps outline an approach that demonstrates it, so that I can implement that, and see how it turns out? Is it measurable at all? I would really like to see this in action.

  • Comment on Re^3: Architecture design for full stack development.

Replies are listed 'Best First'.
Re^4: Architecture design for full stack development.
by anonymized user 468275 (Curate) on Jun 23, 2017 at 16:54 UTC
    I don't and won't have evidence unfortunately, only theory. The theory I am applying is that provided the values are contigious, the mapping used in the btree algorithm can use this to calculate the physical location of the page more quickly than if not and that page seeking will slow down proportionately according to how often it cannot calculate the page location based on the value and has to go walkies looking for it. OK I kind of have evidence because I have subjectively noticed a performance improvement in operating on large tables where the values of the PK are contiguous relative to those where they werent. In fact where PK values were contiguous Postgres crunched through 4 mil records in seconds but slept soundly for same record count but non contiguousPK values. Please don't ask me to submit test results -- I have a system to build!

    One world, one people

[reply]

      CLUSTER [1] rewrites a table in the order of an index (to wit, the index you pass to the CLUSTER command). Access of singular values does not change; but access of values that are now (after CLUSTER) 'near' each other can become faster (because there is a higher chance they are already read). It has to do with /physical/ nearness (or proximity) of the table data, not of the values.

      At least, that is my theory :)

      Please don't ask me to submit test results -- I have a system to build!

      Fair enough :) But I wouldn't undertake any work on the basis of your theory - I don't think it is valid.

      I'll try to devise a test myself, and see if one of our theories can be falsified.

      [1] CLUSTER command (PostgreSQL docs)

[reply]
        I am not sure about a cluster command, I was referring to the 'CLUSTERED' attribute available when creating or altering an index. A clustered index is one which maintains organisation of the underlying data in index order. Postgres allows more than one index to be clustered; I first learned about clustered indexes on Sybase where only one index per table could be clustered for obvious reasons, but how Postgres manages more than one clustered index on the same table is a separate mystery.

        One world, one people

[reply]

In Section Seekers of Perl Wisdom