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

On 9/6/16 11:21 AM, Nicolas Grilly wrote:
> It looks like I can move forward with our migration from MySQL to
> PostgreSQL, without worrying about the lack of clustered indexes,
> because there are better solutions to keep tenant data contiguous!

First rule of performance tuning: don't. :) There are lots of areas
where Postgres can be expected to perform better than MySQL, so without
testing your app you really don't know how it's going to fare.

There's also another option: use a logical replication system (such as
pg_logical, BDR, londiste or Slony) to maintain at least one replica.
You can take that replica down to perform maintenance (such as a
database-wide CLUSTER) as needed, and let replication catch up once you
bring it back online. That, combined with scripted failover makes a lot
of database maintenance items far easier, at the cost of having to
maintain the replication. Depending on your needs, a major benefit to
this method is it makes major version upgrades very simple: you just
stand up a new replica on the new version and then failover to it. If
anything goes wrong, you can fail back to the old version without losing
any data.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461