How we capture real production database traffic at Airbnb and replay it offline to load-test, plan capacity, and de-risk upgrades.By: Zuofei Wang, Erluo LiIntroductionAt Airbnb, MySQL-compatible databases are a critical backbone of our online database infrastructure: a fleet of hundreds of clusters supporting thousands of use cases at millions of queries per second (QPS). Operating databases at scale brings hard problems, including sizing clusters for future growth, keeping behavior consistent across version upgrades and migrations, and reproducing production incidents well enough to debug…
ANTI JOIN is a very useful operator from relational algebra. Regrettably, only few dialects support it in terms of SQL syntax, as we’ve written earlier. In jOOQ, you can write it as follows: If your RDBMS supports this natively (e.g. ClickHouse, Databricks), then it is rendered as such. Otherwise, jOOQ will translate this to: But … Continue reading Simplifying ANTI JOIN with jOOQ Syntax →
Some SQL operators are as esoteric as they’re powerful. One of the oldest operator that you’ve likely hardly ever used in real world applications is NATURAL JOIN which is the default in relational algebra. We’ve covered a funky use-case for NATURAL JOIN earlier on this blog. The main reason why it’s not very useful is … Continue reading Why JOIN USING Can Lead to Errors in SQL →
Expedia Group Technology — DataWorkload‑aware routing for TrinoPhoto by Joseph Barrientos on UnsplashTrino — a fork of PrestoSQL — is a powerful tool in modern data analytics, enabling organizations to query large datasets quickly and efficiently. As a distributed SQL query engine, Trino provides fast, scalable insights without requiring data relocation. While Trino is robust on its own, its capabilities are further enhanced when paired with a Gateway, which introduces features such as query routing, strong security, and streamlined cluster management.A brief overviewThe Gateway project…
Ever been here before? Stuck with a job that needs to be continually revisited because its performance gets worse with every passing day, and each attempt at improving said performance yields diminishing returns? This is the situation we found ourselves in with the portfolio balance calculation system—the code responsible for aggregating data from multiple sources... Read more
ARRAY types are a part of the ISO/IEC 9075 SQL standard. The standard specifies how to: But it is very unopinionated when it comes to function support. The ISO/IEC 9075-2:2023(E) 6.47 specifies concatenation of arrays, whereas the 6.48 section lists a not extremely useful TRIM_ARRAY function, exclusively (using which … Continue reading When SQL Meets Lambda Expressions →
RIGHT JOIN is an esoteric feature in the SQL language, and hardly ever seen in the real world, because almost every RIGHT JOIN can just be expressed as an equivalent LEFT JOIN. The following two statements are equivalent: It’s not unreasonable to expect these two statements to produce the same execution plan on most RDBMS, … Continue reading Think About SQL MERGE in Terms of a RIGHT JOIN →
sqldatabricksexcerpt only · body stays at the source
Here's how to save variables in sql for use later in the query. A with statement will provide the key feature. Make a selection (rows) and projection (columns), and you can bind that 2D result to a name. Only a single with statement is allowed in an sql script. You can create multiple variables by separating the values with commas. There's an example script below that creates a permission row, n user rows and then n mapping table rows to relate the two. There are two names bound: permission_id and user_ids. The insert statement returns an id column for the one inserted row. This means that…
A cool standard SQL:2003 feature is the aggregate FILTER clause, which is supported natively by at least these RDBMS: The following aggregate function computes the number of rows per group which satifsy the FILTER clause: This is useful for pivot style queries, where multiple aggregate values are computed in one go. For most basic types … Continue reading Emulating SQL FILTER with Oracle JSON Aggregate Functions →
I’ve blogged about generic ways of getting top 1 or top n per category queries before on this blog. An Oracle specific version in that post used the arcane KEEP syntax: This is a bit difficult to read when you see it for the first time. Think of it as a complicated way to say … Continue reading Getting Top 1 Values Per Group in Oracle →
In a previous blog post, we’ve advertised the use of SQL EXISTS rather than COUNT(*) to check for existence of a value in SQL. I.e. to check if in the Sakila database, actors called WAHLBERG have played in any films, instead of: Do this: (Depending on your dialect you may require a FROM DUAL clause, … Continue reading An Efficient Way to Check for Existence of Multiple Values in SQL →
Strava challenges offer a fun way for athletes to compete against themselves and others! Back in 2020, our legacy challenge leaderboard system was running into bottlenecks and scalability problems on a regular basis, and we often found ourselves putting out fires to keep the system stable. In late 2020 and early 2021, I worked on a project to replace the old leaderboard system with a new one that could handle a much larger number of athletes competing in challenges. This blog post is about that project. I drafted most of this post when the project wrapped up in 2021, but didn’t get it…
In MySQL, you cannot do this: The UPDATE statement will raise an error as follows: SQL Error [1093] [HY000]: You can’t specify target table ‘t’ for update in FROM clause People have considered this to be a bug in MySQL for ages, as most other RDBMS can do this without any issues, including MySQL clones: … Continue reading Workaround for MySQL’s “can’t specify target table for update in FROM clause” Error →
Do you need to add a JDBC driver to your application, and don’t know its Maven coordinates? This blog post lists the most popular drivers from the jOOQ integration tests. Look up the latest versions directly on https://central.sonatype.com/ with parameters g:groupId a:artifactId, for example, the H2 database and driver: https://central.sonatype.com/search?q=g%3Acom.h2database+a%3Ah2 The list only includes drivers … Continue reading Maven Coordinates of the most popular JDBC Drivers →
Occasionally, you want to write a SQL query and fetch a hierarchy of data, whose flat representation may look like this: The result might be: |id |parent_id|label | |---|---------|-------------------| |1 | |C: | |2 |1 |eclipse | |3 |2 |configuration | |4 |2 |dropins | |5 |2 |features | |7 |2 |plugins | |8 |2 … Continue reading How to Turn a List of Flat Elements into a Hierarchy in Java, SQL, or jOOQ →
One of the more frequent questions about jOOQ is how to write a derived table (or a CTE). The jOOQ manual shows a simple example of a derived table: In SQL: In jOOQ: And that’s pretty much it. The question usually arises from the fact that there’s a surprising lack of type safety when working … Continue reading How to Write a Derived Table in jOOQ →
I’ve found an interesting question on Twitter, recently. Is there any performance impact of using FILTER in SQL (PostgreSQL, specifically), or is it just syntax sugar for a CASE expression in an aggregate function? As a quick reminder, FILTER is an awesome standard SQL extension to filter out values before aggregating them in SQL. This … Continue reading The Performance Impact of SQL’s FILTER Clause →
Optional Google Analytics helps us understand visits. Microsoft Clarity records masked interactions to improve the site. Optional tools stay off unless you choose them. Privacy details.