In the first post of this series, we explained why you absolutely need to use
jsonb columns in the PostgreSQL tables right now, and mildly chided you if you aren’t already.
In the second post of this series, we demonstrated how to use triggers and constraints to make one read/write table with JSON data and an automatically-maintained read-only table for high-speed joins and to attach the foreign-key constraints to. This provides performance and data integrity but at a speed penalty of insertion speed as well as some on-disk data duplication.
In this post, we show how to create a read/write view that exposes relational data as JSON data (mixed with other JSON data), with the view being a thin layer on top of two tables maintained by triggers. One table holds the data we had before, including some JSON data, but with all of the references missing. The other table is used as an associative table and houses only the references and related metadata, complete with foreign-key constraints in place.
In part 2 we briefly demonstrated a view that partially deconstructs JSON but noted that views are read-only without some additional work. Now we’re going to see what it will take to make a read/write view, with the view showing the data constructed as
jsonb columns, but with the database backing the view across at least two tables.
The goals for this schema is:
- All of the relationships are fully managed by the database itself using traditional foreign-key constraints.
- The data is only in one table, not duplicated as in the previous form.
- All views and tables can safely be read or written.
- There is no additional speed penalty for insertion into the backing-store tables (beyond the normal penalty for constraint checking, index updating, etc.).
- There is only a minor penalty for changing rows through the view due to the triggers deconstructing the JSON and calling the relevant
- There is a minor penalty for reads of the view equal to the SELECT statement that makes up the view with joins and functions to construct the
jsonbcolumns on the fly.
In the previous article we had two tables:
enemy_association. We’ll convert
people into a view, keep the same data stored in
enemy_association as before, and create a new table called
people_raw. This new table will have the same definition as the
people table had before, complete with a
jsonb column that will contain the same JSON data as before except
enemies. The new
people view will gather the data for the
enemies key from
enemy_association and merge it into the
Let’s create the two backing tables:
Note that two inserts are required. In the
enemy_association insert, we had to specify the column list in order for the
enemy_association.id column to get default values. Alternatively, we specified the
Now we want a
people view that returns the
enemies list in the
characteristics column, so let’s make that:
And we can select from the view and get the data we’re after:
Making an Updatable View
We can update
people now by manipulating
enemy_association. However, we’ll need to make the view “editable” so that we interact with the data by manipulating the JSON. We’ll do this by adding an
INSTEAD OF trigger function on the view. Note that we sprinkled optional
RAISE NOTICE lines in the code so the code flow can be tracked.
Here’s what the general format will look like, which is actually very similar to what was in Part 2:
For a view, we need an
INSTEAD OF triger, where on a table we use a
AFTER trigger. This mechanism is how we make the view handle
DELETE like a table.
people will simply delete from
people_raw and we can let the foreign-key constraints cascade (or not, as configured in the constraints) to handle the rest.
That wasn’t so bad.
Before we do an insertion or an update, we need to check the
enemies list is a valid array. We provide more checks, but we’ll keep it simple for now:
Now to do the insertion itself, and we’ll come back for the rest of the update processing. There are two tricky parts to watch out for:
- The insertion of the
people_rawtable has to happen before the
enemies_listis added to, so we don’t violate the foreign keys briefly. (We could defer them for the rest of the session, but that’s messy and presumptive.)
idcolumn might be omitted in the
INSERTcall, which on a normal table
DEFAULTvalue be used. (In this case
DEFAULTis a newly generated UUID.) Because of this optional column, we need to have two
INSERTcalls, one with
idand one without.
Like the update handling in Part 2, the update handling is the most complicated. This is all data manipulation, where we have to remove enemy_association entries that are removed, add entries that are added, and leave the rest alone.
ARRAY[(enemy->>'id')::uuid] <@ enemy_ids part is using the array-contains-array operator
<@ to test if the given
enemy->>'id' (a text value cast to a UUID) is in the
enemy_ids list. Since we only have a single value, we need to put it in an array with the
The full trigger code with all the parts above put together can be found in this GitHub Gist.
To sum up the functionality we have created:
- We can perform CRUD (Create/INSERT, Read/SELECT, Update, or Delete) on the tables people_raw and enemy_association directly, and the view will immediately reflect any changes.
- Insertions support defaulted
idcolumn population, with the default defined in the table definition as a generated UUID.
- References from
people_raware guaranteed by the database using foreign-key constraints.
- Changes that would violate those constraints are either prevented or cascaded to remove those referencing rows, controlled with normal constraint management syntax in PostgreSQL.
- Both tables are fully indexable to get the maximum performance out of PostgreSQL.
- Insertions support defaulted
- We can perform CRUD on the people view and it will manipulate the
enemy_associationtables according to the
characteristics->'enemies'column value, ensuring that written data will read back the same.
- This allows both reading and writing a complete enemies relationship in a single query to the view.
- An example of data-shape checking is provided by ensuring that
characteristics->'enemies'is an array if it exists.
Let’s demonstrate a few simple manipulations:
On top of this foundation, we could add:
- Add a
people_rawto ensure proper handling of an enemies list provided, either by blocking it or stripping it off and updating
enemy_association. Blocking would be cleaner, as read and write would remain symmetric.
- More data-shape checks could be added, such as ensuring that other
characteristicsfollow the correct schema.
- For example: create a third table of allowed keys and their acceptable values, and have the trigger read that table for dynamically controlled data-shape checks. That would allow adding to the JSON “schema” by manipulating a table, preventing the need to actually change the database schema.
Over the last three blog posts, we covered why using JSON in PostgreSQL makes sense and its mechanics. We also covered how to create a changelog with triggers and JSON, making views read/write with triggers, as well as a few other tips and tricks like using
For more help or information about this or any other aspect of your enterprise tech stack and practices, contact us for a free consultation.