Distributed SQL Tips and Tricks – Aug 17, 2020

August 17, 2020

Welcome to this week’s 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 since the last post. Got questions? Make sure to ask them on our YugabyteDB Slack channel, Forum, GitHub, or Stackoverflow. Ok, let’s dive right in:

How are secondary indexes stored internally in YugabyteDB?

In the storage layer, the secondary index rows look pretty similar to a main table. At a high level, suppose you have a table:

CREATE TABLE T (a PRIMARY KEY, b, c, d, e);

Let’s consider both flavors of indexes — non-unique and unique.

a) Non-unique index:

CREATE INDEX my_idx ON T(b) INCLUDE (e);

Here, the index, under the covers, will look like a table, where its PRIMARY KEY is (b, a). The key in the index includes the primary key a of the main table for two reasons — one of course is to be able to locate the full row in the main table when looking up a row by b and the other is to allow for multiple entries in the index (or the table) to have the same value for b. The value columns of this index table will include e because the optional INCLUDE clause above in the CREATE INDEX statement mentions e.

If you are looking up column e by providing b as input, then that request can be served off of the index itself without going to the main table. But if you are looking up column d by providing b, then you have to make one extra hop to the main table to extract the value of d.

b) Unique index:

CREATE UNIQUE INDEX my_idx ON T(b) INCLUDE (e);

In this case, the index table’s PRIMARY KEY will be b. And, a (the primary key of the main table) and e (the included column) will be stored like value columns (in the index table).

How can I rename a large YSQL table under high load?

If you need to rename tables in a database in production, you can do so using the ALTER TABLE statement:

ALTER TABLE old_table_name RENAME TO new_table_name;

Renaming a table in YugabyteDB is a transactional metadata change underneath. This does not lock the table and it is a quick operation that does not depend on table size.

How does YugabyteDB handle arbitrary precision numbers?

In the YSQL layer, YugabyteDB inherits PostgreSQL DECIMAL and NUMERIC column types. The precision can be specified per-column, and has a range of up to 131072 digits before the decimal point, and up to 16383 digits after the decimal point. Example:

yugabyte=# CREATE TABLE numerics(id NUMERIC PRIMARY KEY);
yugabyte=# INSERT INTO numerics(id) VALUES (1234857629137.345123891237971231232791239712);
yugabyte=# SELECT * FROM numerics;
                  	id                 	 
----------------------------------------------
 1234857629137.345123891237971231232791239712
(1 row)

While on the YCQL front, the DECIMAL type has an arbitrary precision with no upper bound. Example:

ycqlsh:k> CREATE TABLE t(k INT PRIMARY KEY, v DECIMAL);
ycqlsh:k> INSERT INTO t(k, v) values(1, 10000.0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789);
ycqlsh:k> SELECT * FROM t;
 k | v
---+---------------------------------------------------------------------------------------------------------------------------------------------------------
 1 |                                            	10000.0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789

How to display server-ip in the UI

When starting a new cluster, you have to set the --rpc_bind_addresses to the listening IP address on each yb-tserver. Looking at the yb-master UI on https://<yb-master-ip>:7000/, we see that the links to the yb-tservers don’t link to their IP:

How to display server-ip in the UI, first without the IP as an example

This is fixed by setting the --webserver_interface gflag to the IP address. We can see the IP of each server:

set the --webserver_interface gflag to the IP address to see the IP of each server

New Documentation, Blogs, Tutorials, and Videos

New Blogs

New Videos

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 – What’s New and Improved in YugabyteDB Docs, Aug 2020