Performance trade-offs of large pages

Using large database pages benefits database performance, notably decreasing I/O time. Derby automatically tunes for the database page size. If you have long columns, the default page size for the table is set to 32768 bytes. Otherwise, the default is 4096 bytes. You can change the default database page size with the derby.storage.pageSize property, described in the "Derby properties" section of the Derby Reference Manual. For example:

derby.storage.pageSize=8192

Note: Large database pages require more memory.

If row size is large, generally page size should be correspondingly large. If row size is small, page size should be small. Another rough guideline is to try to have at least 10 average-sized rows per page (up to 32K).

Use a larger page size for tables with large columns or rows. Maximum page size allowed is 32k.

However, some applications involve rows whose size will vary considerably from user to user. In that situation, it is hard to predict what effect page size will have on performance.

If a table contains one large column along with several small columns, put the large column at the end of the row, so that commonly used columns will not be moved to overflow pages. Do not index large columns.

Large page size for indexes improves performance considerably.