Postgres thread replication - slave index only

Question

We have successfully deployed Postgres 9.3 with streaming replication (WAL replication). Currently, we have 2 slaves, the second slave is a cascading slave from the first slave. Both slaves are hot modes with active read-only connections.

In connection with the download, we would like to create a third slave device, with slightly different hardware specifications, and another application that uses it as a read-only database in most cases of using a data warehouse. Since this is for another application, we would like to optimize it specifically for this application and improve performance by using some additional indexes. For size and performance purposes, we prefer not to have these indexes on the main device or on the other 2 slaves.

So, my main question is: can we create different indexes for slave devices for streaming replication, and if not, is there another data storage method that I skip?

Answer 1

So my main question is: can we create different indexes for slaves for streaming replication

No, you can’t. Stream physical replication works at a lower level than when copying disks. In fact, he does not pay attention to “this is an index update”, “this is an insert into a table”, etc. It does not have the information necessary to maintain indexes in standby mode.

and if not, is there another data warehouse method that I skip?

Logical replication solutions, such as:

• Londiste
• pglogical
• Elephants-I

can do what you want. They send row changes, so the secondary server may have additional indexes.