I see Cockroachdb does not support schemas in the Postgresql sense.
https://forum.cockroachlabs.com/t/schema-name-defaults-to-database-not-to-publci/653 says its more like Mysql. My understanding for Mysql is that one can join across databases on the same server. Which is the same as joining across multiple schemas in Postgresql.
Does Cockroachdb support joining across databases?
Yes, CockroachDB supports joining across databases.
You may be interested in this RFC which discusses our use of “database” and “schema” in comparison to other databases, and how we might evolve towards more consistency with postgres in this area.
Thanks,
For clarity, on Postgresql one might have multiple databases each with multiple schemas running on a single instance. Using JDBC, to connect to a database the client specifies the database name in the URL.
To achieve the same on Cockroachdb one would have to start up separate clusters for each database and the JDBC client would specify the cluster’s port to connect to a particular database?
Database here used in the Postgresql sense.
Well, you could do that, creating a three-level hierarchy cluster/database/table to match postgresql’s database/schema/table. But starting additional clusters is a very heavyweight process. What exactly are you trying to achieve? There may be an easier way to do it with multiple databases within a single cluster.
For now I am just trying to understand how I would go about porting existing apps written on Postgresql to Cockroachdb.
We make extensive use of the database/schema/table architecture. Often they run together on one Postgresql instance but occasionally if one of the databases’ usage is resources/io heavy we can move it to its own dedicated hardware without any impact on the clients.
Seems to me that Cockroachdb has one less namespacing level to Postgresql, so its either startup a new cluster or use a naming convention like prefixing the database name.
Are there plans for a 3 tier namespace similar to Postgresql? I did not see mention of such plans in the RFC
if one of the databases’ usage is resources/io heavy we can move it to its own dedicated hardware without any impact on the clients.
If hardware isolation is your concern, you can get this in CockroachDB using zone configurations. You can assign attributes to your nodes and then add a constraint to a database or table that causes it to be replicated only to nodes with matching attributes.
Are there plans for a 3 tier namespace similar to Postgresql? I did not see mention of such plans in the RFC
An earlier draft of the RFC proposed reworking things to use a three-level namespace, but it was too late to make breaking changes as we were stabilizing for 1.0. Now, we’re still vaguely interested in moving towards a three-level namespace, but we’ll have to figure out how to do it without breaking existing applications, and we don’t have any plans to work on this in the near future.
Ok thanks.
For the most part its been a breeze getting things working.
Great stuff!