December 2022 Summaries

Cockroach Labs has introduced a feature in CockroachDB 22.2 that allows users to use Cloud IAM roles for backup-restore and changefeed operations, addressing customer concerns about cloud resource credential security. This enhancement enables the obfuscation of cloud credentials, eliminating the need for AWS access keys or GCP service account credentials in SQL commands, thus reducing the risk of data exfiltration. Customers can now configure cloud IAM roles with necessary permissions, trust a CockroachDB identity, and share the role name with SQL users, allowing for secure access to cloud resources without exposing sensitive credentials. This approach uses short-lived credentials, automatically refreshed by CockroachDB, making it a seamless solution for long-duration operations like full cluster backups or continuous changefeeds. The functionality is available for both CockroachDB dedicated and self-hosted clusters, with specific configurations recommended for each deployment type, enhancing security and simplifying processes for cloud-native databases.

Apache Kafka is a highly scalable, open-source distributed data store designed to handle real-time streaming data, offering capabilities such as data ingestion, processing, and storage in a fault-tolerant manner. It allows application services to publish and subscribe to data feeds, stores data in sequence, and efficiently processes data streams, making it an integral part of event-driven architectures used by large companies like Netflix. Kafka's architecture includes producers, brokers, and consumers, with data organized into topics and partitions for fault tolerance and scalability. It operates on a pull model, contrasting with systems like RabbitMQ that use a push model, which allows Kafka to cater to high-throughput applications. Developed initially at LinkedIn, Kafka was named after the writer Franz Kafka to reflect its optimization for writing, and it is widely used for building sophisticated event-streaming pipelines, often in conjunction with databases like CockroachDB.

In 2020, Hard Rock International and Seminole Gaming launched Hard Rock Digital to develop a compliant and highly available sports betting application, leveraging their extensive customer database and venue network. The primary challenges faced were ensuring compliance with stringent gambling regulations, particularly The Federal Wire Act, and maintaining high availability to support a large number of concurrent users across multiple regions. To address these needs, Hard Rock Digital chose CockroachDB for its ability to offer high availability, data pinning to comply with state regulations, horizontal scalability, and automated operations, all while supporting a hybrid infrastructure on AWS. The application architecture, built on a foundation compatible with Postgres and running on AWS Outposts, ensures compliance by placing CockroachDB gateway nodes in each state where the service is offered, facilitating seamless multi-state operations. Less than a year after launch, the Sportsbook application expanded to several states and aims to become a top sports betting app by 2024, uniquely allowing users to place bets across state lines while maintaining compliance.

In a detailed exploration of cost-saving strategies for data streaming, Abbey Russell discusses how CDC (Change Data Capture) Transformations can significantly reduce the complexity and expenses associated with data pipelines. By utilizing CDC Transformations, businesses can filter out unnecessary data, customize data schemas, and perform upfront data transformations, which simplifies processes and lowers costs related to data storage, processing, and transmission. This technique is particularly beneficial for applications like fintech services that require real-time fraud detection and compliance checks, as it eliminates the need for redundant data storage and custom polling services. Furthermore, by optimizing data streams before they reach data warehouses like Google BigQuery, companies can reduce their total cost of ownership (TCO) by up to 40%. The article emphasizes the potential of CDC Transformations to streamline operations and enhance efficiency in data management systems.

User-defined functions (UDFs) in SQL are custom functions created by users to execute specific tasks within a database, providing a way to optimize application performance by reducing data transfer between application and database layers. UDFs are written in SQL and consist of a name, arguments, a return statement, and a function body, allowing developers to perform operations directly on the database, thus minimizing latency. The creation process for UDFs varies across different database systems like PostgreSQL, MySQL, and CockroachDB, each with its syntax and features. In real-world applications, UDFs can perform complex tasks beyond simple arithmetic, such as converting timestamps into more readable formats. While they offer efficiency, UDFs must be carefully managed to avoid unintended side effects or data mutations, using additional function attributes like IMMUTABLE and LEAKPROOF to guide database behavior and optimize performance.

The blog post describes the creation of a gift exchange application specifically for dogs, inspired by a tedious Instagram-based exchange involving manual processes. The author, Aydrian Howard, decided to streamline this by developing an app using Remix, Prisma, CockroachDB serverless, and Tailwind, which simplifies the referral-based gift exchange system that resembles a pyramid scheme. The app allows participants to join exchanges via referral links, manage their participation through a dashboard, and track gifts and referrals. The development process involved setting up a database schema with models for users, exchanges, and participants, overcoming challenges such as circular references in data relations. The author encourages readers to fork the application's repository, adapt it for other gift exchange themes, and potentially integrate their own CockroachDB serverless clusters.

In a detailed exploration of enhancing performance predictability and isolation for multi-tenant systems like CockroachDB, the text examines the application of control theory to the Go scheduler. It discusses challenges in managing CPU-heavy tasks, such as backups, which can impact latency-sensitive workloads. Traditional static rate limiting was insufficient, leading to the development of more dynamic solutions, such as feedback control systems that adjust CPU usage for elastic tasks based on scheduling latency. The text highlights the use of probabilistic tracing to capture latency outliers and the integration of cooperative scheduling, where elastic work self-regulates its CPU usage through token grants. Additionally, it outlines modifications to the Go runtime to track CPU time per goroutine, enhancing the ability to manage elastic workloads without OS-level interventions. Through experimentation, the scheme proved effective in reducing scheduling latency spikes, thus protecting the latency of foreground tasks.

The blog post by Abhinav Garg and Kyle Patron discusses the implementation of JWT (Json Web Token) for SQL authentication in CockroachDB, marking a significant shift towards modern identity protocols for database access. Traditional authentication methods like PKI, LDAP, and Kerberos, while secure, present challenges in scalability and management, especially for fully-managed cloud databases. JWT-based authentication, introduced in CockroachDB version 22.2, offers a streamlined solution by integrating with cloud-native identity providers such as Okta, GCP, Azure, and AWS, allowing users to manage authentication centrally without additional credential sets. The article details how this mechanism works for both human and application users, with a focus on CockroachDB Cloud and self-hosted clusters, and explains the ease of using JWTs through the Cloud CLI and external issuers, providing a more flexible and secure approach to database authentication.

The blog post by Michael Goddard explores the use of trigram indexes in CockroachDB 22.2 for improving text search capabilities, particularly in scenarios where users may input misspelled or variably formatted text. It highlights the limitations of full-text search methods for certain use cases, such as handling typos, transliterations, and spelling variations, using the example of a restaurant's name that was misspelled due to a hyphen. Trigram indexes are described as a method to efficiently perform fuzzy matching by creating a sliding window of three-character sequences, which helps in matching text despite minor discrepancies. The post provides a detailed tutorial on implementing trigram indexes using SQL queries in CockroachDB, demonstrating how they enhance search performance by reducing the need for full-table scans. The author emphasizes the utility of trigram indexes for short text fields and illustrates their efficiency through a practical example, while also mentioning the new feature of index visibility in CockroachDB for performance tuning.

CockroachDB's Intelligent Insights feature, introduced in version 22.2, enhances query performance optimization and troubleshooting by providing detailed workload and schema insights. It addresses common performance issues such as non-performant indexes, transaction contention, and slow or failed statements through an interactive Console UI that displays historical and real-time data. The new Insights page highlights problematic executions and offers recommendations for improvement, with tabs dedicated to Workload and Schema Insights. Workload Insights identify slow executions and high contention in transactions and statements, while Schema Insights offer index recommendations to optimize read and write performance. The feature is designed to help developers fine-tune their databases and improve end-user experiences by leveraging internal telemetry data and best practices.

Grace Hopper, a pioneering figure in computer science, is celebrated for her groundbreaking contributions, including the development of the first compiler, which allowed computers to interpret English-like instructions. Born on December 9, 1906, in New York City, her early curiosity and determination were nurtured by supportive parents, leading her to pursue degrees in mathematics and physics at Vassar College and Yale. She served in the Navy Reserve during World War II, where she worked on some of the earliest computers, and later contributed to the development of programming languages such as COBOL. Hopper's innovations in subroutines and distributed systems laid the groundwork for modern computing, including the internet and cloud computing. Despite her pivotal role, her contributions remain relatively underrecognized outside the tech community, though she has received honors such as the Presidential Medal of Freedom and having a U.S. Navy battleship named after her. Hopper's legacy continues to inspire, as seen in events like the Grace Hopper Celebration of Women in Computing, promoting the role of women in tech.

CDC Transformations, introduced in CockroachDB, are SQL-like statements that simplify the process of filtering, transforming, and choosing the schema for data streaming, eliminating the need for sidecar services or downstream tools. These transformations enhance the functionality of changefeeds, which stream continuous updates or snapshots from tables to external systems, by allowing users to specify only the data changes they care about, such as specific inserts, updates, or region-specific data, and by reducing data streaming costs through filtering unnecessary data. They also ensure a stable message schema even amidst schema changes and can be used for load balancing across feeds. Currently in preview, CDC Transformations are set to evolve with future releases to remove existing limitations, offering a cost-effective and efficient tool for managing real-time data pipelines for various business needs.

In a blog post by Charlie Custer, CockroachDB introduces its new distributed functions feature with a focus on user-defined functions (UDFs), aimed at enhancing database efficiency by reducing the number of trips between applications and databases. This innovation is designed for modern distributed databases and seeks to overcome the limitations of traditional UDFs, which tend to create bottlenecks by being executed on a single node. CockroachDB's distributed UDFs promise high availability, scalability, and compatibility, particularly with Postgres databases, making it easier for users transitioning from legacy systems. The blog provides examples of UDFs, illustrating their potential for a variety of tasks, such as converting timestamps into human-readable formats, and highlights the ongoing development towards a comprehensive vision of distributed database functions including triggers and stored procedures.

Netflix has developed a sophisticated Data Platform team that empowers developers by offering a wide array of database solutions, such as CockroachDB, to support their diverse application needs. This platform allows developers the freedom to choose their tech stack while alleviating the burden of infrastructure management, enabling them to focus on solving business problems. CockroachDB is particularly favored for its ability to handle distributed, consistent transactions across multiple regions, which is crucial for Netflix's high-volume, real-time data requirements. The Data Platform team automates much of the deployment and maintenance process, ensuring that Netflix can rapidly scale and innovate, maintaining a competitive edge in the industry. With over 100 production clusters of CockroachDB, Netflix continues to explore its capabilities, demonstrating the platform's resilience and scalability in various applications, from data mesh systems to cloud drive services.