DEV Community: Angelico

TIL Postgres: Views & Materialized Views

Angelico — Wed, 18 Nov 2020 05:06:19 +0000

What is a View?

To put it simply, a view is a convenient shortcut to a query. Creating a view does not involve any new tables or data. When ran, the underlying query is executed, returning its results to the user.

Basic Example

Say we have the following tables from a database of a university:

students

id	name	type
1	Arun	undergraduate
2	Zack	graduate
3	Joy	graduate

courses

id	title	code
1	Introduction to Postgres	PG101
2	Authentication Theories	AUTH205
3	Fundamentals of Supabase	SUP412

grades

id	student_id	course_id	result
1	1	1	B+
2	1	3	A+
3	2	2	A
4	3	1	A-
5	3	2	A
6	3	3	B-

Creating a view consisting of all the three tables will look like this:

CREATE VIEW transcripts AS
    SELECT
        students.name,
        students.type,
        courses.title,
        courses.code,
        grades.result
    FROM grades
    LEFT JOIN students where grades.student_id = students.id
    LEFT JOIN courses where grades.course_id = courses.id;

Once done, we can now easily access the underlying query with:

SELECT * FROM transcripts;

For additional parameters or options, refer here.

Why should we use it?

Views are highly useful for reading data based on the following aspects:

Simplicity

As a query become complex and begins to occupy multiple lines, it becomes a hassle to call it. It becomes even more apparent when we run it at regular intervals. In the example above, instead of repeatedly running:

SELECT
    students.name,
    students.type,
    courses.title,
    courses.code,
    grades.result
FROM grades
LEFT JOIN students where grades.student_id = students.id
LEFT JOIN courses where grades.course_id = courses.id;

We can run this instead:

SELECT * FROM transcripts;

Additionally, a view behaves like a typical table. As such, we can safely use it for any subsequent JOINs or even create a view from a query that already involves another view.

Consistency

Along with its simplicity, a view brings along consistency that ensures that the likelihood of mistakes decreases when repeatedly executing a query. With reference to the query above, it could be a part of other queries. One day, we may decide that we want to exclude the course Introduction to Postgres. The above query would become:

SELECT
    students.name,
    students.type,
    courses.title,
    courses.code,
    grades.result
FROM grades
LEFT JOIN students where grades.student_id = students.id
LEFT JOIN courses where grades.course_id = courses.id
WHERE courses.code != 'PG101';

Without a view, we would need to go into every single dependent query to add the new rule. By doing this, there will be an increase in the likelihood of errors and inconsistencies arising from typos and missing out on dependent queries. With views, we would need to just alter the underlying query in the view transcripts. The change will be applied to any other queries using this view.

Logical Organization

With views, we can give our query a name. Doing this is extremely useful in teams working on the same database. Instead of trying to guess what a query is meant to do, having it as a well-named view can easily explain it. For example, by looking at the name of the view transcripts, we can infer that the underlying query could involve the students, courses, and grades tables.

Security

Using views can also restrict the amount and type of data presented to a user. Instead of allowing a user direct access to a set of tables, we provide them a view instead. With it, we can prevent them from reading sensitive columns by not including them in the underlying query.

What is a Materialized View?

A materialized view is a form of view but with the added feature of physically storing its resultant data into storage. Given the same underlying query, in subsequent reads of a materialized view, the time taken to return its results would be much faster than that of the conventional view. And this is because the data is readily available for a materialized view while the typical view only executes the underlying query on the spot.

Basic Example

Using the same set of tables and underlying query as the above, a new materialized view will look like this:

CREATE MATERIALIZED VIEW transcripts AS
    SELECT
        students.name,
        students.type,
        courses.title,
        courses.code,
        grades.result
    FROM grades
    LEFT JOIN students where grades.student_id = students.id
    LEFT JOIN courses where grades.course_id = courses.id;

Afterward, reading the materialized view can be done as such:

SELECT * FROM transcripts;

For additional parameters or options, refer here.

Refreshing

Unfortunately, there is a trade-off - data in materialized views are not always up to date. We would need to refresh it regularly to prevent the data from becoming too stale. To do so:

REFRESH MATERIALIZED VIEW transcripts;

When should we use this over the conventional view?

Materialized views come in handy when execution times for queries or views become unbearable or exceed the service level agreements of a business. These could likely occur in views or queries involving multiple tables and hundreds of thousands of rows. When using such a view, however, there should be tolerance towards data being not up to date. We should schedule refreshes regularly to ensure that data does not become too outdated over time.

It is to note that creating a materialized view is not a solution to inefficient queries. All options to optimize a slow running query should be exhausted before implementing a materialized view.

Conclusion

Postgres views and materialized views are a great way to organize and view results from commonly used queries. Although highly similar to one another, each has its purpose. Views simplify the process of running queries. Materialized views add on to this by speeding up the process of accessing slower running queries at the trade-off of having stale or not up-to-date data.

TIL Postgres is an ongoing series by Supabase that aims to regularly share snippets of information about PostgreSQL and hopefully provide you with something new to learn. 😉

Any new posts or announcements of our future features and freebies will be made here on DEV first. Follow us so that you don't miss out.

TIL Postgres: Setting up WAL-G

Angelico — Tue, 04 Aug 2020 12:45:45 +0000

WAL-G is an open-source continuous archiving tool used to easily set up and recover from physical backups in Postgres. It mainly handles the storage and retrieval of physical backups and WAL archives to and from a chosen cloud storage provider. In this week's edition of TIL Postgres, we will walk you through on how to effortlessly set up WAL-G for your database as well as guide you on what to do if and when disaster strikes.

Prerequisites

For this tutorial, we will be using two instances running Postgres databases on Ubuntu 18.04. One instance will act as your main database, the other is your recovery database. Do note that, if you’re using another operating system some file paths may vary.

Installations

Make sure the below packages are installed in your instances. Alternatively, you can spin up the latest version of Supabase Postgres which would already have everything configured and installed, along with other goodies. It is readily available in either the AWS or Digital Ocean marketplaces and only takes a few minutes to get running.

Postgres 12

A quick installation guide can be found here.

envdir

envdir allows us to run other programs with a modified environment based on the files in the provided directory. This can be installed through the daemontools package:

$ sudo apt-get install -y daemontools

WAL-G

$ wget https://github.com/wal-g/wal-g/releases/download/v0.2.15/wal-g.linux-amd64.tar.gz
$ tar -zxvf wal-g.linux-amd64.tar.gz
$ mv wal-g /usr/local/bin/

AWS credentials and resources

When storing backups, WAL-G has numerous cloud storage provider options for us to choose from. For this tutorial, we will be using AWS. Have the following prepared:

AWS Access & Secret keys.
An S3 bucket.

Setting it up

1. Configure environment variables

The directory /etc/wal-g.d/env is created and contains files that stores environment variables. It would later be used in WAL-G commands via envdir.

$ umask u=rwx,g=rx,o=
$ mkdir -p /etc/wal-g.d/env
$ echo 'secret-key-content' > /etc/wal-g.d/env/AWS_SECRET_ACCESS_KEY
$ echo 'access-key' > /etc/wal-g.d/env/AWS_ACCESS_KEY_ID
$ echo 's3://backup-bucket/project-directory' > /etc/wal-g.d/env/WALG_S3_PREFIX
$ echo 'db password' > /etc/wal-g.d/env/PGPASSWORD
$ chown -R root:postgres /etc/wal-g.d

2. Enable WAL archiving

Here, we enable WAL archiving and instruct Postgres to store the archives in the specified S3 bucket via WAL-G.

$ echo "archive_mode = yes" >> /etc/postgresql/12/main/postgresql.conf
$ echo "archive_command = 'envdir /etc/wal-g.d/env /usr/local/bin/wal-g wal-push %p'" >> /etc/postgresql/12/main/postgresql.conf
$ echo "archive_timeout = 60" >> /etc/postgresql/12/main/postgresql.conf

3. Restart the database

The database is restarted to let the changes in the configuration to take effect.

$ sudo /etc/init.d/postgresql restart

4. Create your first physical backup

$ sudo -su postgres envdir /etc/wal-g.d/env /usr/local/bin/wal-g backup-push /var/lib/postgresql/12/main

At this point, if you were to check the S3 path that you provided, the following two newly created and populated directories would be observed:

From then on, subsequent physical backups would be found in the directory basebackups_005 and any WAL archives would be sent to the directory wal_005.

5. [Optional] Schedule regular physical backups

A CRON job can then be set to schedule physical backups to be performed everyday:

$ echo "0 0 * * * postgres /usr/bin/envdir /etc/wal-g.d/env /usr/local/bin/wal-g backup-push /var/lib/postgresql/12/main" > /etc/cron.d/pg_backup

Here, the instance has been instructed to back up the database at the start of each day at midnight. By physically backing up your instance regularly, overall recovery time could be faster. Restoring from a physical backup from yesterday would lead to fewer WAL archive files to be replayed as compared to restoring from one from a month ago.

Disaster strikes

Something goes wrong with the database or instance. We will now use what available physical backups we have in the S3 bucket to recover and restore all of our data on to a new instance.

1. Configure environment variables

The configuration should be the same as the original instance. For recovery and restoration, we would not need the variable PGPASSWORD.

$ umask u=rwx,g=rx,o=
$ mkdir -p /etc/wal-g.d/env
$ echo 'secret-key-content' > /etc/wal-g.d/env/AWS_SECRET_ACCESS_KEY
$ echo 'access-key' > /etc/wal-g.d/env/AWS_ACCESS_KEY_ID
$ echo 's3://backup-bucket/project-directory' > /etc/wal-g.d/env/WALG_S3_PREFIX
$ chown -R root:postgres /etc/wal-g.d

2. Stop the database

$ sudo /etc/init.d/postgresql stop

3. Switch to the user `postgres`

$ sudo -su postgres

4. Prepare the database for recovery

Set restore_command

Through restore_command, we instruct Postgres to pull all WAL archives from our S3 bucket to use during recovery.

$ echo "restore_command = '/usr/bin/envdir /etc/wal-g.d/env /usr/local/bin/wal-g wal-fetch \"%f\" \"%p\" >> /tmp/wal.log 2>&1'" >> /etc/postgresql/12/main/postgresql.conf

[Optional] Achieve Point in Time Recovery (PITR)

If we want to restore the database only up to a certain point in time (eg. right before the disaster), we can do so by setting both recovery_target_time and recovery_target_action. Do note that the timezone would need to match that of the original instance. This is usually at the UTC (+00) timezone.

$ echo "recovery_target_time = '2020-07-27 01:23:00.000000+00'" >> /etc/postgresql/12/main/postgresql.conf
$ echo "recovery_target_action = 'promote'" >> /etc/postgresql/12/main/postgresql.conf

5. Restore from physical backup

The current data directory is deleted and is replaced with the latest version of the physical backup from the S3 bucket.

$ rm -rf /var/lib/postgresql/12/main
$ envdir /etc/wal-g.d/env /usr/local/bin/wal-g backup-fetch /var/lib/postgresql/12/main LATEST

6. Create a `recovery.signal` file

This file instructs Postgres that the database should undergo recovery mode upon start.

$ touch /var/lib/postgresql/12/main/recovery.signal

7. Log out of `postgres` and start the database

$ exit
$ sudo /etc/init.d/postgresql start

Once Postgres finishes starting up and completes recovery mode, all data or data up to the specified point in time would have been successfully restored on to the new instance. Disaster averted.

TIL Postgres is an ongoing series by Supabase that aims to regularly share snippets of information about PostgreSQL and hopefully provide you with something new to learn. 😉

Any new posts or announcements of our future features and freebies will be made here on DEV first. Follow us so that you don't miss out.

TIL Postgres: Logical vs. Physical Backups

Angelico — Fri, 17 Jul 2020 09:12:30 +0000

As anyone who has been put in charge of a database knows, setting up backups and recovering from them are perhaps essential components of one's skillset. You probably would have already tried one or more of the many tools and ways in Postgres to achieve them, some of which we will cover in future posts. All can be categorized into two types of backups: logical and physical. In this week's edition of TIL Postgres, we will take a quick look at each one and discuss the situations in which they are best used for.

Logical Backups

This form of backup is typically achieved by translating all the data into a set of SQL commands and writing it into a single file. This can then be fed to any database cluster to recreate everything. In your CLI, performing logical backups can be as easy as:

pg_dump db_name > file_name.sql

for a single database, and:

pg_dumpall > file_name.sql

for an entire database cluster. Both the pg_dump and pg_dumpall utilities have their respective additional options for you to choose from and set up your desired logical backup setting. Recovering from them is comparably as simple:

psql -d db_name -f file_name.sql

What is it good for?

Simpler and quicker way of performing backups

As shown above, a single command is enough to perform a logical backup, and another to recover from it. As a novice with databases, this would be an ideal and non-intimidating start in making sure that your database is backed up at all times.

Migration between different major versions of Postgres

If you are planning to migrate to a different major version of Postgres (for eg. from Postgres 11 to Postgres 12), logical backups via pg_dumpall would surely be your tool of choice. This is mainly because internal data storage formats may differ between major versions. This is the basis of physical backups, hence eliminating it as an option. We'll go deeper into migrations and how to perform them in another post.

Backing up a single specific database

With pg_dump, you can constantly just back up the database of your choice without having to think about the rest.

Physical Backups

Physical backups pertain more to the actual set of files or file systems where all your data are stored. Performing a backup can just involve taking a snapshot of all the files involved by making a copy of them and storing it somewhere safe.

What is it good for?

More ideal for larger databases

As your database grows to the size of a few gigabytes, backing it up through physical backups is more ideal than through logical backups. As explained here, over time, performing logical backups in large databases could lead to degraded performance for other queries. Given the long run time as well to successfully perform a logical backup on a large database, errors have a higher chance of occurring, making the eventual backup unusable.

Achieving Point in Time Recovery

Another form of physical backup called Write Ahead Log (WAL) files can be used together with a backed-up file system to recover a database up to any chosen point in time. When disaster strikes, this would be one of the best options in recreating a database up to the point right before the unfortunate happens. This greatly minimizes Recovery Point Objective (RPO) along the way. Even better, a lot of tools such as WAL-G are readily available to simplify the steps involved in setting everything up.

Conclusion

All in all, logical and physical backups are generated differently from one another. Neither has an advantage over the other. Depending on your needs, each brings unique uses to the table:

Logical	Physical
Simpler way of getting started with backups.	Better way of handling backups for larger database clusters.
Using it to migrate between different major versions of Postgres.	Using it for Point in Time Recovery.
Having the option to back up a single database.

TIL Postgres is an ongoing series by Supabase that aims to regularly share snippets of information about PostgreSQL and hopefully provide you with something new to learn. 😉

Any new posts or announcements of our future features and freebies will be made here on DEV first. Follow us so that you don't miss out.

TIL Postgres: Template Databases

Angelico — Thu, 09 Jul 2020 13:09:14 +0000

Whenever you create a new database in Postgres, you are actually basing it off an already present database in your cluster. This database, template1, and another, called template0, are standard system databases that exist in every newly created database cluster. Don't believe me? Why not quickly spin up a database and see it for yourself with this query:

SELECT * FROM pg_database;

In this post, we'll explore these template databases and see how we can make full use of their potential. We'll even look into creating a template database of our own.

template1

By default, running:

CREATE DATABASE new_db_name;

simply copies everything from the database template1. We can modify this template database in any way: add a table, insert some data, create new extensions, or install procedural languages. Any of these actions would be propagated to subsequently created databases.

This, however, is not advisable. Removing any one of these modifications would need you to manually uninstall or drop these changes from template1. You do have the option to drop and recreate the entire template1 database altogether. This unfortunately comes at the risk of committing a mistake along the way, effectively breaking CREATE DATABASE. It would be better to leave template1 alone and create a template database of your own.

Custom Template Databases

To set an existing database as a template database:

ALTER DATABASE template_db_name WITH is_template TRUE;

Doing this allows any user or role with the CREATEDB privilege to utilize it as a template. If not, only superusers or owners of the database would be allowed to do so.

To create a new database with this template:

CREATE DATABASE new_db_name TEMPLATE template_db_name;

Advantages

With this, you can now have customized templates without the need to worry about polluting template1.
You can safely drop the entire custom template database without the risk of breaking CREATE DATABASE.
If you wish, you can create multiple template databases for various use cases.

Limitations

To properly create a database from a custom template database, there should be no other connections present. CREATE DATABASE immediately fails if any connections exist at the start of the query.
As such, if you are looking to replicate a database while maintaining your connections (eg. a production database), it would be more ideal to use the Postgres utility pg_dump.

template0

template0 contains the same data as template1. We could think of this template database as a fallback if anything irreversible happens to template1. As such, this template database should never be modified in any way as soon as the database cluster has been initialized. To create a database with template0 as the template database:

CREATE DATABASE new_db_name TEMPLATE template0;

Applications

If anything goes wrong with template1, It can be dropped and recreated with template0 as the template database.
We can also create a clean database that does not contain any modifications present in template1. This would be useful when restoring from pg_dump. Any conflicts brought about by modifications not present in the dump are eliminated.
template0 can be used to specify new encodings. As pointed out in this article, creating a new database with template1 and new encodings would result in an error.

-- Will succeed
CREATE DATABASE new_db_name TEMPLATE template0 ENCODING 'SQL_ASCII';

-- Will return an error
CREATE DATABASE new_db_name ENCODING 'SQL_ASCII';

Conclusion

To quickly sum things up, we found out that new databases are, by default, created from a template database called template1. template1 can be modified in any way we please and the changes would be present in any database created afterward. We can also create custom template databases and base new databases from them instead. If things go awry, template0 is always there to help.

TIL Postgres is an ongoing series by Supabase that aims to regularly share snippets of information about PostgreSQL and hopefully provide you with something new to learn. 😉

Any new posts or announcements of our future features and freebies will be made here on DEV first. Follow us so that you don't miss out.

DEV Community: Angelico

TIL Postgres: Views & Materialized Views

What is a View?

Basic Example

students

courses

grades

Why should we use it?

Simplicity

Consistency

Logical Organization

Security

What is a Materialized View?

Basic Example

Refreshing

When should we use this over the conventional view?

Conclusion

TIL Postgres: Setting up WAL-G

Prerequisites

Installations

Postgres 12

envdir

WAL-G

AWS credentials and resources

Setting it up

1. Configure environment variables

2. Enable WAL archiving

3. Restart the database

4. Create your first physical backup

5. [Optional] Schedule regular physical backups

Disaster strikes

1. Configure environment variables

2. Stop the database

3. Switch to the user postgres

4. Prepare the database for recovery

Set restore_command

[Optional] Achieve Point in Time Recovery (PITR)

5. Restore from physical backup

6. Create a recovery.signal file

7. Log out of postgres and start the database

TIL Postgres: Logical vs. Physical Backups

Logical Backups

What is it good for?

Simpler and quicker way of performing backups

Migration between different major versions of Postgres

Backing up a single specific database

Physical Backups

What is it good for?

More ideal for larger databases

Achieving Point in Time Recovery

Conclusion

TIL Postgres: Template Databases

template1

Custom Template Databases

Advantages

Limitations

template0

Applications

Conclusion

3. Switch to the user `postgres`

6. Create a `recovery.signal` file

7. Log out of `postgres` and start the database