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.