Understanding transactions / read consistency

[pH14]

Hello! Very intrigued by Neon and was curious to hear about what kind of support it has / plans to have for transactions.

Should we expect all the same transaction isolation levels as Postgres? Do read-write transactions scale with compute nodes, or does Neon rely on a single primary for all writes (and if so, how does primary election work / handle failovers)? Do read-only compute nodes support read transactions? Will there be a way to ensure replicas are at-least-as-recent-as-X (e.g. an LSN) when issuing queries?

Thanks! 🙇

[hlinnaka]

Hello! Very intrigued by Neon and was curious to hear about what kind of support it has / plans to have for transactions.

Should we expect all the same transaction isolation levels as Postgres?

Yes.

Do read-write transactions scale with compute nodes, or does Neon rely on a single primary for all writes (and if so, how does primary election work / handle failovers)?

It's single-writer. We haven't implemented the high availability parts yet, so there is no failover yet. However, due to separation of compute and storage, it's actually pretty fast to just spawn a new primary if the old one dies.

There is a consensus algorithm in the safekeepers that ensures that only one primary can be generating WAL at a time. That avoids split-brain scenarios if you accidentally have two primary nodes running at a time; only one of them will be able to actually write the WAL, and the other one will error out.

Do read-only compute nodes support read transactions?

Yes.

Will there be a way to ensure replicas are at-least-as-recent-as-X (e.g. an LSN) when issuing queries?

Not yet. That's be pretty straightforward to implement in principle, but we would do it in community PostgreSQL, as that's not specific to the Neon storage.