ACID, Partial Indexes and Advanced Patterns in PostgreSQL

ACID Properties

ACID is the set of properties that guarantee reliable transactions in relational databases. PostgreSQL implements all of them rigorously, and understanding each one is essential for making sound data modeling and architecture decisions.

Atomicity

A transaction is indivisible: either all operations within it succeed, or none of them are applied. If any step fails, the database automatically rolls back everything done up to that point.

BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
  UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

If the second UPDATE fails, the first one is also rolled back. The balance never ends up in an inconsistent state.

Consistency

After each transaction, the database must remain in a valid state. This means constraints, foreign keys, check constraints and triggers are all enforced. A transaction that would violate referential integrity is rejected.

INSERT INTO orders (user_id, total) VALUES (9999, 150.00);
-- ERROR: insert or update on table "orders" violates foreign key constraint

Isolation

Concurrent transactions do not interfere with each other. PostgreSQL uses MVCC (Multi-Version Concurrency Control) to allow multiple transactions to read and write simultaneously without unnecessary locking. The default isolation level is READ COMMITTED, but you can configure stricter levels.

Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Committed	No	Possible	Possible
Repeatable Read	No	No	Possible
Serializable	No	No	No

BEGIN ISOLATION LEVEL SERIALIZABLE;
  SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Durability

Once a transaction receives COMMIT, the data is permanently persisted — even if the server crashes immediately after. PostgreSQL guarantees this through the WAL (Write-Ahead Log), which writes changes to disk before confirming the transaction.

Partial Indexes

A partial index covers only a subset of a table’s rows, defined by a WHERE clause. This results in a smaller, faster and more disk-efficient index.

Unique constraint with soft delete

In applications that use soft delete (e.g., the Discard gem in Rails), discarded records receive a timestamp in discarded_at. A traditional unique index would include those records, preventing the reuse of values like tax ID or email.

CREATE UNIQUE INDEX index_employees_on_tax_id
  ON employees USING btree (tax_id)
  WHERE (discarded_at IS NULL);

This allows two records to share the same tax ID as long as only one of them is active. Discarded records take up no space in the index and do not interfere with the uniqueness constraint.

In Rails:

class AddUniqueIndexOnTaxIdToEmployees < ActiveRecord::Migration[7.1]
  def change
    add_index :employees, :tax_id,
      unique: true,
      where: "discarded_at IS NULL",
      name: "index_employees_on_tax_id"
  end
end

When to use partial indexes

Tables with soft delete where most records are active
Columns with skewed value distribution (e.g., only 5% of orders have status pending)
Queries that always filter by the same predicate

CREATE INDEX idx_orders_pending ON orders(created_at)
  WHERE status = 'pending';

If most queries target pending orders and they represent a small fraction of the table, this index is far more efficient than a full index.

Check Constraints

Check constraints validate data at the database level, regardless of the application. Unlike application-level validations, they cannot be bypassed — any operation that violates the rule is rejected.

ALTER TABLE products
  ADD CONSTRAINT chk_price_positive CHECK (price > 0);

ALTER TABLE orders
  ADD CONSTRAINT chk_status_valid
  CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled'));

Attempts to insert invalid data fail immediately:

INSERT INTO products (name, price) VALUES ('Widget', -10);
-- ERROR: new row for relation "products" violates check constraint "chk_price_positive"

Check constraints are especially useful for:

Ensuring numeric values fall within valid ranges
Restricting status columns to allowed values
Validating relationships between columns in the same row (e.g., end_date > start_date)

ALTER TABLE events
  ADD CONSTRAINT chk_dates_valid CHECK (end_date > start_date);

Exclusion Constraints

Exclusion constraints are a generalization of unique constraints. While a unique index prevents exact duplicate values, an exclusion constraint prevents rows from conflicting according to an arbitrary operator.

The classic use case is preventing overlapping intervals — for example, room bookings that must not overlap in time.

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE reservations
  ADD CONSTRAINT no_overlapping_reservations
  EXCLUDE USING gist (
    room_id WITH =,
    tsrange(start_time, end_time) WITH &&
  );

The && operator checks for range overlap. If someone tries to book room 3 from 2pm to 4pm when a booking already exists from 3pm to 5pm, the database rejects it:

INSERT INTO reservations (room_id, start_time, end_time)
  VALUES (3, '2026-03-24 14:00', '2026-03-24 16:00');
-- ERROR: conflicting key value violates exclusion constraint "no_overlapping_reservations"

This validation is impossible to implement reliably in application code alone, because race conditions between concurrent transactions can create overlaps.

Connection Pooling and Timeouts

Each connection in PostgreSQL is an operating system process, typically consuming 5-10 MB of memory. With hundreds of workers, the number of connections can easily exceed the database limit.

Connection Pooling

A connection pooler like PgBouncer acts as middleware between the application and PostgreSQL, maintaining a smaller pool of server-side connections.

[Rails Worker 1] ---\
[Rails Worker 2] -----> [PgBouncer] ----> [PostgreSQL]
[Rails Worker 3] ---/     (20 server      (max_connections = 100)
  (100 client              connections)
   connections)

Transaction pooling mode generally offers the best trade-off: server connections are shared between clients and assigned only during a transaction.

Timeouts for Availability

Timeouts prevent stuck queries from holding connections indefinitely. Configure them in PostgreSQL or in the application:

-- Global statement timeout (in milliseconds)
SET statement_timeout = '5s';

-- Lock acquisition timeout
SET lock_timeout = '3s';

-- Timeout for idle connections in a transaction
SET idle_in_transaction_session_timeout = '30s';

In Rails, configure via database.yml:

production:
  variables:
    statement_timeout: 5000
    lock_timeout: 3000

The combination of connection pooling with aggressive timeouts is what keeps applications stable under high concurrency.