For the past few years, I’ve thought frequently about databases. They’re integral to the products I’ve built, but as a developer rather than a specialist, my relationship with them has sometimes been complicated. Their power is obvious, but their usage can be opaque and maximizing their benefits can be difficult.

I’ve brainstormed some ideas to improve their ergonomics. They’re not entirely fleshed out, and they’re written specifically with SQL databases in mind, but I want to share them sooner than later in hopes someone will find them interesting.

The list covers high-level insights, migrations, deadlock reduction and lambdas. Instead of focusing too deeply on technology, I’m aiming to improve the UX of using a database and to reduce the psychological burden of what can sometimes be seen as a brittle, arcane piece of infrastructure. That sounds like an exaggeration, but I’ve worked at companies where “that’ll need a database change” elicited sighs from the room. I’ve also worked for companies where the databases were offloaded to specialists and the rest of the team felt that there was a barrier to using them. These ideas are part of a foundation that addresses UX concerns and allows future databases to be a continuing source of empowerment for entire teams.

Note that some of them have been implemented in pieces and parts already, but to my knowledge they have never been integrated holistically with a focus on experience.

If you find this article interesting or you’re working in this space, please reach out! My email address is daniel at this domain. I like to talk!

Insights

Idea: provide a high-level overview of whether or not clients are accessing the database in a way that is conducive to sharing resources. Give developers immediate feedback on how well their code adheres to database best practices.

Many challenges in using and scaling databases revolve around them being shared by multiple clients. It’s easy to write clients that unintentionally hog resources, so some developers learn principles to write more cooperative code. For example, they might query smaller amounts of data, keep transactions short, or make sure their query plans can be cached efficiently. An insights feature would report on whether or not the clients are adhering to these and other guidelines, affording developers an understanding of how well the database can manage its resources for multiple clients to access it at the same time.

Developers would write services and other database clients the same way they normally do, but the database would have a configured set of rules or criteria that clients should follow and it would flag the clients that don’t. The results should be accessible centrally via a log or dashboard and ideally, the database should provide instant feedback by sending warnings back to the developers over the database connection. In a stricter environment it might even cancel or modify an operation, like limiting a query’s rows or aborting a long transaction.

By centralizing best practices and exposing them through the database, the insights feature would replace knowledge that’s typically memorized with feedback that’s plain to see. In design or HCI terms, it would replace “knowledge in the head” with “knowledge in the world.” This is a deceptively powerful technique for reducing cognitive load and providing natural cues that lead developers into a pit of success. If instant feedback were implemented, the database would even teach (or remind) developers how to use it in real-time. Tinkerers could ignore the messages until they polish their code for production, and new developers seeing warnings for the first time would have an idea of how to improve their skills.

A key requirement of an insights feature would be identifying each client. Most databases implement access control credentials, but they’re designed for security; in a modern system there might be dozens of instances of a web server that have the same security restrictions and use the same credentials. The database should include an additional way for each client to identify itself and should aggregate its insights based on those client names. They could be grouped or scoped, like “webserver/instance-4.” It might also make sense to try, as best as possible, to identify particular transactions. Writing unique names in the code for each database modification would be too cumbersome, but database libraries could auto-generate an identifier based on the calling code, including a class or method name like a debugger would. The combination of identifiers would make it easy to track down which parts of a client are being flagged.

Migrations

Idea: encapsulate multi-step migration processes. Make the database aware of different versions of the schema so it can help clients transition between them. Update the mental model of schema change from a series of one-offs to a constant, ongoing process.

The term migration has always seemed misleading to me, because while ORMs and migration tools provide the ability to apply a single migration, schema change in practice is a multi-step process if downtime is undesirable. For example, changing a table might involve adding a new table, changing an application to write to both tables, backfilling data from the old table to the new table, changing the application to read from the new table, and then finally stopping writes to the old table and removing it. There are shortcuts depending on the database and the schema change being made, but the general pattern is to support both the old and new version while transitioning between them.

That’s quite a number of steps, including two DDL changes that might be applied as migrations and several client changes that are mandatory for the process to work. Like some of the principles mentioned in the last section, the process has to be learned or memorized because the database doesn’t provide any direction on how to apply it. A built-in schema change feature would be useful to schedule, organize and execute it smoothly.

While it would be possible to implement this process in an ORM or other external tool, there are benefits to making it a native database feature. First, ORM-managed migrations live inside of a particular client, like a Rails app, for example, so they break down when the database is also accessed by cron jobs or other services that are not part of the app with the ORM definitions. The database itself, being a common artifact between the services, is better positioned to manage the process.

Second, giving the database knowledge of the schema change process would allow it to automate some of the steps. It could schedule backfilling of old data in some cases, or it could offer a way to mark the old version of a table as deprecated and ensure that it’s not being accessed before the table is dropped. In some cases, it could even eliminate the need to update what table the clients read from: if the database knows what version of a table a client was written for, and the old table is derivable from the new table, it could seamlessly respond to queries from the client even after the old table is dropped. (An example of when an old table is derivable from a new table is when a column is added; it doesn’t apply to every case.)

Schema change is an enormous topic, so this part of the brainstorm probably needs the most fleshing out. Still, I think it introduces a better mental model of schema change than DDL commands and external ORMs do. Instead of treating a single migration as a prerequisite to a code change, it considers the database and the application(s) to evolve hand-in-hand over time. It also prepares the developers of a fast-changing system to think of schema change as a normal, ongoing process rather than a clunky requirement of living with a database.

Reducing deadlocks

Idea: include a preferred order of table modifications as metadata in the schema itself and use the insights feature to encourage developers to follow it.

In a busy system, deadlocks can be a nuisance that waste resources and impact end-users. There are many techniques to recover from them or to reduce their likelihood, but one efficient way to solve the underlying issue is to take locks out in the same order each time. That’s difficult to organize: in modern companies there can be many, many developers writing database access code, leading to another opportunity to centralize knowledge with a lock-ordering feature built on top of the insights feature idea.

Since every application would have a different ideal locking order, typically starting with tables with fewer writes and ending with the most frequently modified tables, the locking order would be configurable in the database. Then, the insights feature would be extended to compare statements during a transaction to the configured sequence, flagging transactions that access tables in the wrong order. The same details of the insights feature apply: the flags could be viewed after the fact, or developers could be warned immediately with a message, creating instant feedback, or the transaction could be aborted outright if deadlocks were a major concern.

This idea is only a partial solution to deadlocks since it encourages a table access order but doesn’t monitor the order of rows accessed within each table. To be more strict, it should be extended to do that as well. However, writing code that controls what order multiple rows are used in seems at minimum to be annoying and counterproductive towards improving UX, so I’m leaving that as an open question for now. A table access order would combine well with a recovery technique like automatic retries to handle the remaining cases.

Lambdas

Idea: include the foundations for a lambda platform, which is a useful abstraction that immediately makes databases more approachable.

While it may be more of a platform or ecosystem concern than a feature to be implemented directly in a database, the last idea in this brainstorm is support for lambdas, like the ones offered by AWS or other platforms. Lambdas are a great abstraction that immediately removes responsibilities from developers, reducing the amount of code needed to build a new feature and simplifying deployment requirements. Supposedly, Amazon derives quite a bit of benefit from using AWS Lambda internally.

Lambdas and other event-driven abstractions are common nowadays, so I won’t go into too much detail about how this feature should look. A simpler event-driven way to respond to database changes is already widely deployed via Rails and the after_commit callback in its ORM. The challenge with implementing lambdas natively rather than via an ORM would be integrating them into the other aspects of using a database, like keeping up with schema changes, so they reduce cognitive load instead of increasing it. The other ideas on this list are a good starting point so that databases will be prepared to have lambdas built off of them.

Conclusion

The goal of these ideas is to make developers feel more confident interacting with databases and to make it easier to build systems that use databases effectively. For now, they can be considered rough drafts, since databases in operation are much more complex than the summaries I’ve brainstormed here. I haven’t even mentioned the issues that would arise when the database is part of a distributed system, or distributed itself!

If you are curious about adding to this list, one guideline I like to keep in mind is that a database’s core purpose is to facilitate the sharing of a resource between many clients. Ideas to reach that goal will always make a database more usable, even if it ends up in a system that’s low-traffic or small-scale. I’ve used that guideline throughout this brainstorm: insights helps developers write cooperative code, native migrations allow a database’s data to evolve with multiple clients, deadlock reduction reduces waste, and lambdas let developers share those resources more directly via abstraction. The other guideline to keep in mind is that databases are already extremely complex, so new ideas need to feel natural to catch on or they’ll get lost with the myriad other database tips and tricks that have appeared in the last 40 years. I’ve tried to address that using instant feedback (insights), putting knowledge in the world (insights, native migrations, and deadlock reduction) and adjusting mental models (native migrations and lambdas), but there are many other ways to set up database users for success.

If you have questions or comments, feel free to reach out to me. My email address is daniel at this domain. Thanks for reading!