The system tables contain a wide range of different types of table - some are ordinary Redshift tables, many are row-store leader node tables, some are Redshift Spectrum tables, and, which is the relevant type for us now, some are not tables are all, but table-like representations of in-memory database state.
These tables-like representations of in-memory state are the tables
with the prefix in their name of stv
, and are heavily used
in the query which underlies this page, and this matters in that
stv
tables, where they are not really tables, do not
participate in MVCC
.
For all tables participating in MVCC
, a query reading
any of those table will see the rows which were present when the query
began to run, thus ensuring a consistent view of the database; any
changes which happen, or are happening, after the query began, are not
seen.
When a table does not participate in MVCC
, what we get
from each table is simply whatever happened to be in there in the
moments the query reads that table - and queries are starting, modifying
their state, and completing, all the time, with the changes manifesting
as new, changed, or removed rows WHILE the query is in the
process of reading the table.
As such, it’s entirely normal for there to be inconsistencies in the information in any given row in this page, because a query might be present in one table when I read it, but then have finished by the time I join to another table, where I’m expecting additional information about that query.
These anomalies do occur, but they are almost always fleeting, and will be gone in the next refresh.