Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2014

    Unanswered: Slow performance with VIEW table

    Hey all,

    We've run into what seems like a simple issue and may have backed ourselves into a corner, so I'm seeking suggestions on how to adapt.

    BACKGROUND: There are multiple remote sites collecting data into their respective PostgreSQL table onsite. To facilitate viewing that data in our application and to ensure reliable data delivery (VPNs are not always up), I'm using Londiste3 to replicate each site table to the master database. Replication is one-way.

    PROBLEM: After implementing Londiste, we changed one of our main tables to a VIEW to avoid re-coding the application. As a result of this, the application obviously can not use the indexes it "knows" because each individual replicated table had the indexes renamed.

    Summary of Architecture

    SITE 1 ----->---\
    SITE 2 ------>----\
    |------VPN-------> MASTER
    SITE 3 ------>-----/
    SITE 4------>----/

    So now our database looks like:

    site1_table - table
    site2_table - table
    site3_table - table
    site4_table - table

    master_table - CREATE VIEW master_table (SELECT * From site1_table UNION ALL (SELECT * FROM site2_table UNION ALL .... etc. etc.

    The slow queries in question are to master_table.

    SOLUTION: My initial inclination is obviously to re-work the application to query individual site tables and use the individual indexes, but our developer is hesitant.

    All comments and suggestions welcome. Trying to brainstorm here. Thanks in advance!

  2. #2
    Join Date
    Jun 2004
    Arizona, USA
    Is there a reason that you don't want to push the individual records into a single table, with an additional Location column to specify where the data came from?

    Adding an additional table every time you add a location seems both inefficient, and contrary to data normalization guidelines. If there's a reason to partition your data into separate tables, do so in a fashion that makes sense for your data usage. If most of the data usage involves accessing the most recent data more often than older records, for instance, partition the data into separate tables by date.
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts