3 ms·
Great summary. Multi-node relational DBs are very much double-edged swords. This article mentions the potential data loss during failover events. I've complaine
by hot_gril 3y ago
Great summary. Multi-node relational DBs are very much double-edged swords. This article mentions the potential data loss during failover events. I've complained in the past that Heroku Postgres advertises HA like a strict improvement and leaves the actual failover mechanism in the very fine print; the standby master is async, so you can lose data. There's no free lunch here, you either tolerate some data loss or tolerate extra latency before commits.
When you get into sharded DBs, a lot of limitations aren't obvious at first. I've used Citus a long time ago and Spanner much more recently. Spanner feels almost like NoSQL: Each table is like a distributed KV store. Indexes are just tables where the PK is the indexed col(s) and the val is the main table's PK, though you can also store additional cols (aka denormalize) in the index to avoid an extra join. You can't combine two indexes to filter on two cols; you need one composite index. More advanced things like order-limit, WHERE (NOT) IN, and subqueries tend to be slow. The query planner is pretty limited, and often I just force things. You also have to really know what you're doing with the pkeys. Which is all understandable, given its requirements.
I forget the limitations of Citus ACID, but they're significant. Spanner has full ACID, but even basic operations are quite slow. Single-node Postgres actually doesn't have full ACID unless you put it in the slower SERIALIZABLE mode, but you don't really need it.
I agree with those who say it's better to focus on sharding at the application layer if possible. If you can't do that, it's probably due to the underlying nature of your problem, in which case sharding at the DB layer in an efficient way tends to be even harder. Sometimes it makes sense, but it's not magic.
- azurelake 3y ago> You can't combine two indexes to filter on two cols; you need one composite index Can you expand on how you perceive traditional RDMSs to be different for this case?
- hot_gril 3y agoCREATE TABLE foo (id bigserial, bar int, baz int); CREATE INDEX foobar ON foo(bar); CREATE INDEX foobaz ON foo(baz); SELECT \* FROM foo WHERE bar = 2 AND baz = 4; Postgres (and I think MySQL) will use both indexes in the above query*. Spanner can only use one index, which will be slow if there are many non-matching bar=2 or baz=4 rows. So Spanner needs CREATE INDEX foobarbaz ON foo(bar, baz); Which Postgres could also use, and it'd be a bit faster, but the index-combining is decently fast too and much nicer when you consider a table with like 10 cols and many ways to filter/join. * https://www.postgresql.org/docs/current/indexes-bitmap-scans.html https://www.postgresql.org/docs/current/indexes-bitmap-scans...
- franckpachot 3y agoBitmap Scan relies on shared buffers. Distributed SQL cannot share the buffers between nodes. BitmapAnd is nice but expensive. I prefer a compound index on foo(bar, baz) but PostgreSQL needs an additional index if there are query with condition on baz only. YugabyteDB, thanks to hybrid scan, can use this single index also for SELECT * FROM foo WHERE baz = 4;
- franckpachot 3y agoI used your example to show how the 2 indexes solution is better on PostgreSQL, but the one composite index is the best one for YugabyteDB (PostgreSQL on a Spanner-like architecture): https://dev.to/yugabyte/one-fat-index-or-two-indexes-on-each-columns-postgresql-vs-yugabytedb-ngj https://dev.to/yugabyte/one-fat-index-or-two-indexes-on-each...
- hot_gril 3y agoYeah, I rarely use composite indexes in Postgres. Only if I want to squeeze a little more performance out of a frequent combo query.
- emptysea 3y agoI’m not really familiar with the trade offs of modes with Postgres but why isn’t Serializable necessary?
- hot_gril 3y agoThe default read-committed is good enough for most use cases, explained in https://www.postgresql.org/docs/current/transaction-iso.html https://www.postgresql.org/docs/current/transaction-iso.html (search "it is just right for simpler cases"). If somehow you've designed a schema that causes race conditions in the default mode, you're probably better off changing your design than you are switching modes. Serializable mode uses some kind of optimistic concurrency, where your transaction might get halfway through then fail because of a conflict with another one, which also fails. Then you have to do retry + random backoff on the client side. Spanner does something similar. Problem with Postgres serializable mode is it's slow and won't scale well if many readers/writers are touching the same data. I get it if this answer isn't satisfying. Partial ACID is worrisome, and full ACID is expensive.
- hot_gril 3y agoForgot to add, any Spanner query that I expect to be reasonably fast for realtime usage has to be very simple. In Postgres I often get away with much more complex ones.