Tuesday, September 17, 2013

PostgreSQL replication? I need temp tables for my reports

One of the frequent things I hear that many times  PostgreSQL users avoid running PostgreSQL replication  because they want to offload reports that use temporary tables on the slaves. Since PostgreSQL replicas are pure read only, it cannot support temporary tables.

There is one way to overcome this with postgres_fdw - PostgreSQL Foreign Data Wrapper which are improved in PostgreSQL 9.3 which is now released.
  • Create a Master-Slave setup with PostgreSQL 9.3 (with synchronous or asynchronous replication as per your needs).
  • On the slave setup, setup another PostgreSQL 9.3 instance (with different port) with postgres_fdw and map all tables from slaves as foreign tables with same names as their remote counterparts.
  • Run reports which requires temporary tables using this new instance
Of course there are few caveats for this setup
  • Query plans: Currently they are still inefficient but as postgres_fdw improves, this will likely go away. Infact more usage of this use-case scenario will force it to be improved
  • Lot of data moving: Most DW reports do read lot of rows. However by setting it up on the same server most of it are loopback and dont go on the wire outside. 
  • More Resources: This will do require more memory/cpu on the server but it is still cheaper since the management of such a server is still more simpler compared to other complex designs to achieve the same goal
I would like to hear about your experiences on the same too so feel free to send me comments.

No comments: