boringSQL | Supercharge your SQL & PostgreSQL powers

Your database is ticking along nicely - until a simple DELETE brings it to its knees. What went wrong? While we tend to focus on optimizing SELECT and INSERT operations, we often overlook the hidden complexities of DELETE. Yet, removing unnecessary data is just as critical. Outdated or irrelevant data can bloat your database, degrade performance,...

Whether you are designing a standalone application or a microservice, you will inevitably encounter the topic of sharing identifiers. Whether it’s URLs of web pages, RESTful API resources, JSON documents, CSV exports, or something else, the identifier of specific resources will be exposed. /orders/123 /products/345/variants/1 While an identifier...

Designing a database schema, whether for a new application or a new feature, always raises a lot of questions. The choices you make can have a big impact on how well your database performs and how easy it is to maintain and scale. Whether you’re just getting started with PostgreSQL or consider yourself a seasoned pro, it’s easy to rely on old...

Understanding how comfortable someone is with databases and SQL often comes down to the features they use. In PostgreSQL, one such feature that distinguishes more advanced users is the MERGE command, introduced in version 15 and expanded in version 17 (in beta at the time of writing this article). Before MERGE, developers typically relied on...

Understanding the relationship between data points is crucial. For instance, you might need to identify the most recent orders for each customer or track changes in sensor readings over time. Unlike aggregate functions, which summarise data into a single row, it is window functions that allow you to analyse data while preserving each row’s...

Working with PostgreSQL, and virtually any database system, extends far beyond merely inserting and retrieving data. Many application and business processes, maintenance tasks, reporting, and orchestration tasks require the integration of a job scheduler. While third-party tools can drive automation, you can also automate the execution of...

This is the third and final instalment of "Deep Dive into PostgREST". In the first part, we explored basic CRUD functionalities. In the second part, we moved forward with abstraction and used the acquired knowledge to create a simple request/approval workflow. In Part 3, we will explore authentication and authorisation options to finish something...

This article explores the pgrx framework, which simplifies the creation of custom PostgreSQL extensions to bring more logic closer to your database. Traditionally, writing such extensions required familiarity with C and a deep understanding of PostgreSQL internals, which could be quite challenging. pgrx lowers the barrier and allows developers to...

Let's recap the first part of "Deep Dive into PostgREST," where we explored the basic functionality to expose and query any table using an API, demonstrated using cURL. All it took was to set up a db-schema and give the db-anon-role some permissions. But unless you are creating the simplest of CRUD applications, this only scratches the surface. ...

The primary motivation behind boringSQL is to explore the robust world of SQL and the PostgreSQL ecosystem, demonstrating how these "boring" tools can cut through the ever-increasing noise and complexity of modern software development. In this series, I'll guide you through building a simple yet fully functional application—a Time Off Manager....

One of the surprises that comes with developing applications and operating a database cluster behind them is the discrepancy between practice and theory, development environment and the production. A perfect example of such a mismatch is changing a column type. The conventional knowledge on how to change a column type in PostgreSQL (and other...

As the database size increases and the number of transactions per second rise, you'll inevitably face the challenge of the table bloat. Although PostgreSQL assists as much as possible with its auto-vacuum feature, there will come a time when you will compel whether to run VACUUM FULL. Unless you have option of longish downtime windows, this is...

Twenty years ago, I would have laughed if you had told me I'd be promoting databases as the technology to turn to. Back then, databases were just a 'dummy' storage for me—a necessary evil, a sentiment shared by many developers. At that point, I felt so strongly about it that I was a trainer for Hibernate, a tool which helped me keep those pesky...

While it's true the most problems can be solved by the appropriate use of the index, there are cases where you will just waste resources doing so. For casual developer it might seems like PostgreSQL decided to do its own thing, but when you look behind the scenes it all makes perfect sense. Here's a quick run down of the some reasons why planner...