Are there any limitations you have run into?
E.g. can you still use all index types that Postgres offers or are there any special distributed index types that CitusDB adds perhaps?
Correct. CTEs are pretty much a no-go, and you also can't do things like join between distributed and non-distributed tables.
Your queries tend to break down into ones where you're hitting a small number of shards (such as when we serve data for the analytics page), or else ones where you tend to aggregate up into temporary, non-distributed tables for further analysis (such as you'd do for infrequent business reporting).
All told, it's been one of the easier tools for us to operationalize, largely thanks to the fact that it's "just PostgreSQL." The one thing I wish we had was better documentation of what kind of consistency guarantees to expect, although for an append-only store like our current use case, that's less of a concern. And to be fair, Ozgun and the other guys at Citus have always been really happy to answer any questions we have.
With this code now going open source, it should be pretty easy to look into these sorts of internals.
My recollection from the last time I played with it, some bits from core postgres are unsupported; things like sequences and recursive CTEs. Maybe all CTEs?
Your queries tend to break down into ones where you're hitting a small number of shards (such as when we serve data for the analytics page), or else ones where you tend to aggregate up into temporary, non-distributed tables for further analysis (such as you'd do for infrequent business reporting).
All told, it's been one of the easier tools for us to operationalize, largely thanks to the fact that it's "just PostgreSQL." The one thing I wish we had was better documentation of what kind of consistency guarantees to expect, although for an append-only store like our current use case, that's less of a concern. And to be fair, Ozgun and the other guys at Citus have always been really happy to answer any questions we have.
With this code now going open source, it should be pretty easy to look into these sorts of internals.