Data Type Choices With Long-Term Consequences

1. Why data type decisions are hard to reverse

A data type choice often feels like a minor detail when a table is created, but its full weight only unfolds once a table has reached millions of rows. An ALTER TABLE that changes a column's type locks either the entire table or requires rewriting every single row in many database systems, which can take hours on large tables and noticeably impact production systems. What is a single line of code at table creation becomes an elaborate migration with a downtime window and a rollback plan months later.

This asymmetry between the ease of the original decision and the effort of the later correction makes data type choice one of the highest-leverage decisions in database design. An integer type chosen too small for a primary key, a VARCHAR limit set too short, or FLOAT instead of DECIMAL for monetary amounts are typical examples where the consequences only become visible with scale, but are then severe. The following sections cover the four most common decision areas in detail: primary key types, text lengths, monetary amounts and time values.

2. INT vs. BIGINT for primary keys

An INT in most database systems is signed 32 bits and covers values up to about 2.1 billion, unsigned up to about 4.3 billion. For many tables, this range initially appears comfortably large. For tables with a high write frequency, such as event logs, analytics data or order line items in a heavily used shop, this range is exhausted faster than many teams expect: at 5000 new rows per second, the limit of an unsigned INT is already reached after about ten months.

The data type choice BIGINT, 64 bits with a practically inexhaustible value range, costs only four additional bytes per row compared to INT, a negligible overhead in the vast majority of cases. Since a later migration of a primary key from INT to BIGINT affects not only the table itself, but also every referencing foreign key column in every linked table, this migration is among the most elaborate of all. The pragmatic rule: for any table that could potentially grow fast, use BIGINT from the start, unless a hard technical limit argues against it.

-- INT unsigned: fine for slow-growing lookup tables (max ~4.3 billion rows)
CREATE TABLE country (
    country_id  INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(100) NOT NULL
);

-- BIGINT unsigned: recommended for high-write-frequency tables
CREATE TABLE order_event (
    order_event_id  BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    order_id         BIGINT UNSIGNED NOT NULL,
    event_type       VARCHAR(50) NOT NULL,
    occurred_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Estimate how fast an INT primary key would be exhausted
-- at a given insert rate (rows per second)
-- 4294967295 / rows_per_second / 86400 = days until exhaustion

3. Choosing VARCHAR lengths correctly

With VARCHAR columns, two opposing mistakes collide: a limit set too short, which cuts off legitimate values or produces INSERT errors, and a blanket VARCHAR(255) for every text column regardless of the actual content. The data type choice for VARCHAR should be guided by the realistic maximum value of the business domain: a country code is always two or three characters long, an email address has a theoretical upper limit of 254 characters per RFC, a product name varies widely and needs a more generous 255 to 500 characters.

In modern database systems like PostgreSQL and MySQL with InnoDB, the VARCHAR length itself has barely any performance impact, because only the actually stored character count plus a short length prefix requires storage, not the declared maximum length. The real value of a well thought out length limit lies in documenting the business domain directly in the schema and protecting against faulty or malicious oversized input, not in saving storage space.

CREATE TABLE customer (
    customer_id     BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -- domain-driven lengths, not a blanket VARCHAR(255) everywhere
    email             VARCHAR(254) NOT NULL,        -- RFC 5321 limit
    country_code      CHAR(2) NOT NULL,               -- ISO 3166-1 alpha-2
    phone_number      VARCHAR(20) NULL,               -- E.164 max length
    company_name      VARCHAR(255) NULL,
    notes             TEXT NULL                       -- unbounded free text
);

4. Storing money: DECIMAL instead of FLOAT

FLOAT and DOUBLE store numbers in binary floating point representation, which cannot represent many decimal values exactly. The amount 0.1 cannot be represented exactly in binary floating point representation, so operations like 0.1 plus 0.2 in FLOAT arithmetic can produce a result like 0.30000000000000004 instead of exactly 0.3. For monetary amounts, such rounding errors accumulate over many transactions into differences that are not tolerable in accounting or a point-of-sale system.

DECIMAL, called NUMERIC in some systems, stores numbers as an exact decimal representation with a fixed number of digits before and after the decimal point. The data type choice DECIMAL(10,2) for monetary amounts in euros allows amounts up to 99,999,999.99 with exactly two decimal places, without any rounding error. For use cases with foreign currencies or cryptocurrencies that need more decimal places, precision should be chosen accordingly higher, for example DECIMAL(19,8) for cryptocurrency amounts.

-- WRONG: FLOAT introduces rounding errors in monetary calculations
CREATE TABLE bad_invoice (
    amount FLOAT   -- 0.1 + 0.2 may not equal exactly 0.3
);

-- RIGHT: DECIMAL stores an exact decimal representation
CREATE TABLE invoice (
    invoice_id   BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    amount        DECIMAL(10,2) NOT NULL,   -- up to 99,999,999.99
    currency      CHAR(3) NOT NULL,          -- ISO 4217
    tax_rate      DECIMAL(5,4) NOT NULL      -- up to 9.9999 (e.g. 0.1900)
);

-- Verify: with DECIMAL, 0.1 + 0.2 equals exactly 0.3
SELECT CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2));

5. Handling timestamps and time zones correctly

A common mistake in data type choice for time values is storing timestamps without time zone information and implicitly assuming the local server time zone. As soon as an application is operated internationally or runs on servers in different time zones, this leads to inconsistent time values that can no longer be reliably compared. The robust standard approach is to store all timestamps consistently in UTC internally and only perform the conversion to the user's local time zone in the presentation layer.

PostgreSQL offers TIMESTAMPTZ, a column type that accounts for time zone information on input and stores it consistently in UTC internally, which structurally reduces errors from implicit time zone assumptions. MySQL has no real TIMESTAMPTZ type, TIMESTAMP there is automatically converted to and from the session time zone, while DATETIME is time zone agnostic and leaves the conversion entirely to the application. These differences between database systems make a deliberate, documented convention for handling time zones essential.

-- PostgreSQL: TIMESTAMPTZ normalizes input to UTC internally
CREATE TABLE event_log (
    event_log_id  BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    occurred_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- MySQL: store UTC explicitly in a session-independent DATETIME column,
-- convert to the user's local time zone only in the presentation layer
CREATE TABLE event_log (
    event_log_id  BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    occurred_at     DATETIME NOT NULL DEFAULT (UTC_TIMESTAMP())
);

6. Boolean, ENUM and CHAR(1): common pitfalls

MySQL historically has no real BOOLEAN type, BOOLEAN there is merely an alias for TINYINT(1), which in practice can lead to a supposed boolean column accidentally accepting values like 2 or minus 1 if no additional CHECK constraint is set. PostgreSQL, in contrast, offers a real BOOLEAN type with strict checking. This data type choice should always be backed by an explicit CHECK constraint in MySQL, to reach the same level of safety as in PostgreSQL.

ENUM column types seem practical at first glance because they prevent invalid values directly at the column level, but are inflexible when values change often: in MySQL, adding a new ENUM value requires an ALTER TABLE that, depending on the position of the new value, can even rewrite the entire table. A VARCHAR with an accompanying CHECK constraint or a separate lookup table is in most cases the more flexible and long-term maintainable alternative to ENUM. CHAR(1) for flags like 'Y'/'N' should generally be avoided in favor of a real BOOLEAN or a documented ENUM with meaningful values.

-- MySQL: BOOLEAN is just TINYINT(1), enforce it explicitly
CREATE TABLE product (
    product_id  BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    is_active     TINYINT(1) NOT NULL DEFAULT 1,
    CONSTRAINT chk_is_active_bool CHECK (is_active IN (0, 1))
);

-- More maintainable than ENUM for values that change often:
-- VARCHAR with CHECK instead of a hardcoded value list in the schema
CREATE TABLE product (
    product_id  BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    status        VARCHAR(20) NOT NULL,
    CONSTRAINT chk_status_values
        CHECK (status IN ('draft', 'active', 'discontinued'))
);

7. TEXT vs. VARCHAR: which one when

VARCHAR with a fixed maximum length fits values with a natural, business-justifiable upper limit: names, email addresses, postal codes, status codes. TEXT without a fixed length limit fits content whose length is not predictable in business terms, for instance free-text comments, article content or log messages. The data type choice between the two is not purely a performance question, it is a question of correctly modeling the domain: a VARCHAR limit on a comment column implicitly claims a business upper bound that often does not exist at all.

In PostgreSQL, there is barely any internal performance difference between VARCHAR and TEXT, because both use the same underlying storage mechanism. In MySQL with InnoDB, however, a very long VARCHAR declaration can affect the maximum row size or the number of possible indexes on the table, which is why TEXT is the technically cleaner choice there for genuinely unbounded content. An index on a TEXT column requires an explicit prefix limit in most systems, for example the first 100 characters, which must be considered when planning full-text search.

8. Data type migration on a live system

A later correction of a data type choice is straightforward for small tables, but a project of its own for large tables in production. The safe path for a type change on a heavily used table goes through a new column instead of a direct ALTER TABLE MODIFY: first the new column with the target type is added, then existing values are copied and converted in batches step by step, in parallel the application already writes to both columns, only after full validation is the old column removed and the new one renamed.

For the classic case of INT to BIGINT for a primary key, an additional complication is that every referencing foreign key column in every linked table has to go through the same migration in sync, since a type mismatch between primary and foreign key is either rejected by most database systems or leads to implicit, expensive type conversions on every JOIN. Tools like pt-online-schema-change for MySQL or pg_repack for PostgreSQL automate this multi-step process and minimize lock time to a minimum.

9. Checklist for new columns

Before every new column, a short but systematic check of the data type choice pays off: could this table potentially grow to several million rows, making BIGINT rather than INT sensible for reference columns? Does the text value have a business-justifiable upper limit, or is TEXT the more honest choice? Is this a monetary amount that necessarily needs DECIMAL instead of FLOAT? Is the column used internationally, requiring an explicit UTC convention instead of implicit local time?

These four questions cover the most common costly mistakes in data type choice and can be answered in a few minutes while designing a new table, whereas a later correction can mean days to weeks of migration effort. A short code review check specifically for new CREATE TABLE and ALTER TABLE ADD COLUMN statements, checking exactly these four points, reliably prevents the most expensive surprises.

10. Summary

Data type choice is one of the database design decisions with the largest gap between initial effort and later consequences. BIGINT instead of INT for growing primary keys, realistic instead of blanket VARCHAR lengths, DECIMAL instead of FLOAT for every monetary amount, and consistent UTC storage for timestamps are four rules that can be implemented in seconds when a table is created, but whose later correction can cost days to weeks of migration effort.

Boolean flags with a real type instead of CHAR(1), ENUM only for genuinely stable value lists, and the deliberate distinction between VARCHAR with a business upper bound and TEXT for unbounded content round out a solid data type choice. Anyone who systematically checks these points in every schema review avoids the most expensive and hardest to reverse mistakes in database design.

11. FAQ: Data Type Choices With Long-Term Consequences