We're using DB2 LUW 10.5 & 11.1 on Windows & Linux systems, in case it's relevant to he answers.
Question:
Is there a time where it would be correct to use 4K rather than 32K? If so, why? (Does it perform better when it can be used?) Or, is it just a legacy appendage from some prehistoric time when 4K was just the page size?
Background:
I have always just created a 4K, 8K, 16K, & 32K tablespaces and associated bufferpools when I create DB2 db's.
My manager is challenging me on this. (Good for him – I should know this!) He thinks we should just create a 32K tablespace and be done with it.
I'm not able to find anything that tells me, for instance, that when the row size allows it, we should use 4K rather than 32K because XYZ. It tells me I CAN do this, but not that/when I should.
Best Answer
It's a good question, but the answer is worthy of a whole chapter in a book that does not exist, sadly. Ember Crooks' article you're linking to is a good overview; I'll add here some random factors that one might want to take into consideration when deciding on the tablespace page sizes.
TL; DR.
Choose one page size that is most suitable for your data, considering the points below. If your performance testing shows problems that can be addressed by moving some tables to a tablespace with a different page size, do it judiciously.
Decision factors.
As you have mentioned, table row width determines the minimum page size required to accommodate them. It doesn't follow though that you always want "the smallest that will work for your data".
Firstly, the usual arguments of "avoiding unnecessary I/O" and "handling less data at a time" with smaller page sizes may be a bit misplaced. If your tablespace container sits on a ZFS file system on an LVM volume on a VMWare virtual disk on a Ceph volume on an unknown number of RAID6 devices that may use spinning disks or SSDs, do you really know how much physical I/O your 4K (or 32K) read request will cause?
Smaller page sizes certainly help if your workload creates tablespace hotspots (most I/O requests go to a limited number of pages) that cannot be resolved by other means. In such situations smaller pages can improve bufferpool efficiency and reduce page latch waits between agents competing for access to the same page. On the other hand, smaller page size means longer LRU chains and hence possibly less efficient page cleaning.
There are also arguments for larger page sizes.
Presence of LOB data.
Normally LOB data are stored outside table rows, in separate data structures that have several performance disadvantages:
If most of your LOB values are relatively small and could fit into the row itself given larger page size (as is often the case), you could store them inline, alleviating those disadvantages.
Compression.
Larger page sizes improve efficiency of adaptive (page-level) compression. Often I/O reduction that data compression affords outweighs its CPU costs.
Don't forget temp tablespaces.
Even if each of your tables individually can be placed into a 4K tablespace, a system temporary tablespace with a larger page size (and a corresponding bufferpool) might be required. If a query joins sub-4K rows from two or more tables, the result set width might exceed the 4K limit and, if it needs to spill, an appropriately-sized tablespace will be needed.
It's worth mentioning that creating tablespaces of each possible page size "just in case" is not a good idea, because, as you said, each requires a dedicated bufferpool, and multiple bufferpools, unless necessary, are almost always worse (less efficient) than a single large one.