0

According to documentation, Amazon Redshift is derived from PostgreSQL 8.

PosgreSQL 8 supports Multiversion Concurrency Control (MVCC) which is in my understanding achieved on physical level by appending new row versions rather than updating in place or marking rows for deleting rather than deleting rows in place. Advantages of MVCC are:

MVCC locks acquired for querying (reading) data do not conflict with locks acquired for writing data, and so reading never blocks writing and writing never blocks reading. PostgreSQL maintains this guarantee even when providing the strictest level of transaction isolation through the use of an innovative Serializable Snapshot Isolation (SSI) level.

Redshift documentation states that

the specialized data storage schema and query execution engine that Amazon Redshift uses are completely different from the PostgreSQL implementation

Also, unlike Postgres which has row-based storage, Redshift is column-based store.

At the same time, Redshift has vacuuming which makes it similar to PostgreSQL. From Redshift documentation:

Amazon Redshift does not automatically reclaim and reuse space that is freed when you delete rows and update rows. To perform an update, Amazon Redshift deletes the original row and appends the updated row, so every update is effectively a delete followed by an insert. When you perform a delete, the rows are marked for deletion, but not removed. The query processor needs to scan the deleted rows as well as undeleted rows, so too many deleted rows can cost unnecessary processing. You should vacuum following a significant number of deletes or updates to reclaim space and improve query performance.

In essence, this is similar to underlying MVCC mechanisms in Postgres. However, I wasn't able to find confirmation that Redshift supports MVCC. What I am looking for is confirmation that parallel read and write transactions on Redshift do not interfere because they operate on independent versions of rows, therefore the question:

Does Amazon Redshift support MVCC?

1

I found an answer in Redshift documentation soon after asking the question. The chapter is called Managing Concurrent Write Operations:

Amazon Redshift allows tables to be read while they are being incrementally loaded or modified.

In some traditional data warehousing and business intelligence applications, the database is available to users only when the nightly load is complete. In such cases, no updates are allowed during regular work hours, when analytic queries are run and reports are generated; however, an increasing number of applications remain live for long periods of the day or even all day, making the notion of a load window obsolete.

Amazon Redshift supports these types of applications by allowing tables to be read while they are being incrementally loaded or modified. Queries simply see the latest committed version, or snapshot, of the data, rather than waiting for the next version to be committed.

That corresponds to MVCC definition from Wikipedia:

MVCC [...] approach: each user connected to the database sees a snapshot of the database at a particular instant in time. Any changes made by a writer will not be seen by other users of the database until the changes have been completed (or, in database terms: until the transaction has been committed.)

When an MVCC database needs to update an item of data, it will not overwrite the old data with new data, but instead marks the old data as obsolete and adds the newer version elsewhere. Thus there are multiple versions stored, but only one is the latest. This allows readers to access the data that was there when they began reading, even if it was modified or deleted part way through by someone else.

Connected with Redshift vacuuming description that I posted in the question, it makes me believe Amazon Redshift does support MVCC. Although I still was not able to find any mention of MVCC in official docs for Redshift.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.