CosmosDB PostgreSQL limitation issues

Question

Hi,

We are using ComosDB PosgreSQL and found few product Limitations from Microsoft can be found here,

https://learn.microsoft.com/en-us/azure/cosmos-db/postgresql/reference-limits

 

Some of the key items from the list that impacted us:

• Limitation of one database
• Append-only (no UPDATE/DELETE support)
• No index support, index scans, or bitmap index scans
• No support for foreign keys, unique constraints, or exclusion constraints

 

• Additional issue if one user created a table another user could not modify it with out transfering ownership to other users – slowing down development. – no full admin support or ability to create roles to grant specific permissions.

 

Based on these findings PostgreSQL on Cosmos doesn't seem to relational in nature and may not be a good selection for our applications.

As CosmosSD is a mature product, please confirm if these are truly limitations and what are the timelines to address these limitations?

Answer 1

Hi @devla.vsingh ,

Most of the above limitations are from columnar tables section. Columnar table is an optional feature. Regular cosmos db postgres sharded and I sharded tables do not have these limitations.

• The limitation to one database is true. Team is working on this.
• It is not true that role creation is not possible. This can be done from the portal under Cluster management > Roles.
• Changing table ownership is not blocked in the manage service. The newly created user, however, needs CREATE permissions on the schema the table was created in. This is how PostgreSQL works. See below. From PostgreSQL docs.

To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the table's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the table. However, a superuser can alter ownership of any table anyway.) To add a column or alter a column type or use the OF clause, you must also have USAGE privilege on the data type

citus=> alter table citus_created owner to test;

ERROR: permission denied for schema public

citus=> grant all privileges on schema public to test;

GRANT

citus=> \dt+ citus_created

                                  List of relations

Schema | Name | Type | Owner | Persistence | Access method | Size | Description

--------+---------------+-------+-------+-------------+---------------+---------+-------------

public | citus_created | table | citus | permanent | heap | 0 bytes |

(1 row)

citus=>

citus=> alter table citus_created owner to test;

ALTER TABLE

citus=> \dt+ citus_created

                                  List of relations

Schema | Name | Type | Owner | Persistence | Access method | Size | Description

--------+---------------+-------+-------+-------------+---------------+---------+-------------

public | citus_created | table | test | permanent | heap | 0 bytes |

(1 row)

citus=>

That said, this is a managed service. Full admin support on PostgreSQL would essentially give the user access to the underlying infrastructure running the database, I find that ask unreasonable.

Now at this list, it doesn't contain MERGE limitations in the currently released version (full support on single-node, not supported with distributed and managed tables). Team is actively addressing MERGE support in the next two Citus releases. The ones that apply to the service outside of columnar should match what is reported on citusdata.com, placing inline for easier reference:

SQL Support and Workarounds — Citus 11.1 documentation (citusdata.com)

As Citus provides distributed functionality by extending PostgreSQL, it is compatible with PostgreSQL constructs. This means that users can use the tools and features that come with the rich and extensible PostgreSQL ecosystem for distributed tables created with Citus.

Citus has 100% SQL coverage for any queries it is able to execute on a single worker node. These kind of queries are common in Multi-tenant Applications when accessing information about a single tenant.

Even cross-node queries (used for parallel computations) support most SQL features. However, some SQL features are not supported for queries which combine information from multiple nodes.

Limitations for Cross-Node SQL Queries:

• SELECT … FOR UPDATE work in single-shard queries only
• TABLESAMPLE work in single-shard queries only
• Correlated subqueries are supported only when the correlation is on the Distribution Column.
• Outer joins between distributed tables are only supported on the Distribution Column
• Outer joins between distributed tables and reference tables or local tables are only supported if the distributed table is on the outer side
• Recursive CTEs work in single-shard queries only.
• Grouping sets work in single-shard queries only

Hope this helps. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.