Distributed SQL Tips and Tricks – July 9th, 2021

July 9, 2021

Welcome back to our bi-weekly tips and tricks blog where I have the pleasure of recapping some distributed SQL questions from around the Internet. This blog series would not be possible without all of the behind the scenes and community work done by YugabyteDB members such as Dorian Hoxha and Frits Hoogland. All of us here at Yugabyte also have to thank our incredible user community for their work with the technology, and not being afraid to ask questions. Do you have questions? Make sure to ask them on our YugabyteDB Slack channel, Forum, GitHub, or Stack Overflow. Let’s dive in:

Using OpenSSL with YugabyteDB’s YSQL API

You have YugabyteDB installed and now you are looking at the best ways to make sure your connection to that database is secure. The first step you take is to enable TLS. You try connecting through ysqlsh and it works without any issue:

$ ysqlsh -h xxx.xxx.xx.xxx
ysqlsh (11.2-YB-2.7.1.1-b0)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
yugabyte=#

However when you try connecting with OpenSSL you get an error that says the peer has no certificate.

$ /usr/local/bin/openssl s_client  -connect xxx.xxx.xx.xxx:5433
CONNECTED(00000003)
write:errno=0
---
no peer certificate available
---
No client certificate CA names sent
---
SSL handshake has read 0 bytes and written 293 bytes
Verification: OK
---
New, (NONE), Cipher is (NONE)
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
Early data was not sent
Verify return code: 0 (ok)

In order to get around this, you need to elevate the insecure connection to a secure one using STARTTLS. This is referred to as “opportunistic TLS”. This is why the openssl connection initially acts like TLS is not enabled when you attempt to connect. In order to make this work, you need a version of OpenSSL >= 1.1.1 and an additional command line option -starttls postgres. For this specific example you would use the following command:

$ openssl s_client -starttls postgres -connect xxx.xxx.xx.xxx:5433

If you are using Yugabyte Platform or our fully managed Yugabyte Cloud, you do not need to worry about this level of detail. Rather you can set this up in a couple clicks.

Specifying the Number of Tablets with YugabyteDB

When beginning to work with YugabyteDB, it can be difficult to predict the number of tablets (shards) needed. This concept is an important part of the YugabyteDB sharding layer. Recall that the default tablet number for the cluster is 8 for most production workloads, if not explicitly set. This can be changed at the tserver or table/index level.

An example of this change for YSQL on the tserver level would be:

bin/yb-ctl --rf=3 create --ysql_num_shards_per_tserver=1

For YCQL it would be:

bin/yb-ctl --rf=3 create --num_shards_per_tserver=1

On the table level setting, the number of tablets is done during creation. For example the CREATE TABLE...SPLIT INTO command is used for hash sharded tables using the YSQL API. Additional examples can be found in our documentation. The most exciting development on this front is automatic tablet splitting, where you can set tablets to automatically split upon reaching a specified size threshold. The YugabyteDB development team is working hard to enable this as the default option in an upcoming release, so stay tuned. To keep up with the progress being made in regards to tablet splitting you can follow this GitHub issue. Overall, the number of tablets will be controlled at the tserver or table/index level. The only exception to this is for colocated tables which are done on the database level.

For more information about data sharding, and how it works with YugabyteDB, you can read our blog on the topic here. For examples of the CREATE TABLE commands used in order to pre split tables, you can read them here: YSQL and YCQL.

Column Type Conversion with YugabyteDB

If you are looking to convert column types in YugabyteDB today there are a couple avenues you can take. The most straightforward way would be to use the following command, however you will notice that this currently returns an error.

ALTER TABLE "my_table" ALTER COLUMN "my_column" TYPE varchar;
ERROR:  This ALTER TABLE command is not yet supported.

Today we do support ALTER COLUMN type that does not require on-disk changes. This however is limited to increasing the size of varchar and varbit types. Meaning that you can change a column from varchar(50) to varchar(255) for example. However, if you attempt to change from the int type to the varchar type, you will get an error. As called out in this issue, the reason for that is because altering data type may require changing on-disk data for existing rows. Performing this in a distributed cluster, as well as with replication and indexes, will result in overhead and data-transfer. Without such support today we can still accomplish this task in one of two ways. One way would be to create a new column with the correct type and move the data over. Another way would be to create a new table with the correct column types and move the data over. As part of our ongoing effort to continuously provide additional features to YugabyteDB we are aiming to eventually support all of the main ALTER TABLE commands and variants. This master list can be found here.