Keynote-worthy features

Often new database releases come with feature sets that are not all that exciting, and may appear like they are only incremental in functionality. This usually sits okay with me, as boring usually corresponds with useful.

However, the subject of today's post is the opposite of that. I want to have a little bit of fun and try my hand at listing features that I think could be keynote worthy. Don't read too much into the details, since I still stand by the list of my top 5 feature requests as being more important. None of which are mentioned here.

A minimal privilege audit

Granting only the minimum required privileges to users is a real annoyance in MySQL, since even a seasoned DBA can rarely remember which privileges apply to each statement. The tidb-server should be able to record when each privilege was last used by a user, so if after some time (30-90 days) they've not been used, the privilege can be revoked.

For bonus points, a grant on *.* should report when only dbname.* was used (splitting the privilege to be finer grain).

The graphical view of the audit could appear directly in the TiDB dashboard, with a revoke button next to each unused privilege.

Data pipelines

This feature is copied from SingleStore. It's pretty common to load data from an external source into a database (either streamed or on a recurring basis).

In fact, the use-cases are common enough that it can be implemented directly in TiDB as CREATE PIPELINE. On the surface it seems easy enough for users to do this themselves, but that's not entirely true: making the pipeline robust can be a lot of work.

An aside: I really like that there is a proposal to extend LOAD DATA to support importing from s3, and using tidb-lightning's physical import as the underlying engine. This is all a step in the right direction.

Native SaaS Tenancy Support

It's common for SaaS applications to have a single column in all their tables such as customer_id.

This is a bit of a divergence from the SQL standard, but it should be possible to denote that customer_id is the tenant column, and then have a session variable to SET tenancy_filters='customer_id=10'. Unless the user is privileged, they can then not query tenancy tables without tenancy_filters set. When set, the tenancy filters act similar to a view and apply to all queries on tenancy tables.

This might sound like it's not that useful on its own, but it already prevents accidental leaks of data between tenants. If you then add performance views that drill down per tenant, this suddenly becomes very powerful. When customer_id=10 says they have performance issues, you can view their database usage specifically.

Transaction-level digests

This feature is partially implemented in TiDB already, but I'm not sure it's as useful as it could be. What I want to be able to do is summarize a particular transaction and then be able to view key statistics: total time, query time, client latency (total time-query time), number of queries.

Really my goal is to be able to dive in when an internal user reports "cart checkouts are slow". I should be able to see the corresponding transaction on the DB side and confirm all the time was spent in client latency, so it was likely the application calling an external API contributing to the problem.

Since there is some variability to the statements used in "cart checkouts", ideally instead of auto-hashing the transaction digest, I can optionally name it with START TRANSACTION /*T![trxdigest] 'cart_checkout' */.

Better hot row handling

This one is an old request of mine, but support for SKIP LOCKED really is keynote worthy. As I said previously, "Hot rows are the bane of existence for distributed systems" and SKIP LOCKED provides some welcome relief.

I saw that there was recently some movement on the TiDB issue for this, but unfortunately it has stalled because the work is not easy.