Re: Clustered index to preserve data locality in a multitenant application?

On Tue, Aug 30, 2016 at 7:26 PM, Vick Khera <vivek(at)khera(dot)org> wrote:

> I'll assume you have an index on the tenant ID. In that case, your
> queries will be pretty fast.
>
> On some instances, we have multi-column indexes starting with the
> tenant ID, and those are used very effectively as well.
>
> I never worry about data locality.
>

Yes, we have an index starting with the tenant ID, and the query uses the
index.

But I'm still worried about PostgreSQL having to fetch 10 times more pages
from the disk than MySQL. If each 8K page contains approximately 10 rows,
fetching one page in MySQL will return 10 "useful" rows belonging to the
tenant. By comparison, fetching one page in PostgreSQL will probably return
only 1 "useful" row belonging to the tenant. In terms of IO, it's a big
difference.

> Depending on your data distribution, you may want to consider table
> partitions based on the tenant id. I personally never bother with
> that, but split based on some other key in the data.
>

You're right. I don't really need a clustered index (like InnoDB). What I
need is to store rows belonging to the same tenant close from each other.
Partitioning can help with that. But the lack of declarative partitioning
makes it cumbersome (I've read this is worked on).