aboutsummaryrefslogtreecommitdiff
path: root/content/posts/2022-01-23-pg-trigger-tests.md
blob: 5c58b6fc08dd69e3173092f79c94a7390813e7ec (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
---
slug: pg-trigger-tests
title: "Postgres Trigger Tests"
date: "2022-01-23T10:01:49-04:00"
---
We already know statement-level `INSERT` [triggers][tr] are faster than
row-level [triggers][tr] in [Postgres][].

Out of curiosity I decided to time row-level and statement-level
`INSERT` triggers with a variety of row counts.

Here are the results:

{{< figure
  src="/files/posts/pg-trigger-tests/results.svg"
  class=image
  caption="Row Count vs Query Time"
>}}

The takeaway here is that if speed is a concern, then you should prefer
statement-level [triggers][tr] to row-level [triggers][tr].

The scripts used to create the test database, run the tests, and
generate the results are available in the [companion GitHub
repository][repo].

[docker]: https://docker.com/
  "Docker container orchestrator."
[ruby]: https://ruby-lang.org/
  "Ruby programming language."
[python]: https://python.org/
  "Python programming language."
[matplotlib]: https://matplotlib.org/
  "Matplotlib plotting library."
[tr]: https://www.postgresql.org/docs/current/sql-createtrigger.html
  "Postgres CREATE TRIGGER statement."
[csv]: https://en.wikipedia.org/wiki/Comma-separated_values
  "Comma-separated values"
[svg]: https://en.wikipedia.org/wiki/Scalable_Vector_Graphics
  "Scalable Vector Graphics"
[postgres]: https://www.postgresql.org/
  "PostgreSQL relational database."
[repo]: https://github.com/pablotron/pg-trigger-tests
  "Companion GitHub repository."