Redshift's ACID compliance
Amazon Redshift provides ACID transactions, but its implementation reflects what it is built for. Understanding where it differs from a transactional database prevents two common mistakes: assuming Redshift cannot be trusted with a multi-statement change, and assuming it behaves like PostgreSQL under concurrent writes.
Atomicity
Section titled “Atomicity”Fully supported. A transaction either commits entirely or rolls back entirely; a failure part-way through leaves no partial change behind.
Consistency
Section titled “Consistency”Supported. Transactions and constraints keep the data in a valid state, with the caveat that Redshift does not enforce primary key, foreign key or uniqueness constraints — they are declared for the query planner’s benefit and are not checked on write. Consistency of that kind has to be guaranteed by the loading process.
Isolation
Section titled “Isolation”This is where Redshift differs most from an OLTP engine. It offers two serializable
isolation levels. SNAPSHOT isolation is the default for both provisioned clusters and
Serverless workgroups: each transaction reads the latest committed snapshot, and concurrent
writes conflict only when they touch the same rows. SERIALIZABLE is stricter — it also
prevents write skew, at the cost of cancelling more transactions with a serializable
isolation violation (error 1023). Query STV_DB_ISOLATION_LEVEL to see which one a
database uses. Either way, the mechanics differ from an OLTP engine:
- Read operations do not take locks, so a long analytical query never blocks a load.
- Write operations take table-level locks rather than row-level ones.
- Under SERIALIZABLE, two concurrent writes to the same table serialise against each other regardless of whether they touch the same rows.
- Serialisation failures are reported to the application, which must retry the transaction.
- Long-running queries can be affected by maintenance operations such as
VACUUM.
The practical consequence is that a workload of many small concurrent writes performs badly and produces serialisation conflicts, while a workload of large batch loads and concurrent reads performs exactly as intended.
Durability
Section titled “Durability”Supported. Data is replicated across the nodes of a cluster and backed up continuously to Amazon S3. Redshift Serverless creates a recovery point every 30 minutes, retained for 24 hours, from which a longer-lived snapshot can be taken.
What this means
Section titled “What this means”Redshift is a data warehouse, not a transactional database. It offers ACID transactions so that a batch load or a multi-statement transformation is safe, not so that it can serve an application’s write path. Frequent small transactions belong on RDS or Aurora; the warehouse should be loaded in bulk and read heavily. See OLTP vs OLAP.