On December 26, 2003 07:11 pm, Keith Bottner wrote: > I have a database where the vast majority of information that is related to > a customer never changes. However, there is a single field (i.e. balance) > that changes potentially tens to hundreds of times per day per customer > (customers ranging in the 1000s to 10000s). This information is not > indexed. Because Postgres requires VACUUM ANALYZE more frequently on > updated tables, should I break this single field out into its own table, > and if so what kind of a speed up can I expect to achieve. I would be > appreciative of any guidance offered.
We went through this recently. One thing we found that may apply to you is how many fields in the client record have a foreign key constraint. We find that tables with lots of FKeys are a lot more intensive on updates. In our case it was another table, think of it as an order or header table with a balance, that has over 10 million records. Sometimes we have 200,000 transactions a day where we have to check the balance. We eventually moved every field that could possibly be updated on a regular basis out to separate tables. The improvement was dramatic. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]|vex}.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner. ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
