2

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?

Improve this question
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. Commented Jul 16, 2021 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. Commented Jul 16, 2021 at 12:28
  • 1
    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. Commented Aug 7, 2021 at 4:06

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.