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.

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 )

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.

Yes, PostgreSQL allows for view updateability, but at present you must set it up "manually", by defining an ON INSERT rule.


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/a...ok/node124.html

Hi

Thanks a million for the valuable information.

Best Regards.