Is data loss expected in spock ?

[SK-000]

Hi Everyone ,

I'm just exploring this spock multi master in postgresql.

Is data loss is expected in spock when high transactional application connecting from multiple region and doing frequent update / insert / delete for same row in a table?

Exactly where this spock will fit along with it's limitation. Just trying to understand before getting into it.

Pls share your thoughts and suggestion..

Thanks in advance !

SK

[danolivo]

@SK-000, such a question deserves a full lecture ;). Basically, it depends on what you designate as 'data loss'.
Concurrent transactions could overwrite the same rows in parallel on different nodes. So, usually you should use REPEATABLE READ transactions with 2PC/prepared transactions machinery to ensure that other transactions (which must also be forced to repeatable read isolation) see your update before changing the row.

I know of one multimaster extension that, as a design choice, forced such a mode to provide the same level of guarantees as a single Postgres instance does - https://github.com/postgrespro/mmts, but it works way too slowly in real life - that's why Spock, EDB, and AWS are looking for a proper trade-off.

So, generally, it is assumed that you choose appropriate settings for your transactions on your own, and we invent tools to organise scalable active-active replication with sufficient guarantees.

[SK-000]

Thanks for your response danolivo

[danolivo]

@SK-000 , Feel free to close the issue if that's it for you ;)

[mason-sharp]

@SK-000 As danolivo mentioned, there are tradeoffs to different approaches. If you are updating the same row 1000 times per second in multiple locations with 150ms ping time in between, it is not ideal. However, if you are frequently updating the same row and it only involves increasing and decreasing numeric data, you could consider using delta apply columns in Spock. We also have a tool, ACE, that can be used to detect any data differences between the nodes. Feel free to reach out to us if you would like to discuss the nature of your workload.