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?

Revels

Since it's Data Warehouse, does it have to be real-time? Or would something like "load nightly from 2nd slave" work? Something like "replicate slave from backup and logs, cut off from replication, build warehouse indexes" (repeat that every day). – Townsley

Ideally close to real-time. We've tried loading nightly backups, but unfortunately it's a 100+GB database and can take almost 4 hours to load from scratch. WAL replication has been a godsend though. – Revels

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.

Merrileemerrili

beauty...thanks so much. Used slony in the past, and wasn't a huge fan but I'll have a gander at the other 2. – Revels