Engineering

At Cockroach Labs, we’ve spent a lot of time getting our SQL semantics to match PostgreSQL as much as possible - all so that you can use the awesome PostgreSQL language with a powerful, distributed backend like! Getting this right involves getting the Time, TimeTZ, Timestamp and TimestampTZ types right - which has proven quite the quest! Numerous bugs have been filed and fixed - and with it, our mastery of these types in PostgreSQL (and the Go time library) has increased! In this blog post, we’ll explore some intricacies we’ve found with these data types in PostgreSQL, and look at how we reproduce some of these features using Go and its ecosystem. We’ll share our recommendations for using these types along the way.

Foreign keys are an important element of any relational database. But when you’re setting up your database schema, it’s relatively easy to create problems for yourself if the foreign keys aren’t set up correctly. Here are three of the most common mistakes made with foreign keys, and a little advice on how to avoid them!

Charlie Custer

June 28, 2023

NOT NULL constraints are a ubiquitous tool for upholding data integrity. However, their performance benefits are less obvious. NOT NULL constraints provide a query optimizer with additional context that unlocks optimizations that significantly reduce query latency. In this post we’ll examine a few of these optimizations that transform query plans involving foreign key relationships.

Marcus Gartner

June 27, 2023

During the Winter of 2023, I had the wonderful opportunity to work as a Software Engineering Intern on the SQL Queries team at Cockroach Labs. During the scope of this internship, I found myself working with indexes and specifically implementing forward indexes on columns storing JSONB, which resulted in speeding up query execution time by up to 98% in some cases! This blog post will go over the motivations for the implementation of these indexes, the main challenges that came up with the implementation, and some demonstrations of the staggering results.

In a distributed database, a hotspot is an overworked node – in other words, a part of the database that’s processing a greater share of the total workload than it is meant to handle.

Charlie Custer

June 7, 2023

A foreign key is a column or columns in a database that (e.g. table_1.column_a) that are linked to a column in a different table (table_2.column_b). The existence of a foreign key column establishes a foreign key constraint – a database rule that ensures that a value can be added or updated in column_a only if the same value already exists in column_b.

Charlie Custer

May 4, 2023

In the context of a database and how it’s organized, the term database schema describes the structure of the data and how the elements within the database relate to each other. This information is often presented visually with diagrams that illustrate how different tables and elements connect and to give you a logical view of the entire database. However, the schema itself exists in the database as the coded rules that define the data’s structure and relationships.

Charlie Custer

April 13, 2023

In this post we’re going to talk about one of the techniques that can be used in CockroachDB for getting good performance from databases accessed from multiple geographical regions: follower reads. These are operations presenting a slightly stale view of data, which can be served with local latencies from different regions.

I once went to a website that had “hours of operation,” and was only “open” when its brick and mortar counterpart had its lights on. I felt perplexed and a little frustrated; computers are capable of running all day every day, so why shouldn’t they? I’d been habituated to the internet’s incredible availability guarantees. However, before the internet, 24/7 high availability wasn’t “a thing.” Availability was desirable, but not something to which we felt fundamentally entitled. We used computers only when we needed them; they weren’t waiting idly by on the off-chance a request came by. As the internet grew, those previously uncommon requests at 3am local time became prime business hours partway across the globe, and making sure that a computer could facilitate the request was important.

Jessica Edwards

March 23, 2023