Distributed SQL Tips and Tricks – June 10th, 2021

June 10, 2021

Welcome back to our bi-weekly tips and tricks blog where we recap some distributed SQL questions from around the Internet. We’ll also review upcoming events, new documentation, and blogs that have been published recently. Got questions? Make sure to ask them on our YugabyteDB Slack channel, Forum, GitHub, or Stack Overflow. Let’s dive in:

Connection Pooling with YugabyteDB

When looking to pool your connections with YugabyteDB many users take a look at PgBouncer, which is a lightweight connection pooler for PostgreSQL. Reusing the PostgreSQL query layer for YugabyteDB allows us to keep many of the same third party integrations out of the box. When looking at a client-layer connection pool, any connection pool library or framework specific to your application language that supports Postgres can be used. For example HikariCP and Vibur DBCP for Java or pgxpool for Go. The YugabyteDB JDBC Driver is currently in beta and will allow for automatic failover without the need for an external load balancer.

Connecting to a Yugabyte Cloud Database

When you have launched YugabyteDB Managed or a YugabyteDB Managed Free Tier cluster you can use the same IDEs and drivers you have used in the past to connect to your fully managed YugabyteDB cluster. Configuring the database connection however will look a little different. When you login to your cloud console and select your cluster, in the top right you will see a Connect tab. Let us say we are trying to connect using the YSQL API. Select YSQL Shell. This information will be important whether you want to gain access directly through the cloud shell, using a remote YSQL shell, or using third party clients. If you want to use the YSQL cloud shell you can click Run in Cloud Shell, and the YSQL shell will appear in a separate page and is ready to use. If you choose one of the other methods you will have to select the user credentials shown in the Connect dialog box. These user credentials are for the default user. To get the user credentials for a different user, click Database Access and then click INFO for that user. The generated command includes flags specifying the host (-h), port (-p), username (-U), and database (-d). Paste and run the generated command within the Yugabyte home directory in order to use the local YSQL shell (ysqlsh) connected to the remote cluster.

Please mind that if you have the shell HISTCONTROL variable include the option ‘ignorespace’, you can begin the line which sets PGPASSWORD with a space, which keeps it from being recorded in shell history.

PGPASSWORD=xxxx ./bin/ysqlsh -h a90037d6c599c11eaa26e06f3dee61fd-XXXXX.us-east-2.elb.amazonaws.com -p 11401 -U admin -d yugabyte

Using the above to connect to an IDE (DBeaver, DataGrip, etc.) would use the following settings:

Host – a90037d6c599c11eaa26e06f3dee61fd-XXXXX.us-east-2.elb.amazonaws.com
Port – 11401
Username – admin
Database – yugabyte

The format to connect to YB with a Spring Boot Java application is:

spring.datasource.url=jdbc:postgresql://<IP_OF_YUGABYTEDB>:<PORT>/<DATABASE>

Using the connection string and the spring format you can connect using:

spring.datasource.url=jdbc:postgresql://a90037d6c599c11eaa26e06f3dee61fd-XXXXX.us-east-2.elb.amazonaws.com:11401/ddemo

Transaction Retry Errors

YugabyteDB currently supports SERIALIZABLE and SNAPSHOT transaction isolation levels. SNAPSHOT isolation is mapped to the SQL isolation level REPEATABLE READ, whereas SERIALIZABLE maps to the SQL isolation level of the same name. In order to support these isolation levels YugabyteDB incorporates explicit locking on the transactions layer. This locking in databases ensures that multiple transactions can execute concurrently while preserving data integrity. Locking is essential for correctness in environments where two or more transactions can access the same data at the same time. YugabyteDB currently supports optimistic locking, which delays the checking of whether a transaction meets the isolation and other integrity rules until its end, without blocking any of the operations performed as a part of the transaction. In scenarios where there are two concurrent transactions that conflict with each other (meaning that both transactions try to change the same data), one of these transactions is aborted. The higher the isolation level, the more likely the chance of conflicting transactions. In such a circumstance you could programmatically restart and re-execute the aborted transaction, or surface an error to the end user. This GitHub issue calls out the cases where the user application should retry the transaction. The YugabyteDB development team is also working on supporting pessimistic locking. Pessimistic locking means a transaction obtains appropriate locks for the data to be changed prior to performing the transaction to prevent concurrency that would corrupt the data. A transaction that wants to change specific data which is already being changed by another process waits for the other process to finish. This waiting is ordered by transaction arrival time. If you are interested in the effect of isolation levels on distributed SQL performance check out this benchmark performed by our Co-Founder and CTO Karthik Ranganathan.

How to add a Column on a Large Table in YSQL

In the case of the new column being initialized to null, adding a column is just a metadata operation using a simple ALTER TABLE command (we do not store nulls explicitly) so this will not be a burden on the system. However if the new column is not nullable, or has a default value, there are some limitations regarding adding columns with default on tables with existing data. In this case, we advise the user to create a new table with the additional column and migrate the data with the additional column information included.

New Documentation, Blogs, Tutorials, and Videos

New Blogs

New Videos

Upcoming Events

June 24, 2021 – YugabyteDB YCQL Fundamentals Community Training and Certification
June 30, 2021 – High Performance Design and Architecture in a Distributed SQL Database

New and Updated Docs

We’re continually adding to and updating the documentation to give you the information you need to make the most out of YugabyteDB. We had so many new and updated docs for you, that we wrote a blog post this month to cover recent content added, and changes made to the YugabyteDB documentation.