ACID Database Properties with PostgreSQL: Part 3 - Consistency
Understanding Database Consistency
Welcome to Part 3 of our ACID database properties series! After exploring transactions, Atomicity, and Isolation, we now tackle Consistency - arguably the most multifaceted property in ACID.
Consistency is unique because it operates at multiple levels: it’s about data validity, read consistency, and how these concepts interact with the other ACID properties. Let’s unpack what consistency really means in the context of PostgreSQL.
What is Consistency?
Consistency in ACID means that any transaction will bring the database from one valid state to another valid state. But here’s where it gets interesting: consistency is largely defined by you, the user. You set the rules through constraints, triggers, and business logic, and the database enforces them.
Think of consistency as having two dimensions:
- Consistency in Data: The database enforces rules you define (constraints, foreign keys, check constraints)
- Consistency in Reads: Whether transactions see a consistent snapshot of the data
Consistency is User-Defined
Unlike Atomicity, Isolation, and Durability (which the database manages internally), Consistency is primarily your responsibility. The database provides the tools—constraints, triggers, transactions—but you define what “valid” means for your application.
For example:
- PostgreSQL can enforce that a number is positive, but you decide if account balances should never be negative
- PostgreSQL can ensure foreign key integrity, but you design the relationships
- PostgreSQL can guarantee serializable transactions, but you choose when to use them
Consistency in Data: Database Constraints
Consistency in data is about enforcing rules that keep your database in a valid state. PostgreSQL provides several mechanisms to ensure data consistency.
1. Referential Integrity (Foreign Keys)
Foreign keys are the cornerstone of relational database consistency. They ensure that relationships between tables remain valid.
Analogy: Like a library catalog system—you can’t check out a book that doesn’t exist in the catalog, and you can’t delete a book from the catalog if someone has it checked out.
-- Create users table
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL
);
-- Create orders table with foreign key constraint
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)
);
-- This maintains consistency
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO orders (user_id, total_amount) VALUES (1, 99.99); -- Works!
-- This violates referential integrity
INSERT INTO orders (user_id, total_amount) VALUES (999, 99.99);
-- ERROR: insert or update on table "orders" violates foreign key constraint "fk_user"
-- DETAIL: Key (user_id)=(999) is not present in table "users".Foreign Key Actions: Maintaining Consistency on Delete/Update
PostgreSQL provides several options to maintain consistency when referenced rows change:
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
-- CASCADE: Delete orders when user is deleted
CONSTRAINT fk_user FOREIGN KEY (user_id)
REFERENCES users(id) ON DELETE CASCADE
);
-- Or use RESTRICT to prevent deletion
ALTER TABLE orders DROP CONSTRAINT fk_user;
ALTER TABLE orders ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT;
-- Or SET NULL to preserve the order but remove the user reference
ALTER TABLE orders ALTER COLUMN user_id DROP NOT NULL;
ALTER TABLE orders DROP CONSTRAINT fk_user;
ALTER TABLE orders ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL;2. Check Constraints: Enforcing Business Rules
Check constraints allow you to define custom rules that data must satisfy.
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
discount_percentage INTEGER DEFAULT 0,
stock_quantity INTEGER NOT NULL,
-- Domain integrity: price must be positive
CONSTRAINT positive_price CHECK (price > 0),
-- Business rule: discount cannot exceed 75%
CONSTRAINT reasonable_discount CHECK (
discount_percentage >= 0 AND discount_percentage <= 75
),
-- Business rule: stock cannot be negative
CONSTRAINT non_negative_stock CHECK (stock_quantity >= 0)
);
-- This works
INSERT INTO products (name, price, discount_percentage, stock_quantity)
VALUES ('Laptop', 999.99, 20, 50);
-- This violates positive_price constraint
INSERT INTO products (name, price, stock_quantity)
VALUES ('Invalid Product', -10.00, 100);
-- ERROR: new row for relation "products" violates check constraint "positive_price"
-- This violates reasonable_discount constraint
INSERT INTO products (name, price, discount_percentage, stock_quantity)
VALUES ('Over-discounted Item', 50.00, 80, 10);
-- ERROR: new row for relation "products" violates check constraint "reasonable_discount"3. NOT NULL and UNIQUE Constraints
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
account_number VARCHAR(20) UNIQUE NOT NULL, -- Must be unique and present
balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- This violates NOT NULL constraint
INSERT INTO accounts (account_number, email) VALUES (NULL, 'test@example.com');
-- ERROR: null value in column "account_number" violates not-null constraint
-- This violates UNIQUE constraint
INSERT INTO accounts (account_number, email) VALUES ('ACC001', 'user@example.com');
INSERT INTO accounts (account_number, email) VALUES ('ACC001', 'another@example.com');
-- ERROR: duplicate key value violates unique constraint "accounts_account_number_key"How Consistency Interacts with Other ACID Properties
Consistency doesn’t exist in isolation—it works in tandem with Atomicity and Isolation to maintain database integrity.
Consistency + Atomicity
Atomicity ensures that constraint violations cause complete rollback.
When a transaction violates a consistency rule, atomicity ensures that none of the transaction’s changes are applied:
BEGIN;
-- Initial state: Account has $1000
INSERT INTO accounts (account_number, balance, email)
VALUES ('ACC001', 1000.00, 'user@example.com');
-- Try to create an order and update balance
INSERT INTO orders (user_id, total_amount) VALUES (1, 500.00);
UPDATE accounts SET balance = balance - 500 WHERE account_number = 'ACC001';
-- Attempt to create another order that would make balance negative
INSERT INTO orders (user_id, total_amount) VALUES (1, 600.00);
UPDATE accounts SET balance = balance - 600 WHERE account_number = 'ACC001';
-- This violates our business rule if we have a CHECK constraint
-- Because of Atomicity, the ENTIRE transaction rolls back
-- The account still has $1000, and no orders were created
ROLLBACK;Let’s add a proper constraint and see atomicity in action:
-- Add check constraint to prevent negative balances
ALTER TABLE accounts ADD CONSTRAINT positive_balance CHECK (balance >= 0);
BEGIN;
UPDATE accounts SET balance = 1000.00 WHERE account_number = 'ACC001';
-- This succeeds
UPDATE accounts SET balance = balance - 400 WHERE account_number = 'ACC001';
-- Balance is now 600
-- This fails
UPDATE accounts SET balance = balance - 700 WHERE account_number = 'ACC001';
-- ERROR: new row for relation "accounts" violates check constraint "positive_balance"
-- Because of atomicity, the first update is also rolled back
ROLLBACK;
-- Check the balance - it's still 1000, not 600
SELECT balance FROM accounts WHERE account_number = 'ACC001';
-- Returns: 1000.00Consistency + Isolation
Isolation levels affect read consistency—what a transaction sees during its execution.
This is where “Consistency in Reads” becomes crucial. Different isolation levels provide different consistency guarantees:
-- Setup: Create a table with initial data
CREATE TABLE inventory (
product_id INTEGER PRIMARY KEY,
quantity INTEGER NOT NULL CHECK (quantity >= 0)
);
INSERT INTO inventory VALUES (1, 100);Read Committed: Each Statement Sees Latest Committed Data
-- Transaction A
BEGIN;
SELECT quantity FROM inventory WHERE product_id = 1;
-- Returns: 100
-- Transaction B (executes and commits)
UPDATE inventory SET quantity = 50 WHERE product_id = 1;
COMMIT;
-- Back in Transaction A
SELECT quantity FROM inventory WHERE product_id = 1;
-- Returns: 50 (sees the committed change!)
-- This is "read consistency" at statement level
COMMIT;The question: If a transaction committed a change, will a new transaction immediately see the change?
With Read Committed, the answer is: Yes, but only after each statement. Within the same statement, you see a consistent snapshot, but different statements in your transaction might see different data.
Repeatable Read: Snapshot Consistency
-- Transaction A
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT quantity FROM inventory WHERE product_id = 1;
-- Returns: 100
-- Transaction B (executes and commits)
UPDATE inventory SET quantity = 50 WHERE product_id = 1;
COMMIT;
-- Back in Transaction A
SELECT quantity FROM inventory WHERE product_id = 1;
-- Returns: 100 (still sees the snapshot from transaction start!)
COMMIT;
-- Transaction C (new transaction)
BEGIN;
SELECT quantity FROM inventory WHERE product_id = 1;
-- Returns: 50 (new transactions see committed changes)
COMMIT;The question again: If a transaction committed a change, will a new transaction immediately see the change?
With Repeatable Read: No during the transaction, but Yes for new transactions. Your transaction sees a consistent snapshot from when it started, but new transactions see the latest committed state.
Real-World Example: E-commerce Order Processing
Here’s how consistency, atomicity, and isolation work together:
-- Setup: Product inventory with constraints
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price > 0),
stock_quantity INTEGER NOT NULL CHECK (stock_quantity >= 0)
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL CHECK (quantity > 0),
CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products(id)
);
INSERT INTO products (name, price, stock_quantity) VALUES ('Laptop', 999.99, 10);
-- Processing an order that respects consistency
BEGIN;
-- Check stock and create order
INSERT INTO orders (product_id, quantity) VALUES (1, 2);
UPDATE products SET stock_quantity = stock_quantity - 2 WHERE id = 1;
COMMIT; -- Success! Stock is now 8
-- What happens when consistency is violated?
BEGIN;
INSERT INTO orders (product_id, quantity) VALUES (1, 20); -- More than available!
UPDATE products SET stock_quantity = stock_quantity - 20 WHERE id = 1;
-- ERROR: new row violates check constraint "products_stock_quantity_check"
ROLLBACK; -- Because of atomicity, nothing changes
SELECT stock_quantity FROM products WHERE id = 1; -- Still 8Consistency Affects the System as a Whole
An important concept to understand: consistency constraints affect the entire database system, not just individual transactions.
When you define a constraint, it applies to all transactions, all users, all the time. This is different from isolation, which is transaction-specific.
-- This constraint affects EVERYONE
ALTER TABLE accounts ADD CONSTRAINT positive_balance CHECK (balance >= 0);
-- Transaction 1 cannot violate it
BEGIN;
UPDATE accounts SET balance = -100 WHERE id = 1; -- ERROR
ROLLBACK;
-- Transaction 2 cannot violate it either
BEGIN;
UPDATE accounts SET balance = -50 WHERE id = 1; -- ERROR
ROLLBACK;
-- Even direct modifications cannot violate it
UPDATE accounts SET balance = -200 WHERE id = 1; -- ERROR
-- The system as a whole enforces this ruleSystem-Wide Consistency Example
-- Define a system-wide rule: Orders must reference existing customers
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL,
total_amount DECIMAL(10,2),
CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
);
-- Every transaction must respect this
BEGIN;
INSERT INTO orders (customer_id, total_amount) VALUES (999, 100.00); -- ERROR
ROLLBACK;
-- Even bulk operations must respect it
COPY orders FROM '/tmp/orders.csv' CSV; -- Will fail for invalid customer_ids
-- The entire system maintains this consistency ruleConsistency Beyond Relational Databases
While we’ve focused on PostgreSQL (a relational database), consistency challenges exist in all database systems, including NoSQL databases.
NoSQL and Eventual Consistency
Many NoSQL databases trade immediate consistency for availability and partition tolerance (CAP theorem). This leads to eventual consistency—changes propagate over time rather than immediately.
Analogy: Like updating your social media profile. You make a change, but it might take a few seconds before all your friends see the update. Eventually, everyone sees the same version, but there’s a brief period of inconsistency.
Relational vs NoSQL Consistency Trade-offs
-- PostgreSQL (Relational): Immediate Consistency
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;
-- The next transaction immediately sees the new balance
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- New value immediately visible
COMMIT;In contrast, a distributed NoSQL database might:
- Accept the write on one node
- Return success to the client
- Asynchronously replicate to other nodes
- During replication, different nodes might show different values
When to Choose What?
Strong Consistency (PostgreSQL, SQL Server):
- Financial transactions
- Inventory management
- Any scenario where correctness is critical
- When you need ACID guarantees
Eventual Consistency (Cassandra, DynamoDB):
- Social media feeds
- Analytics and logging
- High-availability requirements
- Scenarios where temporary inconsistency is acceptable
Handling Eventual Consistency
Even in PostgreSQL, you might deal with eventual consistency when using:
- Logical Replication: Replicas may lag behind the primary
- Foreign Data Wrappers: External data sources may not be synchronized
- Asynchronous Operations: Background jobs processing data
-- Example: Check replication lag
SELECT
application_name,
client_addr,
state,
sent_lsn,
write_lsn,
flush_lsn,
replay_lsn,
sync_state,
-- Calculate lag in bytes
sent_lsn - replay_lsn AS replay_lag
FROM pg_stat_replication;
-- If replay_lag > 0, replicas are behind (eventual consistency scenario)Advanced Consistency Patterns
PostgreSQL offers powerful tools for complex consistency rules:
Deferrable Constraints - Temporarily violate constraints within a transaction:
CREATE TABLE child (
id INTEGER PRIMARY KEY,
parent_id INTEGER,
CONSTRAINT fk_parent FOREIGN KEY (parent_id)
REFERENCES parent(id) DEFERRABLE INITIALLY DEFERRED
);
-- Allows: INSERT child first, then parent, check at COMMITExclusion Constraints - Prevent overlapping data:
CREATE TABLE room_bookings (
room_id INTEGER,
booked_during TSRANGE,
EXCLUDE USING GIST (room_id WITH =, booked_during WITH &&)
);
-- Prevents double-booking the same room at overlapping timesTriggers - Complex business logic:
CREATE FUNCTION check_order_value() RETURNS TRIGGER AS $$
BEGIN
IF NEW.total_amount > 10000 AND NEW.approved_by IS NULL THEN
RAISE EXCEPTION 'Orders over $10,000 require approval';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;Common Consistency Pitfalls
1. Application-Level Checks Only
-- BAD: Only in app code (race conditions possible!)
-- if balance >= amount: execute("UPDATE...")
-- GOOD: Enforce at database level
ALTER TABLE accounts ADD CONSTRAINT positive_balance CHECK (balance >= 0);2. Missing Foreign Keys
-- BAD: No foreign key - orphaned data possible
CREATE TABLE orders (customer_id INTEGER);
-- GOOD: Always use foreign keys
CREATE TABLE orders (
customer_id INTEGER NOT NULL,
CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
);3. No Transactions for Multi-Step Operations
-- BAD: Separate statements can leave inconsistent state
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- GOOD: Use transactions
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;Key Takeaways
Consistency is user-defined - you define validity through constraints and business logic
Two dimensions:
- Data Consistency: Constraints (CHECK, FOREIGN KEY, UNIQUE, NOT NULL)
- Read Consistency: Isolation levels control what transactions see
Works with other ACID properties:
- Atomicity: Violations cause complete rollback
- Isolation: Controls when changes become visible
System-wide enforcement - constraints apply to all transactions
Visibility of committed changes depends on isolation level:
- Read Committed: Immediately visible
- Repeatable Read: Not during transaction, yes for new ones
Referential integrity maintains consistency across tables
Trade-offs: Relational (strong) vs NoSQL (eventual consistency)
Enforce at database level - don’t rely on application code alone
What’s Next?
In Part 4 of our ACID series, we’ll explore Durability - the property that ensures once a transaction is committed, it stays committed, even in the face of system crashes, power failures, and other disasters.
Stay tuned for Part 4: Durability, where we’ll learn how databases ensure your data survives the unexpected!
Have questions about consistency or want to share your experiences? Drop them in the comments below!