When designing a database schema, an important decision is deciding on the type used for the key columns. These are the primary key and foreign key columns in a database, such as users.id and posts.user_id, respectively. The two most common choices are auto-incrementing integer types and Universally Unique Identifiers (UUIDs).

In this article, we’ll examine each of these to understand the trade offs between them, and then examine a third option that our experience dictates offers the best of both worlds: Time-Sorted Unique Identifiers (TSIDs). We’ll explain how TSIDs work, what the pros and cons versus the other two options are, and look at an implementation of TSIDs in PostgreSQL that we’re currently using in our production systems today.

In software development, “Technical Debt” often emerges as a foreboding specter, casting a long shadow over codebases and development teams alike. Yet, herein lies a provocative truth: technical debt is not a tangible entity lurking within lines of code. It’s a metaphor, a way of thinking about the accumulated consequences of past decisions and shortcuts.

In this article, we’ll examine this metaphor and see how technical debt has less to do with the code itself and more about the choices and compromises that emerge from the challenges within software development. We’ll delve into two forms of it: emergent technical debt, which arises from evolving system requirements, and deliberate technical debt, a strategic choice to prioritize rapid development over code quality. By looking at technical debt in this way, we not only refine our understanding of the concept but also identify effective strategies for managing it.

When someone with an idea for a software product sets out to research how to create it, they’ll quickly run into two pieces of advice regarding building software, often presented in tandem:

1. You need to determine if there’s demand in the market for your idea by validating it.
2. In order to validate your idea, you need to build an MVP.

An ‘MVP’ here is a minimum viable product. The term has been in use since the early 2000s, when it rose to popularity in the startup world. In general an MVP is a product that has a minimal set of features but is entirely usable. The intent behind an MVP is that it allows someone who sets out to build software to do so at a more modest cost by keeping the initial feature set slim but still offering something of value to consumers. Whatever our view is on the totality of the concept, there’s useful advice that can be distilled from this idea. But there’s also something missing.

Our application’s SQL database is a good place to start with performance optimization, as it doesn’t require changing our infrastructure or major rewrites of the code. Adding indexes and rewriting queries are generally isolated measures that we can take to improve the performance of our application. However, sometimes even after optimization we’ll find performance is still worse than required. This may be for a variety of reasons—our request volume is so large that our database is struggling to serve all the queries even after optimization, or we’re running already optimized but more complex queries whose performance is still inadequate.

One technique we can employ in such a situation is server side caching. In simple terms, server side caching is saving the result of an expensive query or computation and making it more quickly retrievable. The results are typically written against and retrieved using a particular ID or URL that acts as a distinct identifier for the particular data.

In the previous post in this series we discussed the importance of proper use of indexes for performance when defining your SQL database. Creating indexes is a simple but essential part of application performance. However, once your database schema is created and indexes are employed, another key aspect of building a fast application is effectively leaning on the database in order to quickly execute work and perform computations. Doing so effectively will often reduce the total amount of work that your application servers as well as your database have to perform. What this looks like in practice is writing SQL queries that use the full breadth of features available.