CockroachDB With Flyway Schema Migration Tool

Today, I’m going to quickly introduce you to Flyway and some new capabilities in CockroachDB to take advantage of schema migrations. This is by no means an in-depth look at Flyway, therefore, I highly recommend getting acquainted with the Flyway documentation. With that, let’s dive in.

I’ll continue to use the docker build environment for the next tutorial because it fits nicely into the iterative model of developing and deploying with schema migration tools. We will need a recent CockroachDB image. The current folder tree looks like this:

crdb-flyway
└── docker-compose.yml

0 directories, 1 file

My docker-compose file looks like this:

version: '3.9'

services:

 crdb:
   image: cockroachdb/cockroach:v21.2.4
   container_name: crdb
   ports:
     - "26257:26257"
     - "8080:8080"
   command: start-single-node --insecure
   volumes:
     - ${PWD}/cockroach-data/crdb:/cockroach-data:rw

networks:
  default:
    external:
      name: roachnet

The Flyway is shipped as a docking container but since it is only used to carry out the relay it is not necessary to keep the flight path service active. We will operate a Flyway container upon request. However, we need to create a network so that the running CockroachDB node and the Flyway instance can communicate.

The first thing we need to do after creating the build file is docker network create roachnet. You can see that we are referring to that network in the build file after defining the service. We can check network availability with docker network ls

➜  crdb-flyway docker network create roachnet
a9456bd109efef37c582b36719b73c48e43a9d2d90ae83faa3b0098e7e1b8bdc
➜  crdb-flyway docker network ls
NETWORK ID          NAME                DRIVER              SCOPE
5bab0ec87901        bridge              bridge              local
0113aeaf0346        host                host                local
5208e02099d3        none                null                local
a9456bd109ef        roachnet            bridge              local

Start the build process with docker-compose up -d And let the cockroach run in the background.

At this point, our project tree should contain an author file and a cockroach data directory.

.
├── cockroach-data
│   └── crdb
└── docker-compose.yml

2 directories, 1 file

Now that we can confirm Cockroach accessibility using Flyway, let’s pull the latest version of the Flyway compatible image of Cockroach, at the time of writing it was 6.4.4.

➜  crdb-flyway docker pull flyway/flyway:6.4.4
6.4.4: Pulling from flyway/flyway
Digest: sha256:22d97ceb0c47182c04e5f45be6dcc29d3f5bb5d7c2218fa236670f793693f501
Status: Image is up to date for flyway/flyway:6.4.4
docker.io/flyway/flyway:6.4.4

At this point, since we have a network defined, we can run the following command to make sure Flyway can access our instance of Cockroach.

docker run --rm --network roachnet -v $PWD/flyway/sql:/flyway/sql flyway/flyway:6.4.4 -url=jdbc:postgresql://crdb:26257/defaultdb -user=root -password="" -connectRetries=3 info

We run Flyway with info Predicate for reporting the current status of immigration. We run a container, we indicate a volume called $PWD/flyway/sql:/flyway/sql Where all of our SQL migration files will be located, the CRDB instance can be accessed using the url jdbc://postgres://crdb:26257/defaultdb, where crdb is the name of the container with the user root, this is an insecure instance, so we pass an empty string for the password and an option -connectRetries Tick ​​to retry the connection 3 times. Output the command below:

Flyway Community Edition 6.4.4 by Redgate
Database: jdbc:postgresql://crdb:26257/defaultdb (PostgreSQL 9.5)
Schema version: << Empty Schema >>

+----------+---------+-------------+------+--------------+-------+
| Category | Version | Description | Type | Installed On | State |
+----------+---------+-------------+------+--------------+-------+
| No migrations found                                            |
+----------+---------+-------------+------+--------------+-------+

With this command, we have added a new directory called flyway in our project directory.

.
├── cockroach-data
│   └── crdb
├── docker-compose.yml
└── flyway
    └── sql

4 directories, 1 file

This is quite long, let’s simplify this a bit with the flyway conf.

mkdir -p flyway/conf
touch flyway/conf/flyway.conf

This is a configuration file where we can pass some arguments that we have explicitly passed into a file info The above command.

flyway.url=jdbc:postgresql://crdb:26257/defaultdb
flyway.user=root
flyway.password=""
flyway.connectRetries=3

Our docker command changes to docker run --rm --network roachnet -v $PWD/flyway/sql:/flyway/sql -v $PWD/flyway/conf:/flyway/conf flyway/flyway:6.4.4 info

Refer to the Flyway documentation for other configuration parameters.

We now have the following project tree.

.
├── cockroach-data
│   └── crdb
├── docker-compose.yml
└── flyway
    ├── conf
    │   └── flyway.conf
    └── sql

All that away, let’s begin to explore the potential of the Flyway and Cockroach scheme.

Let’s write a DDL instruction and save it as a .sql file in the . format flyway/sql Guide.

We will create a table with a PK on an integer column. Later, we’ll demonstrate the online primary key change capabilities shipped with 20.1.

1. Create a table using INT PK

  CREATE TABLE fruits (
        id INT NOT NULL PRIMARY KEY DEFAULT unique_rowid(),
        name STRING,
        color STRING
  );
  SHOW CREATE TABLE fruits;

Save this as flyway/sql/V1__Create_table.sql

.
├── cockroach-data
│   └── crdb
├── docker-compose.yml
└── flyway
    ├── conf
    │   └── flyway.conf
    └── sql
        └── V1__Create_table.sql

At this point we’re ready to run the migration, so let’s run a file info command again:

Flyway Community Edition 6.4.4 by Redgate
Database: jdbc:postgresql://crdb:26257/defaultdb (PostgreSQL 9.5)
Schema version: << Empty Schema >>

+-----------+---------+--------------+------+--------------+---------+
| Category  | Version | Description  | Type | Installed On | State   |
+-----------+---------+--------------+------+--------------+---------+
| Versioned | 1       | Create table | SQL  |              | Pending |
+-----------+---------+--------------+------+--------------+---------+

immigration is in a suspended state; Let’s migrate!

docker run --rm --network roachnet -v $PWD/flyway/sql:/flyway/sql -v $PWD/flyway/conf:/flyway/conf flyway/flyway:6.4.4 migrate

Our command is changed with one pretext, migrate

Flyway Community Edition 6.4.4 by Redgate
Database: jdbc:postgresql://crdb:26257/defaultdb (PostgreSQL 9.5)
Successfully validated 1 migration (execution time 00:00.023s)
Creating Schema History table "defaultdb"."flyway_schema_history" ...
Current version of schema "defaultdb": << Empty Schema >>
Migrating schema "defaultdb" to version 1 - Create table [non-transactional]
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table_name | create_statement                                                                                                                                                                         |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| fruits     | CREATE TABLE fruits (
    id INT8 NOT NULL DEFAULT unique_rowid(),
    name STRING NULL,
    color STRING NULL,
    CONSTRAINT "primary" PRIMARY KEY (id ASC),
    FAMILY "primary" (id, name, color)
) |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Successfully applied 1 migration to schema "defaultdb" (execution time 00:00.367s)

We will load the data into our newly created table. Since we are using the unique_rowid() function, we don’t have a view of the identifier being generated at runtime, RETURNING Joomla that does exactly that, prints to stdout the identifiers generated with that entry.

2. Load the data with RETURNING Keyword to display the output unique_rowid()

INSERT INTO fruits (name, color) VALUES ('apple', 'red'),('orange', 'orange'),('plum', 'purple') RETURNING id;

Save the file as a file flyway/sql/V2__Load_data.sql. V#__ It is a standard naming convention for Flyway, please refer to their docs for an explanation. Run the migration again.

Flyway Community Edition 6.4.4 by Redgate
Database: jdbc:postgresql://crdb:26257/defaultdb (PostgreSQL 9.5)
Successfully validated 2 migrations (execution time 00:00.037s)
Current version of schema "defaultdb": 1
Migrating schema "defaultdb" to version 2 - Load data [non-transactional]
+--------------------+
| id                 |
+--------------------+
| 566986134960537601 |
| 566986134960603137 |
| 566986134960635905 |
+--------------------+

Successfully applied 1 migration to schema "defaultdb" (execution time 00:00.177s)

We can check info repeatedly:

Flyway Community Edition 6.4.4 by Redgate
Database: jdbc:postgresql://crdb:26257/defaultdb (PostgreSQL 9.5)
Schema version: 2

+-----------+---------+--------------+------+---------------------+---------+
| Category  | Version | Description  | Type | Installed On        | State   |
+-----------+---------+--------------+------+---------------------+---------+
| Versioned | 1       | Create table | SQL  | 2020-06-25 15:55:40 | Success |
| Versioned | 2       | Load data    | SQL  | 2020-06-25 16:00:36 | Success |
+-----------+---------+--------------+------+---------------------+---------+

You can start seeing the benefits of schema migration tools: versioning, audit trail, SDLC, etc.

At this point, I would like to outline some typical development scenarios an engineer might go through to develop a product. Let’s say we change our mind about the primary key and need to change that to better fit our use case and access patterns. We will neglect id Column and filtration name And color We also have a new PK. Because the fields name And color It can contain NULLLet’s change the table definition for these columns to prevent them.

3. Change the name and color to NOT NULL

ALTER TABLE fruits ALTER COLUMN name SET NOT NULL;
ALTER TABLE fruits ALTER COLUMN color SET NOT NULL;
SHOW CREATE TABLE fruits;

Once you save the SQL statement above and start the file with V3__Run the relay command.

Successfully validated 3 migrations (execution time 00:00.032s)
Current version of schema "defaultdb": 2
Migrating schema "defaultdb" to version 3 - Change color and name to not null [non-transactional]
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table_name | create_statement                                                                                                                                                                                 |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| fruits     | CREATE TABLE fruits (
    id INT8 NOT NULL DEFAULT unique_rowid(),
    name STRING NOT NULL,
    color STRING NOT NULL,
    CONSTRAINT "primary" PRIMARY KEY (id ASC),
    FAMILY "primary" (id, name, color)
) |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Successfully applied 1 migration to schema "defaultdb" (execution time 00:00.708s)

Now comes the bulk, change the PK to name + color.

4. Modify the table to change the PK to the name and color

ALTER TABLE fruits ALTER PRIMARY KEY USING COLUMNS (name, color);
SHOW CREATE TABLE fruits;

Again, save the DDL as a file in the . format flyway/sql/V4__<desired_name>.sql

Flyway Community Edition 6.4.4 by Redgate
Database: jdbc:postgresql://crdb:26257/defaultdb (PostgreSQL 9.5)
Successfully validated 4 migrations (execution time 00:00.038s)
Current version of schema "defaultdb": 3
Migrating schema "defaultdb" to version 4 - Change pk to name and color [non-transactional]
DB: primary key changes are finalized asynchronously; further schema changes on this table may be restricted until the job completes
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table_name | create_statement                                                                                                                                                                                                                                    |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| fruits     | CREATE TABLE fruits (
    id INT8 NOT NULL DEFAULT unique_rowid(),
    name STRING NOT NULL,
    color STRING NOT NULL,
    CONSTRAINT "primary" PRIMARY KEY (name ASC, color ASC),
    UNIQUE INDEX fruits_id_key (id ASC),
    FAMILY "primary" (id, name, color)
) |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Successfully applied 1 migration to schema "defaultdb" (execution time 00:00.560s)

Notice the change of PK, we can now drop a file id column because it is no longer useful to us.

5. Drop the unique index constraint and identifier column

SHOW INDEX FROM fruits;
DROP INDEX fruits_id_key CASCADE;
ALTER TABLE fruits DROP COLUMN id CASCADE;
SHOW CREATE TABLE fruits;
Flyway Community Edition 6.4.4 by Redgate
Database: jdbc:postgresql://crdb:26257/defaultdb (PostgreSQL 9.5)
Successfully validated 5 migrations (execution time 00:00.038s)
Current version of schema "defaultdb": 4
Migrating schema "defaultdb" to version 5 - Drop unique constraint and id column [non-transactional]
+------------+---------------+------------+--------------+-------------+-----------+---------+----------+
| table_name | index_name    | non_unique | seq_in_index | column_name | direction | storing | implicit |
+------------+---------------+------------+--------------+-------------+-----------+---------+----------+
| fruits     | primary       | f          | 1            | name        | ASC       | f       | f        |
| fruits     | primary       | f          | 2            | color       | ASC       | f       | f        |
| fruits     | fruits_id_key | f          | 1            | id          | ASC       | f       | f        |
| fruits     | fruits_id_key | f          | 2            | name        | ASC       | f       | t        |
| fruits     | fruits_id_key | f          | 3            | color       | ASC       | f       | t        |
+------------+---------------+------------+--------------+-------------+-----------+---------+----------+

DB: the data for dropped indexes is reclaimed asynchronously
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table_name | create_statement                                                                                                                                                |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| fruits     | CREATE TABLE fruits (
    name STRING NOT NULL,
    color STRING NOT NULL,
    CONSTRAINT "primary" PRIMARY KEY (name ASC, color ASC),
    FAMILY "primary" (name, color)
) |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+

Successfully applied 1 migration to schema "defaultdb" (execution time 00:00.801s)

6. Load more data

INSERT INTO fruits (name, color) VALUES ('avocado', 'green'),('peach', 'yellow');
SELECT * FROM fruits;
Flyway Community Edition 6.4.4 by Redgate
Database: jdbc:postgresql://crdb:26257/defaultdb (PostgreSQL 9.5)
Successfully validated 6 migrations (execution time 00:00.042s)
Current version of schema "defaultdb": 5
Migrating schema "defaultdb" to version 6 - Load data [non-transactional]
+---------+--------+
| name    | color  |
+---------+--------+
| apple   | red    |
| avocado | green  |
| orange  | orange |
| peach   | yellow |
| plum    | purple |
+---------+--------+

Successfully applied 1 migration to schema "defaultdb" (execution time 00:00.161s)

Let’s make it a little fun by adding UNIQUE cling to name field. This means that we can have an exact copy color as long name Of the fruit is still unique.

7. Add a unique constraint to the name in descending order

CREATE UNIQUE INDEX ON fruits (name DESC);
SHOW CONSTRAINTS FROM fruits;
SHOW CREATE TABLE fruits;
Flyway Community Edition 6.4.4 by Redgate
Database: jdbc:postgresql://crdb:26257/defaultdb (PostgreSQL 9.5)
Successfully validated 7 migrations (execution time 00:00.044s)
Current version of schema "defaultdb": 6
Migrating schema "defaultdb" to version 7 - Add unique constraint on name column [non-transactional]
+------------+-----------------+-----------------+-----------------------------------+-----------+
| table_name | constraint_name | constraint_type | details                           | validated |
+------------+-----------------+-----------------+-----------------------------------+-----------+
| fruits     | fruits_name_key | UNIQUE          | UNIQUE (name DESC)                | t         |
| fruits     | primary         | PRIMARY KEY     | PRIMARY KEY (name ASC, color ASC) | t         |
+------------+-----------------+-----------------+-----------------------------------+-----------+

+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table_name | create_statement                                                                                                                                                                                           |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| fruits     | CREATE TABLE fruits (
    name STRING NOT NULL,
    color STRING NOT NULL,
    CONSTRAINT "primary" PRIMARY KEY (name ASC, color ASC),
    UNIQUE INDEX fruits_name_key (name DESC),
    FAMILY "primary" (name, color)
) |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Successfully applied 1 migration to schema "defaultdb" (execution time 00:00.556s)

8. Load more data

Note the colors yellow And green It has been used before (a unique constraint or name that does not affect the color).

INSERT INTO fruits (name, color) VALUES ('clementine', 'yellow'), ('pear', 'green');
SELECT * FROM fruits;
Flyway Community Edition 6.4.4 by Redgate
Database: jdbc:postgresql://crdb:26257/defaultdb (PostgreSQL 9.5)
Successfully validated 8 migrations (execution time 00:00.045s)
Current version of schema "defaultdb": 7
Migrating schema "defaultdb" to version 8 - Load data respecting unique [non-transactional]
+------------+--------+
| name       | color  |
+------------+--------+
| apple      | red    |
| avocado    | green  |
| clementine | yellow |
| orange     | orange |
| peach      | yellow |
| pear       | green  |
| plum       | purple |
+------------+--------+

Successfully applied 1 migration to schema "defaultdb" (execution time 00:00.151s)

Let me explain what would happen if we tried to introduce another duplicate fruit:

root@:26257/defaultdb> insert into fruits (name, color) values ('plum', 'red');
ERROR: duplicate key value (name)=('plum') violates unique constraint "fruits_name_key"
SQLSTATE: 23505
root@:26257/defaultdb>

9. Cut table

TRUNCATE TABLE fruits;
Flyway Community Edition 6.4.4 by Redgate
Database: jdbc:postgresql://crdb:26257/defaultdb (PostgreSQL 9.5)
Successfully validated 9 migrations (execution time 00:00.051s)
Current version of schema "defaultdb": 8
Migrating schema "defaultdb" to version 9 - Truncate table [non-transactional]
Successfully applied 1 migration to schema "defaultdb" (execution time 00:00.227s)

Now we want to introduce a new unique world id with uid data type. We will add a new column called revised_id, make DEFAULT The behavior has to create a new key every time a record is inserted, change the PK to that new field, and eventually drop the index on the name and color fields.

10. Modify the table to change the PK to a UUID and drop the unique index of the old PK (name and color)

ALTER TABLE fruits ADD COLUMN revised_id UUID NOT NULL DEFAULT gen_random_uuid();
ALTER TABLE fruits ALTER PRIMARY KEY USING COLUMNS (revised_id);
SHOW CONSTRAINTS FROM fruits;
DROP INDEX fruits_name_color_key CASCADE;
SHOW CONSTRAINTS FROM fruits;
Flyway Community Edition 6.4.4 by Redgate
Database: jdbc:postgresql://crdb:26257/defaultdb (PostgreSQL 9.5)
Successfully validated 10 migrations (execution time 00:00.052s)
Current version of schema "defaultdb": 9
Migrating schema "defaultdb" to version 10 - Alter table change pk [non-transactional]
DB: primary key changes are finalized asynchronously; further schema changes on this table may be restricted until the job completes
+------------+-----------------------+-----------------+------------------------------+-----------+
| table_name | constraint_name       | constraint_type | details                      | validated |
+------------+-----------------------+-----------------+------------------------------+-----------+
| fruits     | fruits_name_color_key | UNIQUE          | UNIQUE (name ASC, color ASC) | t         |
| fruits     | fruits_name_key       | UNIQUE          | UNIQUE (name DESC)           | t         |
| fruits     | primary               | PRIMARY KEY     | PRIMARY KEY (revised_id ASC) | t         |
+------------+-----------------------+-----------------+------------------------------+-----------+

DB: the data for dropped indexes is reclaimed asynchronously
+------------+-----------------+-----------------+------------------------------+-----------+
| table_name | constraint_name | constraint_type | details                      | validated |
+------------+-----------------+-----------------+------------------------------+-----------+
| fruits     | fruits_name_key | UNIQUE          | UNIQUE (name DESC)           | t         |
| fruits     | primary         | PRIMARY KEY     | PRIMARY KEY (revised_id ASC) | t         |
+------------+-----------------+-----------------+------------------------------+-----------+

Successfully applied 1 migration to schema "defaultdb" (execution time 00:00.893s)

Until now, any new entry will have a new key with the data type uuid.

11. Upload data respecting UUID

INSERT INTO fruits (name, color) VALUES ('clementine', 'yellow'), ('pear', 'green'), ('avocado', 'green'),('peach', 'yellow'), ('apple', 'red'),('orange', 'orange'),('plum', 'purple');
SELECT * FROM fruits;
Flyway Community Edition 6.4.4 by Redgate
Database: jdbc:postgresql://crdb:26257/defaultdb (PostgreSQL 9.5)
Successfully validated 11 migrations (execution time 00:00.049s)
Current version of schema "defaultdb": 10
Migrating schema "defaultdb" to version 11 - Reload data [non-transactional]
+------------+--------+--------------------------------------+
| name       | color  | revised_id                           |
+------------+--------+--------------------------------------+
| plum       | purple | 0f9eb934-f29d-4aee-a3b7-4f3431421c99 |
| clementine | yellow | 1f38f571-027d-450c-8c65-a0675f25b963 |
| apple      | red    | 4d47207f-d186-43c9-9321-0cb400c432b0 |
| pear       | green  | 776093f9-9b36-4ca3-a5fa-6e78483baf69 |
| orange     | orange | d49b6792-eaf8-47ad-8a5b-94451471c1c9 |
| avocado    | green  | da259e58-30c8-4ee6-b01b-e2c46d999478 |
| peach      | yellow | e0fe4ba5-119f-474e-8007-0928c8b715e7 |
+------------+--------+--------------------------------------+

Successfully applied 1 migration to schema "defaultdb" (execution time 00:00.143s)

I’m not entirely happy with the name revised_id And you want to rename it to primary_id.

12. Explanation of column renaming

ALTER TABLE fruits RENAME COLUMN revised_id TO primary_id;
SHOW CREATE TABLE fruits;
Database: jdbc:postgresql://crdb:26257/defaultdb (PostgreSQL 9.5)
Successfully validated 12 migrations (execution time 00:00.054s)
Current version of schema "defaultdb": 11
Migrating schema "defaultdb" to version 12 - Rename column [non-transactional]
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table_name | create_statement                                                                                                                                                                                                                                                       |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| fruits     | CREATE TABLE fruits (
    name STRING NOT NULL,
    color STRING NOT NULL,
    primary_id UUID NOT NULL DEFAULT gen_random_uuid(),
    CONSTRAINT "primary" PRIMARY KEY (primary_id ASC),
    UNIQUE INDEX fruits_name_key (name DESC),
    FAMILY "primary" (name, color, primary_id)
) |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Successfully applied 1 migration to schema "defaultdb" (execution time 00:00.409s)

CockroachDB supports column families for heterogeneous access patterns in a single row. A similar concept exists in regular NoSQL databases. We will add a new column of type JSONB, the common pattern is class A binary or json Payload separated from normal data as one of them may not be accessed regularly. Filtering by column family can make the performance of each query significantly faster.

13. Add a new column to a new CF with JSON

ALTER TABLE fruits ADD COLUMN payload JSONB NULL CREATE IF NOT EXISTS FAMILY secondary;
Database: jdbc:postgresql://crdb:26257/defaultdb (PostgreSQL 9.5)
Successfully validated 13 migrations (execution time 00:00.057s)
Current version of schema "defaultdb": 12
Migrating schema "defaultdb" to version 13 - Add json column new cf [non-transactional]
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table_name | create_statement                                                                                                                                                                                                                                                                                                         |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| fruits     | CREATE TABLE fruits (
    name STRING NOT NULL,
    color STRING NOT NULL,
    primary_id UUID NOT NULL DEFAULT gen_random_uuid(),
    payload JSONB NULL,
    CONSTRAINT "primary" PRIMARY KEY (primary_id ASC),
    UNIQUE INDEX fruits_name_key (name DESC),
    FAMILY "primary" (name, color, primary_id),
    FAMILY secondary (payload)
) |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Successfully applied 1 migration to schema "defaultdb" (execution time 00:00.547s)

Let’s add rows with JSON embedded in them.

14. Load JSON data

INSERT INTO fruits (name, color, payload) VALUES
    ('apricot', 'yellow', '{"name":"apricot", "color":"yellow"}'),
    ('mango', 'orange', '{"name":"mango", "color":"orange"}'),
    ('snake fruit', 'brown', '{"name":"snake fruit", "color":"brown"}'),
    ('mangostin', 'red', '{"name":"mangostin", "color":"red"}'),
    ('jackfruit', 'yellow', '{"name":"jackfruit", "color":"yellow"}'),
    ('durian', 'yellow', '{"name":"durian", "color":"yellow"}');
SELECT * FROM fruits;
Database: jdbc:postgresql://crdb:26257/defaultdb (PostgreSQL 9.5)
Successfully validated 14 migrations (execution time 00:00.060s)
Current version of schema "defaultdb": 13
Migrating schema "defaultdb" to version 14 - Load json [non-transactional]
+-------------+--------+--------------------------------------+-------------------------------------------+
| name        | color  | primary_id                           | payload                                   |
+-------------+--------+--------------------------------------+-------------------------------------------+
| plum        | purple | 0f9eb934-f29d-4aee-a3b7-4f3431421c99 |                                           |
| clementine  | yellow | 1f38f571-027d-450c-8c65-a0675f25b963 |                                           |
| apple       | red    | 4d47207f-d186-43c9-9321-0cb400c432b0 |                                           |
| jackfruit   | yellow | 51289d06-3b4b-47cf-a85d-4dc8739aad9f | {"color": "yellow", "name": "jackfruit"}  |
| mangostin   | red    | 53b661e0-3406-4e7f-a9bd-9c227ad5ab1b | {"color": "red", "name": "mangostin"}     |
| mango       | orange | 54260768-a7c8-49c7-8181-9810c9368f11 | {"color": "orange", "name": "mango"}      |
| pear        | green  | 776093f9-9b36-4ca3-a5fa-6e78483baf69 |                                           |
| durian      | yellow | 7ebf21fc-9c20-4332-970e-19d299bd6ff2 | {"color": "yellow", "name": "durian"}     |
| snake fruit | brown  | 8f68436b-2b34-4fa2-9247-b2b63d2a6eb1 | {"color": "brown", "name": "snake fruit"} |
| apricot     | yellow | be4cc470-64e0-410c-b186-5186a0a17818 | {"color": "yellow", "name": "apricot"}    |
| orange      | orange | d49b6792-eaf8-47ad-8a5b-94451471c1c9 |                                           |
| avocado     | green  | da259e58-30c8-4ee6-b01b-e2c46d999478 |                                           |
| peach       | yellow | e0fe4ba5-119f-474e-8007-0928c8b715e7 |                                           |
+-------------+--------+--------------------------------------+-------------------------------------------+

Successfully applied 1 migration to schema "defaultdb" (execution time 00:00.168s)

Now, if you choose to query the table as is, performance may be significantly slower.

root@:26257/defaultdb> SELECT * FROM fruits WHERE payload @> '{"color": "yellow"}}';
    name    | color  |              primary_id              |                 payload
------------+--------+--------------------------------------+-------------------------------------------
  jackfruit | yellow | 30b823f8-eaa3-4426-8198-5f55286eb07c | {"color": "yellow", "name": "jackfruit"}
  durian    | yellow | 5aba39fa-58cb-4ecc-a066-b45a7f57f561 | {"color": "yellow", "name": "durian"}
  apricot   | yellow | c2598274-d826-44e8-b536-c4a2513eb33b | {"color": "yellow", "name": "apricot"}
(3 rows)

Time: 2.0718ms

Adding an inverted index to a file JSONB field, you will improve the performance of your JSON queries.

CREATE INVERTED INDEX ON fruits(payload)
SHOW CREATE TABLE fruits;
Database: jdbc:postgresql://crdb:26257/defaultdb (PostgreSQL 9.5)
Successfully validated 15 migrations (execution time 00:00.072s)
Current version of schema "defaultdb": 14
Migrating schema "defaultdb" to version 15 - Create inverted index [non-transactional]
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table_name | create_statement                                                                                                                                                                                                                                                                                                                                                       |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| fruits     | CREATE TABLE fruits (
    name STRING NOT NULL,
    color STRING NOT NULL,
    primary_id UUID NOT NULL DEFAULT gen_random_uuid(),
    payload JSONB NULL,
    CONSTRAINT "primary" PRIMARY KEY (primary_id ASC),
    UNIQUE INDEX fruits_name_key (name DESC),
    INVERTED INDEX fruits_payload_idx (payload),
    FAMILY "primary" (name, color, primary_id),
    FAMILY secondary (payload)
) |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Successfully applied 1 migration to schema "defaultdb" (execution time 00:00.540s)

16. Query the table using the index

SELECT * FROM fruits WHERE payload @> '{"color": "yellow"}}';
Database: jdbc:postgresql://crdb:26257/defaultdb (PostgreSQL 9.5)
Successfully validated 16 migrations (execution time 00:00.064s)
Current version of schema "defaultdb": 15
Migrating schema "defaultdb" to version 16 - Query json with inverted index [non-transactional]
+-----------+--------+--------------------------------------+------------------------------------------+
| name      | color  | primary_id                           | payload                                  |
+-----------+--------+--------------------------------------+------------------------------------------+
| jackfruit | yellow | 30b823f8-eaa3-4426-8198-5f55286eb07c | {"color": "yellow", "name": "jackfruit"} |
| durian    | yellow | 5aba39fa-58cb-4ecc-a066-b45a7f57f561 | {"color": "yellow", "name": "durian"}    |
| apricot   | yellow | c2598274-d826-44e8-b536-c4a2513eb33b | {"color": "yellow", "name": "apricot"}   |
+-----------+--------+--------------------------------------+------------------------------------------+

Successfully applied 1 migration to schema "defaultdb" (execution time 00:00.153s)

Note the execution time, 00:00.153s against. 2.0718ms

At this point, the project tree looks like this:

├── cockroach-data
│   └── crdb
├── docker-compose.yml
└── flyway
    ├── conf
    │   └── flyway.conf
    └── sql
        ├── V10__Alter_table_change_pk.sql
        ├── V11__Reload_data.sql
        ├── V12__Rename_column.sql
        ├── V13__Add_json_column_new_cf.sql
        ├── V14__Load_json.sql
        ├── V15__Create_inverted_index.sql
        ├── V16__Query_json_with_inverted_index.sql
        ├── V1__Create_table.sql
        ├── V2__Load_data.sql
        ├── V3__Change_color_and_name_to_not_null.sql
        ├── V4__Change_pk_to_name_and_color.sql
        ├── V5__Drop_unique_constraint_and_id_column.sql
        ├── V6__Load_data.sql
        ├── V7__Add_unique_constraint_on_name_column.sql
        ├── V8__Load_data_respecting_unique.sql
        └── V9__Truncate_table.sql

5 directories, 18 files

and running info On Flyway will result in:

Flyway Community Edition 6.4.4 by Redgate
Database: jdbc:postgresql://crdb:26257/defaultdb (PostgreSQL 9.5)
Schema version: 16

+-----------+---------+--------------------------------------+------+---------------------+---------+
| Category  | Version | Description                          | Type | Installed On        | State   |
+-----------+---------+--------------------------------------+------+---------------------+---------+
| Versioned | 1       | Create table                         | SQL  | 2020-06-29 20:03:09 | Success |
| Versioned | 2       | Load data                            | SQL  | 2020-06-29 20:03:09 | Success |
| Versioned | 3       | Change color and name to not null    | SQL  | 2020-06-29 20:03:10 | Success |
| Versioned | 4       | Change pk to name and color          | SQL  | 2020-06-29 20:03:11 | Success |
| Versioned | 5       | Drop unique constraint and id column | SQL  | 2020-06-29 20:03:11 | Success |
| Versioned | 6       | Load data                            | SQL  | 2020-06-29 20:03:11 | Success |
| Versioned | 7       | Add unique constraint on name column | SQL  | 2020-06-29 20:03:12 | Success |
| Versioned | 8       | Load data respecting unique          | SQL  | 2020-06-29 20:03:12 | Success |
| Versioned | 9       | Truncate table                       | SQL  | 2020-06-29 20:03:12 | Success |
| Versioned | 10      | Alter table change pk                | SQL  | 2020-06-29 20:03:13 | Success |
| Versioned | 11      | Reload data                          | SQL  | 2020-06-29 20:03:13 | Success |
| Versioned | 12      | Rename column                        | SQL  | 2020-06-29 20:03:14 | Success |
| Versioned | 13      | Add json column new cf               | SQL  | 2020-06-29 20:03:14 | Success |
| Versioned | 14      | Load json                            | SQL  | 2020-06-29 20:03:14 | Success |
| Versioned | 15      | Create inverted index                | SQL  | 2020-06-29 20:05:54 | Success |
| Versioned | 16      | Query json with inverted index       | SQL  | 2020-06-29 20:08:49 | Success |
+-----------+---------+--------------------------------------+------+---------------------+---------+

.

Leave a Comment