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.

There are many aspects that go into making a fast application. Web application performance is a broad topic because there are numerous concerns in making a page load quickly or a button click feel responsive. One of the difficulties developers must grapple with in pursuit of performance is that any one of these facets can become a bottleneck if overlooked.

Building fast web applications requires a comprehensive understanding and examination of the entire system. In this post, we kickoff a series that covers essential elements that go into building high performance web applications. Throughout this series, we are going to discuss the performance of web applications written in a high level language (such as PHP or Python), backed by a SQL database, and where the frontend interacts with the back end through HTTP requests that download HTML, JSON, or a combination of both, since this structure is the most common one found today.

Recently, a client asked us to work on a new Rules Engine for them. This system serves as the back end to a highly configurable dashboard where non-technical users define business rules for how a certain component of the system behaves. When deployed, this system has to handle a substantial production workload, so performance is also a key consideration.

There’s a common misconception as to what final tagless, and more specifically, a final encoding is. A common claim I see is that final tagless means coding against typeclasses. The mtl library and code written in MTL style are raised as examples of final tagless.

I would like to argue that what people are referring to as final tagless is in fact just coding against an interface and that the novelty of final tagless really has very little to do with abstract interfaces. So then what is final tagless? It’s a complicated name for a not-so-complicated idea.

SQL is a very powerful tool for querying data. It allows you to write queries against your relational data in a declarative manner, letting you describe what data that you want to retrieve without having to describe how to retrieve it. In most cases, this works very well, and the query optimizer in many database engines (MySQL, PostgreSQL, etc.) will create an efficient query plan.

Efficient query plans rely on a schema that uses appropriate data types, especially for primary key columns, where doing things such as misusing VARCHAR can kill performance. Another critical element of enabling fast query plans is appropriately indexing columns, which eliminates the need to perform full table scans when retrieving data. Unfortunately, even following these schema rules, it’s possible to write SQL queries that have surprisingly poor performance, often leading to the bewilderment of the developer writing such a query. Perhaps the most surprising aspect of this type of query is that it is often written in the most intuitive way to describe the data.