postgres streaming replication - slave only index

Question

We have successfully deployed Postgres 9.3 with streaming replication (WAL replication). We currently have 2 slaves, the second slave being a cascaded slave from the first slave. Both slaves are hot-standby's with active read-only connections in use.

Due to load, we'd like to create a 3rd slave, with slightly different hardware specifications, and a different application using it as a read-only database in more of a Data Warehouse use-case. As it's for a different application, we'd like to optimize it specifically for that application, and improve performance by utilizing some additional indexes. For size and performances purposes, we'd rather not have those indexes on the master, or the other 2 slaves.

So my main question is, can we create different indexes on slaves for streaming replication, and if not, is there another data warehouse technique that I'm missing out on?

Answer 1

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

No, you can't. Streaming physical replication works at a lower level than that, copying disk blocks around. It doesn't really pay attention to "this is an index update," "this is an insert into a table," etc. It does not have the information it'd need to maintain standby-only indexes.

and if not, is there another data warehouse technique that I'm missing out on?

Logical replication solutions like:

• Londiste
• pglogical
• Slony-I

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