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.

Performance

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.

Smaller Indexes with B-Tree Deduplication

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 index 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 pg_upgrade.

Extended Statistics Improvements in Postgres 13

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 IN/ANY constant lists, allowing consideration of multiple extended statistics objects in planning a query, and support for setting a statistics target for extended statistics:

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.

Parallel VACUUM & Better Support for Append-only Workloads

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 improvements in VACUUM work. Parallel VACUUM is the default and can be controlled with the PARALLEL option:

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 autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor variables.

Incremental Sorting

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 work.

Monitoring

Monitoring improvements in Postgres 13 include more details on WAL usage, more options for logging your queries, and more information on query planning.

WAL Usage Stats

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 WAL records:

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 pg_stat_statements, the wal_records, wal_fpi, and wal_bytes values here are cumulative since the last pg_stat_statements_reset call.

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.

Better Statement Logging in Postgres 13

Settings like 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?

Until now, 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_statement_sample_rate or 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_statement and 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 with log_parameter_max_length_on_error. Here's an example of setting this to -1 (unlimited) and trying to run SELECT pg_sleep($1) (with parameter $1 set to 3) on a connection with a statement_timeout of 1s:

2020-09-17 12:23:03.161 PDT [321349] maciek@maciek ERROR:  canceling statement due to statement timeout
2020-09-17 12:23:03.161 PDT [321349] maciek@maciek CONTEXT:  extended query with parameters: $1 = '3'
2020-09-17 12:23:03.161 PDT [321349] 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).

More Planning Information

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:

First, the 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)

Second, pg_stat_statements will keep track of time spent planning if you enable the pg_stat_statements.track_planning setting:

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.

Usability

Postgres 13 usability improvements include better documentation, better built-in UUID support, and some handy psql enhancements.

Glossary

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.

Tuple

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.

Better UUID Support

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 uuid_generate_v4.

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)

psql Improvements

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 psql 11:

maciek=# \e
maciek-# ;
 ?column? 
----------
        1
(1 row)

versus psql 13:

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 .psqlrc), but there's a couple of new substitutions available:

• %x will 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)
• %w will pad PROMPT2 (used when more input is expected) to be the same width as PROMPT1 to 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 server!

Conclusion

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.