YugaByte DB Engineering & Community Update – Aug 9, 2019
We are pleased to announce that YugabyteDB 1.3.1 is live! You can read the release notes of this and previous versions here. This release is shipping with 22 new features, enhancements and bug fixes. This release also brings us closer to our 2.0 release which will be shipping in a few weeks. The flagship feature in 2.0 will be the YSQL API becoming generally available.
To learn more about the additional features shipping in the 2.0 release check out “YugabyteDB 2.0: A Distributed SQL Roadmap.”
Now, let’s highlight a few items in this release:
[#1590] Support for bulk inserts in a COPY FROM command
Prior to 1.3.1, the, COPY FROM command went through our distributed transaction path and did not use the bulk/multi-insert path. This meant that data loads were much slower than expected. Now with 1.3.1, performance is greatly improved! In our testing on
n1-highcpu-8 Google Cloud instance with an RF of 3, we saw an improvement from ~3 minutes and 30 seconds to under 6 seconds (using a batch size of 512 writes) for inserting 50,000 rows into a table with a single integer primary key. For the test we used both the pgsql
\copy command and also the server side COPY FROM and they yielded the same performance when run on a local cluster.
[#1156] Support for Triggers
With this code landing, YugabyteDB now supports triggers! A trigger is a function invoked automatically whenever an event associated with a table occurs. An event could be anything like an INSERT, UPDATE, DELETE or TRUNCATE.
There are two main types of triggers: row and statement level triggers. Which one you use determines how many times the trigger is invoked and at what time. For example, if you issue an UPDATE statement that affects 100 rows, the row level trigger will be invoked 100 times, while the statement level trigger will be invoked just one time. A few other things to note about PostgreSQL triggers:
- Triggers can be invoked before or after an event
- Triggers can help with data integrity rules, like if a new row is added to the
subscribertable, then we might also want another row to be created in the
- Unlike other SQL systems, PostgreSQL fires a trigger for the TRUNCATE event
- It also allows you to define statement-level triggers on views
- PostgreSQL requires the use of a UDF as the action of the trigger
Below is an example trigger from the PostgreSQL documentation that works as expected in YugabyteDB. This example trigger ensures that any insert, update or delete of a row in the
emp table is recorded (i.e., audited) in the
emp_audit table. The current time and user name are stamped into the row, together with the type of operation performed on it.
First, create two tables:
CREATE TABLE emp ( empname text NOT NULL, salary integer ); CREATE TABLE emp_audit( operation char(1) NOT NULL, stamp timestamp NOT NULL, userid text NOT NULL, empname text NOT NULL, salary integer );
Next, create the function:
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$ BEGIN -- -- Create a row in emp_audit to reflect the operation performed on emp, -- make use of the special variable TG_OP to work out the operation. -- IF (TG_OP = 'DELETE') THEN INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*; RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*; RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*; RETURN NEW; END IF; RETURN NULL; -- result is ignored since this is an AFTER trigger END; $emp_audit$ LANGUAGE plpgsql;
Now, create the trigger:
CREATE TRIGGER emp_audit AFTER INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();
Finally, let’s check that it all works by inserting a row and verifying the results.
INSERT INTO emp (empname, salary) VALUES ('Jimmy', 30); SELECT * FROM emp_audit; operation | stamp | userid | empname | salary -----------+----------------------------+----------+---------+-------- I | 2019-08-08 09:13:30.616571 | postgres | Jimmy | 30 (1 row)
Thanks to @m-iancu!
[#1154] Support for Extensions
With the resolution of this issue we’ve laid the groundwork for fully supporting PostgreSQL extensions. Some extensions require additional features to land first, but a few should already work out-of-the-box…for example pgcrypto, which provides cryptographic functions for PostgreSQL. If you are looking for PostGIS support, stay tuned! It will be supported in 2.0.
Thanks to @m-iancu!
[#1734] Support for NULLS LAST in SELECT
With NULLS LAST specified in your query, all NULL values are sorted after non-NULL values in the result set. For example:
SELECT customer_id, ship_region FROM orders WHERE shipped_date BETWEEN '1996-01-01' and '1996-07-20' ORDER BY ship_region DESC NULLS LAST; customer_id | ship_region -------------+------------- WELLI | SP HANAR | RJ HANAR | RJ VICTE | RICSU | TOMSP | SUPRD | VINET | (8 rows)
New Documentation, Blogs, Tutorials, and Videos
- Getting Started with SQL Workbench/J on a Distributed SQL Database
- PostgreSQL How To: Installing the Chinook Sample DB on a Distributed SQL Database
- Getting Started with DBeaver on a Distributed SQL Database
- PostgreSQL How-to: SportsDB Running on a Distributed SQL Database
- How-to: The PGExercises PostgreSQL Tutorial Running on a Distributed SQL Database
- Working with DATE and TIME – thanks @mrpanda!
- Working with Strings and Text – thanks @mrpanda!
- Build a Java App with Spring Data JPA with Hibernate ORM Example
- Build a NodeJS App with a Sequelize ORM Example
- Build a Go App with a GORM ORM Example
- Build a Python App with SQL Alchemy ORM Example
- Build a Ruby App with Rails ActiveRecord ORM Example
- Want to contribute code to the YugabyteDB project? Contributor guides are now live!
- A new Benchmarking guide is also available.
Upcoming Meetups and Conferences
We will be at a number of conferences and meetups over the next few months, below are some select few with the highlights. Do stop by, say hello and ask us any questions you have.
Distributed SQL Summit at PostgresConf Silicon Valley
- September 20: San Jose, CA
- September 20: Silicon Valley PostgreSQL Meetup
- Oct 7-10: Austin
- Dec 2-7: Las Vegas
We are Hiring!
Yugabyte is growing fast and we’d like you to help us keep the momentum going! Check out our currently open positions:
- Software Engineer – Cloud Infrastructure – Sunnyvale, CA
- Software Engineer – Core Database – Sunnyvale, CA
- Software Engineer – Full Stack – Sunnyvale, CA
- Developer Advocate – Remote
- Senior Technical Writer – Remote
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? Getting up and running locally with a three node cluster on your laptop is fast. Just download, extract, create the cluster, check its status and your ready to go!
- Using YugabyteDB at your company? Tell us about it and we’ll send you a hoodie!
- Compare YugabyteDB in depth to databases like CockroachDB, Google Cloud Spanner and MongoDB.
- Get started with YugabyteDB on macOS, Linux, Docker, and Kubernetes.
- Contact us to learn more about licensing, pricing or to schedule a technical overview.