loading . . . What I Wish Someone Told Me About Postgres <p>I’ve been working professionally for the better part of a decade on web apps and, in that time, I’ve had to learn how to use a lot of different systems and tools.
During that education, I found that the official documentation typically proved to be the most helpful.</p>
<p>Except…Postgres.
It’s not because the official docs aren’t stellar (they are!)–they’re just <em>massive</em>.
For the current version (17 at the time of writing), if <a href="https://www.postgresql.org/files/documentation/pdf/17/postgresql-17-US.pdf">printed as a standard PDF on US letter-sized paper</a>, it’s 3,200 pages long.<sup id="fnref:a4_correction" role="doc-noteref"><a class="footnote" href="https://challahscript.com/what_i_wish_someone_told_me_about_postgres#fn:a4_correction" rel="footnote">1</a></sup>
It’s not something any junior engineer can just sit down and read start to finish.</p>
<p>So I want to try to catalog the bits that I wish someone had just told me before working with a Postgres database.
Hopefully, this makes things easier for the next person going on a journey similar to mine.</p>
<p>Note that many of these things may also apply to other SQL database management systems (DBMSs) or other databases more generally,
but I’m not as familiar with others so I’m not sure what does and does not apply.</p>
<h2 id="normalize-your-data-unless-you-have-a-good-reason-not-to">Normalize your data unless you have a good reason not to</h2>
<p>Database normalization is the process of removing duplicate or redundant data from a <a href="https://en.wikipedia.org/wiki/Database_schema">database schema</a>.
For example, say you have a site where users can upload documents and users can subscribe to email notifications when folks view their documents.
If you have a <code class="language-plaintext highlighter-rouge">documents</code> table, you shouldn’t have a <code class="language-plaintext highlighter-rouge">user_email</code> column on said table:
When a user wants to change their email, you shouldn’t have to update the hundreds of rows for all the documents they’ve uploaded.
Instead, you can have each row in <code class="language-plaintext highlighter-rouge">documents</code> represent a row in another table (e.g. <code class="language-plaintext highlighter-rouge">users</code>) with a foreign key (e.g. <code class="language-plaintext highlighter-rouge">user_id</code>).</p>
<p>If you search for “database normalization” online, you’ll find a bunch of results about “1st normal form” and the like.
You definitely don’t need to know what each of those <a href="https://en.wikipedia.org/wiki/Database_normalization#Normal_forms">“normal forms”</a> are,
but <a href="https://en.wikipedia.org/wiki/Database_normalization#Example_of_a_step-by-step_normalization">the general process</a> is good to be familiar with as it can lead you to a more maintainable database schema.</p>
<p>There are occasions when having redundant data (that is, <a href="https://en.wikipedia.org/wiki/Denormalization">denormalizing your schema</a>) can make sense:
this is typically to make reading certain data faster so it doesn’t have to be recomputed every time it’s requested.
E.g. you have an application that manages employee shifts at a bakery.
A user might want to see how many hours they’ve worked so far this year.
To calculate that, you would need to do something like determine the duration of each of their shifts and then sum all of them together.
It might make sense to instead calculate this amount on a regular interval or whenever the number of hours worked changes.
This data can be denormalized within the Postgres database or outside of it (e.g. in a caching layer like Redis).
Note that there is almost always a cost to denormalized data,
whether that’s possible data inconsistency or increased write complexity.</p>
<h2 id="follow-all-the-advice-from-the-folks-that-make-postgres">Follow all the advice from the folks that make Postgres</h2>
<p>There’s a big list aptly titled <a href="https://wiki.postgresql.org/wiki/Don%27t_Do_This">“Don’t do this”</a> on the official Postgres wiki.
You may not understand all of the things listed.
That’s fine!
If you don’t understand, then you probably won’t make the mistake.
Some suggestions worth highlighting:</p>
<ol>
<li><a href="https://wiki.postgresql.org/wiki/Don%27t_Do_This#Text_storage">Just use the <code class="language-plaintext highlighter-rouge">text</code> type for all text storage</a></li>
<li><a href="https://wiki.postgresql.org/wiki/Don%27t_Do_This#Date.2FTime_storage">Just use the <code class="language-plaintext highlighter-rouge">timestampz</code>/<code class="language-plaintext highlighter-rouge">time with time zone</code> type for all timestamp storage</a></li>
<li><a href="https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_upper_case_table_or_column_names">Name your tables in snake_case</a></li>
</ol>
<h2 id="note-some-general-sql-eccentricities">Note some general SQL eccentricities</h2>
<h3 id="saving-your-pinkies-you-dont-have-to-write-sql-in-all-caps">Saving your pinkies: you don’t have to write SQL in all caps</h3>
<p>In most documentation and tutorials, you’ll see SQL written like so:</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">my_table</span> <span class="k">WHERE</span> <span class="n">x</span> <span class="o">=</span> <span class="mi">1</span> <span class="k">AND</span> <span class="n">y</span> <span class="o">></span> <span class="mi">2</span> <span class="k">LIMIT</span> <span class="mi">10</span><span class="p">;</span>
</code></pre></div></div>
<p>The case for SQL keywords does not matter.
That means the snippet above is the same as this:</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">select</span> <span class="o">*</span> <span class="k">from</span> <span class="n">my_table</span> <span class="k">where</span> <span class="n">x</span> <span class="o">=</span> <span class="mi">1</span> <span class="k">and</span> <span class="n">y</span> <span class="o">></span> <span class="mi">2</span> <span class="k">limit</span> <span class="mi">10</span><span class="p">;</span>
</code></pre></div></div>
<p>or this:</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">SELECT</span> <span class="o">*</span> <span class="k">from</span> <span class="n">my_table</span> <span class="k">WHERE</span> <span class="n">x</span> <span class="o">=</span> <span class="mi">1</span> <span class="k">and</span> <span class="n">y</span> <span class="o">></span> <span class="mi">2</span> <span class="k">LIMIT</span> <span class="mi">10</span><span class="p">;</span>
</code></pre></div></div>
<p>As far as I know, this is not specific to Postgres.
Your pinkies will thank you.</p>
<h3 id="null-is-weird">
<code class="language-plaintext highlighter-rouge">NULL</code> is weird</h3>
<p>You’re probably familiar with <code class="language-plaintext highlighter-rouge">null</code> or <code class="language-plaintext highlighter-rouge">nil</code> values from other programming languages.
SQL’s <code class="language-plaintext highlighter-rouge">NULL</code> is not like that.
<code class="language-plaintext highlighter-rouge">NULL</code> is really more accurately an “unknown”.
E.g. <code class="language-plaintext highlighter-rouge">NULL = NULL</code> returns <code class="language-plaintext highlighter-rouge">NULL</code> (because it’s unknown if one unknown equals another unknown!)
This is true for almost any operator, not just <code class="language-plaintext highlighter-rouge">=</code>(we’ll go through some of the exceptions in a moment):
if one side of the comparison is <code class="language-plaintext highlighter-rouge">NULL</code>, the result will be <code class="language-plaintext highlighter-rouge">NULL</code>.</p>
<p>There are a few operators that you can use to compare against <code class="language-plaintext highlighter-rouge">NULL</code> which don’t result in <code class="language-plaintext highlighter-rouge">NULL</code>:</p>
<table>
<thead>
<tr>
<th>Operation</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<tr>
<td><code class="language-plaintext highlighter-rouge">x IS NULL</code></td>
<td>returns <code class="language-plaintext highlighter-rouge">true</code> if <code class="language-plaintext highlighter-rouge">x</code> evaluates to <code class="language-plaintext highlighter-rouge">NULL</code>, <code class="language-plaintext highlighter-rouge">false</code> otherwise</td>
</tr>
<tr>
<td><code class="language-plaintext highlighter-rouge">x IS NOT NULL</code></td>
<td>returns <code class="language-plaintext highlighter-rouge">true</code> if <code class="language-plaintext highlighter-rouge">x</code> does not evaluate to <code class="language-plaintext highlighter-rouge">NULL</code>, <code class="language-plaintext highlighter-rouge">false</code> otherwise</td>
</tr>
<tr>
<td><code class="language-plaintext highlighter-rouge">x IS NOT DISTINCT FROM y</code></td>
<td>the same as <code class="language-plaintext highlighter-rouge">x = y</code> but <code class="language-plaintext highlighter-rouge">NULL</code> is treated as a normal value</td>
</tr>
<tr>
<td><code class="language-plaintext highlighter-rouge">x IS DISTINCT FROM y</code></td>
<td>the same as <code class="language-plaintext highlighter-rouge">x != y</code>/<code class="language-plaintext highlighter-rouge">x <> y</code> but <code class="language-plaintext highlighter-rouge">NULL</code> is treated as a normal value</td>
</tr>
</tbody>
</table>
<p><code class="language-plaintext highlighter-rouge">WHERE</code> clauses only match if the condition evaluates to <code class="language-plaintext highlighter-rouge">true</code>.
This means that a query like <code class="language-plaintext highlighter-rouge">SELECT * FROM users WHERE title != 'manager'</code> won’t return rows where <code class="language-plaintext highlighter-rouge">title</code> is <code class="language-plaintext highlighter-rouge">NULL</code> because <code class="language-plaintext highlighter-rouge">NULL != 'manager'</code> is <code class="language-plaintext highlighter-rouge">NULL</code>.</p>
<p>Another useful function when attempting to wrangle <code class="language-plaintext highlighter-rouge">NULL</code> is <code class="language-plaintext highlighter-rouge">COALESCE</code>:
<code class="language-plaintext highlighter-rouge">COALESCE</code> will take any number of arguments and return the first one that is not <code class="language-plaintext highlighter-rouge">NULL</code>:</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="n">COALESCE</span><span class="p">(</span><span class="k">NULL</span><span class="p">,</span> <span class="mi">5</span><span class="p">,</span> <span class="mi">10</span><span class="p">)</span> <span class="o">=</span> <span class="mi">5</span>
<span class="n">COALESCE</span><span class="p">(</span><span class="mi">2</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="mi">9</span><span class="p">)</span> <span class="o">=</span> <span class="mi">2</span>
<span class="n">COALESCE</span><span class="p">(</span><span class="k">NULL</span><span class="p">,</span> <span class="k">NULL</span><span class="p">)</span> <span class="k">IS</span> <span class="k">NULL</span>
</code></pre></div></div>
<h2 id="you-can-make-psql-more-useful">You can make <code class="language-plaintext highlighter-rouge">psql</code> more useful</h2>
<h3 id="fix-your-unreadable-output">Fix your unreadable output</h3>
<p>Have you ever done a query on a table with many columns or long values in those columns and the output is basically unreadable?
It’s probably because you don’t have the pager enabled.
A <a href="https://en.wikipedia.org/wiki/Terminal_pager">terminal pager</a> allows you to view a file (or table, in the case of <code class="language-plaintext highlighter-rouge">psql</code>) of text by scrolling a viewport around some larger canvas.
Without a pager, it just dumps the text in your terminal, wrapping to a new line wherever it runs out of space.</p>
<p><code class="language-plaintext highlighter-rouge">less</code> is a decent pager available on any Unix-like system.
You can set it as your pager by setting the environment variable in your <code class="language-plaintext highlighter-rouge">~/.bashrc</code>/<code class="language-plaintext highlighter-rouge">~/.zshrc</code>/etc.:</p>
<div class="language-sh highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="c"># Use the `-S` option so it truncates long lines for scrolling instead of wrapping them</span>
<span class="nb">export </span><span class="nv">PAGER</span><span class="o">=</span><span class="s1">'less -S'</span>
</code></pre></div></div>
<p>Sometimes, even viewing things as a properly formatted table isn’t particularly useful, especially with tables with <em>many</em> columns.
For this, you can switch to “expanded” mode with <code class="language-plaintext highlighter-rouge">\pset expanded</code> (or the shortcut <code class="language-plaintext highlighter-rouge">\x</code>) in your <code class="language-plaintext highlighter-rouge">psql</code> session.
If you want to have this as the default, you can add a file called <code class="language-plaintext highlighter-rouge">.psqlrc</code> to your home directory (i.e. <code class="language-plaintext highlighter-rouge">~/.psqlrc</code>) and add <code class="language-plaintext highlighter-rouge">\x</code> to it.
Any time you start up a <code class="language-plaintext highlighter-rouge">psql</code> session, it’ll run all the commands in that file first.</p>
<h3 id="clarify-ambiguous-nulls">Clarify ambiguous nulls</h3>
<p>It’s <em>very</em> important to know when a value is <code class="language-plaintext highlighter-rouge">NULL</code> in an output, but the default settings don’t make that particularly clear.
You can give <code class="language-plaintext highlighter-rouge">psql</code> a string to output when it’s referring to <code class="language-plaintext highlighter-rouge">NULL</code>.
I have it set to <code class="language-plaintext highlighter-rouge">[NULL]</code> by running</p>
<pre><code class="language-psql">\pset null '[NULL]'
</code></pre>
<p>Any Unicode string is fine!
It’s a bit past spooky season, but you can be like my friend <a href="https://stevenharman.net">Steven Harman</a> and set it to “👻”.</p>
<p>Once again, if you want to have this as the default, you can add a file called <code class="language-plaintext highlighter-rouge">.psqlrc</code> to your home directory (i.e. <code class="language-plaintext highlighter-rouge">~/.psqlrc</code>) and add <code class="language-plaintext highlighter-rouge">\pset null '[NULL]'</code> to it.
Any time you start up a <code class="language-plaintext highlighter-rouge">psql</code> session, it’ll run all the commands in that file first.</p>
<h3 id="use-auto-completion">Use auto-completion</h3>
<p><code class="language-plaintext highlighter-rouge">psql</code>, like many interactive consoles, will allow for auto-completion.
It helps that SQL is a fairly rigid and fairly structured language.
You can just start typing most keywords or table names and hit <kbd>Tab</kbd> and let <code class="language-plaintext highlighter-rouge">psql</code> fill out the rest:</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="c1">-- start typing "SEL"</span>
<span class="n">SEL</span>
<span class="c1">-- ^ hit `Tab`</span>
<span class="k">SELECT</span>
</code></pre></div></div>
<h3 id="lean-on-backslash-shortcuts">Lean on backslash shortcuts</h3>
<p>There are a whole bunch of useful shortcut commands in <code class="language-plaintext highlighter-rouge">psql</code> for looking up stuff, command line editing, and more.</p>
<table>
<thead>
<tr>
<th>Command</th>
<th>What it does</th>
</tr>
</thead>
<tbody>
<tr>
<td><code class="language-plaintext highlighter-rouge">\?</code></td>
<td>List all of the shortcuts</td>
</tr>
<tr>
<td><code class="language-plaintext highlighter-rouge">\d</code></td>
<td>Shows list of relations (tables and sequences) as well as said relation’s owner</td>
</tr>
<tr>
<td><code class="language-plaintext highlighter-rouge">\d+</code></td>
<td>Same as <code class="language-plaintext highlighter-rouge">\d</code> but also includes the size and some other metadata</td>
</tr>
<tr>
<td><code class="language-plaintext highlighter-rouge">\d table_name</code></td>
<td>Shows the schema of a table (list of columns, including said column’s type, nullability, and default) as well as any indexes or foreign key constraints on said table</td>
</tr>
<tr>
<td><code class="language-plaintext highlighter-rouge">\e</code></td>
<td>Opens your default editor (set as the <code class="language-plaintext highlighter-rouge">$EDITOR</code> environment variable) to edit your query there</td>
</tr>
<tr>
<td><code class="language-plaintext highlighter-rouge">\h SQL_KEYWORD</code></td>
<td>Get syntax and link to docs for <code class="language-plaintext highlighter-rouge">SQL_KEYWORD</code>
</td>
</tr>
</tbody>
</table>
<p>There are a ton of these and the table above only scratches the surface.</p>
<h3 id="copy-to-a-csv">Copy to a CSV</h3>
<p>Sometimes, you just want to share the output of a command with someone who wants to put it in Excel or something.
In Postgres, it’s actually really easy to copy the output of any query to a CSV on your local machine<sup id="fnref:copy_permissions" role="doc-noteref"><a class="footnote" href="https://challahscript.com/what_i_wish_someone_told_me_about_postgres#fn:copy_permissions" rel="footnote">2</a></sup>:</p>
<pre><code class="language-psql">\copy (select * from some_table) to 'my_file.csv' CSV
</code></pre>
<p>If you want it to include an initial line with all the column names, you can add the <code class="language-plaintext highlighter-rouge">HEADER</code> option:</p>
<pre><code class="language-psql">\copy (select * from some_table) to 'my_file.csv' CSV HEADER
</code></pre>
<p>For way more information (including how to get it to do the reverse: insert rows from data in a CSV!), check out <a href="https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS-COPY">the docs on this command</a>.</p>
<h3 id="use-column-shorthands-and-aliases">Use column shorthands and aliases</h3>
<p>When doing a <code class="language-plaintext highlighter-rouge">SELECT</code> statement in <code class="language-plaintext highlighter-rouge">psql</code>, you can re-title each of the output columns to whatever you want (an “alias”) with the <code class="language-plaintext highlighter-rouge">AS</code> keyword:</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">SELECT</span> <span class="n">vendor</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="n">number_of_backpacks</span> <span class="k">FROM</span> <span class="n">backpacks</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">vendor</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">number_of_backpacks</span> <span class="k">DESC</span><span class="p">;</span>
</code></pre></div></div>
<p>This will also rename the column in the output.</p>
<p>What’s more is that <code class="language-plaintext highlighter-rouge">GROUP BY</code> and <code class="language-plaintext highlighter-rouge">ORDER BY</code> have their own nice shorthand as well:
you can reference output columns by the number they appear after <code class="language-plaintext highlighter-rouge">SELECT</code>.
Therefore, you can write the previous query as</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">SELECT</span> <span class="n">vendor</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="n">number_of_backpacks</span> <span class="k">FROM</span> <span class="n">backpacks</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="mi">1</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="mi">2</span> <span class="k">DESC</span><span class="p">;</span>
</code></pre></div></div>
<p>While useful, don’t put this in any queries that you ship to production–your future self will thank you!</p>
<h2 id="its-possible-that-adding-an-index-will-do-nothing-particularly-if-its-misconfigured">It’s possible that adding an index will do nothing (particularly if it’s misconfigured)</h2>
<h3 id="what-is-an-index">What is an index?</h3>
<p>An index is a data structure intended to help with looking up data–giving Postgres the responsibility of maintaining a “shortcut directory” to a table’s rows by various fields.
By far the most common kind is a <a href="https://www.baeldung.com/cs/b-tree-data-structure">B-tree</a> index, which is a kind of search tree that work for both exact equality conditions (e.g. <code class="language-plaintext highlighter-rouge">WHERE a = 3</code>) as well as range conditions (e.g. <code class="language-plaintext highlighter-rouge">WHERE a > 5</code>).</p>
<p>But you can’t tell Postgres to use a specific index.
It needs to predict (using statistics it maintains for each table) that it’ll be faster than just reading the table from top to bottom to find the relevant data
(known as a “sequential scan” or “seq. scan”–pronounced “seek scan”–for short).
You can see how Postgres is planning on executing a query by adding <code class="language-plaintext highlighter-rouge">EXPLAIN</code> before your <code class="language-plaintext highlighter-rouge">SELECT ... FROM ...</code>.
This will give you a “query plan”: a plan for how Postgres is going to find the data and its estimate for how much work each task will take.
There are many good guides for reading the output of these query plans like <a href="https://thoughtbot.com/blog/reading-an-explain-analyze-query-plan">this one from thoughtbot</a> or <a href="https://pganalyze.com/docs/explain">this one from pganalyze</a>.
<a href="https://www.postgresql.org/docs/current/using-explain.html">The official docs</a> are also a good reference (albeit a bit overwhelming for a newbie).
For analyzing query plans, I’ve found <a href="https://explain.depesz.com/">this tool</a> to often be very helpful</p>
<h3 id="an-index-isnt-much-use-for-a-table-with-barely-any-rows-in-it">An index isn’t much use for a table with barely any rows in it</h3>
<p>This is particularly important when doing development on your local database.
Chances are, you don’t have millions of rows in your local database.
Postgres may find that it’s faster just to do a seq. scan rather than use the index at all if it’s just dealing with 100 rows.</p>
<h3 id="when-indexing-multiple-columns-the-order-matters">When indexing multiple columns, the order matters</h3>
<p>Postgres supports multicolumn indexes which do what you might expect:
if you create an index on columns <code class="language-plaintext highlighter-rouge">a</code> and <code class="language-plaintext highlighter-rouge">b</code> like so:</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">CREATE</span> <span class="k">INDEX</span> <span class="n">CONCURRENTLY</span> <span class="k">ON</span> <span class="n">tbl</span> <span class="p">(</span><span class="n">a</span><span class="p">,</span> <span class="n">b</span><span class="p">);</span>
</code></pre></div></div>
<p>then a <code class="language-plaintext highlighter-rouge">WHERE</code> clause like</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">tbl</span> <span class="k">WHERE</span> <span class="n">a</span> <span class="o">=</span> <span class="mi">1</span> <span class="k">AND</span> <span class="n">b</span> <span class="o">=</span> <span class="mi">2</span><span class="p">;</span>
</code></pre></div></div>
<p>will be faster than if two separate indexes were created for <code class="language-plaintext highlighter-rouge">a</code> and <code class="language-plaintext highlighter-rouge">b</code> respectively.
This is because in one multicolumn index, Postgres just needs to traverse one B-tree which is able to efficiently combine the constraints of the search query.</p>
<p>This index speeds up queries filtering against just <code class="language-plaintext highlighter-rouge">a</code> just as much as an index against <code class="language-plaintext highlighter-rouge">a</code> alone.</p>
<p>But what about a query like <code class="language-plaintext highlighter-rouge">SELECT * FROM tbl WHERE b = 5;</code>?
Is that faster too?
Possibly, but not as fast as it could be.
It turns out the above index does <em>not</em> obviate the need for an index on <code class="language-plaintext highlighter-rouge">b</code> alone.
That’s because the B-tree in the index is first keyed on <code class="language-plaintext highlighter-rouge">a</code> and secondarily keyed on <code class="language-plaintext highlighter-rouge">b</code>.
So it will need to traverse all of the <code class="language-plaintext highlighter-rouge">a</code> values in the index to find all of the <code class="language-plaintext highlighter-rouge">b</code> values in the index.
Often if you need to use any combination of columns for you queries,
you’ll want to have indexes for both <code class="language-plaintext highlighter-rouge">(a, b)</code> as well as <code class="language-plaintext highlighter-rouge">b</code> alone.
That said, you may be able to rely on indexes on <code class="language-plaintext highlighter-rouge">a</code> and <code class="language-plaintext highlighter-rouge">b</code> separately,
depending on your needs.</p>
<h3 id="if-doing-prefix-matches-use-text_pattern_ops">If doing prefix matches, use <code class="language-plaintext highlighter-rouge">text_pattern_ops</code>
</h3>
<p>Let’s say you’re storing a hierarchical system of directories in your database using a <a href="https://www.slideshare.net/slideshow/models-for-hierarchical-data/4179181#18">materialized path approach</a> (where you store a list of all of a row’s ancestors’ ids in each row) and for some part of your application you need to get all descendant directories.
So you need to query a table to find all rows where a column matches some common prefix:</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="c1">-- % is a wildcard: the `WHERE` clause here is asking for `directories` where `path` starts with '/1/2/3/'</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">directories</span> <span class="k">WHERE</span> <span class="n">path</span> <span class="k">LIKE</span> <span class="s1">'/1/2/3/%'</span>
</code></pre></div></div>
<p>To keep things speedy, you add an index to the <code class="language-plaintext highlighter-rouge">path</code> column of <code class="language-plaintext highlighter-rouge">directories</code>:</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">CREATE</span> <span class="k">INDEX</span> <span class="n">CONCURRENTLY</span> <span class="k">ON</span> <span class="n">directories</span> <span class="p">(</span><span class="n">path</span><span class="p">);</span>
</code></pre></div></div>
<p>Unfortunately, this may not be used:
Most kinds of indexes
(including the default B-tree index implicitly created in the <code class="language-plaintext highlighter-rouge">CREATE INDEX</code> statement above)
depend on the ordering of values to work.
To make Postgres able to do the basic character-by-character sorting that you need for this sort of prefix matching or pattern matching in general,
you need to give it a different <a href="https://www.postgresql.org/docs/current/indexes-opclass.html">“operator class”</a> when defining the index:</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">CREATE</span> <span class="k">INDEX</span> <span class="n">CONCURRENTLY</span> <span class="k">ON</span> <span class="n">directories</span> <span class="p">(</span><span class="n">path</span> <span class="n">text_pattern_ops</span><span class="p">);</span>
</code></pre></div></div>
<h2 id="long-held-locks-can-break-your-app-even-access-share">Long-held locks can break your app (even <code class="language-plaintext highlighter-rouge">ACCESS SHARE</code>)</h2>
<h3 id="what-is-a-lock">What is a lock?</h3>
<p>A “lock” or “mutex” (short for “mutual exclusion”) ensures only one client can do something dangerous at a time.
This is a concept you’ll see in a lot of places but they’re particularly important in Postgres, as in any database, because updating an individual entity (whether that’s a row, table, view, etc.) must entirely succeed or entirely fail.
One way that operations could only partially succeed is if two different clients/processes were attempting to perform them at the same time.
As a result, any operation will need to acquire a “lock” on the relevant entity.</p>
<h3 id="how-locks-work-in-postgres">How locks work in Postgres</h3>
<p>In Postgres, there are several different locking levels for tables which are more or less restrictive.
Here’s just a few in order of less to more restrictive:</p>
<table>
<thead>
<tr>
<th>Lock Mode</th>
<th>Example Statements</th>
</tr>
</thead>
<tbody>
<tr>
<td><code class="language-plaintext highlighter-rouge">ACCESS SHARE</code></td>
<td><code class="language-plaintext highlighter-rouge">SELECT</code></td>
</tr>
<tr>
<td><code class="language-plaintext highlighter-rouge">ROW SHARE</code></td>
<td><code class="language-plaintext highlighter-rouge">SELECT ... FOR UPDATE</code></td>
</tr>
<tr>
<td><code class="language-plaintext highlighter-rouge">ROW EXCLUSIVE</code></td>
<td>
<code class="language-plaintext highlighter-rouge">UPDATE</code>, <code class="language-plaintext highlighter-rouge">DELETE</code>, <code class="language-plaintext highlighter-rouge">INSERT</code>
</td>
</tr>
<tr>
<td><code class="language-plaintext highlighter-rouge">SHARE UPDATE EXCLUSIVE</code></td>
<td><code class="language-plaintext highlighter-rouge">CREATE INDEX CONCURRENTLY</code></td>
</tr>
<tr>
<td><code class="language-plaintext highlighter-rouge">SHARE</code></td>
<td>
<code class="language-plaintext highlighter-rouge">CREATE INDEX</code> (not <code class="language-plaintext highlighter-rouge">CONCURRENTLY</code>)</td>
</tr>
<tr>
<td><code class="language-plaintext highlighter-rouge">ACCESS EXCLUSIVE</code></td>
<td>Many forms of <code class="language-plaintext highlighter-rouge">ALTER TABLE</code> and <code class="language-plaintext highlighter-rouge">ALTER INDEX</code>
</td>
</tr>
</tbody>
</table>
<p>And here’s how they conflict (X means they are conflicting):</p>
<div class="scrollable-wrapper-x-mobile">
<table class="table" summary="Conflicting Lock Modes">
<colgroup>
<col/>
<col/>
<col/>
<col/>
<col/>
<col/>
<col/>
</colgroup>
<thead>
<tr>
<th rowspan="2">Requested Lock Mode</th>
<th align="center" colspan="8">Existing Lock Mode</th>
</tr>
<tr>
<th><code>ACCESS SHARE</code></th>
<th><code>ROW SHARE</code></th>
<th><code>ROW EXCL.</code></th>
<th><code>SHARE UPDATE EXCL.</code></th>
<th><code>SHARE</code></th>
<th><code>ACCESS EXCL.</code></th>
</tr>
</thead>
<tbody>
<tr>
<td><code>ACCESS SHARE</code></td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center">X</td>
</tr>
<tr>
<td><code>ROW SHARE</code></td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center">X</td>
</tr>
<tr>
<td><code>ROW EXCL.</code></td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center">X</td>
<td align="center">X</td>
</tr>
<tr>
<td><code>SHARE UPDATE EXCL.</code></td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center">X</td>
<td align="center">X</td>
<td align="center">X</td>
</tr>
<tr>
<td><code>SHARE</code></td>
<td align="center"> </td>
<td align="center"> </td>
<td align="center">X</td>
<td align="center">X</td>
<td align="center"> </td>
<td align="center">X</td>
</tr>
<tr>
<td><code>ACCESS EXCL.</code></td>
<td align="center">X</td>
<td align="center">X</td>
<td align="center">X</td>
<td align="center">X</td>
<td align="center">X</td>
<td align="center">X</td>
</tr>
</tbody>
</table>
</div>
<p>For example consider the following for a single table:</p>
<table>
<thead>
<tr>
<th>Client 1 is doing…</th>
<th>Client 2 wants to do a …</th>
<th>Can Client 2 start?</th>
</tr>
</thead>
<tbody>
<tr>
<td><code class="language-plaintext highlighter-rouge">UPDATE</code></td>
<td><code class="language-plaintext highlighter-rouge">SELECT</code></td>
<td>✅ <strong>Yes</strong>
</td>
</tr>
<tr>
<td><code class="language-plaintext highlighter-rouge">UPDATE</code></td>
<td><code class="language-plaintext highlighter-rouge">CREATE INDEX CONCURRENTLY</code></td>
<td>🚫 <strong>No, must wait</strong>
</td>
</tr>
<tr>
<td><code class="language-plaintext highlighter-rouge">SELECT</code></td>
<td><code class="language-plaintext highlighter-rouge">CREATE INDEX</code></td>
<td>✅ <strong>Yes</strong>
</td>
</tr>
<tr>
<td><code class="language-plaintext highlighter-rouge">SELECT</code></td>
<td><code class="language-plaintext highlighter-rouge">ALTER TABLE</code></td>
<td>🚫 <strong>No, must wait</strong><sup id="fnref:alter_table_special_cases" role="doc-noteref"><a class="footnote" href="https://challahscript.com/what_i_wish_someone_told_me_about_postgres#fn:alter_table_special_cases" rel="footnote">3</a></sup>
</td>
</tr>
<tr>
<td><code class="language-plaintext highlighter-rouge">ALTER TABLE</code></td>
<td><code class="language-plaintext highlighter-rouge">SELECT</code></td>
<td>🚫 <strong>No, must wait</strong><sup id="fnref:alter_table_special_cases:1" role="doc-noteref"><a class="footnote" href="https://challahscript.com/what_i_wish_someone_told_me_about_postgres#fn:alter_table_special_cases" rel="footnote">3</a></sup>
</td>
</tr>
</tbody>
</table>
<p>For a full list of all of this information, look at <a href="https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES">the official documentation</a>.
<a href="https://postgres-locks.husseinnasser.com/">This guide</a> is also a great reference to see what conflicts with what on an operation-by-operation basis (which is typically what you’re thinking about, instead of lock levels).</p>
<h3 id="how-this-can-cause-problems">How this can cause problems</h3>
<p>In the previous section, we noted that an if one client is performing an <code class="language-plaintext highlighter-rouge">ALTER TABLE</code> statement, that can block a <code class="language-plaintext highlighter-rouge">SELECT</code> from running.
This can be just as bad as it sounds if the <code class="language-plaintext highlighter-rouge">ALTER TABLE</code> statement takes a long time.
If you’re updating a core table (e.g. <code class="language-plaintext highlighter-rouge">users</code>, one that all requests for your web app may need to reference) all <code class="language-plaintext highlighter-rouge">SELECT</code>s reading from that table will just be waiting.
Before timing out, of course, causing your app to return 503s.</p>
<p>Common recipes for slow <code class="language-plaintext highlighter-rouge">ALTER TABLE</code> statements include</p>
<ul>
<li>Adding a column with a non-constant default<sup id="fnref:default_changes" role="doc-noteref"><a class="footnote" href="https://challahscript.com/what_i_wish_someone_told_me_about_postgres#fn:default_changes" rel="footnote">4</a></sup>
<ul>
<li>In my experience this is by far the most common cause of slowness</li>
</ul>
</li>
<li>Changing a column’s type</li>
<li>Adding a uniqueness constraint</li>
</ul>
<p>So, let’s say you’re adding a new column to a heavily used table.
You aren’t doing anything silly with your <code class="language-plaintext highlighter-rouge">ALTER TABLE</code> statements.
Sure, you’re adding a new column, but it doesn’t have a variable default.
This can still break your app.</p>
<p>See, that <code class="language-plaintext highlighter-rouge">ALTER TABLE</code> statement <em>will</em> be fast…once it acquires the lock.
But say that, years ago, you made some internal dashboard that does periodic queries against that table.
Over time, that query got slower and slower.
What once took milliseconds now takes minutes.
Which is normally fine–it’s just a <code class="language-plaintext highlighter-rouge">SELECT</code> statement after all.
But if your <code class="language-plaintext highlighter-rouge">ALTER TABLE</code> statement gets executed while one of those is running, it will have to wait.</p>
<p>That all probably isn’t too surprising.
But you might find this a bit surprising:
Any subsequent statements querying that table will have to wait, too.
That’s because Postgres locks form a queue:</p>
<p><img class="mermaid" src="https://mermaid.ink/svg/eyJjb2RlIjoiJSV7aW5pdDogeyd0aGVtZSc6J2RhcmsnfX0lJVxuZmxvd2NoYXJ0IEJUXG5zdWJncmFwaCBDdXJyZW50bHkgRXhlY3V0aW5nXG5uMFtcIlNFTEVDVCAuLi4gRlJPTSAuLi4gdXNlcnMgLi4uIDtcbkhvbGRpbmcgQUNDRVNTIFNIQVJFO1xuTG9jayBBY3F1aXJlZCBhdCB0aW1lIHQ9MFxu8J-QjCA8dT4qKipTTE9XKioqPC91PiDwn5CMXCJdXG5uMVtcIlNFTEVDVCAuLi4gRlJPTSAuLi4gdXNlcnMgLi4uIDtcbkhvbGRpbmcgQUNDRVNTIFNIQVJFO1xuTG9jayBBY3F1aXJlZCBhdCB0aW1lIHQ9MVwiXVxuZW5kXG5uMltcIkFMVEVSIFRBQkxFIHVzZXJzIC4uLiA7XG5SZXF1ZXN0aW5nIEFDQ0VTUyBFWENMVVNJVkU7XG5Mb2NrIFJlcXVlc3RlZCBhdCB0aW1lIHQ9MlwiXVxubjNbXCJTRUxFQ1QgLi4uIEZST00gLi4uIHVzZXJzIC4uLiA7XG5SZXF1ZXN0aW5nIEFDQ0VTUyBTSEFSRTtcbkxvY2sgUmVxdWVzdGVkIGF0IHRpbWUgdD0zXCJdXG5uNFtcIi4uLlwiXVxubjVbXCJTRUxFQ1QgLi4uIEZST00gLi4uIHVzZXJzIC4uLiA7XG5SZXF1ZXN0aW5nIEFDQ0VTUyBTSEFSRTtcbkxvY2sgUmVxdWVzdGVkIGF0IHRpbWUgdD0yMFwiXG5dXG5uMi0tV2FpdGluZyBvbi0tPm4wXG5uMi0tV2FpdGluZyBvbi0tPm4xXG5uMy0tV2FpdGluZyBvbi0tPm4yXG5uNC0tV2FpdGluZyBvbi0tPm4yXG5uNS0tV2FpdGluZyBvbi0tPm4yXG5zdHlsZSBuMCBmaWxsOiM5ZmE2NDcsY29sb3I6YmxhY2siLCJtZXJtYWlkIjpudWxsfQ"/></p>
<p>For a great article on this exact scenario happening, see <a href="https://xata.io/blog/migrations-and-exclusive-locks">here</a>.</p>
<h3 id="long-running-transactions-can-be-just-as-bad">Long-running transactions can be just as bad</h3>
<p>If you’re not familiar with <a href="https://www.postgresql.org/docs/current/tutorial-transactions.html">transactions</a>, they’re a way of grouping together a series of database statements so they act as all-or-nothing (in fancy lingo, they’re “atomic”).
Once you begin a transaction (with <code class="language-plaintext highlighter-rouge">BEGIN</code>, of course), you’re hiding away.
No other clients can see any changes you’re making.
You can finish the transaction (with <code class="language-plaintext highlighter-rouge">COMMIT</code>) which then “publishes” them to the rest of the database.
Transactions are, in an abstract way, similar to locks: they allow you avoid issues with other clients messing up what you’re trying to do.</p>
<p>A classic example of a task begging for transactions is transferring money from one bank account to another.
You would want to decrement the balance of one account and increment the balance of the other.
If the database goes down or the originating account balance goes negative midway, you want to cancel the whole operation.
Transactions can make that possible.</p>
<p>However, you can easily shoot yourself in the foot when writing transactions if you keep them running too long.
That’s because once a transaction acquires a lock, it holds onto it until the transaction commits.
For example, let’s say Client 1 opened up <code class="language-plaintext highlighter-rouge">psql</code> and wrote the following:</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">BEGIN</span><span class="p">;</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">backpacks</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">2</span><span class="p">;</span>
<span class="k">UPDATE</span> <span class="n">backpacks</span> <span class="k">SET</span> <span class="n">content_count</span> <span class="o">=</span> <span class="mi">3</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">2</span><span class="p">;</span>
<span class="k">SELECT</span> <span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">backpacks</span><span class="p">;</span>
<span class="c1">-- ...</span>
</code></pre></div></div>
<p>Oops! Client 1 has stepped away from their desk:
Someone just brought home some cupcakes!
Even though Client 1 is effectively “done” updating the row with <code class="language-plaintext highlighter-rouge">id = 2</code>, they still have the lock.<sup id="fnref:row_locks" role="doc-noteref"><a class="footnote" href="https://challahscript.com/what_i_wish_someone_told_me_about_postgres#fn:row_locks" rel="footnote">5</a></sup>
If another client wanted to delete this row, they would run this:</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">DELETE</span> <span class="k">FROM</span> <span class="n">backpacks</span> <span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">2</span><span class="p">;</span>
<span class="c1">-- ...</span>
<span class="c1">-- ?</span>
</code></pre></div></div>
<p>But it would just hang.
It wouldn’t actually delete anything until Client 1 came back and committed the transaction.</p>
<p>You can imagine how this can lead to all kinds of scenarios where clients are holding onto locks for much longer than they need, preventing others from successfully making queries against or updates to the database.</p>
<h2 id="jsonb-is-a-sharp-knife">JSONB is a sharp knife</h2>
<p>Postgres has a remarkably powerful feature: you can store queryable and efficiently serialized JSON as a value in a row.<sup id="fnref:json_vs_jsonb" role="doc-noteref"><a class="footnote" href="https://challahscript.com/what_i_wish_someone_told_me_about_postgres#fn:json_vs_jsonb" rel="footnote">6</a></sup>
In many ways, it makes Postgres have all the strengths of a <a href="https://en.wikipedia.org/wiki/Document-oriented_database">document-oriented database</a> (like MongoDB, for example) without having to spin up a new service or coordinate between two different data stores.</p>
<p>However, it has its downsides if used improperly.</p>
<h3 id="jsonb-can-be-slower-than-normal-columns">JSONB can be slower than normal columns</h3>
<p>While JSONB is quite flexible, Postgres doesn’t keep track of JSONB columns’ statistics which can mean that an equivalent query against a single JSONB column can be significantly slower than against a set of “normal” columns.
<a href="https://www.heap.io/blog/when-to-avoid-jsonb-in-a-postgresql-schema">Here’s a great blog post with someone demonstrating it making things 2000x slower!</a></p>
<h3 id="jsonb-is-not-as-self-documenting-as-a-standard-table-schema">JSONB is not as self-documenting as a standard table schema</h3>
<p>A JSONB column can have basically anything in it–that’s one of the main reasons it’s so powerful!
But it also means you have few guarantees about how it’s structured.
With a normal table, you can look up the schema and see what a query will return.
Will a key be written in camelCase? snake_case?
Will states be described with boolean <code class="language-plaintext highlighter-rouge">true</code>/<code class="language-plaintext highlighter-rouge">false</code>? Or with enums like <code class="language-plaintext highlighter-rouge">yes</code>/<code class="language-plaintext highlighter-rouge">maybe</code>/<code class="language-plaintext highlighter-rouge">no</code>?
You have no idea with JSONB as it doesn’t have the same static typing that Postgres data normally has.</p>
<h3 id="jsonb-postgres-types-are-a-bit-awkward-to-work-with">JSONB Postgres types are a bit awkward to work with</h3>
<p>Let’s say you have a table called <code class="language-plaintext highlighter-rouge">backpacks</code> with a JSONB column <code class="language-plaintext highlighter-rouge">data</code> where you have a <code class="language-plaintext highlighter-rouge">brand</code> field.
You want to find the JanSport <code class="language-plaintext highlighter-rouge">backpacks</code> because you love the early 90s aesthetic.
So you write the query:<sup id="fnref:jsonb_subscripting" role="doc-noteref"><a class="footnote" href="https://challahscript.com/what_i_wish_someone_told_me_about_postgres#fn:jsonb_subscripting" rel="footnote">7</a></sup></p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="c1">-- WARNING: Does not work!</span>
<span class="k">select</span> <span class="o">*</span> <span class="k">from</span> <span class="n">backpacks</span> <span class="k">where</span> <span class="k">data</span><span class="p">[</span><span class="s1">'brand'</span><span class="p">]</span> <span class="o">=</span> <span class="s1">'JanSport'</span><span class="p">;</span>
</code></pre></div></div>
<p>and you get the error back</p>
<div class="language-plaintext highlighter-rouge"><div class="highlight"><pre class="highlight"><code>ERROR: invalid input syntax for type json
LINE 1: select * from backpacks where data['brand'] = 'JanSport';
^
DETAIL: Token "JanSport" is invalid.
CONTEXT: JSON data, line 1: JanSport
</code></pre></div></div>
<p>What gives?
Postgres is expecting the right-hand side type of the comparison to match the left-hand side type; that is, for it to be a correctly formatted JSON document–therefore, it needs to be a JSON object, array, string, number, boolean, or null.
Keep in mind that none of these types have anything to do with Postgres types like <code class="language-plaintext highlighter-rouge">boolean</code> or <code class="language-plaintext highlighter-rouge">integer</code>. And <code class="language-plaintext highlighter-rouge">NULL</code> in SQL works very differently from JSONB’s <code class="language-plaintext highlighter-rouge">null</code>, which acts more just like a normal type.<sup id="fnref:jsonb_null" role="doc-noteref"><a class="footnote" href="https://challahscript.com/what_i_wish_someone_told_me_about_postgres#fn:jsonb_null" rel="footnote">8</a></sup>
To correctly write this query, you need to enable Postgres to be able to do some coercion.</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">select</span> <span class="o">*</span> <span class="k">from</span> <span class="n">backpacks</span> <span class="k">where</span> <span class="k">data</span><span class="p">[</span><span class="s1">'brand'</span><span class="p">]</span> <span class="o">=</span> <span class="s1">'"JanSport"'</span><span class="p">;</span>
<span class="c1">-- ^ This is really equivalent to the following (because Postgres knows the left-hand side is `jsonb`)</span>
<span class="k">select</span> <span class="o">*</span> <span class="k">from</span> <span class="n">backpacks</span> <span class="k">where</span> <span class="k">data</span><span class="p">[</span><span class="s1">'brand'</span><span class="p">]</span> <span class="o">=</span> <span class="s1">'"JanSport"'</span><span class="p">::</span><span class="n">jsonb</span><span class="p">;</span>
<span class="c1">-- Alternatively, you could convert the left-hand side to Postgres `text`:</span>
<span class="k">select</span> <span class="o">*</span> <span class="k">from</span> <span class="n">backpacks</span> <span class="k">where</span> <span class="k">data</span><span class="o">->></span><span class="s1">'brand'</span> <span class="o">=</span> <span class="s1">'JanSport'</span><span class="p">;</span>
</code></pre></div></div>
<p>Note the double quotes inside the single quotes.
<code class="language-plaintext highlighter-rouge">JanSport</code> on its own isn’t valid JSON.</p>
<p>What’s more is <a href="https://www.postgresql.org/docs/current/functions-json.html">there are a bunch more operators and functions</a> that are specific to JSONB and are hard to remember all at once.</p>
<h2 id="anyway">Anyway…</h2>
<p>Hope you found this useful.
Thank you to Lillie Chilen, <a href="https://github.com/monicacowan">Monica Cowan</a>, <a href="https://stevenharman.net">Steven Harman</a>, and <a href="https://kwugirl.github.io/">KWu</a> for encouragement and feedback on this post.
If you have any corrections, feedback, or comments, you can find me on basically all sites as <code class="language-plaintext highlighter-rouge">hibachrach</code>.</p>
<hr/>
<p><br/></p>
<div class="footnotes" role="doc-endnotes">
<ol>
<li id="fn:a4_correction" role="doc-endnote">
<p>If you print it on A4 paper, it’s 3,024 pages; just another reason that standard is better, I guess. <a class="reversefootnote" href="https://challahscript.com/what_i_wish_someone_told_me_about_postgres#fnref:a4_correction" role="doc-backlink">↩</a></p>
</li>
<li id="fn:copy_permissions" role="doc-endnote">
<p>This avoids having to use the more standard <code class="language-plaintext highlighter-rouge">COPY</code> statement which unfortunately often requires escalated privileges which you may or may not have access to. <a class="reversefootnote" href="https://challahscript.com/what_i_wish_someone_told_me_about_postgres#fnref:copy_permissions" role="doc-backlink">↩</a></p>
</li>
<li id="fn:alter_table_special_cases" role="doc-endnote">
<p>That is…<em>typically</em>. Some <code class="language-plaintext highlighter-rouge">ALTER TABLE</code> forms (like adding constraints) require a less restrictive lock.
See <a href="https://www.postgresql.org/docs/current/sql-altertable.html">its page in the docs</a> for more info. <a class="reversefootnote" href="https://challahscript.com/what_i_wish_someone_told_me_about_postgres#fnref:alter_table_special_cases" role="doc-backlink">↩</a> <a class="reversefootnote" href="https://challahscript.com/what_i_wish_someone_told_me_about_postgres#fnref:alter_table_special_cases:1" role="doc-backlink">↩<sup>2</sup></a></p>
</li>
<li id="fn:default_changes" role="doc-endnote">
<p>It used to be that <em>any</em> default could make adding a column slow, but <a href="https://www.postgresql.org/docs/current/ddl-alter.html#DDL-ALTER-ADDING-A-COLUMN">this was fixed in Postgres 11</a>, which feels recent to me but of course was released 6 years ago. <a class="reversefootnote" href="https://challahscript.com/what_i_wish_someone_told_me_about_postgres#fnref:default_changes" role="doc-backlink">↩</a></p>
</li>
<li id="fn:row_locks" role="doc-endnote">
<p>In this case, the lock at issue is a <a href="https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-ROWS">row lock</a>, which (as you might guess) is a lock that is on the row.
Row locks work fairly similarly to the table-level locks we were discussing earlier, but are a bit simpler and only have 4 levels. <a class="reversefootnote" href="https://challahscript.com/what_i_wish_someone_told_me_about_postgres#fnref:row_locks" role="doc-backlink">↩</a></p>
</li>
<li id="fn:json_vs_jsonb" role="doc-endnote">
<p>Postgres has both JSON values (where the text is stored as text) and JSONB where the JSON is converted to an efficient binary format.
JSONB has a number of advantages (e.g. you can index it!) to the point where one can consider the JSON format to just be for special cases (in my experience, anyway). <a class="reversefootnote" href="https://challahscript.com/what_i_wish_someone_told_me_about_postgres#fnref:json_vs_jsonb" role="doc-backlink">↩</a></p>
</li>
<li id="fn:jsonb_subscripting" role="doc-endnote">
<p>This is leveraging <a href="https://www.crunchydata.com/blog/better-json-in-postgres-with-postgresql-14">the relatively new subscripting syntax introduced in Postgres 14</a> (in my opinion, better than the original <code class="language-plaintext highlighter-rouge">-></code> syntax which feels less familiar compared with how JSON is traversed elsewhere). <a class="reversefootnote" href="https://challahscript.com/what_i_wish_someone_told_me_about_postgres#fnref:jsonb_subscripting" role="doc-backlink">↩</a></p>
</li>
<li id="fn:jsonb_null" role="doc-endnote">
<p>Most notably, <code class="language-plaintext highlighter-rouge">'null'::jsonb = 'null'::jsonb</code> is <code class="language-plaintext highlighter-rouge">true</code> whereas <code class="language-plaintext highlighter-rouge">NULL = NULL</code> is <code class="language-plaintext highlighter-rouge">NULL</code> <a class="reversefootnote" href="https://challahscript.com/what_i_wish_someone_told_me_about_postgres#fnref:jsonb_null" role="doc-backlink">↩</a></p>
</li>
</ol>
</div> https://challahscript.com/what_i_wish_someone_told_me_about_postgres
