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:
- You can only access them via synchronous direct reads and writes that bypass the database bufferpools; the only caching available is that of the underlying file system, if enabled for the tablespace; direct reads also don't take advantage of page prefetching.
- Since LOBs are not loaded into the bufferpools, repeated access to the same data will cause repeated direct read requests.
- They are not compressed, even if the table compression is enabled.
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.
