On Tue, Aug 30, 2016 at 7:26 PM, Vick Khera <vivek@xxxxxxxxx> 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).
