Tips, Expertise, Articles and Advice from the Pro's for Your Website or Blog to Succeed
A prospect was having issues with TLS and Flyway schema migrations, and since both topics are near and dear to my heart, I felt obligated to respond.
Spinning up a CockroachDB Dedicated cluster is fairly straightforward. Follow this guide.
Protocol Buffers are language-neutral, platform-neutral extensible mechanisms for serializing structured data. It's a common choice for platforms needing to pass messages between systems. CockroachDB is a distributed SQL database built on a transactional and strongly-consistent key-value store. It scales horizontally; survives disk, machine, rack, and even datacenter failures with minimal latency disruption and no manual intervention; supports strongly consistent ACID transactions; and provides a familiar SQL API for structuring, manipulating, and querying data.
There is no official support for Protocol Buffers in CockroachDB Changefeeds, even though we use Protocol Buffers extensively in the code. A recent customer conversation led to this experiment where I'm going to use several recent features to demonstrate the ability to serialize CockroachDB rows to proto and emit via CDC Queries. This is the first time we're looking at CDC Queries. This is a new flexible way to express CockroachDB streams.
cockroach and psql clients for client verification of server certificatesI was using CockroachDB Serverless with a product called PolyScale.ai for an article I was writing and I ran into a strange issue with certificate-based AuthN. When you provision the instance of their service, it provides a client connection string.
I used the connection string to connect:
CockroachDB makes multi-region simple. When CockroachDB spans geographically, we often need to add a minimum of two more regions for a multi-region cluster. This unique capability has many strengths but it comes at a cost. Oftentimes, our customers demand CockroachDB be available in regions where we see low demand, and bringing those regions online is not cost-effective to the organization. As of this writing, we support the most popular regions in GCP and AWS; some regions are not exposed in the cloud console but are available via support ticket.
PolyScale operates a global network of PoPs (Points of Presence). Think of PoPs as regional database connections. This versatility provides a cost-effective solution to reduce global network latency by bringing the database closer to the end user. The network of PoPs spans multiple cloud providers, thereby bridging the gap between cloud providers. PolyScale complements CockroachDB in the way that CockroachDB can be more accessible in many more geographic locations and many other cloud providers than provided out of the box by Cockroach Cloud.
For reference: Checkout my previous article where I discuss connection pool high availability, "Connection Pool High Availability With CockroachDB and PgCat."
The load balancer is a core piece of architecture for CockroachDB. Given its importance, I'd like to discuss the methods to overcome the SPOF scenarios.
Today, I'd like to cover pooler high availability as this is one aspect of connection pooling I've never focused on. Due in large part because my previous attempts were based on PgBouncer, and it standing up PgBouncer is not easy. Luckily, it's been an absolute pleasure working with PgCat as it has a lightweight installation process.
I have a Docker Compose environment with all of the necessary services here. Primarily, we have to set up two instances of PgCat.
PgCatpgbench client disconnectsPGSERVICEFILEOur engineering team has issued a technical advisory #96924 where certain schema changes like dropping columns referenced in partial indexes will fail. A customer asks how to identify databases, tables, and for the associated partial indexes referencing columns to be dropped. The following methods will assist in finding those pesky indexes.
Considering a table with the following data:
Apache Pulsar is a cloud-native distributed messaging and streaming platform. In my customer conversations, it most often comes up when compared to Apache Kafka. I have a customer needing a Pulsar sink support as they rely on Pulsar's multi-region capabilities. CockroachDB does not have a native Pulsar sink; however, the Pulsar project supports Kafka on Pulsar protocol support, and that's the core of today's article.
This tutorial assumes you have an enterprise license, you can also leverage our managed offerings where enterprise changefeeds are enabled by default. I am going to demonstrate the steps using a Docker environment instead.
CockroachDB Multi-Region Abstractions for MongoDB Developers With FerretDB
I was working with a prospect on a use case where my knowledge of CockroachDB multi-region abstractions was put to the test. After trying and failing to gain low latency write behavior from a geographically distributed table, I reached out to the engineering team who corrected my understanding. This is my way of documenting my understanding and committing it to memory.
I stumbled on pgcli When I was writing my auto-completion topic from the last volume. I initially needed a CLI tool with auto-completion because I desperately needed it to deal with very long schema and table names. When I first tried it with version 3.4.1, which was the latest at the time, I was seeing many errors. I decided to pause my evaluation and come back later. Luckily, a month later, version 3.5.0 was released which seemed to fix those issues for me. I no longer see Python stack traces. Let's take it for a spin...
Pgcli describes itself as a PostgreSQL client with auto-completion and syntax highlighting. Get a pgurl connection string, i.e. postgresql:// or Postgresql environment variables to connect to a cluster.
I've written about pgbench before, you may find those articles below:
I am writing a two-part article on optimizations in pgbench. Pgbench is a common benchmarking utility that comes bundled with PostgreSQL. It's ubiquitous and widely accepted as a standard tool to test database performance. Up until version 15 of pgbench, I was unable to improve performance with CockraochDB and pgbench. With this version, we now have the ability to retry transactions and it improves the performance posture for CockroachDB. However, this is not the only way to address performance issues. Today, I am going to expand on the options available to us and move the performance needle further.
I'm passionate about my job and I document all of my CockroachDB shenanigans. You can find them in my profile.
The previous article left a lot to be desired and I've been meaning to get back to it. Today, we're going to improve on the original article by demonstrating what can be done to improve the performance of pgbench with CockroachDB. Typically, when customers begin their CockroachDB journey, they are faced with a learning curve having no prior knowledge of CockroachDB and many times, the biggest hurdle is isolation. CockroachDB operates in SERIALIZABLE isolation by default, full stop! There are no plans to change the status quo. Instead of weakening isolation, we advise our customers to practice defensible programming and change their application to work around the errors and retries. We're going to treat pgbench like a legacy application and demonstrate the problems and how we can work around them and modernize them for CockroachDB.
In my previous article, I demonstrated how JWT tokens can replace passwords for a safer and more secure cloud-native future.
Check out my previous articles covering SSO for DB Console using Google OAuth, Microsoft Identity Platform, and Okta.
CockroachDB supported Single Sign On for DB Console and CC Console for a while. Today, we're going to introduce Single Sign On for CockroachDB CLI. It is an industry-first method to authenticate to a database via JWT tokens. This capability allows you to authenticate with a cluster via an IdP of your choice and issue SQL commands.
I've written articles covering SSO for DB Console previously. You may find articles covering Google OAuth, Microsoft Identity Platform and Okta.
This is my series of articles covering short "today I learned" topics as I work with CockroachDB. Read the previous installments:
DISCARD ALLCockroachDB adopted ULID several releases ago as yet another version of UUID type. There is no explicit ULID type in CockroachDB. There are functions available to generate ULID in UUID format. So in case you need to generate lexicographically sortable IDs, you can use the built-in gen_random_ulid() function. Also, there are several conversion functions to convert from ULID to UUID; i.e., ulid_to_uuid and vice versa, uuid_to_ulid. One point of friction in CockroachDB is where one needs to generate an actual ULID string and not a UUID-formatted ULID. This may or may not be obvious. Let's take a look:
Azure Event Hubs is a critical part of the Azure ecosystem. We're in the early stages of adopting Azure and while we focus on the official integration, I'd like to provide workarounds in the meantime.
This tutorial is using enterprise changefeeds: you will need an enterprise license, access to a CockroachDB dedicated cluster, or enable billing in your CockroachDB Serverless cluster to activate enterprise features like CDC to Kafka.