0

We have a read-only replica of our main PostgreSQL database being used by our data analytics team.

They would like to create indexes on the read replica to improve performance of analytics queries, without affecting the main database.

I've found that this is possible for MySQL RDS instances. However, for PostgreSQL this indicates it's not possible:

Each PostgreSQL read replicas is read-only. You can't make a writable read replica.

Is there any way to create an index on a read-only replica in PostgreSQL? If not, is there some workaround for this problem?

3
  • Welcome to DBA.SE Your findings state that you can't create indexes on a read replica. Where is the information that you found that describes that this would be possible? Your question is contradicting itself: you can create an index <-> you can't create an index.
    – John K. N.
    Jul 16 at 9:59
  • aws.amazon.com/rds/features/read-replicas under the Enhanced performance you'll see that you can add indexes to read replica for MySQL. I was wondering is it possible for PostgreSQL as well. Jul 16 at 12:28
  • In mysql, the replication methods will allow us to do any writes on read replica by enabling read-only mode=disabled. But PostgreSQL is not like that, you can't do any writes on the read replica. Thats why this option is not available as a feature.
    – TheDataGuy
    Aug 7 at 4:06
1

You can't do that with "physical" replication. You could use logical replication to set up a replica with a different structure, including different indexes. It will not inherently be only a read-only replica, so you either need to play around with permissions, or just trust your users not to screw it up.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.