Distributed SQL Tips and Tricks – Oct 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
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>
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
- Presentation Recap: The Distributed Database Behind Twitter
- Presentation Recap: Kubernetes as a Universal Control Plane – Joe Beda, VMware
- Yugabyte Adopts a New Versioning System
- Using Terraform to Provision a YugabyteDB Cluster
- A PostgreSQL-Compatible, Distributed SQL Cheat Sheet: The Basics
- Version Control for Distributed SQL with Flyway’s Maven Plugin
- Getting Started with IntelliJ IDEA and Distributed SQL
- Use Retool to Quickly Build Distributed SQL and React Apps
- How Justuno Leveraged YugabyteDB to Consolidate Multiple SQL & NoSQL Databases
- Heroku Add-on for Yugabyte Cloud Now Available in Public Beta
- Watch the Playbacks from the Distributed SQL Summit 2020
- Testing PostgreSQL Compatibility in YugabyteDB
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
Oct 29, 8am PT: Join us for free online training course and free certification – Getting Started with YugabyteDB
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:
- Senior Product Manager, Core Database
- QA Engineer
- Site Reliability Engineer (SRE)
- Community Success Engineer
- Software Engineer, Cloud Infrastructure
- Software Engineer, Core Database
- Software Engineer, Full Stack
- Solutions Engineer
- Developer Advocate
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!
Ready to start exploring YugabyteDB features? Getting up and running locally on your laptop is fast. Visit our quickstart page to get started.