VistaDB Pagesize Limits

Pagesize Limitations on tables, index, etc

Pagesize has always had a direct effect on the number of columns that could be present in a table, size of a description, max length on a view, etc. Now we are enforcing those limits a little more strictly, and PackDatabase has been updated to promote a database to the correct pagesize for the data in the database.

There are a number of other features that are also impacted by changing the pagesize on a database as well. See the Database Specifications page for more information.

All database pages have a 96 byte header that is required for tracking structures, trees, versions, etc. There are other limits on things like max binary that can be stored in extended data, etc. But that is best left for an entire page explaining things like in row versus extended row data.

Page Size* Max Columns Max Index Key Columns Max Index
Columns Total
Max Fixed Data Row Size

1 Kb

16

1

8

956

2 Kb

32

1

16

1,688

3 Kb

64

2

32

2,420

4 Kb

128

2

64

3,152

6 Kb

512

4

256

4,936

8 Kb

1024

6

1024

7,872
16 Kb 1,024 6 1024 13,472

*SQL Server has a fixed 8 Kb pagesize. We have limited some of our variables to match SQL Server, this ensures easier migration of data from VistaDB up to SQL Server. Note that 16 Kb pagesize can generate data larger than SQL Server can normally handle.

Pagesize impacts performance

As an example of why limits are needed, lets say you have a database with an average row length of 500 bytes, but your pagesize is set to 1k. More than 2 of these rows will usually fit in a page (resulting in very poor performance and fragmentation of indexes, etc). Pack will now increase the pagesize to attempt to fit 3-5 rows in a page. This can have some consequences on filesize as well.

Other things like the maximum number of columns in a table were all dynamically calculated based upon the data in the schema. This is great from a technical standpoint as it provides the most flexibility, but unless you love databases and schema the way we do you probably don't ever check this stuff and just expect it to handle it. Now we are enforcing limits based upon the pagesize that make sense in what we see from user databases.

When you add in some of the overhead per page or index there are numbers that don't appear to line up evenly. The numbers have been rounded to make more sense and are now enforced at creation and update. For example if you have a 1k database and attempt to add a new column to that database you may get an error that the max number of columns have been exceeded for your pagesize. Packing the database may modify the pagesize for you, or you may need to go do it by hand.

We tried to model our limits on SQL Server limits so users will have no problem upsizing later if they need to do so. SQL Server always has an 8 kb page size. So do not use a larger pagesize (we support up to 16 kb for corporate licenses), but this should only be used with careful consideration.

Self Imposed Limits

VistaDB was designed using 32-bit and 64-bit memory addressing in most situations that required large upper limits. The effect of supporting large upper limits are ranges that exceed 4 billion or 4GB for 32-bit values and provides massive Exabytes limits for 64-bit values.

Because of our focus on supporting the creation of small to mid size applications, supporting such enormous upper limits is not feasible for testing. Therefore we have implemented some self-imposed limits when it seemed appropriate.