The Distributed SQL Blog

Thoughts on distributed databases, open source and cloud native

YugaByte DB Engineering & Community Update – Aug 9, 2019

VP Developer Relations

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.

What’s YSQL? It’s YugabyteDB’s PostgreSQL-compatible, distributed SQL API.

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.

Thanks to @ndeodhar and @JDNdeveloper!

[#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 subscriber table, then we might also want another row to be created in the payment_info table.
  • 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)

Thanks to @jj-kim and @mrpanda!

New Documentation, Blogs, Tutorials, and Videos

New Blogs

New Docs

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

Meetups

SpringOne Platform

AWS re:Invent

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:

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!

Get Started

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!

What’s Next?

  • 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.

Related Posts

VP Developer Relations