In How to Write SQL we saw how to write SQL queries as separate .sql files, and we learnt about using query parameters with the psql syntax for that (:variable, :'variable', and :"identifier").

For writing our database model, the same tooling is all we need. An important aspect of using psql is its capacity to provide immediate feedback, and we can also have that with modeling too.

This article is extracted from my book Mastering PostgreSQL in Application Development. If you like what you read here, you might enjoy a full book of SQL learning material!

How to Write a Database Model

create database sandbox;

Now you have a place where to try things out without disturbing existing application code. If you need to interact with existing SQL objects, it might be better to use a schema rather than a full-blown separate database:

create schema sandbox;
set search_path to sandbox;

In PostgreSQL, each database is an isolated environment. A connection string must pick a target database, and it’s not possible for one database to interact with objects from another one, because catalogs are kept separated. This is great for isolation purposes. If you want to be able to join data in between your sandbox and your application models, use a schema instead.

When trying a new schema, it’s nice to be able to refine it as you go, trying things out. Here’s a simple and effective trick to enable that: write your schema as a SQL script with explicit transaction control, and finish it with your testing queries and a rollback.

In the following example, we iterate over the definition of a schema for a kind of forum application about the news. Articles are written and tagged with a single category, which is selected from a curated list that is maintained by the editors. Users can read the articles, of course, and comment on them. In this MVP, it’s not possible to comment on a comment.

We would like to have a schema and a data set to play with, with some categories, an interesting number of articles and a random number of comments for each article.

Here’s a SQL script that creates the first version of our schema and populates it with random data following the specifications above, which are intentionally pretty loose. Notice how the script is contained within a single transaction and ends with a rollback statement: PostgreSQL even implements transaction for DDL statements.

begin;

create schema if not exists sandbox;

create table sandbox.category
 (
   id    serial primary key,
   name  text not null
 );

insert into sandbox.category(name)
     values ('sport'),('news'),('box office'),('music');

create table sandbox.article
 (
   id         bigserial primary key,
   category   integer references sandbox.category(id),
   title      text not null,
   content    text
 );

create table sandbox.comment
 (
   id         bigserial primary key,
   article    integer references sandbox.article(id),
   content    text
 );

insert into sandbox.article(category, title, content)
     select random(1, 4) as category,
            initcap(sandbox.lorem(5)) as title,
            sandbox.lorem(100) as content
       from generate_series(1, 1000) as t(x);

insert into sandbox.comment(article, content)
     select random(1, 1000) as article,
            sandbox.lorem(150) as content
       from generate_series(1, 50000) as t(x);
            
select article.id, category.name, title
  from      sandbox.article
       join sandbox.category
         on category.id = article.category
 limit 3;

select count(*),
       avg(length(title))::int as avg_title_length,
       avg(length(content))::int as avg_content_length
  from sandbox.article;

   select article.id, article.title, count(*)
     from      sandbox.article
          join sandbox.comment
            on article.id = comment.article
group by article.id
order by count desc
   limit 5;

select category.name,
       count(distinct article.id) as articles,
       count(*) as comments
  from      sandbox.category
       left join sandbox.article on article.category = category.id
       left join sandbox.comment on comment.article = article.id
group by category.name
order by category.name;

rollback;

This SQL script references ad-hoc functions creating a random data set. This time for the book I’ve been using a source of Lorem Ipsum texts and some variations on the random() function. Typical usage of the script would be at the psql prompt thanks to the \i command:

yesql# \i .../path/to/schema.sql
BEGIN
...
CREATE TABLE
INSERT 0 4
CREATE TABLE
CREATE TABLE
INSERT 0 1000
INSERT 0 50000
 id │    name    │                  title                  
════╪════════════╪═════════════════════════════════════════
  1 │ sport      │ Debitis Sed Aperiam Id Ea
  2 │ sport      │ Aspernatur Elit Cumque Sapiente Eiusmod
  3 │ box office │ Tempor Accusamus Quo Molestiae Adipisci
(3 rows)

 count │ avg_title_length │ avg_content_length 
═══════╪══════════════════╪════════════════════
  1000 │               35 │                738
(1 row)

 id  │                    title                    │ count 
═════╪═════════════════════════════════════════════╪═══════
 187 │ Quos Quaerat Ducimus Pariatur Consequatur   │    73
 494 │ Inventore Eligendi Natus Iusto Suscipit     │    73
 746 │ Harum Saepe Hic Tempor Alias                │    70
 223 │ Fugiat Sed Dolorum Expedita Sapiente        │    69
 353 │ Dignissimos Tenetur Magnam Quaerat Suscipit │    69
(5 rows)

    name    │ articles │ comments 
════════════╪══════════╪══════════
 box office │      322 │    16113
 music      │      169 │     8370
 news       │      340 │    17049
 sport      │      169 │     8468
(4 rows)

ROLLBACK

As the script ends with a ROLLBACK command, you can now edit your schema and do it again, at will, without having to first clean up the previous run.

Generating Random Data

In the previous script, you might have noticed calls to functions that don’t exist in the distribution of PostgreSQL, such as random(int, int) or sandbox.lorem(int). Here’s a complete ad-hoc definition for them:

begin;

create schema if not exists sandbox;

drop table if exists sandbox.lorem;

create table sandbox.lorem
 (
   word text
 );

with w(word) as
(
     select regexp_split_to_table('Lorem ipsum dolor sit amet, consectetur
        adipiscing elit, sed do eiusmod tempor incididunt ut labore et
        dolore magna aliqua. Ut enim ad minim veniam, quis nostrud
        exercitation ullamco laboris nisi ut aliquip ex ea commodo
        consequat. Duis aute irure dolor in reprehenderit in voluptate velit
        esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat
        cupidatat non proident, sunt in culpa qui officia deserunt mollit
        anim id est laborum.'
            , '[\s., ]')
      union
     select regexp_split_to_table('Sed ut perspiciatis unde omnis iste natus
        error sit voluptatem accusantium doloremque laudantium, totam rem
        aperiam, eaque ipsa quae ab illo inventore veritatis et quasi
        architecto beatae vitae dicta sunt explicabo. Nemo enim ipsam
        voluptatem quia voluptas sit aspernatur aut odit aut fugit, sed quia
        consequuntur magni dolores eos qui ratione voluptatem sequi
        nesciunt. Neque porro quisquam est, qui dolorem ipsum quia dolor sit
        amet, consectetur, adipisci velit, sed quia non numquam eius modi
        tempora incidunt ut labore et dolore magnam aliquam quaerat
        voluptatem. Ut enim ad minima veniam, quis nostrum exercitationem
        ullam corporis suscipit laboriosam, nisi ut aliquid ex ea commodi
        consequatur? Quis autem vel eum iure reprehenderit qui in ea
        voluptate velit esse quam nihil molestiae consequatur, vel illum qui
        dolorem eum fugiat quo voluptas nulla pariatur?'
            , '[\s., ]')
      union
     select regexp_split_to_table('At vero eos et accusamus et iusto odio
        dignissimos ducimus qui blanditiis praesentium voluptatum deleniti
        atque corrupti quos dolores et quas molestias excepturi sint
        occaecati cupiditate non provident, similique sunt in culpa qui
        officia deserunt mollitia animi, id est laborum et dolorum fuga. Et
        harum quidem rerum facilis est et expedita distinctio. Nam libero
        tempore, cum soluta nobis est eligendi optio cumque nihil impedit
        quo minus id quod maxime placeat facere possimus, omnis voluptas
        assumenda est, omnis dolor repellendus. Temporibus autem quibusdam
        et aut officiis debitis aut rerum necessitatibus saepe eveniet ut et
        voluptates repudiandae sint et molestiae non recusandae. Itaque
        earum rerum hic tenetur a sapiente delectus, ut aut reiciendis
        voluptatibus maiores alias consequatur aut perferendis doloribus
        asperiores repellat.'
            , '[\s., ]')
)
  insert into sandbox.lorem(word)
       select word
         from w
        where word is not null
          and word <> '';

create or replace function random(a int, b int)
  returns int
  volatile
  language sql
as $$
  select a + ((b-a) * random())::int;
$$;

create or replace function sandbox.lorem(len int)
  returns text
  volatile
  language sql
as $$
  with words(w) as (
      select word
       from sandbox.lorem
   order by random()
      limit len
  )
  select string_agg(w, ' ')
    from words;
$$;

commit;

The not-so-random Latin text comes from Lorem Ipsum and is a pretty good base for generating random content. We go even further by separating words from their context and then aggregating them together completely at random in the sandbox.lorem(int) function.

The method we use to get N words at random is known to be rather inefficient given large data sources. If you have this use case to solve with a big enough table, then have a look at selecting random rows from a table article from Andrew Gierth, now a PostgreSQL committer.

Modeling Example

Now that we have some data to play with, we can test some application queries for known user stories in the MVP, like maybe listing the most recent articles per category with the first three comments on each article.

That’s when we realize our previous schema design misses publication timestamps for articles and comments. We need to add this information to our draft model. As it is all a draft with random data, the easiest way around this you already committed the data previously (by editing the script) is to simply drop schema cascade as shown here:

yesql# drop schema sandbox cascade;

NOTICE:  drop cascades to 5 other objects
DETAIL:  drop cascades to table sandbox.lorem
drop cascades to function sandbox.lorem(integer)
drop cascades to table sandbox.category
drop cascades to table sandbox.article
drop cascades to table sandbox.comment
DROP SCHEMA

The next version of our schema then looks like this:

begin;

create schema if not exists sandbox;

create table sandbox.category
 (
   id    serial primary key,
   name  text not null
 );

insert into sandbox.category(name)
     values ('sport'),('news'),('box office'),('music');

create table sandbox.article
 (
   id         bigserial primary key,
   category   integer references sandbox.category(id),
   pubdate    timestamptz,
   title      text not null,
   content    text
 );

create table sandbox.comment
 (
   id         bigserial primary key,
   article    integer references sandbox.article(id),
   pubdate    timestamptz,
   content    text
 );

insert into sandbox.article(category, title, pubdate, content)
     select random(1, 4) as category,
            initcap(sandbox.lorem(5)) as title,
            random( now() - interval '3 months',
                    now() + interval '1 months') as pubdate,
            sandbox.lorem(100) as content
       from generate_series(1, 1000) as t(x);

insert into sandbox.comment(article, pubdate, content)
     select random(1, 1000) as article,
            random( now() - interval '3 months',
                    now() + interval '1 months') as pubdate,
            sandbox.lorem(150) as content
       from generate_series(1, 50000) as t(x);
            
select article.id, category.name, title
  from      sandbox.article
       join sandbox.category
         on category.id = article.category
 limit 3;

select count(*),
       avg(length(title))::int as avg_title_length,
       avg(length(content))::int as avg_content_length
  from sandbox.article;

   select article.id, article.title, count(*)
     from      sandbox.article
          join sandbox.comment
            on article.id = comment.article
group by article.id
order by count desc
   limit 5;

select category.name,
       count(distinct article.id) as articles,
       count(*) as comments
  from      sandbox.category
       left join sandbox.article on article.category = category.id
       left join sandbox.comment on comment.article = article.id
group by category.name
order by category.name;

commit;

To be able to generate random timestamp entries, the script uses another function that’s not provided by default in PostgreSQL, and here’s its definition:

create or replace function random
 (
  a timestamptz,
  b timestamptz
 )
 returns timestamptz
 volatile
 language sql
as $$
  select a
         + random(0, extract(epoch from (b-a))::int)
           * interval '1 sec';
$$;

Querying the Schema: our Minimal Viable Product

Now we can have a go at solving the first query of the product’s MVP, as specified before, on this schema draft version. That should provide a taste of the schema and how well it implements the business rules.

The following query lists the most recent articles per category with the first three comments on each article:

\set comments 3
\set articles 1

  select category.name as category,
         article.pubdate,
         title,
         jsonb_pretty(comments) as comments

    from sandbox.category
         /*
          * Classic implementation of a Top-N query
          * to fetch 3 most recent articles per category
          */
         left join lateral
         (
            select id,
                   title,
                   article.pubdate,
                   jsonb_agg(comment) as comments
              from sandbox.article
                  /*
                   * Classic implementation of a Top-N query
                   * to fetch 3 most recent comments per article
                   */
                  left join lateral
                  (
                      select comment.pubdate,
                             substring(comment.content from 1 for 25) || '…'
                             as content
                        from sandbox.comment
                       where comment.article = article.id
                    order by comment.pubdate desc
                       limit :comments
                  )
                  as comment
                  on true   -- required with a lateral join
              
             where category = category.id

          group by article.id
          order by article.pubdate desc
             limit :articles
         )
         as article
         on true -- required with a lateral join

order by category.name, article.pubdate desc;

The first thing we notice when running this query is the lack of indexing for it. Let’s fix this:

create index on sandbox.article(pubdate);
create index on sandbox.comment(article);
create index on sandbox.comment(pubdate);

Here’s the query result set, with some content removed. The query has been edited for a nice result text which fits in the book pages, using jsonb_pretty() and substring(). When embedding it in application’s code, this extra processing ougth to be removed from the query. Here’s the result, with a single article per category and the three most recent comments per article, as a JSONB document:

─[ RECORD 1 ]───────────────────────────────────────────────────
category │ box office
pubdate  │ 2017-09-30 07:06:49.681844+02
title    │ Tenetur Quis Consectetur Anim Voluptatem
comments │ [                                                    ↵
         │     {                                                ↵
         │         "content": "adipisci minima ducimus r…",     ↵
         │         "pubdate": "2017-09-27T09:43:24.681844+02:00"↵
         │     },                                               ↵
         │     {                                                ↵
         │         "content": "maxime autem modi ex even…",     ↵
         │         "pubdate": "2017-09-26T00:34:51.681844+02:00"↵
         │     },                                               ↵
         │     {                                                ↵
         │         "content": "ullam dolorem velit quasi…",     ↵
         │         "pubdate": "2017-09-25T00:34:57.681844+02:00"↵
         │     }                                                ↵
         │ ]
═[ RECORD 2 ]═══════════════════════════════════════════════════
category │ music
pubdate  │ 2017-09-28 14:51:13.681844+02
title    │ Aliqua Suscipit Beatae A Dolor
...
═[ RECORD 3 ]═══════════════════════════════════════════════════
category │ news
pubdate  │ 2017-09-30 05:05:51.681844+02
title    │ Mollit Omnis Quaerat Do Odit
...
═[ RECORD 4 ]═══════════════════════════════════════════════════
category │ sport
pubdate  │ 2017-09-29 17:08:13.681844+02
title    │ Placeat Eu At Consequuntur Explicabo
...

We get this result in about 500ms to 600ms on a laptop, and the timing is down to about 150ms when the substring(comment.content from 1 for 25) || ‘…’ part is replaced with just comment.content. It’s fair to use it in production, with the proper caching strategy in place, i.e. we expect more article reads than writes.

Our schema is a good first version for answering the MVP:

  • It follows normalization rules.

  • It allows writing the main use case as a single query, and even if the query is on the complex side it runs fast enough with a sample of tens of thousands of articles and fifty thousands of comments.

  • The schema allows an easy implementation of workflows for editing categories, articles, and comments.

This draft schema is a SQL file, so it’s easy to check it into your versioning system, share it with your colleagues and deploy it to development, integration and continuous testing environments.

For visual schema needs, tools are available that connect to a PostgreSQL database and help in designing a proper set of diagrams from the live schema.