Re: A more general approach (Re: [HACKERS] Data archiving/warehousing idea)
Ühel kenal päeval, N, 2007-02-01 kell 12:31, kirjutas Tom Lane: Hannu Krosing [EMAIL PROTECTED] writes: A more radical variation of the restricted-use archive table approach is storing all tuple visibility info in a separate file. At first it seems to just add overhead, but for lots (most ? ) usecases the separately stored visibility should be highly compressible, so for example for bulk-loaded tables you could end up with one bit per page saying that all tuples on this page are visible. The more you compress, the slower and more complicated it will be to access the information. I'd put my money on this being a net loss in the majority of scenarios. define majority :) In real life it is often faster to access compressed information, especially if it is stored in something like trie where compression and indeked access are the same thing. the most gain will of course come from bulk-loaded data, where the compressed representation can just say something like pages 1 to 20 are all visible starting from transaction 5000 and so is first half of page 21, second half of 21 and pages up to 25 are visible from trx 6000. In this case the visibility info will always stay in L1 cache and thus be really fast, maybe even free if we account for cache reloads and such. But it may be better to still have a bitmap there for sake of simplicity and have some of it be accessible from L2 cache (20 pages times say 2 bits is still only 100kB bitmap for 1.6GB of data). Of course there are cases where this approach is worse, sometimes much woorse, than current one, but the possibility of independently compressing visibility info and making some types of VACUUM vastly cheaper may make it a net win in several cases. Also, for higly dynamic tables a separate visibility heap might also speed up bitmap scans, as access to heap happens only for visible tuples. This can also be one way to get rid of need to write full data tuples thrice - first the original write, then commit bits and then deleted bits. instead we can just write the bits once for a whole set of tuples. Usually most of visibility info can be thrown out quite soon, as the active transaction window advances, so the first level of compression is just thtrowing out cmin/cmax and setting commit bit, then setting the tuple to just visible. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
A more general approach (Re: [HACKERS] Data archiving/warehousing idea)
Ühel kenal päeval, N, 2007-02-01 kell 13:24, kirjutas Gavin Sherry: A different approach discussed earlier involves greatly restricting the way in which the table is used. This table could only be written to if an exclusive lock is held; on error or ABORT, the table is truncated. The problem is that a lot of this looks like a hack and I haven't seen a very clean approach which has gone beyond basic brain dump. A more radical variation of the restricted-use archive table approach is storing all tuple visibility info in a separate file. At first it seems to just add overhead, but for lots (most ? ) usecases the separately stored visibility should be highly compressible, so for example for bulk-loaded tables you could end up with one bit per page saying that all tuples on this page are visible. Also this could be used to speed up vacuums, as only the visibility table needs to be scanned duting phase 1 of vacuum, and so tables with localised/moving hotspots can be vacuumed withoutd scanning lots of static data. Also, storing the whole visibility info, but in a separate heap, lifts all restrictions of the restricted-use archive table variant. And the compression of visibility info (mostly replacing per-tuple info with per-page info) can be carried out by a separate vacuum-like process. And it has many of the benefits of static/RO tables, like space saving and index-only queries. Index-only will of course need to get the visibility info from visibility heap, but if it is mostly heavily compressed, it will be a lot cheaper than random access to data heap. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: A more general approach (Re: [HACKERS] Data archiving/warehousing idea)
Ühel kenal päeval, N, 2007-02-01 kell 14:38, kirjutas Hannu Krosing: Ühel kenal päeval, N, 2007-02-01 kell 13:24, kirjutas Gavin Sherry: A different approach discussed earlier involves greatly restricting the way in which the table is used. This table could only be written to if an exclusive lock is held; on error or ABORT, the table is truncated. The problem is that a lot of this looks like a hack and I haven't seen a very clean approach which has gone beyond basic brain dump. A more radical variation of the restricted-use archive table approach is storing all tuple visibility info in a separate file. At first it seems to just add overhead, but for lots (most ? ) usecases the separately stored visibility should be highly compressible, so for example for bulk-loaded tables you could end up with one bit per page saying that all tuples on this page are visible. Also this could be used to speed up vacuums, as only the visibility table needs to be scanned duting phase 1 of vacuum, and so tables with localised/moving hotspots can be vacuumed withoutd scanning lots of static data. Also, storing the whole visibility info, but in a separate heap, lifts all restrictions of the restricted-use archive table variant. And the compression of visibility info (mostly replacing per-tuple info with per-page info) can be carried out by a separate vacuum-like process. And it has many of the benefits of static/RO tables, like space saving and index-only queries. Index-only will of course need to get the visibility info from visibility heap, but if it is mostly heavily compressed, it will be a lot cheaper than random access to data heap. For tables with fixed-width tuples it can probably be extended to support vertical fragmentation as well, to get DWH benefits similar to http://monetdb.cwi.nl/ . -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: A more general approach (Re: [HACKERS] Data archiving/warehousing idea)
Hannu Krosing [EMAIL PROTECTED] writes: A more radical variation of the restricted-use archive table approach is storing all tuple visibility info in a separate file. At first it seems to just add overhead, but for lots (most ? ) usecases the separately stored visibility should be highly compressible, so for example for bulk-loaded tables you could end up with one bit per page saying that all tuples on this page are visible. The more you compress, the slower and more complicated it will be to access the information. I'd put my money on this being a net loss in the majority of scenarios. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
