|
In general, temporary tables are way faster for writing than normal tables as they don't generate WAL records.
On Tuesday, February 12, 2013 11:45:22 AM Little, Douglas wrote: Hi,
Design question. Does it make a difference for a function to repeatedly update a temp table verses the permanent table?
We are working in a data warehousing environment. We have daily etl that’s used to update our dimension table which has approx. 500k rows. A dimension row holds all of the descriptive attributes related to any number of transaction records. The dimension table has about 50 attributes sourced from various lookup tables. In the dimension table we have the id and the name/description. If any of the lookup tables are updated, we need to propagate the new description to all dimension rows that use that value.
Instead of a cursor going thru all of the rows and updating the columns, we use a column update approach where we update specific columns for all rows that need a update.
So the function has about 50 update statements, each setting 1 column at a time. Individual update statements update between a few thousand to 300k rows.
We’re refactoring the code now and considering updating a temp table repeatedly and finally updating the perm table, instead of updating the perm table 50 times.
I don’t see much difference between a temp and perm table. I suspect that the update process is the same for both types. Can anybody comment if there’d be any difference in update performance? Would it be worth the effort to vacuum after each update?
Note – any of the rows in the perm table may need to be updated, so the temp table would be a copy of the perm table.
Thanks in advance.
Doug Little
Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz Worldwide 500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741
-- postgresql.org.mx |
