Tips, Expertise, Articles and Advice from the Pro's for Your Website or Blog to Succeed
Want to learn about deploying CockroachDB on Azure Kubernetes Service? We do not officially publish such documentation, so this article is an attempt to do just that.
There are certain differences in operating Kubernetes on Azure. For more information, please refer to their docs.
This is a new series of articles covering short "Today I Learned" topics as I peel the layers of CockroachDB. This is meant to resemble release notes. I decided to mix it up with the format for these posts as they may not justify an entire blog. Link here for past articles.
I get this question a lot: "What is the best way to unit test CockroachDB as part of application development?" Depending on the use case, there are several approaches we can take. There is a venerable [cockroach demo](https://www.cockroachlabs.com/docs/stable/cockroach-demo.html), which has been my trusty go-to tool when I needed something in a hurry. It comes with a one-hour license for enterprise features, which you can also unit test, but it also comes with certain limitations. For example, there is reported latency at table creation, ports until recently were random and not configurable, and by default it loads data which you can also overcome with a flag. The goal for cockroach demo is to quickly spin up a working environment for demo purposes and not necessarily something designed for unit testing.
For those familiar with the Outbox Pattern, CockroachDB provides some unique capabilities for handling these types of architectural patterns. One common method is to use Changefeeds in CockroachDB to send an acknowledgment message back to the originating service that the database transaction was committed. Changefeeds are great in this scenario in that they can be emitted on a record mutation on the table (except Import), connect to a message bus like Kafka, and emit the payload in a mildly low latent (~100ms) fashion. However, one circumstance of this pattern is having historical records build up in the Outbox table. Fortunately, we have a rather nifty solution that can clean up these Outbox tables.
So the goal in this post is to show how you can programmatically remove records from an Outbox table that have been flushed to its sink (i.e Kafka). The idea here is to create a clean-up job that removes records where the MVCC timestamp of an Outbox record is adequately past the high watermark of a Changefeed.
The construction, process, and usage of databases has evolved a lot over the last few decades. Traditional relational databases were enough to work with the data present at that time, but with the innate reliance on the Internet, the progression of cloud-native architecture, and the advancement of how businesses utilize and analyze data science, relational databases are not cutting it. What happens if a node fails in a traditional single machine of a relational database? Your database would go down along with any applications that depend on it.
Over time as NoSQL databases were introduced—which are capable of handling a large amount of data in real-time—the risk of apps failing began to decrease but the risk of data inconsistencies increased. So, there has been a growing need for a better storage solution for data to cope with today’s dynamic cloud-native architecture. CockroachDB was specifically designed to solve and meet this need.
CockroachDB is a cloud-native distributed database that works across various cloud, hybrid, and on-premise environments. The flexibility of deployments demands varying degrees of security protocols. Most of the time, on-premise customers won't accept anything less than Kerberos for their system of record authentication mechanisms. In my Hadoop time, that was the bare minimum requirement to play. CockroachDB today supports Kerberos via GSSAPI for authentication.
In this post, I'm going to walk you through setting up Kerberos for CockroachDB and provide a sort of cheat sheet, to make this process more seamless. I'm using a single Centos VM provisioned with Vagrant. It servers as my KDC as well as my CockroachDB instance. On to the setup. The following documents may assist in the entire process: CockroachDB GSSAPI, how to install CockroachDB and configuring CockroachDB for secure access. I recorded the entire process with Asciinema, I split the screencast into two parts.
Today I am going to show you how to get started with distributed SQL databases so you can start developing your application locally on your laptop.
curl https://binaries.cockroachdb.com/cockroach-v20.2.8.darwin-10.9-amd64.tgz | tar -xJI had a customer inquiring about whether CockroachDB is capable of working with multi-level JSON. Considering their JSON would have up to 3 levels of hierarchy, they wanted to know whether CockroachDB is able to use native capability to access data multiple levels down. This prompted my interest and led to this tutorial. Surprisingly, CockroachDB does not inhibit any limitations to the number of levels in hiearchy and performance can be improved using various optimizations also discussed below.
cockroach start-single-node --max-sql-memory=.25 --insecure --background
cockroach sql --insecure --host=localhostCockroachDB supports enterprise-grade backup and restores using object storage targets. For local development, a userfile scheme was introduced to allow fast prototyping without a heavy burden of spinning up costly infra. A customer requested the ability to quickly validate whether they can recover from a complete teardown of a cluster without using object storage. This is my experience and current workaround to get this to work.
This tutorial takes a short detour compared to my other articles due to a specific requirement that we need to recover from a local disaster into a new cluster. userfile is very helpful but it assumes the cluster is up and restore can proceed as intended. However, when you intend to shut down the source cluster, remove all of the underlying storage along with the user space where userfile stores its backups, userfile stops being useful. In that case, we have to download the backups out of the user space prior to shutting down the cluster and removing the data volumes.
In our previous blog posts (1, 2), we explored how query optimizers may consider different equivalent plans to find the best execution path. But what does it mean for the plan to be the "best" in the first place? In this blog post, we will discuss what a plan cost is and how it can be used to drive optimizer decisions.
Consider the following query:
This tutorial demonstrates the ability to recover from accidental truncate or table drop.
This scenario came out of a real-world situation where I got paged in the early hours of the day by a contractor evaluating our product on behalf of a customer. The contractor was in panic and concerned that she'd just lost four days worth of work by executing an older version of her application which included a hardcoded TRUNCATE statement. There was no backup available either. We were faced with a situation where we were going to miss customer timeline and lose the precious time reloading the data. We are talking about a multi-TB dataset. Once I had some coffee and we huddled to disucss next steps, we agreed the best next option is to leverage CockroachDB capability where dropped data does not leave the filesystem until garbage collection is complete, which by default is set to 25hours and luckily for us, that property was not changed in the cluster. Needless to say, we were able to recover the lost data following the procedure below.
CockroachDB is a cloud-native SQL database for building global, scalable cloud services that survive disasters. It makes building geo-distributed databases easy. Ability to anchor data to geographic localities is a unique capability of CockroachDB. Today, I will demonstrate this ability to extend beyond regions, availability zones, nodes, data centers and racks. The architecture allows us to domicile data to specific storage devices using storage attributes.
export storepath="${PWD}/workdir"
mkdir -p "${storepath}"PGBouncer is a lightweight connection pooler for PostgreSQL. CockroachDB is a cloud-native SQL database for building global, scalable cloud services that survive disasters.
CockroachDB is a PostgreSQL wire compatible database, which means it aims to have tight compatibility with the PG ecosystem. Today, we're going to wire PGBouncer to work with CockroachDB. This article is meant to scratch the surface of possibilities unblocked by PGBouncer with CockroachDB and not meant to be an in-depth overview. We're currently researching this topic and will follow up with official docs on the proper architecture and sizing of PGBouncer and CockroachDB.
The goal of the query optimizer is to find the query execution plan that computes the requested result efficiently. In this blog post, we discuss rule-based optimization - a common pattern to explore equivalent plans used by modern optimizers. Then we explore the implementation of several state-of-the-art rule-based optimizers. Then we analyze the rule-based optimization in Apache Calcite, Presto, and CockroachDB.
A query optimizer must explore the space equivalent execution plans and pick the optimal one. Intuitively, plan B is equivalent to plan A if it produces the same result for all possible inputs.
Industry: Logistics
Authors:
When running your production workloads on CockroachDB, you'll want to take regular backups. CockroachDB is frequently deployed into a cloud, such as EC2, GCP, or Azure, and these cloud environments consistently offer a highly durable "blob store". That, coupled with how well CockroachDB's backup/restore works with these blob stores, makes them an excellent choice of backup target. In certain cases, organizations may choose to limit outbound traffic from their workloads running in the cloud, so they may deploy a proxy to manage these HTTP and/or HTTPS requests. Having just configured this myself, I figure sharing it here would make sense.
In my case, I'm running a three-node cluster right on my MacBook Pro, so I'm cheating a little bit in that it's not running in the cloud. Still, for the purposes of this experiment, I think it's okay. I should note that, in that startup procedure, the one shown on the link to the docs, you need to add this step prior to starting each of the three cockroach processes:
If you’re building and managing applications in public cloud providers like GCP or AWS, chances are you’ve heard of VPC peering. This blog post explains what VPC peering is, why you’d want to use it, and, if you’re using CockroachCloud today, how you can get started with our new VPC peering functionality.
First thing’s first - a virtual private cloud (VPC) is a logically isolated, virtual network within a cloud provider. A VPC peering connection is a networking connection between two VPCs that enables you to route traffic between them using private IP addresses. VPC peering allows you to deploy cloud resources in a virtual network that you have defined. Instances in either VPC can communicate with each other as if they were within the same network. Data can be transferred across these resources with more security.
As organizations transition to the cloud, they eventually find that the legacy relational databases that are behind some of their most critical applications simply do not take advantage of the promise of the cloud and are difficult to scale. It is the database that is limiting the speed and effectiveness of this transition. To address this, organizations want the reliability of a tested relational data store, such as Oracle, SQL Server, Postgres, and MySQL, but with the benefits of scale and global coverage that comes with the cloud
Some have turned to NoSQL stores to try to meet these requirements. These alternatives can typically meet the scale requirements but then fall short as a transactional database because they were not designed from the ground up to provide true consistency. Recently, some of the NoSQL solutions have offered “ACID transactions” but they’re full of caveats and fail at delivering isolation levels necessary for mission-critical workloads like a financial ledger, inventory control, and identity management.
Distributed ACID transactions form the beating heart of CockroachDB. They allow users to manipulate all of their data transactionally, no matter where it physically resides.
They're so important to CockroachDB’s goal to “Make Data Easy” that we spend a lot of time thinking about how to make them as fast as possible. Specifically, CockroachDB specializes in globally distributed deployments, so we put a lot of effort into optimizing CockroachDB’s transaction protocol for clusters with high inter-node latencies.