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
- Announcing YugabyteDB 2.2 – Distributed SQL Made Easy
- GraphQL and Distributed SQL Tips and Tricks
- Polymorphism in SQL part one – anyelement and anyarray
- Polymorphism in SQL part two – variadic functions
New Videos
- Distributed SQL Meets PostgreSQL
- Getting Started with YugabyteDB on GKE with Helm 3
- Getting Started with YugabyteDB and GraphQL on Kubernetes
New and Updated Docs
To support the release of YugabyteDB 2.2, we published these new docs:
- Transactional distributed backups – YSQL
- Transactional distributed backups – YCQL
- Online index builds: Simple and unique indexes for YCQL
- Online index builds: Simple indexes for YSQL
- Deferred constraints: YSQL now supports DEFERRABLE INITIALLY IMMEDIATE and DEFERRABLE INITIALLY DEFERRED clauses on foreign keys
- Colocated tables: Database-level colocation for YSQL
- Benchmark YugabyteDB using TPC-C
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
Get Started
Ready to start exploring YugabyteDB features? Getting up and running locally on your laptop is fast. Visit our quickstart page to get started.
