Distributed SQL Tips and Tricks – Oct 16, 2020

October 16, 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 use joins in UPDATE statements

Sometimes, you need to update rows in a table based on values in another table. In this case, you can use the PostgreSQL UPDATE join syntax with the FROM clause as follows.

First we create a table of products and product segments:

yugabyte=# CREATE TABLE product_segment(id BIGINT PRIMARY KEY, name TEXT NOT NULL, discount NUMERIC (4,2));
yugabyte=# INSERT INTO product_segment (id,name,discount) VALUES (1, 'Luxury', 0.06),(2, 'Mass', 0.1);
yugabyte=# SELECT * FROM product_segment;
 id |  name  | discount
----+--------+----------
  2 | Mass   | 	0.10
  1 | Luxury | 	0.06
(2 rows)

yugabyte=# CREATE TABLE product (id BIGINT PRIMARY KEY, name TEXT NOT NULL, price NUMERIC(10,2), net_price NUMERIC(10,2), segment_id BIGINT NOT NULL);
yugabyte=# INSERT INTO product(id,name,price,segment_id) VALUES (1, 'fancy clothes', 199, 1), (2, 'tshirt', 3.50, 2);
yugabyte=# SELECT * FROM product;
 id |     name      | price  | net_price | segment_id 
----+---------------+--------+-----------+------------
  2 | tshirt        |   3.50 |           |          2
  1 | fancy clothes | 199.00 |           |          1
(2 rows)

Now we want to set the net_price of products based on the discount of the product_segment that they belong to.

We can use the FROM clause to join other tables when we’re updating the base table:

yugabyte=# UPDATE product SET net_price = price - price * discount FROM product_segment WHERE product.segment_id = product_segment.id;

Then we can query the products to see the updated rows:

yugabyte=# SELECT * FROM product;
 id |     name      | price  | net_price | segment_id 
----+---------------+--------+-----------+------------
  2 | tshirt        |   3.50 |      3.15 |          2
  1 | fancy clothes | 199.00 |    187.06 |          1
(2 rows)

Does YugabyteDB support namespaces and can multiple applications use a cluster without interfering with each other?

For YSQL, YugabyteDB supports the PostgreSQL notion of database as a namespace mechanism. Role-based access control can be used to limit user access to these databases using the GRANT command.

Another way is to use multiple schemas in a database to support multiple tenants in one database. This way you can have multiple applications each having its own database, and multiple tenants in an application each having its own data.

For YCQL, YugabyteDB supports the Apache Cassandra like notion of keyspace. And there’s a corresponding role-based access control mechanism supported by the GRANT PERMISSION command.

How to run large scans in YSQL

PostgreSQL doesn’t have any query timeouts by default. While this may be OK in single node databases, in a cluster environment a bad query may end up slowing down your whole cluster. Therefore we have enabled query timeouts of 60000 milliseconds (60 seconds) by default.

Sometimes you have queries that should run for longer than that. The timeout can be increased by setting the statement_timeout parameter in YSQL to a bigger value. This can be set when you’re sending queries from your application driver or in the ysqlsh CLI for example:

yugabyte=# set statement_timeout = 180000;
yugabyte=# BEGIN ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE;
yugabyte=# <some long SELECT query>

Using ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE also helps long running queries by ensuring that the read snapshot used is consistent with later database states by waiting for a point in the transaction stream at which no anomalies can be present, so that there is no risk of the query failing due to conflicts from concurrent transactions.

How to disable foreign keys in YSQL for faster data import

Foreign keys in PostgreSQL and YSQL are enforced using triggers in the database. You can temporarily disable foreign keys by disabling triggers on the table that you’re loading data to. This is done using the ALTER TABLE statement like below:

yugabyte=# ALTER TABLE table_name DISABLE TRIGGER ALL;
yugabyte=# INSERT INTO table_name …...
yugabyte=# ALTER TABLE table_name ENABLE TRIGGER ALL;

Foreign keys will start being enforced after enabling triggers.

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, September 2020