Mastering Bitstring Optimization for Boolean Data
In this lesson, you will learn how to drastically reduce storage footprints by replacing multiple BOOLEAN columns with a single BIT VARYING type using bitwise logic.
The Storage Efficiency Problem
In PostgreSQL, a BOOLEAN type typically consumes 1 byte of storage. If you have a table tracking eight different user preferences (e.g., is_active, wants_email, is_admin, etc.), storing these as individual columns takes 8 bytes per row. By packing these flags into a BIT(8) or BIT VARYING type, we can represent all eight states in a single byte.
Bitwise Mapping
Imagine our user settings defined as a bitmask where each position corresponds to a specific permission. We can use bitwise operators like & (AND), | (OR), and ~ (NOT) to manipulate these flags directly at the database level.
Consider a table storing user features as a bitstring:
CREATE TABLE user_settings (
user_id INT PRIMARY KEY,
flags BIT(4) -- Position 1: active, 2: email, 3: admin, 4: notifications
);
-- Turn on the "admin" bit (the 3rd bit) using bitwise OR
UPDATE user_settings
SET flags = flags | B'0010'
WHERE user_id = 1;
-- Check if a user is an admin using bitwise AND
SELECT * FROM user_settings
WHERE (flags & B'0010') = B'0010';
Why Use Bitstrings Instead of Enums?
While ENUM types are great for categorical data, they are fixed and harder to combine. Bitstrings shine when a single entity can hold multiple independent boolean states simultaneously. By storing these as bits, you keep your table row narrow, which increases the number of rows that fit into a single data page (the "cache hit" ratio).
When retrieving data, you don't need to join against a secondary table or check eight separate columns; a single bitwise comparison against the packed BIT value identifies exactly which features are active. This is a highly efficient pattern for high-traffic telemetry or feature-flagging systems where row density is critical.
Key Takeaway: By packing multiple boolean flags into a single bitstring, you minimize storage overhead and enable high-performance filtering using bitwise operators.