Postgresql – Size of Postgres database growing

For high performance systems, I think virtualization usually causes a lot more problems than it solves. Save yourself the headache. Build a decent db server inhouse.

It is hard to say what your current problem is, and virtualization makes troubleshooting much harder. Is the load cpu bound? Is it I/O bound? Are you having concurrency issues? Something else?

Now, if your 8k queries every 5 sec are not very complex queries, then the immediate question is why are you having performance problems. It may just take some db tuning. I have seen some indications that you should be able to get several times that on good hardware if things are well tuned. Again virtualization is probably making things harder to fix rather than easier.

Finally, I would highly recommend looking into Postgres-XC. It allows you to do sharding for write-extensibility while managing concurrency and consistency a layer above. I would start tuning what you have and move from there though.

According to the AWS Documentation, logical replication is possible from RDS Postgres instances, starting with version 9.4.9. That would solve the RDS -> local direction.

The documentation doesn't make it very clear, but it seems that from version 10.4 an RDS instance can even be the receiver end (the subscriber) of logical replication:

Beginning with PostgreSQL version 10.4, RDS supports the publication and subscription SQL Syntax for PostgreSQL 10 Logical Replication.