Efficient PostgreSQL Database Design and Data Types

1. Designing Efficient Database Tables

When designing a database table, it’s essential to choose data types that are representative and compact:

• Representative: The data type should cover the full range of expected values.
• Compact: The type should not over-allocate space. For example, there’s no need to use a BIGINT if the value range only requires a SMALLINT.

Example: Age

A person’s age typically falls between 0 and 150. So, instead of using INTEGER or BIGINT, which would use more space, it’s more efficient to use SMALLINT.

CREATE TABLE people (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age SMALLINT
);

Choosing the correct data type is crucial for both storage efficiency and query performance.

2. Understanding PostgreSQL Numeric Data Types

Numeric vs Floating-Point Types

In PostgreSQL, you can choose between numeric and floating-point types depending on the precision requirements of your application:

• Numeric types (NUMERIC, DECIMAL) are precise and can handle very large values, but they are slower for arithmetic operations.
• Floating-point types (REAL, DOUBLE PRECISION) are fast but approximate values, making them suitable for scenarios where small precision loss is acceptable.

Numeric Example:

The NUMERIC(PRECISION, SCALE) type lets you define both the total number of digits (PRECISION) and the number of decimal places (SCALE).

SELECT 12345::NUMERIC(5, 3); -- Result: 123.450

If you try to insert a value that exceeds the defined precision, PostgreSQL will raise an error:

SELECT 125345::NUMERIC(5, 3); -- Error: Numeric Field Overflow

Floating Point Example:

Floating-point types are ideal for storing approximations where speed is more critical than absolute precision.

SELECT 6.0::float4 * (3.0 / 10.0)  as float,
       6.0::NUMERIC * (3.0 / 10.0) as numeric

Output:
+------------------+-------+
|float             |numeric|
+------------------+-------+
|1.7999999999999998|1.8    |
+------------------+-------+

3. Storing Money in PostgreSQL

Using the money Data Type

PostgreSQL has a built-in money data type, which is convenient for storing currency values in the default format (US Dollars). However, it has some limitations:

Advantages:

• It’s simple to use and displays values with the dollar sign.
• It can accept different numeric values in formats like VARCHAR, INT, and DECIMAL in INSERT statements.

CREATE TABLE account (
    id SERIAL PRIMARY KEY,
    amount MONEY
);

INSERT INTO account (amount)
VALUES ('$123.00'),
       ('$23.99'),
       (22.22),
       (19.99),
       (1200);

Disadvantages:

• Precision loss: When performing math operations, precision may be lost. It’s not ideal for high-precision financial calculations.
• Currency handling: The currency is determined by the lc_monetary setting (default is en_US.utf8 for USD), which can cause confusion if you need to handle multiple currencies. Changing lc_monetary will change the display format but not convert the actual values.

Alternative Approaches:

• Use NUMERIC: This data type is flexible and precise, but it can be slower for arithmetic operations.
• Use INTEGER: For better performance, consider storing monetary values as integers by converting the currency to the smallest unit (e.g., cents). This maintains precision and optimizes math operations.

-- Store $199.99 as 19999 cents
CREATE TABLE account (
    id SERIAL PRIMARY KEY,
    amount INTEGER -- Amount stored in cents
);

If you’re working with multiple currencies, it’s best to store a separate currency column to differentiate between them:

CREATE TABLE account (
    id SERIAL PRIMARY KEY,
    amount INTEGER,  -- In cents
    currency CHAR(3) -- e.g., 'USD', 'EUR', 'AED'
);

4. Special Values: Infinity and -Infinity

PostgreSQL allows you to store extremely large or small values using Infinity and -Infinity. These values are useful when dealing with scenarios where a value is beyond the representable range.

SELECT '-inf'::float;  -- Result: -Infinity
SELECT 'inf'::float + 'inf'::float;  -- Result: Infinity
SELECT 'inf'::float - 'inf'::float;  -- Result: NaN (Not a Number)

These values can be used to represent extremes like maximum stock values or when you want to indicate that a value is unbounded.

5. Type Casting in PostgreSQL

PostgreSQL offers powerful type casting functionality that allows you to convert values between different data types.

Example:

SELECT '100'::INTEGER, '23.99'::NUMERIC, pg_typeof('100'::INT2);

This will cast the string '100' to an INTEGER and the string '23.99' to a NUMERIC. You can also check the type of a cast using the pg_typeof() function:

SELECT pg_typeof('100'::INT2); -- Result: smallint

6. PG_COLUMN_SIZE Function

PostgreSQL offers the PG_COLUMN_SIZE() function, which can be helpful when you need to understand the storage size of different data types. This is important for optimizing table design and query performance.

Example:

SELECT PG_COLUMN_SIZE(200::int2) AS int2,
       PG_COLUMN_SIZE(3000) AS int4,
       PG_COLUMN_SIZE(299.99) AS numeric,
       PG_COLUMN_SIZE(2922139.21199) AS numeric;

Output:

+----+----+-------+-------+
|int2|int4|numeric|numeric|
+----+----+-------+-------+
|2   |4   |10     |14     |
+----+----+-------+-------+

This can help you assess whether you’re using an unnecessarily large data type for a given value.

Conclusion

PostgreSQL offers a rich set of data types and functions that can significantly optimize your database design. By selecting the appropriate data type — whether it’s a numeric, floating-point, or special type like money or Infinity—you can make your applications more representative, efficient, precise and scalable.

• Always choose compact and representative data types for your columns.
• Consider using NUMERIC for financial data and INTEGER for money values in the smallest units (e.g., cents).
• Leverage type casting and PG_COLUMN_SIZE to optimize your queries and understand how data types impact storage.

By keeping these considerations in mind, you’ll be well-equipped to design robust, high-performance PostgreSQL schemas.