Data type selection for storage
Parent: Storage and Data-Lifecycle Cost Levers · Published reference · snapshot 2026-10-21 · skill ranked-ab842e47
↓ Facts as markdownall context files
Source-anchored research for Data type selection for storage. Run status: COMPLETED.
These notes link each claim to its source. Some citations identify archived research reports that are no longer hosted on this site. A published reference means the content is available; it does not certify independent review or accuracy.Read the editorial policy and follow the sources before relying on a claim.
How it works
- Choose a type whose range covers expected values and whose precision preserves the application's meaning. PostgreSQL documents integer as the common balance of range, storage size, and performance; it describes smallint as useful when disk space is at a premium and bigint as appropriate when integer range is insufficient. PostgreSQL integer widths are 2, 4, and 8 bytes for smallint, integer, and bigint. (high confidence) [source]
- At the BSON value-encoding level, Int32 uses 4 bytes, Int64 and Double use 8 bytes, and Decimal128 uses 16 bytes. MongoDB community guidance notes that Decimal128 therefore uses twice the Int64 value bytes in an uncompressed WiredTiger cache; this does not determine whole-collection disk savings because document overhead and compression also contribute. (high confidence) [source]
- For MongoDB decimal workloads, Decimal128 supports exact decimal values, while a scale-factor model stores values as a 64-bit integer after multiplying by a power of ten. The integer model fits only when the required scale and range are known and applications apply that scale consistently; MongoDB documents Decimal128 as its preferred monetary model. (high confidence) (verified as of 2026-10-16) [source]
Problems, failure modes and limitations
- Use an exact decimal type when decimal precision must be preserved, such as for monetary values. The MongoDB Decimal128 specification says BSON Double is insufficient for some values that require strict decimal rounding; PostgreSQL recommends numeric for money and other exact quantities, while warning that numeric calculations are slower than integer or floating-point calculations. (high confidence) [source]
Comparisons and alternatives
- When decimal values need exact storage or arithmetic, select an exact decimal type instead of binary floating point. PostgreSQL describes numeric as exact but slower than integer and floating-point types, and its storage varies with the digits stored; MySQL describes DECIMAL and NUMERIC as exact fixed-point types. This makes required precision and arithmetic part of the storage decision. (high confidence) [source]
Facts and statements
- For MongoDB time-series collections, rounding numeric measurements to the precision the application requires can improve the compression ratio. Preserve any precision the application needs. (low confidence) [source]
- In PostgreSQL, row alignment can add padding between columns, so declared field widths alone do not determine total row size. Column order affects padding. Inspect the physical row layout before narrowing a type solely to save storage. (medium confidence) [source]
- For bounded whole-number fields, choose a width that covers the full valid range and expected growth. PostgreSQL documents 2-, 4-, and 8-byte smallint, integer, and bigint types with different ranges; MySQL documents 1-, 2-, 3-, 4-, and 8-byte integer types with signed and unsigned ranges. (high confidence) (verified as of 2026-10-21) [source]
- Inference: nominal type widths do not always translate into proportional whole-table savings. MySQL states that storage-engine layout and compression affect on-disk size; its NDB engine aligns values to four bytes, so TINYINT, SMALLINT, MEDIUMINT, and INT each use four bytes per record there. Estimate savings against the target engine's physical layout. (medium confidence) (verified as of 2026-10-15) [source]
Children
- No children recorded.