Speeding up Postgres Restores Part 2
Tags: devops, softwaredevelopment, technology, tech
Published: 2018-01-16 17:55:01

---
title: "Speeding up Postgres Restores Part 2"
published: true
tags: devops,software-development,technology,tech
canonical_url: https://procrastinatingdev.com/speeding-up-postgres-restores-part-2/
---

#### Because cutting the time to restore in half just wasn’t&nbsp;enough

![](https://cdn-images-1.medium.com/max/1024/1*Cf4I9LTIdGTtyFTlqCFZKw.jpeg)<figcaption>Apparently there’s a lot of pugs in blankets</figcaption>

In [Part 1 of Speeding up Postgres Restores](https://procrastinatingdev.com/speeding-up-postgres-restores/) I talked about how we improved the time it took to restore our local environments. Initially we started out naively pg\_dump’ing (is this a word?), gzipping, unzipping, and then piping the output using `psql < file.sql`. This took over 30 minutes to do a full restore. In the end we used [Postgres’ custom format](https://www.postgresql.org/docs/current/static/app-pgdump.html) and used the job’s argument to speed up the restore to only 16 minutes.

In this article I’m going to outline how we reduced the file size of our backup that in turn sped up our restores even&nbsp;further.

#### Investigation into Size of Backup

When I first wrote about reducing the size of our backups the compressed backup size was sitting around 2GB (30GB uncompressed). Over time our database almost doubled (3.7GB compressed, 68GB uncompressed). This meant that not only was it taking significantly more time to restore, it was also taking double the time to transfer the file&nbsp;across.

With the knowledge that the backup had doubled in size I started trying to figure out why and where the data was growing the&nbsp;most.

The first thing I did was actually find out what the uncompressed size of the database&nbsp;was.

```
# SELECT pg_size_pretty(pg_database_size('dbname'));

pg_size_pretty
----------------
 68 GB
(1 row)
```

Next I decided to see if I could dig into the sizes of the individual tables to see if there were any big culprits.

```
# SELECT table_name, pg_relation_size(table_name), pg_size_pretty(pg_relation_size(table_name))
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER by 2 DESC;

table_name | pg_relation_size | pg_size_pretty
-------------------+------------------+---------------
 logging | 19740196864 | 18 GB
 reversions | 15719358464 | 15 GB
 webhook_logging | 8780668928 | 8374 MB
 ... | 1994645504 | 1902 MB
 ... | 371900416 | 355 MB
 ... | 304226304 | 290 MB
```

As you can see the top 3 tables, which make up over 60% of the entire database, are all related to history or logging, both of which generally aren’t necessary when restoring your dev environment. When you include the indexes to those tables (17GB, 1GB, 1.5GB respectively) these tables make up 89% of the database. With this information in hand I decided to move on from discovery (89% reduction is good enough for me) and see if I could exclude these tables from our dev&nbsp;backups.

#### Minimizing the&nbsp;Backup

Whenever I’m approaching a problem I like to start by reading the project documentation. PostgreSQL’s docs are fantastic and after a few minutes of reading through [pg\_dump's](https://www.postgresql.org/docs/current/static/app-pgdump.html) documentation I found exactly what I&nbsp;needed.

```
pg_dump dbname -Fc \
   --exclude-table-data 'logging*' \
   --exclude-table-data 'reversions*' \
   --exclude-table-data 'webhooks_logging*' > postgres.dev.sql
```

_`*` is used as a wildcard to remove the indexes for these tables as well._

By specifying the `--exclude-table-data` parameter I was able to reduce our database size from 3.7GB compressed (68GB uncompressed) to GB compressed 0.7GB (5.4GB compressed).

As you can see below the results are pretty great. It sped&nbsp;up

**Previously:**

```
$ pg_restore -d db -j 8 dumpfc.gz

real 16m49.539s
user 1m1.344s
sys 0m39.522s
```

**Now:**

```
$ pg_restore -d db -j 8 devfc.gz

real 5m38.156s
user 0m24.574s
sys 0m13.325s
```

As you can see, removing those tables removed 89% from the total size and sped up the overall restore time by 66%! If you remember back to part 1 we started off with an initial restore time of 32.5 minutes. This means we’ve managed to improve the restore time by over 26.9 minutes or&nbsp;87%.

In the end our active restore time got cut from 16 minutes to 5 minutes. **This saves us an additional 57 hours of restore time a year** (6 devs, 52 restores a year, 11 minutes). In total we’ve removed 130 hours worth of time waiting for restores to&nbsp;happen.

#### Final Tips and&nbsp;Thoughts

Going back to the [PostgreSQL docs](https://www.postgresql.org/docs/current/static/populate.html) there’s a number of things we could do to potentially make restoring faster. Things such as using the `-j` parameter on `pg_dump` to make backing up our database quicker (only available on PostgreSQL 9.3+). Disabling `autocommit`, increasing the `maintenance_work_mem` value to be much larger or setting the `max_wal_size` value to be larger as well.

At this point though I’m happy with the overall time it takes to restore our local environments.

* * *


_This post was originally published on [procrastinatingdev.com](https://procrastinatingdev.com/speeding-up-postgres-restores-part-2/)_