Materialize: musing: consistency and transactions using namespaces

[rjnn]

In our quest to support transactional SQL semantics, we have a few design constraints:

1. materialize needs some kind of 'heads up' about the sets of sources that will be queried together, to ensure that there is the possibility to take a consistent snapshot. @mjibson has proposed extending the BEGIN TRANSACTION syntax to force the user to say upfront what relations will be queried in the transaction.
2. This works, but at the cost of breaking compatibility - many external tools emit BEGIN TRANSACTION statements and expect them to "just work".

I call this approach "pre-registration" and it is a fine approach. As an aside, it reminds me of the sorts of pre-registration required in Calvin-based transactional databases (e.g. FaunaDB) that need a heads up on the rw-sets that will be affected by a transaction in order to time-order them. The cost, however, is too high for this to be the only way one can get transactional semantics. I would like this to be a "last resort", and for there to be an alternate path for compatibility.

One of the things that comes up is that we could perhaps have "pre-pre-" registration of sets of relations that are expected to be queried together. Such pre-pre-registration will ensure that such relations can always be queried, at any time, together, without any additional work. This, of course, comes at a cost - those relations need to be maintained and timestamped in a coordinated fashion.

One natural way to approach this - to me at least - I'm curious if @frankmcsherry has some thoughts on the ergonomics - is to tie this to namespacing. So far, we don't really use the separate "databases" for anything. We do have databases - and we even have separate "schemas". We do not currently use these for anything meaningful (perhaps we will in the future for roles and access control, etc.) but we could use namespaces to separate out relations that can be joined with each other without pre-registration.

So here's what I propose: all relations in a "database" must be queryable, at any time, with each other, consistently. This will produce strain on the timestamper/coordinator (I don't actually know which subsystem, I welcome elucidation on this point). For users who want to tell the system to relinquish this coordination for performance reasons, they are welcome to create separate databases and to put the various sources/relations in the separate databases. This explicitly creates separated time-domains, so it makes sense that users would - if they choose to later - have to manually specify reasons why the system should go out of its way to all joining across them. Strictly speaking, we are using the term "database" for compatibility purposes, but we're treating them more as "time domains" (A phrase @frankmcsherry has used several times, perhaps even when verbally proposing this exact behavior, I have forgotten, and I mention only because on the off chance that this is a good idea, I do not claim originality here)

I'm assigning this to @mjibson to hold the token on consistency/transaction semantics, but this is more of a musing than directly actionable. cc @justinj who I had a verbal conversation with about this as well.

[frankmcsherry]

Related: https://github.com/MaterializeInc/materialize/issues/244

[benesch]

Assorted thoughts:

1. I'd like to avoid tying databases to time domains. This came up before in #244, and several times in Slack. I'm uncomfortable with the idea that a database/schema in Materialize would have more semantic meaning than it does in other systems. I think it is safer to have a separate time domain object (e.g. CREATE TIME DOMAIN) which tables can be moved into and out of irrespective of their database.

2. I'm not quite seeing how the concept of time domains solves the issue of BEGIN TRANSACTION requiring you to declare the tables you wish to touch up front. Is the idea that when you touch the table in a time domain in a transaction, you keep that timestamp alive for all tables in that time domain? And touching tables in a different time domain in that same transaction would be rejected? I believe you would also need to enforce that within a time domain, the slowest view determines the visible timestamp for _every_ query involving tables in that time domain. Otherwise a transaction that reads fast table A might choose a time later than a subsequent transaction that reads slow table B, even within the same time domain.

3. Can we close either this issue or #244? They seem to have nearly complete overlap.

[benesch]

There is also issue #885.

[frankmcsherry]

I'm uncomfortable with the idea that a database/schema in Materialize

I think this derives from the dual use of the word "database". For some hunk of people that includes me but not you, a database is a thing you turn on because you want various properties including consistency. In SQL land it is a level of some naming hierarchy.

We can try and invent a new word for "the quantum of things that are consistent", but database is unfortunately a pre-existing term that would fit perfectly, were it not for the (imo, way less common) other use.

What is the prior art for naming in systems that manage multiple "databases in the sense of RDBMS"?

you keep that timestamp alive for all tables in that time domain?

I think that was the intent. Each "time domain" would be linearized. Transactions spanning "time domains" would have no such guarantee. If you have slow tables, you get stale views for all of its tables.

Can we close either this issue or #244? They seem to have nearly complete overlap.

It's a musing about how to address issues like #244 and #855. I'm not sure it needs to get closed out? Or, is there a better place to put cross-cutting proposals?

[benesch]

I'm uncomfortable with the idea that a database/schema in Materialize

I think this derives from the dual use of the word "database". For some hunk of people that includes me but not you, a database is a thing you turn on because you want various properties including consistency. In SQL land it is a level of some naming hierarchy.

We can try and invent a new word for "the quantum of things that are consistent", but database is unfortunately a pre-existing term that would fit perfectly, were it not for the (imo, way less common) other use.

Yeah, the SQL standard really screwed everyone over here. The use of the term "schema" to mean "namespace inside of a database" is a big sin too. Regardless, that's the world we live in. If we tie consistency domains to databases, you'll end up with some very unhappy RBAC customers down the line, because someone who only has access to one database will want to create multiple consistency domains, and they won't be able to.

What is the prior art for naming in systems that manage multiple "databases in the sense of RDBMS"?

PostgreSQL calls this a "database cluster" (https://www.postgresql.org/docs/9.5/creating-cluster.html) which everyone agrees is an awful name. I don't know about other database products.

you keep that timestamp alive for all tables in that time domain?

I think that was the intent. Each "time domain" would be linearized. Transactions spanning "time domains" would have no such guarantee. If you have slow tables, you get stale views for all of its tables.

👍

Can we close either this issue or #244? They seem to have nearly complete overlap.

It's a musing about how to address issues like #244 and #855 [sic]. I'm not sure it needs to get closed out? Or, is there a better place to put cross-cutting proposals?

But essentially this musing already took place, by Arjun, a year ago in #885. It was more terse, but the bones were there. And I raised the concern about conflating CREATE DATABASE and time domains in #244, but no one ever responded, and now we're having the discussion here again.

I certainly don't want to discourage people from jotting down thoughts! It just feels like we're going in circles, rather than making forward progress.

[mjibson]

I am more convinced by the argument to invent a new thing instead of use database hierarchy for this problem. If we want to present as postgres and plan to work with ORMs then users will at some point expect us to behave similarly and support features like RBAC mentioned above.

After discussion with Nikhil we propose:

1. Move forward with #4625 under Arjun's propsed changes. A txn that doesn't specify read tables gets isolation read committed, if it does it gets serializable. That should allow metabase and other applications to not break. We document this.
2. Figure out how time domains will fit in (either new syntax or as part of naming hierarchy). A txn that doesn't specify read tables but whose first query touches a set of tables all within a time domain is serializable within that time domain. An error is produced if a table outside that time domain is later read or if the first query specifies a table in a time domain and a table in another time domain or not in any time domain.

This makes read committed the default. But allows serializable if they pre-pre opt in via time domains or pre opt in via BEGIN WITH READS FROM. Will that make sense to users? I think we can present it as "if you do nothing you get read committed, if you want serializable do one of these two things" and we maybe don't need to document the edge cases, or just document them at the bottom.

[rjnn]

Preface: I'm fine with @benesch's procedural recommendations. Part of this is I'm just not a regular github user so I'm violating norms around opening new issues. Feel free to close what you want. Sorry.

It just feels like we're going in circles, rather than making forward progress.

This is true. But we now have an owner to own moving this forward (@mjibson) so hopefully this is the last time.

OK, to the meat: I don't find these arguments convincing, and still think the fact that we have already two levels of nesting (Databases and Schemas) makes databases work well as time domains without inventing yet another layer for users to learn about. It also has sensible defaults for people that err on the side of consistency rather than read committed. If we want to hit other people on the head about their lack of consistency guarantees, I'd like to occupy the highest ground first. @mjibson's proposal has us read committed by default particularly when users explicitly say BEGIN TRANSACTION which is usually when they actually want some sort of atomicity guarantee. Letting them down at that point makes me sad, and I hope it makes you sad too!

If we tie consistency domains to databases, you'll end up with some very unhappy RBAC customers down the line, because someone who only has access to one database will want to create multiple consistency domains, and they won't be able to.

Disagree. This works fine with RBAC. For example, if databases are used to assign time domains (as per my and frank's proposal):

• If a user wants to have separate roles with different privileges that share a single time domain, they can do so by creating two schemas within that database, and GRANTing the two separate roles different privileges for those schemas.
• If a user wants to assign separate time domains for a single role, they can create two databases, and then GRANT one role privileges for both.

They have the exact degrees of freedom to manipulate role privileges and time domains separately: two.

I don't want to pull rank here, I just think my idea is better. I don't like inventing new concepts, and think the default behaviors that fall out are better when we re-use databases. That said, Matt owns the decision here, and can pick the direction he thinks is best (so I'm reluctantly LGTM on @mjibson's proposal above, if that's the direction he chooses, I've said my dissent).

Yeah, the SQL standard really screwed everyone over here.

On this note, related issues: 1 through 4659.

[mjibson]

Letting them down at that point makes me sad, and I hope it makes you sad too!

It does!

Disagree. This works fine with RBAC. For example, if databases are used to assign time domains (as per my and frank's proposal):

Thank you for having thought of specific examples here and addressing them. I hadn't thought through the possible negative repercussions of grouping time domains and RBAC but the possibility that they existed is why I was generally on the side of separating them. I'm fickle and now think that unless we can come up with a "X won't work and users want it" example it is better to do the time-domains-at-database-level thing.

Some questions (maybe to myself) to answer before moving forward with time domains:

1) Will users ever expect to need cross-database time domains? Is begin with reads from enough to handle that?
2) What are the performance/memory downsides to having time domains? What needs to be documented to avoid those? (I'm planning on researching and writing a blog/doc on how time works all throughout mz so I think this will be answered during that investigation.) If there are big perf problems here that would encourage users to use separate time domains, the cross-database time domain question becomes more relevant.

[benesch]

The procedural issues are moot provided there is a path to making forward progress, and getting some subset of these issues closed. I agree that having Matt own this is likely to result in more success than we've had in the past. When this is done, I would love to see #244, #885, _and_ this issue get closed!

I don't want to pull rank here, I just think my idea is better.

I don't think anyone is disputing that using databases as time domains is the more ergonomic solution. The question is whether doing so would doom the product to costly PostgreSQL incompatibilities down the line, like so many of Cockroach's "improvements" to PostgreSQL semantics did.

Disagree. This works fine with RBAC. For example, if databases are used to assign time domains (as per my and frank's proposal):

• If a user wants to have separate roles with different privileges that share a single time domain, they can do so by creating two schemas within that database, and GRANTing the two separate roles different privileges for those schemas.
• If a user wants to assign separate time domains for a single role, they can create two databases, and then GRANT one role privileges for both.

This doesn't address the case of a user who has been granted permission to access only one database. What if that user wants to create sources in two time domains without involving the superuser? That's not possible in this model.

In Postgres, databases are often used for multitenancy, where only the DBA has permissions to create a new database, and users/applications get access to only their provisioned databases. To that end, the only two truly global objects in a Postgres installation are databases and users. (Well, also tablespaces, but those aren't relevant to the discussion at hand.)

[mjibson]

We could design this feature so that database-as-time-domain is a default but at some future date add syntax that would disable it, and allow users to create whatever time domains they want. That might allow for all possible use cases and make most people happy most of the time.

[benesch]

One alternative, that I think would resolve my concerns, is to introduce the concept of a database-level time domain. There would be a default time domain per database, but you could also create new time domains via e.g.

CREATE TIME DOMAIN name ...  FOR TABLE ...

The inspiration for the design is publications. See CREATE PUBLICATION in the PostgreSQL documentation for details.

Then you'd be able to split a database into multiple time domains as you like. The one thing you wouldn't be able to do is put tables from different databases into the same time domain, but it sounds like that's ok with everyone.

[rjnn]

+1 on @benesch 's default. I'm fine with new syntax that allows folks to do customization but where the defaults are the same as what I proposed (one per database).

[rjnn]

Yeah, upon reread my main concern is about sensible defaults that preserve both consistency and compatibility. Introducing the third layer (explicit TIME DOMAIN syntax) is fine - as long as to the user who chooses not to bother (mostly I think because they're using tools like Metabase that tie their hands), this lines up on the side of consistent results and 'just works out of the box' as opposed to TRANSACTION ABORT.

@benesch 's "one time domain per database" by default gets that behavior exactly.

[mjibson]

A version of this got implemented in #5849. Closing.