Page Sizes in 2023

[PCollins]

Page size has always been a grey area and we have never risked pushing above 4k although that said we haven't revisited for a number of years and things are remarkable different these days what with SSD arrays, IOPS are through the roof, plus the amount of RAM typically allocated to servers.

If we have a large (in our case) db file, say 750GB which has maybe 50 tables, some of which hold just 4 or 5 columns of small string data, others which hold large binary PDF data and XML content in the 100,000,000's of rows should we be looking at using a larger page size?

[MarkCulp · Accepted solution]

See my answer to this FAQ. The size of your database as you describe it should not be an issue so you should likely simply pick a database page size equal to the file system sector size (which should be set to the underlying raw allocation page size). Even though SSDs are fast, it is still best to match the sizes across all three layers.

However, see my note at the end of the FAQ (last two paragraphs): ie. "It depends"! You have not described your work load - rate of inserts, updates, or deletes on the various tables - so you may want to consider doing some performance testing using your specific workload mix and as a result choose a database page size which is twice the size of the file system sector size. FWIW I doubt you will see much of a difference if you are using an SSD.

[PCollins · Accepted solution]

Thanks Mark, I had read your post although I was curious if things had changed given hardware performance increases but I guess not.

[MCMartin · Accepted solution]

Any plans to support bigger page sizes than 32k in the future?