It’s less to do with rdbms specifics and more to do with things like globally available replication and super low latency.

As for features I mean you could use Postgres and some extensions and you’re good to go. If you want a document db you can just use jsonb type.

You need to know your access patterns and what queries you will be running beforehand when working with something like dynamo. You can also do relational modeling with dynamo following single table design and basically pre join.

Transactions and joins are the parts that are tricky to implement in a performant way in distributed systems. No SQL systems generally scale well by leaving those features out. Or at least heavily restricting them.