From e57d9e1d55c4880d3d2bc8d6df7e6cdf54906032 Mon Sep 17 00:00:00 2001 From: Paul Duncan Date: Sun, 23 Jan 2022 10:27:35 -0500 Subject: add content/posts/2022-01-23-pg-trigger-tests.md --- content/posts/2022-01-23-pg-trigger-tests.md | 44 + static/files/posts/pg-trigger-tests/results.svg | 1477 +++++++++++++++++++++++ 2 files changed, 1521 insertions(+) create mode 100644 content/posts/2022-01-23-pg-trigger-tests.md create mode 100644 static/files/posts/pg-trigger-tests/results.svg diff --git a/content/posts/2022-01-23-pg-trigger-tests.md b/content/posts/2022-01-23-pg-trigger-tests.md new file mode 100644 index 0000000..5c58b6f --- /dev/null +++ b/content/posts/2022-01-23-pg-trigger-tests.md @@ -0,0 +1,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." diff --git a/static/files/posts/pg-trigger-tests/results.svg b/static/files/posts/pg-trigger-tests/results.svg new file mode 100644 index 0000000..46853c3 --- /dev/null +++ b/static/files/posts/pg-trigger-tests/results.svg @@ -0,0 +1,1477 @@ + + + + + + + + + 2022-01-23T09:06:16.730923 + image/svg+xml + + + Matplotlib v3.3.4, https://matplotlib.org/ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + -- cgit v1.2.3