Tips, Expertise, Articles and Advice from the Pro's for Your Website or Blog to Succeed
In today's discourse, we delve into the intricacies of accessing Snowflake via the Trino project. This article illuminates the seamless integration of Trino with Snowflake, offering a comprehensive analysis of its benefits and implications.
Previous articles on Snowflake and Trino:
Previous articles on PgBench and CockoachDB:
For the longest time, CockroachDB only supported serializable isolation. In our next release after 23.1, CockroachDB will additionally support read committed isolation. The idea here is that users from other RDBMS tend to face many challenges with application migrations due to the excessive contention stemming from serializable isolation. To ease the migration woes, read committed isolation will provide an easy on-ramp for those applications. Since read-committed isolation is less strict, it has the additional benefit of being more performant at the cost of data anomalies. Today, we're going to take everything we've learned so far in my series and apply it to read committed isolation.
I've written about Presto and CockroachDB in the past, you may find the article below:
Once I completed my first two articles, I realized there are a lot of possibilities exposed by proxying MongoDB collections through FerretDB backed by CockroachDB. CockroachDB has unique data domiciling capabilities available through multi-region abstractions, inverted and partial indexes, computed columns and of course strong consistency. Today, we're going to discuss unique constraints in MongoDB and CockroachDB.
I am going to use the same CockroachDB Dedicated cluster from the previous article. Please refer to the previous article for the detailed steps. You can get a 30-day trial of CockroachDB Dedicated following this link.
Using CockroachDB as a Backend for OSS MongoDB Alternative FerretDB
Once I completed my first article, I realized there are a lot of possibilities exposed by proxying MongoDB collections through FerretDB backed by CockroachDB. CockroachDB has unique data domiciling capabilities available through multi-region abstractions, inverted and partial indexes, computed columns, and of course strong consistency. Today, we're going to restore a MongoDB collection into CockroachDB via FerretDB and expand on our previous learnings.
This is my series of articles covering short "today I learned" topics as I work with CockroachDB. Read the previous installments:
There was a question in our community Slack on capturing schema changes from a cluster. CockroachDB has a system.eventlog table that can be inspected for audit events like schema changes, cluster configuration changes, user and role changes, etc. This table is a running record of these events and by default, it retains 90 days of events controlled by the "server.eventlog.ttl" property. This is a notable event table and not a replacement for a schema change tool, which typically persists in schema change history on a permanent basis. Here's a sample output from the table:
I first heard of FerretDB formerly known as MangoDB in 2021, right around the time CockroachDB 21.1 was available. FerretDB is an open-source alternative to MongoDB. It serves as a proxy, converting MongoDB protocol queries to SQL. The storage backend in FerretDB is Postgresql and naturally, I had to try swapping Postgresql with CockroachDB. It didn't work the last time I tried it last year; now that FerretDB has had time to mature, I am going to attempt it again.
This is where it used to break:
This is my series of articles covering short "today I learned" topics as I work with CockroachDB. Read the previous installments:
cockroach binaryThe standard approach to generating workload data, say for tpcc workload, has always been the following:
This is my series of articles covering short "today I learned" topics as I work with CockroachDB. Read the previous installments:
CockroachDB has a very user-friendly and helpful UI called DB Console. I like to refer to it when I debug query performance. There is a very useful Statements page that shows a statement overview and explains plans and execution stats.
The problem this tutorial is trying to solve is the lack of a native Fivetran connector for CockroachDB. My customer has built their analytics pipeline based on Fivetran. Given there is no native integration, their next best guess was to set up a Postgres connector:
CockroachDB is PostgreSQL wire compatible, but it is not correct to assume it is 1:1. Let's attempt to configure the connector:
Given CockroachDB scales with vCPU, there's a hard limit to how many active connections we can support per vCPU before a serious problem arises. PGBouncer stretches the limits a bit making it a cost-effective option. In serverless architectures, there is no client-side connection pooling, and using middleware like PGBouncer can alleviate the problem of connection storms. Please see my previous articles on the topic for more details.
We've covered how to deploy PGBouncer with a self-hosted CockroachDB cluster. Today, I'm going to demonstrate how to run PGBouncer along with the Cockroach Cloud free-forever tier database. The overall concepts are identical, but we will highlight some of the major differences in deploying PGBouncer with a cloud product.
This is my series of articles covering short "Today I learned" topics as I work with CockroachDB.
Use init container to initialize Cockroach and exit, you no longer need to explicitly run init when you bring up CockroachDB.
We just pushed a new release of CockroachDB and Postgres also had a recent release with a vulnerability impacting GSS. I figured it was a good time as any to update my repos with the latest versions of Postgres and Cockroach, and thereby test that everything works. I discovered an issue that is easily fixed but changes the behavior of cockroach and psql clients.
There's nothing more special about this tutorial than what was covered in my previous tutorials. Feel free to set up a stand-alone environment to follow along or use my docker-compose environment.
CockroachDB Dedicated is a fully-managed, reserved CockroachDB cluster ideal for a cloud database. We frequently get asked how to set up SSO for the individual CockroachDB Dedicated clusters and we have a detailed tutorial to walk you through that with a local, self-hosted cluster. What was unclear was that you can use the same steps to set up SSO with Dedicated. Based on this detailed document, CockroachDB Dedicated supports OIDC authentication for the DB Console today. In a future release, we are going to bring OIDC integration across the stack. Today, we're going to provide details on how to leverage OIDC specifically with the CockroachDB Dedicated DB Console and Microsoft using the OpenID Connect protocol.
Follow this tutorial to set up a Dedicated cluster.
Cockroach Labs ships new images when a new maintenance release is available, typically on monthly basis. CockroachDB does not rely on the base OS image for any third-party libraries except for geospatial and kerberos packages. That said, OS images may be vulnerable to security exposures depending on their age. Given CockroachDB is written in Golang, replacing the OS can be a trivial task.
Originally, CockroachDB image was shipped with a Debian OS image. CRL then switched to UBI to accommodate a wider scope of use cases including Red Hat OpenShift. UBI images are shipped regularly with CVE patches and given the nature of security vulnerabilities, it is common that the latest and greatest images may or may not have CVEs. Given the preamble, we're going to cover how to replace the base image for CockroachDB and use it in Kubernetes.
GSSAPI authentication is becoming increasingly popular as CockroachDB starting to make inroads in Fortune 2000 customer bases and financial services segment. That said, ecosystem coverage for GSS needs to improve for parity with other authN methods. Today, we are providing a workaround and a look at the future. By the way, do you realize this is my 15th article on Kerberos and CockroachDB?
I find the topic of Kerberos very interesting and my colleagues commonly refer to me for help with this complex topic. I am by no means an expert at Kerberos, I am however familiar enough with it to be dangerous. That said, I've written multiple articles on the topic which you may find below:
I find the topic of Kerberos very interesting and my colleagues commonly refer to me for help with this complex topic. I am by no means an expert at Kerberos, I am however familiar enough with it to be dangerous. That said, I've written multiple articles on the topic which you may find below:
Systemd has become a standard approach for deploying Linux services. We have documentation to deploy CockroachDB via systemd, however, we do not have steps documented to deploy CockroachDB with GSSAPI and systemd. This tutorial attempts to bridge that gap.
As with most of my tutorials, topic ideas come from user inquiries. I see this question come up quite often and we don't have a documented approach to bridge the gap today. Cockroach Labs engineering is hard at work to build an API that will make this point moot but until then this can be a viable alternative. It is primarily directed at our cloud offering where we rely on password authentication today. There are also cases where password authentication serves other purposes and we need ways to automate the provisioning of passwords other than ALTER USER username WITH PASSWORD "password"; command.
You can take this approach and incorporate it into your CI/CD pipelines to onboard new users and manage their passwords in absence of certificate-based authentication and its associated revocation mechanisms or directory services and its password management capabilities.