PostgreSQL Deep Dive Part 1 - Schema Organization and Numeric Types | Abhishek Kumar Singh

Access complete, unfiltered study notes maintained on GitHub for easily maintaing Second Brain Dir

Schema Organization in PostgreSQL

PostgreSQL's approach to database organization differs fundamentally from other RDBMS systems like MySQL. Understanding these differences is crucial for effective database design and management.

Hierarchical Structure

PostgreSQL implements a three-tier hierarchy:

bash

postgres_cluster/
├── database_1/
├   ├── schema_1/
├   ├   ├── table_1
├   ├   └── table_2
├   └── schema_2/
├       ├── table_3
├       └── table_4
└── database_2/
    └── schema_1/
        └── table_5

bash

postgres_cluster/
├── database_1/
├   ├── schema_1/
├   ├   ├── table_1
├   ├   └── table_2
├   └── schema_2/
├       ├── table_3
├       └── table_4
└── database_2/
    └── schema_1/
        └── table_5

This differs from MySQL's two-tier structure:

bash

mysql_instance/
├── database_1/
├   ├── table_1
├   └── table_2
└── database_2/
    └── table_3

bash

mysql_instance/
├── database_1/
├   ├── table_1
├   └── table_2
└── database_2/
    └── table_3

Schema Fundamentals

Default Configuration:
- Every PostgreSQL database initializes with a 'public' schema
- This schema is automatically in the search path
- Objects in the public schema can be referenced without schema qualification
Schema Benefits:
- Logical grouping of database objects
- Namespace isolation for large applications
- Enhanced security through granular access control
- Support for multi-tenant architectures

Numeric Types Deep Dive

PostgreSQL offers a comprehensive suite of numeric types, each optimized for specific use cases.

Integer Types

PostgreSQL provides three primary integer types:

sql

CREATE TABLE integer_examples (
    small_val SMALLINT,  -- 2 bytes, -32768 to +32767
    normal_val INTEGER,  -- 4 bytes, -2147483648 to +2147483647
    big_val BIGINT      -- 8 bytes, -9223372036854775808 to +9223372036854775807
);

sql

CREATE TABLE integer_examples (
    small_val SMALLINT,  -- 2 bytes, -32768 to +32767
    normal_val INTEGER,  -- 4 bytes, -2147483648 to +2147483647
    big_val BIGINT      -- 8 bytes, -9223372036854775808 to +9223372036854775807
);

Storage considerations:

sql

SELECT
    pg_column_size(1::SMALLINT) as smallint_size,
    pg_column_size(1::INTEGER) as integer_size,
    pg_column_size(1::BIGINT) as bigint_size;

sql

SELECT
    pg_column_size(1::SMALLINT) as smallint_size,
    pg_column_size(1::INTEGER) as integer_size,
    pg_column_size(1::BIGINT) as bigint_size;

Output:

smallint_size | integer_size | bigint_size
--------------|--------------|--------------
     2        |      4       |      8

smallint_size | integer_size | bigint_size
--------------|--------------|--------------
     2        |      4       |      8

Aliases:

SMALLINT → INT2
INTEGER → INT4
- This covers most of our needs
- Give data much room to grow, specially for primary key.
BIGINT → INT8

Key characteristics:

No unsigned integer support
Automatic overflow detection
Consistent behaviour across platforms

Numeric/Decimal Types

The NUMERIC type provides exact decimal arithmetic:

sql

-- Numeric accepts two parameters "numeric(precision, scale)"
CREATE TABLE numeric_examples (
    precise_value NUMERIC(10,2),  -- 10 total digits, 2 after decimal
    arbitrary_precision NUMERIC    -- unlimited precision
);
 
-- Examples of precision and scale
INSERT INTO numeric_examples VALUES
    (123.45, 123.45),
    (123.4567, 123.4567),  -- truncates to 123.46 in first column
    (12345678.90, 12345678.90);
 
INSERT INTO numeric_examples VALUES
    (12345678123.901, 12345678.90231); -- Error: A field with precision 10, scale 2 must round to an absolute value less than 10^8.
-- this max_value number 10^8 comes from = 10^(p-s) - 10^(-s)

sql

-- Numeric accepts two parameters "numeric(precision, scale)"
CREATE TABLE numeric_examples (
    precise_value NUMERIC(10,2),  -- 10 total digits, 2 after decimal
    arbitrary_precision NUMERIC    -- unlimited precision
);
 
-- Examples of precision and scale
INSERT INTO numeric_examples VALUES
    (123.45, 123.45),
    (123.4567, 123.4567),  -- truncates to 123.46 in first column
    (12345678.90, 12345678.90);
 
INSERT INTO numeric_examples VALUES
    (12345678123.901, 12345678.90231); -- Error: A field with precision 10, scale 2 must round to an absolute value less than 10^8.
-- this max_value number 10^8 comes from = 10^(p-s) - 10^(-s)

Advanced numeric operations:

sql

-- Scale manipulation
SELECT
    123.456::NUMERIC(10,2),   -- 123.46
    123.456::NUMERIC(10,1),   -- 123.5
    123.456::NUMERIC(10,0);   -- 123
 
-- Negative scale handling
SELECT
    1234.56::NUMERIC(10,-2);  -- 1200

sql

-- Scale manipulation
SELECT
    123.456::NUMERIC(10,2),   -- 123.46
    123.456::NUMERIC(10,1),   -- 123.5
    123.456::NUMERIC(10,0);   -- 123
 
-- Negative scale handling
SELECT
    1234.56::NUMERIC(10,-2);  -- 1200

Performance considerations:

NUMERIC operations are significantly slower than integer operations
Storage size varies based on the number of digits
Ideal for financial calculations requiring exact precision

Floating-Point Types

PostgreSQL offers two floating-point types:

sql

CREATE TABLE float_examples (
    single_precision REAL,             -- 4 bytes, 6 decimal digits precision
    double_precision DOUBLE PRECISION  -- 8 bytes, 15 decimal digits precision
);
 
-- Precision demonstration
INSERT INTO float_examples VALUES
    (1.234567890123456, 1.234567890123456);
 
SELECT * FROM float_examples;
-- Output shows precision differences
-- single_precision | double_precision
-- 1.23457          | 1.234567890123456

sql

CREATE TABLE float_examples (
    single_precision REAL,             -- 4 bytes, 6 decimal digits precision
    double_precision DOUBLE PRECISION  -- 8 bytes, 15 decimal digits precision
);
 
-- Precision demonstration
INSERT INTO float_examples VALUES
    (1.234567890123456, 1.234567890123456);
 
SELECT * FROM float_examples;
-- Output shows precision differences
-- single_precision | double_precision
-- 1.23457          | 1.234567890123456

Special values handling:

sql

SELECT
    'Infinity'::REAL,    -- Infinity
    '-Infinity'::REAL,   -- -Infinity
    'NaN'::REAL;         -- NaN
 
-- Mathematical operations with special values
SELECT
    'Infinity'::REAL + 'Infinity'::REAL,  -- Infinity
    'Infinity'::REAL - 'Infinity'::REAL,  -- NaN
    0 * 'Infinity'::REAL;                 -- NaN

sql

SELECT
    'Infinity'::REAL,    -- Infinity
    '-Infinity'::REAL,   -- -Infinity
    'NaN'::REAL;         -- NaN
 
-- Mathematical operations with special values
SELECT
    'Infinity'::REAL + 'Infinity'::REAL,  -- Infinity
    'Infinity'::REAL - 'Infinity'::REAL,  -- NaN
    0 * 'Infinity'::REAL;                 -- NaN

Aliases:

REAL → float4
DOUBLE → float8 → DOUBLE PRECISION

Numeric Type Selection Guidelines

For whole numbers:
- Default to INTEGER
- Use SMALLINT for space optimization when range is known
- Reserve BIGINT for specific large-number requirements
For decimal numbers:
- Use NUMERIC for financial calculations
- Use REAL/DOUBLE PRECISION for scientific calculations
- Consider INTEGER with implicit decimal point for money

Selection matrix:

Use Case                 | Recommended Type
------------------------|------------------
Primary Keys            | INTEGER
Money (cents)           | INTEGER
Scientific Calculations | DOUBLE PRECISION
Financial Calculations  | NUMERIC
Small Ranges (<32K)     | SMALLINT
Large Sequences         | BIGINT

Use Case                 | Recommended Type
------------------------|------------------
Primary Keys            | INTEGER
Money (cents)           | INTEGER
Scientific Calculations | DOUBLE PRECISION
Financial Calculations  | NUMERIC
Small Ranges (<32K)     | SMALLINT
Large Sequences         | BIGINT

This concludes Part 1 of our PostgreSQL deep dive. Next, we'll explore character types, binary data, and UUIDs in detail.

🌿

PostgreSQL Deep Dive Part 1 - Schema Organization and Numeric Types

Schema Organization in PostgreSQL

Hierarchical Structure

Schema Fundamentals

Numeric Types Deep Dive

Integer Types

Numeric/Decimal Types

Floating-Point Types

Numeric Type Selection Guidelines

Join My Exclusive Newsletter Community