#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    69
    Rep Power
    13

    Performance Issue - large table


    Hi

    I have a large table, which is expected to grow at the rate of minimum 1000 rows per day. (i.e., minimum of 30,000 rows in 1 month, max may be 90,000 rows in 1 month also or even more)

    Any idea about how I can avoid a performance issue with such a large table ?

    Thank you for your valuable input.

    Best Regards.
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Index it, VACUUM it regularly, VACUUM analyze your database regularly to optimize any queries involved. Make sure that any queries involved are only pulling the minimum amount of data needed (No SELECT * queries)

    On the hardware side of things, here are some considerations:

    1. mount your data on a dedicated hard drive partition.
    2. Use SCSI RAID 0+1 if possible. That will give you the best combination of speed+fault tolerance.
    3. RAM is your friend. Give the system lots of RAM, and then increase all your cache settings in postgresql.conf.

    Really, PostgreSQL seems to do fine with flat queries on large tables. The real problem comes when you start doing complicated JOINS, etc... Sometims, if your views and queries are too intensive for the large table, you can have a trigger or RULE upon INSERT of the table to also update a smaller table with important parts of the information. If your table is meant as a read-only table (logging, etc...), then you can use this method without worrying about data integrity, as long as you enforce a rule that NO updates be performed on the table itself. (In fact, you can write a RULE to do that also )
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    69
    Rep Power
    13
    Hi

    Thanks for the reply.

    I was thinking of using views to update the table based on a attribute (insert / select also). This is in addition to having necessary indexes on the base table.

    But can I use a views in PostgreSQL to update the base table?

    Thanks for your reponse.

    Best Regards.
  6. #4
  7. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Originally posted by ggnanaraj
    I was thinking of using views to update the table based on a attribute (insert / select also). This is in addition to having necessary indexes on the base table.

    But can I use a views in PostgreSQL to update the base table?
    Yes, PostgreSQL allows for view updateability, but at present you must set it up "manually", by defining an ON INSERT rule.
    Code:
    CREATE RULE vw_myview_insert_rl AS ON INSERT
    TO vw_myview
    DO INSTEAD
    (
       INSERT INTO basetable1 VALUES(
       NEW.col1,
       NEW.col2
       --etc...
       );
    
       INSERT INTO basetable2 VALUES(
       NEW.othercol1,
       NEW.othercol2
       --etc...
       );
    );
    You will then want to define another rule for UPDATE, and for DELETE, etc... If you want to prevent any updates or deletes the respective rules would just specify DO INSTEAD NOTHING.

    Soon, PostgreSQL will automatically create a rule upon view definition that allows for updateability. The good thing is, it will still be a rule, so you can tweak it in additional ways, as wanted. For example, you could just add an additional statement onto the end of the rule that logs updates to a separate table, or even conditionally inserts changed data to another table, so you can query only the changes etc...

    Learn the PostgreSQL RULE system! It rocks. It allows you to have an incredible level of logical control over actions in your database. (be careful, though, not to create any circular logic) Rules are kind of like triggers, but simpler, and in some ways more elegant. With triggers, you always have to call a procedure, while with rules, you just use inline SQL (obviously, you can call a stored procedure inside your rule's SQL if you want to).

    Read:
    http://www.postgresql.org/idocs/index.php?rules.html
    http://www.ca.postgresql.org/docs/aw...k/node124.html
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    69
    Rep Power
    13
    Hi

    Thanks a million for the valuable information.

    Best Regards.

IMN logo majestic logo threadwatch logo seochat tools logo