This approach mixes well with hash partitioning. It would be neat indeed if PostgreSQL do something equivalent on its own, and pluggable storage work being done could enable index organized tables that would help. But you probably need something right now.
Fixing glaring issues (no vacuum and thus no Index-Only Scan on append-only tables, vacuum processing all of the eternity of btree) by 11 will get most of spike-nails out of the microservice code, and we can probably live with them until 11 gets to RDS.
I also don't see why a pluggable storage is a must for the clustered write. Postgres does have a mechanism for selecting the next page to write tuple to, right now it's just looking at FSM - but what if it just peeked at existing index that already has enough the data to route tuple to correct page on write?
I guess I don't have to tell you that it looks like your needs have outgrown what RDS works well with and you are in for a painful move sooner or later.
Painful move where to? If we just run a Postgres instance without RDS we'll get the pain of setting up Postgres and replication and backups and autofailover, with no visible gain except if we get some private / unaccepted patches applied to it. If we can get these things right upstream why would we want to switch?
Per my colleagues, MySQL offers clustered index, also MySQL is available on RDS without the need of "painful move", which is doable by writing to two locations for a day and then pointing readers to new DB. But if we can instead do no move and be sure the issues are gone upstream before we hit the limit of spike-nails we're running on currently, wouldn't that be better? :)
