Distributed SQL Tips and Tricks – July 17, 2020

July 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 to identify a colocated database from the rest

One way to identify a colocated table in YugabyteDB is to look at the yb-master UI page (https://<yb-master-ip>:7000/tables). All colocated databases will have a system table entry with the name (<id>.colocated.parent.tablename).

You can also use the yb-admin utility like below:

./bin/yb-admin --master_addresses <master_addresses> list_tables_with_db_types | grep '\.colocated\.parent\.tablename

How to increment a counter in a YCQL MAP collection

At the moment doing an UPDATE a SET m['k'] = m['k'] + 1 WHERE... is not supported in YCQL (or Apache Cassandra).

For Collection Data Types, MAP/LIST/SET, + and - operators are used to append or remove the <key, value> entries from the collection.

As an alternative way we can use common columns (using of the map-key as a clustering column for better performance):

ycqlsh:k> create table t2 (id int, m_key text, m_val int, primary key((id), m_key));
ycqlsh:k> insert into t2 (id, m_key, m_val) values (100, 'k', 1);
ycqlsh:k> select * from t2;
 id  | m_key | m_val
-----+-------+-------
 100 | 	k | 	1
(1 rows)
ycqlsh:k> update t2 set m_val = m_val + 1 where id = 100 and m_key = 'k';
ycqlsh:k> select * from t2;
 id  | m_key | m_val
-----+-------+-------
 100 | 	k | 	2
(1 rows)

Limits in YCQL collections

Collections are designed for storing small sets of values that are not expected to grow to an arbitrary size (such as phone numbers or addresses for a user, rather than posts or messages). While collections of larger sizes are allowed, they may have a significant impact on performance for queries involving them. In particular, some list operations (insert at an index and remove elements) require a read-before-write.

If the application warrants a big number of values, it’s better to model the collection as another separate table like in the question above.

What’s the recommended way to use pg_dump/ysql_dump with YugabyteDB to export data when a table is still receiving inserts?

Running a plain ysql_dump while the table is receiving inserts may timeout and result in Query error: Restart read required errors.

./tserver/postgres/bin/ysql_dump -h 127.0.0.1 -d postgres --data-only --table my_table -f out.txt
ysql_dump: Dumping the contents of table "sqlsecondaryindex" failed: PQgetResult() failed.
ysql_dump: Error message from server: ERROR:  Query error: Restart read required at: { read: { physical: 1592265362684030 } local_limit: { physical: 1592265375906038 } global_limit: <min> in_txn_limit: <max> serial_no: 0 }

To read against a consistent snapshot and avoid running into the “read restart” error, pass the --serializable-deferrable option to ysql_dump. For example:

~/tserver/postgres/bin/ysql_dump -h 127.0.0.1 -d postgres \
   --data-only --table sqlsecondaryindex \
   --serializable-deferrable -f data1.csv

New Documentation, Blogs, Tutorials, and Videos

New Blogs

New Videos

New and Updated Docs

To support the release of YugabyteDB 2.2, we published these new docs:

Upcoming Events

July 22 @ 10am PT [1pm ET] – Webinar: What’s new in YugabyteDB 2.2
July 29 @ 10am PT [1pm ET] – Webinar: Introduction to SQL; if you need a relational database, but you never learned SQL, then this webinar is for you.
Aug 7 @ 11:30 PT [2:30pm ET] – YugabyteDB Community Q&A, Topic: Cloud Native
Aug 12 @ 10am PT [1pm ET] – Webinar: Geo-distributed SQL databases: 9 techniques to reduce cross-region latency
Aug 17 – 20 – KubeCon + CloudNativeCon Europe 2020 Virtual

We Are Hiring!

Yugabyte is growing fast and we’d like you to help us keep the momentum going! All Yugabyte interviews are held virtually, so we can all continue doing our part with social distancing. Although we are based in Silicon Valley, we hire exceptional folks wherever they are. Check out our currently open positions:

Our team consists of domain experts from leading software companies such as Facebook, Oracle, Nutanix, Google, and LinkedIn. We have come a long way in a short time but we cannot rest on our past accomplishments. We need your ideas and skills to make us better at every function that is necessary to create the next great software company. All while having tons of fun and blazing new trails!