Lessons Learned from Running Postgres 13: Better Performance, Monitoring & More
Postgres 13 is almost here. It's been in beta since May, and the general availability release is coming any day. We've been following Postgres 13 closely here at pganalyze, and have been running the beta in one of our staging environments for several months now.
There are no big new features in Postgres 13, but there are a lot of small but important incremental improvements. Let's take a look.
Postgres 13 performance improvements include both built-in optimizations and heuristics that will make your database run better out of the box, as well as additional features to give you more flexibility in optimizing your schema and queries.
Postgres 13 introduces a way for B-Tree indexes to avoid storing duplicate entries in some situations. In general, a B-Tree index consists of a tree of indexed values, with each leaf node pointing to a particular row version. Because each leaf points to one row version, if you are indexing non-unique values, those values need to be repeated.
The de-duplication mechanism avoids that by having a leaf node point to several row versions if possible, which leads to smaller indexes.
Here is an example from our own pganalyze application schema: We have a
queries table to
track all the queries we monitor, and a
database_id field to track which database they belong to. We
database_id (so we can quickly fetch queries for a specific database), and because each database
typically has more than one query, there is a lot of duplication in this index.
New B-Tree indexes in Postgres 13 use the deduplication feature by default, but if for some reason,
you need to turn it off, you can control it with the
deduplicate_items storage parameter. Here we
create the same index in two different ways, with deduplication explicitly on and off (though again,
you don't need to specify
on—this is the default):
CREATE INDEX CONCURRENTLY queries_db_id_idx_no_dedup ON queries(database_id) WITH (deduplicate_items=off); CREATE INDEX CONCURRENTLY queries_db_id_idx_yes_dedup ON queries(database_id) WITH (deduplicate_items=on); SELECT relname, pg_size_pretty(pg_relation_size(oid)) FROM pg_class WHERE relname IN ('queries_db_id_idx_no_dedup', 'queries_db_id_idx_yes_dedup');
relname | pg_size_pretty -----------------------------+---------------- queries_db_id_idx_no_dedup | 218 MB queries_db_id_idx_yes_dedup | 67 MB (2 rows)
With deduplication, the new index is more than three times smaller! Smaller indexes are faster to load from disk, and take up less space in memory, meaning there's more room for your data.
One interesting note here is that the index entries point to row versions (as in, a row the way it exists in one specific MVCC state), not rows themselves, so this feature can improve index size even for unique indexes, where one would not expect any duplication to occur.
Note that deduplication is not possible in all cases (see above link for details), and that you
will need to reindex before you can take advantage of it if upgrading via
Postgres 10 introduced the concept of extended statistics. Postgres keeps some statistics about the "shape" of your data to ensure it can plan queries efficiently,
but the statistics kept by default cannot track things like inter-column dependencies. Extended statistics
were introduced to address that: These are database objects (like indexes) that you create manually with
CREATE STATISTICS to give the query planner more information for more specific situations. These would be
expensive for Postgres to determine automatically, but armed with an understanding of the semantics of your
schema, you can provide that additional info. Used carefully, this can lead to
massive performance improvements.
Postgres 13 brings a number of small but important improvements to extended statistics, including
support for using them with
OR clauses and in
ANY constant lists, allowing consideration
of multiple extended statistics objects in planning a query, and support for
setting a statistics target for
ALTER STATISTICS table_stx SET STATISTICS 1000;
Like with the regular statistics target, this is a trade-off between additional planning time (and longer
ANALYZE runs), versus having more precise plans. We recommend using this in a targeted manner using EXPLAIN plans to confirm plan changes.
Postgres multi-version concurrency control means you need to run
VACUUM regularly (usually you can rely
on the autovacuum process, though it may need some tuning). In Postgres 13, one notable improvement is
that multiple indexes for a single table can be vacuumed in parallel. This can lead to big performance
VACUUM work. Parallel
VACUUM is the default and can be controlled with the
VACUUM (PARALLEL 2, VERBOSE) queries;
INFO: vacuuming "public.queries" INFO: launched 2 parallel vacuum workers for index vacuuming (planned: 2) INFO: scanned index "index_queries_on_database_id" to remove 1403418 row versions by parallel vacuum worker DETAIL: CPU: user: 0.98 s, system: 0.15 s, elapsed: 2.37 s INFO: scanned index "index_queries_on_last_occurred_at" to remove 1403418 row versions by parallel vacuum worker DETAIL: CPU: user: 0.88 s, system: 0.27 s, elapsed: 2.60 s ...
Parallel VACUUM occurs when the following is true:
- Sufficient parallel workers are available, based on the system-wide limit set by
max_parallel_maintenance_workers(defaults to 2)
- There are multiple indexes on the table (one index can be processed by one worker at a time)
- Index types support it (all built-in index types support parallelism to some extent)
- The indexes are large enough to exceed
min_parallel_index_scan_size(defaults to 512 kB)
Be aware that parallel VACUUM is currently not supported for autovacuum. This new feature is intended for use in manual VACUUM runs that need to complete quickly, such as when insufficient autovacuum tuning has lead to an imminent TXID wraparound, and you need to intervene to fix it.
On that note, an important
autovacuum improvement in Postgres 13 is that the autovacuum background process can now be triggered by
INSERT statements for append-only tables. The main purpose of VACUUM is to clean up old versions of updated and deleted rows, but it is also essential to set pages as all-visible for MVCC bookkeeping. All-visible pages allow index-only scans to avoid checking visibility status row-by-row, making them faster.
We make extensive use of append-only tables at pganalyze for our timeseries data, and this improvement will make our lives considerably easier, avoiding the occasional manual VACUUM run on these tables. This new behavior can be controlled by the
Sorting data is a common database task, and Postgres has a number of features to avoid unnecessary work here. For example, if you have a B-Tree index on a column, and you query your table ordered by that column, it can just scan that index in order to get sorted data.
In Postgres 13, this is improved to handle partially sorted data. If you have an index on
(a, b) (or
the data is already sorted by
(a, b) for another reason), and you issue a query to order by
(a, b, c),
Postgres understands that the input data is already partially sorted, and can avoid re-sorting the whole
dataset. This is especially useful if you have a
LIMIT in your query, since this can avoid even more
Monitoring improvements in Postgres 13 include more details on
WAL usage, more options for logging your
queries, and more information on query planning.
The write-ahead log (
WAL) ensures your data stays consistent in the event of a crash, even mid-write. Consistency
is a fundamental property of databases—it ensures your transaction either committed or did not commit; you don't
have to worry about in-between states. But on a busy system,
WAL writes can often be a bottleneck. To help
diagnose this, Postgres 13 includes more information on
WAL usage from your queries.
EXPLAIN now supports information about
WAL records generated during execution:
EXPLAIN (ANALYZE, WAL) DELETE FROM users;
QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Delete on users (cost=0.00..5409.00 rows=100000 width=6) (actual time=108.910..108.911 rows=0 loops=1) WAL: records=100000 fpi=741 bytes=11425721 -> Seq Scan on users (cost=0.00..5409.00 rows=100000 width=6) (actual time=8.519..51.850 rows=100000 loops=1) Planning Time: 6.083 ms Execution Time: 108.955 ms (5 rows)
You can see that the
WAL line includes the number of records generated, the number of full page images (fpi), and
the number of
WAL bytes generated. Only non-zero values are printed in the default text format.
This is also available in
pg_stat_statements. For example, on our staging environment, here is what we ran to get
the statement that produced the most
SELECT query, calls, wal_records, wal_fpi, wal_bytes FROM pg_stat_statements ORDER BY wal_records DESC LIMIT 1;
-[ RECORD 1 ]--------------------------------------------------------------------------------------------- query | CREATE TEMPORARY TABLE upsert_data (server_id uuid NOT NULL, backend_id uuid NOT NULL, | query_start timestamp NOT NULL, query_fingerprint bytea NOT NULL, query_text text NOT NULL) calls | 7974948 wal_records | 966960816 wal_fpi | 1018412 wal_bytes | 100086092097
Like many other values in
wal_bytes values here are
cumulative since the last
This info can help you identify your write-heavy queries and optimize as necessary. Note that write-heavy queries can also affect replication: If you see replication lag, you can use these new features to understand better which statements are causing it.
log_min_duration_statement are great to help you understand your slow queries, but how
slow is slow? Is the reporting query that runs overnight slow compared to the 5s query that runs
in the context of a web request? Is that 5s query, that runs once in a rarely-used endpoint, slow
compared to a 100ms query that runs twenty times to load your home page?
log_min_duration_statement was one blunt tool for all these situations, but Postgres 13 brings some
flexibility with sampling-based statement logging. You can set
log_min_duration_sample to enable sampling, and then either set
log_transaction_sample_rate to control sampling.
Both of these settings work in a similar manner: they range from 0 to 1, and determine the chance that
a statement will be randomly selected for logging. The former applies to individual statements, the latter
determines logging for all statements in a transaction. If both
log_min_duration_sample are set, the former should be a higher threshold that logs everything,
and the latter can be a lower threshold that logs only occasionally.
Another great statement logging improvement is being able to log parameters for failed statements
log_parameter_max_length_on_error. Here's an example of setting this to
and trying to run
SELECT pg_sleep($1) (with parameter
$1 set to
3) on a connection with a
2020-09-17 12:23:03.161 PDT  maciek@maciek ERROR: canceling statement due to statement timeout 2020-09-17 12:23:03.161 PDT  maciek@maciek CONTEXT: extended query with parameters: $1 = '3' 2020-09-17 12:23:03.161 PDT  maciek@maciek STATEMENT: select pg_sleep($1)
The timeout case is especially useful: Since both the query text and the parameters are now available
in the logs, you could run
EXPLAIN on any failed query to figure out what query plan caused
it to hit the time-out (N.B.: you are not guaranteed to get the same plan that failed, but depending
on your workload, the odds are pretty good).
The usual culprit in slow queries is the query execution itself, but with a complex schema and an elaborate query, planning can take significant time as well. Postgres 13 introduces two new changes that make it easier to keep an eye on planning:
BUFFERS option to
EXPLAIN gives you more information on memory usage during query planning.
Postgres manages memory for your data and indexes using a "buffer pool", and the
BUFFERS option can
show you which parts of your query are using that memory and how. The
EXPLAIN documentation has some more details. New
in Postgres 13 is the ability to see how buffers are used during query planning:
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM pg_class;
QUERY PLAN --------------------------------------------------------------------------------------------------------- Seq Scan on pg_class (cost=0.00..16.86 rows=386 width=265) (actual time=0.014..0.120 rows=390 loops=1) Buffers: shared hit=13 Planning Time: 1.021 ms Buffers: shared hit=118 Execution Time: 0.316 ms (5 rows)
pg_stat_statements will keep track of time spent planning if you enable the
SELECT query, plans, total_plan_time, min_plan_time, max_plan_time, mean_plan_time, stddev_plan_time FROM pg_stat_statements WHERE queryid = -7012080368802260371;
-[ RECORD 1 ]----+---------------------------------------------------------------------- query | SELECT query, plans, total_plan_time, + | min_plan_time, max_plan_time, mean_plan_time, stddev_plan_time+ | FROM pg_stat_statements WHERE queryid = $1 plans | 1 total_plan_time | 0.083102 min_plan_time | 0.083102 max_plan_time | 0.083102 mean_plan_time | 0.083102 stddev_plan_time | 0
This is turned off by default due to performance overhead for certain workloads, but if you suspect planning time is an issue, it's definitely worth checking out. For more details on the performance regression, see this mailing list discussion; this is expected to be resolved in the future and the default may change.
Postgres 13 usability improvements include better documentation, better built-in UUID support, and some handy
TOAST? Tuple? Postmaster?
Any complex system will develop its own jargon, and Postgres is no exception. Some of it comes from the database field in general, some of it is Postgres-specific. Having dedicated language to talk precisely about specific technical concepts is very useful, but it can be confusing for newcomers.
A collection of attributes in a fixed order. That order may be defined by the table (or other relation) where the tuple is contained, in which case the tuple is often called a row. It may also be defined by the structure of a result set, in which case it is sometimes called a record. - PostgreSQL Glossary
You are likely familiar with the terms above, but if you ever run across something you are unclear on, those and many others are now documented in a new glossary. And now that there's an established place to do so, we can look forward to other technical terms being added here in the future.
If you use UUIDs in your system (and you should consider it—they're pretty handy), you're probably
pretty familiar with the
uuid-ossp extension. The base
uuid type is built in, but by default,
there's no simple mechanism to automatically generate new ones. The
uuid-ossp extension ships with Postgres,
but must be enabled explicitly to create UUID-generation functions like the common
Postgres 13 ships with a
gen_random_uuid function that is equivalent to
uuid_generate_v4, but available
by default. If you were only using
uuid-ossp for that function, you no longer need the extension:
=> \dx List of installed extensions Name | Version | Schema | Description ------+---------+--------+------------- (0 rows) => SELECT gen_random_uuid(); gen_random_uuid -------------------------------------- 07b45dae-e92e-4f91-8661-5fc0ef947d03 (1 row)
There are a number of small
psql improvements in Postgres 13. My favorite is that
\e, the command
to invoke your
$EDITOR on the current query buffer, will now display the query text when you save
and exit (unless you directly submit it by ending with a semicolon or
\g). Previously, the query
text was saved, but hidden. Compare opening your editor and saving
SELECT 1 in
maciek=# \e maciek-# ; ?column? ---------- 1 (1 row)
maciek=# \e maciek=# select 1 maciek-# ; ?column? ---------- 1 (1 row)
It's now clear what query text will be submitted when you complete your query.
Postgres 13 also includes additional ways to customize your
psql prompt. You can do so, as always, with
\set (typically in your
but there's a couple of new substitutions available:
%xwill display the status of the current transaction: an empty string for no transaction,
*when in an open transaction,
!when in a failed transaction, or
?when the transaction state is unknown (typically when there is no connection to the server)
PROMPT2(used when more input is expected) to be the same width as
PROMPT1to keep things nicely aligned
There are some other small improvements
as well. And these are all client-side changes, so they will also work if you are using a new
psql with an older
These are just a few of the many small improvements that come with Postgres 13. There are many others, like partial TOAST decompression, trusted extensions (so you can enable them without being superuser), PL/pgSQL performance improvements, and more. You can check out the full release notes on the Postgres web site.
We're very excited for this release. We already support monitoring Postgres 13 in pganalyze, and are already working on incorporating the new monitoring features directly into the product to give you better insights into your database.
Share this article: If you liked this article you might want to tweet it to your peers.