My experience with PG ended a few years ago. The thing that killed PG for me is the lack of collation for searches. I haven't tested since the last updates that mentioned something along these lines, so this may have improved.
PG for years relied on unaccent which is not a true solution as it requires manually storing two versions of a text (the original and the un-accented one) and handle indexes + searches manually for each field... Not to mention unaccent itself is an extension you have to install. Whereas collation is built-in and it just works by handling it all under the hood in SQL Server. This feature alone is worth switching databases for my use cases.
That and the updates being a royal pain.
thanks for the quick reply, will surely look into it.
1. I'd like a native way to scale horizontally based on tenant ids without using citus.
2. array constraints (foreign keys) -> meaning that each item in the array references an id in another table.
I’d like to see a true monotonic counter in Postgres. Serials can increment out of order when multiple transactions are active. We can obtain the WAL LSN, but it’s not related to the currently active transaction. I really like FoundationDB’s versionstamp, would be great to have that in Postgres.
How is a "true monotonic counter" supposed to interact with atomicity and isolation of transactions? Sounds like a global bottleneck.
Is uniqueness sufficient or is it really required to step through all integers without missing any?
EDIT: Missing primary keys in-between also bothers me, however, never found a decent solution for this at scale.
Thanks for the reply,will surely look into it.
Parallell insert. I can easily saturate a CPU with insert statements, with io very low.
What can I say, except thank you very much.
That seems like an unintuitive consequence. Saturating disk would have been obvious. But CPU?
Does anything about the INSERT pattern seem "special"? Or does it happen for many different "kinds of INSERT statements"?