Jonathan Vanasco-7 wrote > This is really a theoretical/anecdotal question, as I'm not at a scale yet > where this would measurable. I want to investigate while this is fresh in > my mind... > > I recall reading that unless a row has columns that are TOASTed, an > `UPDATE` is essentially an `INSERT + DELETE`, with the previous row marked > for vacuuming. > > A few of my tables have the following characteristics: > - The Primary Key has many other tables/columns that FKEY onto it. > - Many columns (30+) of small data size > - Most columns (90%) are 1 WRITE(UPDATE) for 1000 READS > - Some columns (10%) do a bit of internal bookkeeping and are 1 > WRITE(UPDATE) for 50 READS > > Has anyone done testing/benchmarking on potential efficiency/savings by > consolidating the frequent UPDATE columns into their own table? Consider another in-database attempt to mitigate the need to do this manually: HOT (heap-only-tuple) http://pgsql.tapoueh.org/site/html/misc/hot.html I haven't done any bench-marking but I do currently use this idea to segregate read-only fields from read-write fields. Likely there is also a model reason why these fields have different update frequencies and so can both logically and physically be partitioned out. The only harm I see is that it can make using the schema more difficult - though that can be somewhat mitigated by using (updateable) views in front of the partitioned tables. If you can logically partition them I would go for it; if it is a purely physical concern then I'd probably ponder it for another couple of days and then go for it anyway. The main additional thing to ponder is the cost of additional parsing and the additional join. Neither is super expensive but if only doing this for physical reasons you need to evaluate your planned usage patterns. With a logical split you are more likely to find situations where you do not even care about one table or the other and so can avoid the join entirely. David J. -- View this message in context: http://postgresql.nabble.com/splitting-up-tables-based-on-read-write-frequency-of-columns-tp5834646p5834911.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
