Boolean, Binary and UUID Types

Introduction

Next, we will explore Boolean, Binary and UUID Types as follows.

Boolean Type

BOOLEAN is a logical data type used to store true/false states, supporting three-valued logic such as TRUE, FALSE and NULL (unknown or missing data).
Storage size is 1 byte.
Commonly used to toggle features, activation status and soft delete flags (is_deleted).
Postgres supports flexible INSERT syntax, so you do not necessarily have to write TRUE or FALSE. You can use equivalent keywords such as:
- TRUE state: 'true', 't', 'yes', 'y', '1', 1
- FALSE state: 'false', 'f', 'no', 'n', '0', 0

Binary Data Types

BYTEA (byte array) is used to store raw binary data without text processing, such as image files, PDF files, audio files, encryption certificates, RSA keys and more.
Storage size is highly flexible.
- For short binary strings: Data size + 1 byte overhead.
- For long binary strings (over 2 KB): Uses compression and TOAST storage area, incurring 4 bytes overhead or an 18-byte pointer.
- Maximum limit is 1 GB per field.
Display, input and output formats:
- Hex format (Default): Data is represented as a string prefixed with x followed by Hex characters (such as xDEADBEEF).
- Escape format (Legacy): Represented in ASCII format with escape sequences.
Applications:
- Storing thumbnails, encryption keys and configuration files.
- Although BYTEA allows storing files up to 1 GB, in production systems, you should not store large files like images or videos directly in the Database. Instead, upload files to Object Storage (such as Amazon S3 or MinIO) and store only the file path string (URL/Path) using the TEXT type in Postgres to optimize performance for backups and queries.

UUID Type

UUID (Universally Unique Identifier) is a 128-bit string used to create globally unique identifiers without risk of collision, even when generated independently across different servers without communicating with each other.
Displayed as a 32-character Hex string divided into 5 groups separated by hyphens, such as a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11.
Storage size is fixed at 16 bytes on disk, which is far more optimal than storing a UUID as a TEXT or VARCHAR(36) string that consumes at least 37 bytes.
The total number of possible UUID values is 2^128 unique values (3.4 x 10^38 = 340 undecillion = 340 trillion trillion trillion trillion). This number is vast, making the collision probability virtually zero.
Comparison with auto-incrementing SERIAL types:
- Provides better security (no risk of counting or scanning ID sequences like id=1, id=2), making it ideal for Distributed or Microservices architectures where each service generates IDs independently without collision.
- Using UUIDv4 results in completely random values, which can cause Node Splitting and data fragmentation in a B-Tree Index.
- PostgreSQL 18+ supports the native uuidv7() function to generate timestamp-ordered UUIDs, overcoming the limitations of older UUID versions.

Detail

Create the table as follows:

CREATE TABLE test (
    id SERIAL PRIMARY KEY,

    col_boolean BOOLEAN DEFAULT TRUE,
    col_boolean_not_null BOOLEAN NOT NULL DEFAULT TRUE,

    col_bytea BYTEA,

    col_uuid_v4 UUID NOT NULL DEFAULT gen_random_uuid(),
    col_uuid_v7 UUID NOT NULL DEFAULT uuidv7(),

    col_text TEXT,
    col_varchar_36 VARCHAR(36)
);

BOOLEAN

-- Statement 1
INSERT INTO test(col_boolean) VALUES (TRUE);
INSERT INTO test(col_boolean) VALUES (FALSE);
INSERT INTO test(col_boolean) VALUES (NULL);

-- Query 2
SELECT
  id,
  col_boolean,
  pg_column_size(col_boolean) AS size_bytes
FROM test;

Statement 1: You can combine this with DEFAULT TRUE to set up a default value when you do not need to INSERT a value into this column.

By default, you can INSERT a NULL value.
Using NOT NULL will block this behavior.

Query 2: Check the storage size in bytes of the value. The Boolean type consumes only 1 byte.

BYTEA

-- Statement 1
INSERT INTO test (col_bytea) VALUES ('\x89504e470d0a1a0a0000000d49484452');

-- Statement 2
INSERT INTO test (col_bytea) VALUES (decode('89504e470d0a1a0a0000000d49484452', 'hex'));

-- Statement 3
INSERT INTO test (col_bytea) 
VALUES (decode('iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVR42mNk+M9QDwADhgGAWjR9awAAAABJRU5ErkJggg==', 'base64'));

-- Statement 4
INSERT INTO test (col_bytea) VALUES (pg_read_binary_file('/path-to-file'));

-- Query 5
SELECT 
  relname AS table_name,
  pg_size_pretty(pg_relation_size(oid)) AS table_volume,
  relTOASTrelid::regclass AS table_TOAST_name,
  pg_size_pretty(pg_relation_size(relTOASTrelid)) AS table_TOAST_volume
FROM pg_class
WHERE relname = 'test';

-- Query 6
SELECT
  id,
  col_bytea
  length(col_bytea) AS col_length,
  pg_column_size(col_bytea) AS col_size
FROM test

Statement 1: INSERT hex strings directly starting with the \x character sequence. Statement 2: Use the decode function with Hex input strings. It functions similarly to Statement 1, but offers a safer approach when passing Hex values as input parameters. Statement 3: Use the decode function with a Base64 input string. Statement 4: Read files directly using the pg_read_binary_file function. This method only applies if the file resides on the same server as the Database.

Query 5: Used to inspect the size of the TOAST table when utilized.

By default, if a cell stores more than 2KB (2040 bytes), the data is compressed and stored in the TOAST table.
You can see that the initial table has a size of 8192 bytes, while the TOAST table consumes 1064 kB.

Query 6: Used to check column size after compression. You can see the original value had a length of 866280, which was reduced to 530288 after compression.

UUID

-- Statement 1
INSERT INTO test (col_text, col_varchar_36)
VALUES ('019fdf26-9c5c-7844-a464-42bed8138e86', '019fdf26-9c5c-7844-a464-42bed8138e86');

-- Query 2
SELECT 
  col_uuid_v4,
  pg_column_size(col_uuid_v4) AS col_uuid_v4_size_bytes,
  col_uuid_v7,
  pg_column_size(col_uuid_v7) AS col_uuid_v7_size_bytes,
  col_text,
  pg_column_size(col_text) AS col_text_size_bytes,
  col_varchar_36,
  pg_column_size(col_varchar_36) AS col_varchar_36_size_bytes
FROM test;

Statement 1: You can use TEXT and VARCHAR(36) to store UUID data.

Statement 2: Used to compare sizes across UUID, TEXT and VARCHAR(36) data types.

You can see that UUID consumes only 16 bytes, whereas TEXT and VARCHAR(36) require 37 bytes.
To generate a UUIDv4, use the gen_random_uuid function. For UUIDv7 (which is better), use the uuidv7 function.
You can check the UUID version by inspecting the 15th character in the string:
- 6da84f43-2c8a-[4]ec5-99bc-6d6db49a110f => version 4
- 019fdf26-9c5c-[7]844-a464-42bed8138e86 => version 7