5mins of Postgres
Companion articles for the 5mins of Postgres video series — short, focused explorations of a single Postgres topic in each episode.
Waiting for Postgres 19: Reduced timing overhead for EXPLAIN ANALYZE with RDTSC
11 April, 2026In today’s E122 of “5mins of Postgres” we're talking about the upcoming Postgres 19 release, and how a change in the Postgres instrumentation handling reduces overhead of timing measurements in EXPLAIN ANALYZE using the RDTSC instruction, and why this will allow turning on for more workloads. We dive into the recently committed change that I (Lukas) authored together with Andres Freund and David Geier. See the full transcript with examples below. Share this episode: Click here to share this…
Continue readingWaiting for Postgres 19: Better Planner Hints with Path Generation Strategies
05 February, 2026In today’s E121 of “5mins of Postgres” we're talking about the upcoming Postgres 19 release, and how better extensibility of the Postgres planner will enable better plan management and planner hint extensions. We dive into the recently committed "path generation strategies" change, the proposed pg_plan_advice extension it was developed for, and show how can evolve and lose 2,500 lines of code! Share this episode: Click here to share this episode on LinkedIn. Feel free to sign up for our…
Continue readingPostgres Planner Quirks: Incremental Sort, and when it goes wrong
01 October, 2024In today’s E120 of “5mins of Postgres” we're returning to our Postgres planner quirks series to talk about Incremental Sort, and when it goes wrong. Incremental Sort can often speed up query plans when you have an existing sort order; however, there can be edge cases where the planner chooses a sub-optimal plan. Share this episode: Click here to share this episode on LinkedIn. Feel free to sign up for our newsletter and subscribe to our YouTube channel. The benefits of Incremental Sort A real…
Continue readingThe surprising logic of the Postgres work_mem setting, and how to tune it
16 June, 2024In today’s E119 of “5mins of Postgres” we discuss tuning the Postgres setting for your workload, and why it can be quite confusing to interpret the meaning of correctly for a given query. We also discuss the impact of , and why it's default changed from 1.0 to 2.0 in Postgres 15. Share this episode: Click here to share this episode on LinkedIn. Feel free to sign up for our newsletter and subscribe to our YouTube channel. The risk of too high work_mem: Running out of memory Surprising facts…
Continue readingWaiting for Postgres 17: Better Query Plans for Materialized CTE Scans
09 June, 2024In today’s E118 of “5mins of Postgres” we discuss two changes in the upcoming Postgres 17 release that improve query plans for queries that involve CTEs. This can improve query plans where you would see an explicit CTE scan, due to use of the keyword, or because Postgres wasn't able to pull up a query to the upper plan level. Share this episode: Click here to share this episode on LinkedIn. Feel free to sign up for our newsletter and subscribe to our YouTube channel. Column statistics from…
Continue readingPostgres Planner Quirks: JOIN Equivalence Classes and IN/ANY filters
02 June, 2024In today’s E117 of “5mins of Postgres” we continue our series on Postgres planner quirks. Today, we discuss JOIN column equivalence and when there are issues with IN/ANY filters not being considered as part of the equivalence class in Postgres. Share this episode: Click here to share this episode on LinkedIn. Feel free to sign up for our newsletter and subscribe to our YouTube channel. The start of the puzzle: An inefficient Nested Loop Join The role of parameterized index scans The fix: Adding…
Continue readingWhat's (not) in Postgres 17 beta1, and how to test it
25 May, 2024In today’s E116 of “5mins of Postgres” we discuss the Postgres 17 beta1 release that came out earlier this week, some highlights of the release, some changes that unfortunately got reverted since the feature freeze, and how to help the community during the beta testing process. Share this episode: Click here to share this episode on LinkedIn. Feel free to sign up for our newsletter and subscribe to our YouTube channel. A summary of the Postgres release schedule What's in Postgres 17 beta…
Continue readingPostgres Planner Quirks: How to fix bad JSONB selectivity estimates
18 May, 2024In today’s E115 of “5mins of Postgres” we discuss a common challenge when using equality and contains operators for querying JSONB columns, and why the Postgres planner will often get the estimates wrong for such queries, causing bad query plans. Share this episode: Click here to share this episode on LinkedIn or on X/Twitter. Feel free to sign up for our newsletter and subscribe to our YouTube channel. The problem: A slow query on a JSONB field EXPLAIN shows an unexpected Merge Join Adding…
Continue readingPostgres CVE-2024-4317 and how to fix the system views
11 May, 2024In today’s E114 of “5mins of Postgres” we discuss the updated Postgres minor releases that were just released this week. And specifically, we're going to talk about the security issue that was fixed in this release. Now I'll tell you first off, that this security issue, CVE-2024-4317 is a fairly minor one, but the reason I want to talk about it is, because I was the one who reported it. I'll explain to you when this can be a problem and how you can apply the fix. If you want to fix this issue…
Continue readingPostgres Planner Quirks: The impact of ORDER BY + LIMIT on index usage
04 May, 2024Today we're going to start a new series on 5mins of Postgres called "Postgres Planner Quirks". When we say "quirks", we mean odd behavior that might make sense to you if you're a Postgres hacker, but certainly is confusing when you're the end user, like a DBA, data platform, engineer, or application developer. In today’s E113 of “5mins of Postgres” we discuss a commonly encountered Postgres planner quirk, which is how Postgres behaves when you have a LIMIT and an ORDER BY clause, and it picks…
Continue readingWaiting for Postgres 17: Streaming I/O for sequential scans & ANALYZE
25 April, 2024In today’s E112 of “5mins of Postgres” we discuss streaming and vectored I/O in Postgres 17. This is an important step towards implementing asynchronous I/O in Postgres. In Postgres 17 we do not yet have asynchronous I/O, but we do see a performance benefit from what is essentially a refactoring work around the internal APIs. In this episode, I give you a little bit of a behind the scenes view on my understanding of the changes made in 17 and how they impact future asynchronous I/O work. Share…
Continue readingWaiting for Postgres 17: Faster B-Tree Index Scans for IN(...) lists and ANY =
21 April, 2024In today’s E111 of “5mins of Postgres” we discuss faster B-tree index scans in Postgres 17 for queries that involve IN lists or other cases where multiple array values are being passed to Postgres (ScalarArrayOpExpr). We show how even simple cases now avoid repeated page access, and how turning filters into index conditions and processing like an Index Skip Scan can yield significant speedups for certain queries. Share this episode: Click here to share this episode on LinkedIn or on X/Twitter…
Continue readingWaiting for Postgres 17: Benchmarking with pg_buffercache_evict
12 April, 2024In today’s E110 of “5mins of Postgres” we discuss the function recently committed to Postgres 17, that lets you remove individual pages from the Postgres shared buffer cache. We show how this can be used for testing query performance on a test system, and the impact of double buffering. We also discuss how to flush the OS page cache for a given table to run a benchmark on a query and avoid confusing output. Share this episode: Click here to share this episode on LinkedIn or on X/Twitter. Feel…
Continue readingWaiting for Postgres 17: Faster VACUUM with Adaptive Radix Trees
06 April, 2024In today’s E109 of “5mins of Postgres” we discuss a recently committed change to the Postgres 17 development branch that introduces an improved dead tuple storage for autovacuum based on adaptive radix trees. This significantly reduces autovacuum memory consumption and avoids the need for multiple index vacuum phases. Share this episode: Click here to share this episode on LinkedIn or on X/Twitter. Feel free to sign up for our newsletter and subscribe to our YouTube channel. The building blocks…
Continue readingWaiting for Postgres 17: Improved EXPLAIN for SubPlan nodes
30 March, 2024In today’s E108 of “5mins of Postgres” we discuss a recently committed change to the Postgres 17 development branch that improves how EXPLAIN represents and nodes. We compare the output with 16, and discuss the background of how a subplan can result from a sub-SELECT, and when it results in a regular JOIN instead. Share this episode: Click here to share this episode on LinkedIn or on X/Twitter. Feel free to sign up for our newsletter and subscribe to our YouTube channel. Improving how SubPlan…
Continue readingWaiting for Postgres 17: The new built-in C.UTF-8 locale
22 March, 2024In today’s E107 of “5mins of Postgres” we discuss a recently committed change to the Postgres 17 development branch that adds a built-in collation provider to Postgres, as well as a new built-in locale that allows fast binary sorting, whilst supporting Unicode-aware operations on texts. Share this episode: Click here to share this episode on LinkedIn or on X/Twitter. Feel free to sign up for our newsletter and subscribe to our YouTube channel. The new builtin collation provider in Postgres 1…
Continue readingHow Figma built DBProxy for sharding Postgres
15 March, 2024In E106 of "5mins of Postgres" we discuss how Figma scaled out their Postgres installation by 100x over 4 years, and recently switched to horizontal sharding using their DBProxy query proxy. We also compare their approach to Notion's sharding setup, as well as the Citus extension for Postgres. Share this episode: Click here to share this episode on LinkedIn or on X/Twitter. Feel free to sign up for our newsletter and subscribe to our YouTube channel. Reaching the biggest RDS instance, and what…
Continue readingTuning random_page_cost and how index correlation affects query plans
08 March, 2024In today’s E105 of “5mins of Postgres” we discuss why changing from the default of 4 is usually a good idea, and a specific example of where a high caused a bad plan due to index correlation. We dive into the relevant parts of the Postgres source, and explain how planner costing works. Share this episode: Click here to share this episode on LinkedIn or on X/Twitter. Feel free to sign up for our newsletter and subscribe to our YouTube channel. A puzzling index scan with Rows Removed By Filter…
Continue readingWaiting for Postgres 17: Configurable SLRU cache sizes for increased performance
01 March, 2024In today’s E104 of “5mins of Postgres” we discuss a recently committed change to the Postgres 17 development branch that allows configuring the size of the Postgres SLRU caches, as well as improvements to LWLock contention on SLRU related locks. We review the background on what SLRU caches do in Postgres, and historic performance challenges experienced at scale. Share this episode: Click here to share this episode on LinkedIn or on X/Twitter. Feel free to sign up for our newsletter and subscribe…
Continue readingSpeeding up partial COUNT(*) in Postgres by using LIMIT in a subquery
22 February, 2024In today’s E103 of “5mins of Postgres” we look at optimizing the performance of COUNT statements when only a subset of the data needs to be counted, through use of a LIMIT in a sub-SELECT, or what I like to call a "limited COUNT". We also discuss how this can be represented effectively in a web application. Share this episode: Click here to share this episode on LinkedIn or on X/Twitter. Feel free to sign up for our newsletter and subscribe to our YouTube channel. EXISTS vs COUNT(*) Partial…
Continue readingOut of range planner statistics and "get_actual_variable_range" in Postgres
15 February, 2024In today’s E102 of “5mins of Postgres” we talk about how Postgres handles situations where planner statistics are out of date. We look at the logic for deciding when the planner goes to the actual indexes on a table to get the maximum value using the function, and performance implications of this approach. Share this episode: Click here to share this episode on LinkedIn or on X/Twitter. Feel free to sign up for our newsletter and subscribe to our YouTube channel. How ANALYZE impacts Postgres…
Continue readingFaster query plans with Postgres 16: Incremental Sorts, Anti-JOINs and more
08 February, 2024In today’s E101 of “5mins of Postgres” we talk about the planner improvements in Postgres 16, including better use of Incremental Sort, more efficient anti-JOINs, Hash Join improvements, more efficient window functions, and more. And all of that without having to change your queries! Share this episode: Click here to share this episode on LinkedIn or on X/Twitter. Feel free to sign up for our newsletter and subscribe to our YouTube channel. Reuse the existing sort order more often Incremental…
Continue readingReducing table size with optimal column ordering and array-based storage
01 February, 2024In today’s E100 of “5mins of Postgres” we discuss how to optimize the row size in Postgres to reduce the overall table size. Specifically, we take a look at how column ordering affects storage size, how you can play column tetris to optimize it, and the big benefits that array-based storage can yield for some workloads. Share this episode: Click here to share this episode on LinkedIn or on X/Twitter. Feel free to sign up for our newsletter and subscribe to our YouTube channel. The optimal column…
Continue readingFinding the root cause of locking problems in Postgres
25 January, 2024In today’s E99 of “5mins of Postgres” we're showing how to go about finding the source of a locking problem in Postgres. Specifically, we are looking at how to debug heavyweight locks, how to end a process that’s holding a lock in Postgres, and general helpful settings for handling Postgres locking. Share this episode: Click here to share this episode on LinkedIn or on twitter. Feel free to sign up for our newsletter and subscribe to our YouTube channel. Debugging a lock problem in Postgres…
Continue readingHow to partition Postgres tables by timestamp based UUIDs
18 January, 2024In today’s E98 of “5mins of Postgres” we're going to talk about partitioning Postgres tables by timestamp based UUIDs. We're also going to talk about the status of UUIDv7 in the current Postgres development branch. Share this episode: Click here to share this episode on LinkedIn or on twitter. Feel free to sign up for our newsletter and subscribe to our YouTube channel. Partitioning Postgres tables by ULID What are ULIDs? Challenges with time-based partitioning Partitioning with ULIDs UUIDv…
Continue readingThe different trade-offs of Distributed Postgres architectures
12 January, 2024Happy new year! We hope you are doing well and are excited to be back with our “5mins of Postgres” video series. In E97 we're going to talk about distributed Postgres architectures. Share this episode: Click here to share this episode on LinkedIn or on twitter. Feel free to sign up for our newsletter and subscribe to our YouTube channel. Understanding distributed Postgres Downsides of distributed systems Latency The different architectures for distributed Postgres Network-attached block storage…
Continue readingWaiting for Postgres 17: Incremental base backups
21 December, 2023In E96 of “5mins of Postgres”, we talk about the incremental backup feature that was just committed to the Postgres 17 development branch. To be clear, this is very much off the presses and this may yet change or be removed completely. Share this episode: Click here to share this episode on LinkedIn or on twitter. Feel free to sign up for our newsletter and subscribe to our YouTube channel. Introducing incremental backups in Postgres 17 Benefits of incremental updates vs. WAL The WAL summarizer…
Continue readingZero downtime Postgres upgrades and how to logically replicate very large tables
14 December, 2023In E95 of “5mins of Postgres”, we talk about zero downtime Postgres upgrades and how to logically replicate very large tables. It’s an exciting episode packed with lots of real world experiences and examples from GitLab and Instacart! Share this episode: Click here to share this episode on LinkedIn or on twitter. Feel free to sign up for our newsletter and subscribe to our YouTube channel. Using logical replication to update Postgres with zero downtime Upgrading Postgres with pg_upgrade Logical…
Continue readingThe un-fun work of making Postgres FIPS compliant
07 December, 2023In today’s episode 94 of “5mins of Postgres”, we’ll walk through Postgres and FIPS. We’ll explain what the FIPS mode is, where it is required, how the MD5 algorithm presents issues, and more. Share this episode: Click here to share this episode on LinkedIn or on twitter. Feel free to sign up for our newsletter and subscribe to our YouTube channel. Postgres and FIPS mode What is FIPS mode? Postgres is not FIPS compliant by default OpenSSL and MD5 authentication pgcrypto Passing the Postgres 1…
Continue readingAvoiding deadlocks in Postgres migrations
30 November, 2023In today’s episode 93 of “5mins of Postgres”, we're talking about how to avoid deadlocks in Postgres migrations and surprising lock behavior with migrations that I personally encountered. Share this episode: Click here to share this episode on LinkedIn or on twitter. Feel free to sign up for our newsletter and subscribe to our YouTube channel. Deadlocks and other surprising behavior in Postgres migrations Using separate transactions when changing multiple tables Which locks are being acquired by…
Continue readingHow to use CREATE STATISTICS to improve a plan rows estimate, and why it matters
23 November, 2023In E92 of “5mins of Postgres”, we're going to talk about creating functional dependency statistics to improve plan cost estimates in Postgres. We’re looking at a problem that I myself encountered last week when I was trying to optimize the estimates we're getting for a particular query inside our own system. Share this episode: Click here to share this episode on LinkedIn or on twitter. Feel free to sign up for our newsletter and subscribe to our YouTube channel. Which type of statistics does…
Continue readingGitLab's challenge with Postgres LWLock lock_manager contention
16 November, 2023In E91 of “5mins of Postgres”, we're going to talk about lock_manager LWLock contention in Postgres and how the team at GitLab has addressed this production problem. Share this episode: Click here to share this episode on LinkedIn or on twitter. Feel free to sign up for our newsletter and subscribe to our YouTube channel. Debugging a problem with LWLock lock_manager contention Using pg_stat_activity for more insights Heavyweight locks vs. Lightweight Locks in Postgres When a fast path happens A…
Continue readingThe pg_stat_checkpointer view in Postgres 17
09 November, 2023Today, we're going to talk about the new in Postgres 17, and how that changed the , or background writer, statistics. We are also looking at the impact these changes have on shared buffers, the Postgres WAL, and . Share this episode: Click here to share this episode on LinkedIn or on twitter. Feel free to sign up for our newsletter and subscribe to our YouTube channel. pg_stat_checkpointer and its impact on pg_stat_bgwriter How this works in Postgres 16 The role of the checkpointer process The…
Continue readingPerformance implications of medium size values and TOAST in Postgres and how to mitigate them
02 November, 2023Today, we're going to talk about the surprising impact of medium sized texts on Postgres performance. We take a look at a great blog post by Haki Benita. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. Understanding medium size values and TOAST performance in Postgres Categories of text in your database How TOAST works TOAST Performance in Postgres Slow performance with medium sized values Indexes Mitigating the…
Continue readingPgBouncer 1.21 adds prepared statement support in transaction mode
26 October, 2023In this episode, we're going through the new PgBouncer 1.21 release which adds prepared statement support for PgBouncer when running in transaction mode. We talked about this previously in episode 73 of 5mins of Postgres a couple months ago and now this is actually released in a production version of PgBouncer. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. What is transaction mode in PgBouncer? Added support…
Continue readingWhat the new Rails 7.1 brings for Postgres users
19 October, 2023Today, we're going to talk about the new Ruby on Rails 7.1 release and what it brings for Postgres users. This is 5mins of Postgres episode 87. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. Rails 7.1 and Postgres Improvements to async queries in Rails 7.1 find_by_sql How Rails handles ActiveRecord load_async Composite primary keys in Rails 7.1 Compiling Common Table Expressions in Postgres with ActiveRecord…
Continue readingHOT Updates and BRIN indexes in Postgres 16
12 October, 2023Today, I want to talk about a new change in Postgres 16 that helps you ignore BRIN indexes when Postgres checks for HOT updates. Join me for 5mins of Postgres E86. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. Postgres Heap-Only-Tuples When can you do HOT Updates? BRIN Indexes no longer block HOT Updates in Postgres 16 or newer How do HOT updates exactly work? What we have discussed in this episode of 5mins of…
Continue readingPostgres I/O Basics, and how to efficiently pack table pages
05 October, 2023In E85 of “5mins of Postgres” we’ll talk about understanding Postgres IOPS and how Postgres structures its physical table structure. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. What is IOPS in PostgreSQL? IOPS capacity and burst IOPS Waiting on disk I/O Measuring Postgres IOPS track_io_timing EXPLAIN ANALYZE (BUFFERS) pg_stat_io How Postgres stores tuples in a table Load data faster with densely packed pages…
Continue reading4 improvements in Postgres 16 that you might not know about yet
28 September, 2023In E84 of “5mins of Postgres” we’ll talk about four new features in Postgres 16 that you might not know about yet. You’ll learn about pg_input_is_valid, pg_input_error_info, improvements to \watch, number separators in the SQL:2023 standard, and more! Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. pg_input_is_valid and pg_input_error_info Define how often \watch issues statements Separator for numbers in SQL…
Continue readingEXPLAIN (ANALYZE, BUFFERS) and interpreting shared buffers hit in Nested Loops
21 September, 2023In E83 of “5mins of Postgres” we're talking about using EXPLAIN (ANALYZE, BUFFERS) to optimize Postgres queries, and what to know when interpreting the shared hits counter for Nested Loops. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. The basics of EXPLAIN (ANALYZE, BUFFERS) Optimizing slow queries with the BUFFERS option How to interpret buffer counts The challenge with shared buffer hits How Nested Loops…
Continue readingPostgres 16 is released!
14 September, 2023In E82 of “5mins of Postgres” we're talking about Postgres 16, which was released today! There are a lot of exciting features in Postgres 16. I want to give you a quick rundown of a few things that I find exciting, as well as a few things that you might have not known about yet that people in the community pointed out in the last couple of weeks. We had talked about some of them previously in 5mins of Postgres episodes. We will link them at the end of this article! Share this episode: Click here…
Continue readingHow partition-wise joins and aggregates improve query performance
17 August, 2023In E81 of “5mins of Postgres” we're talking about how partition-wise joins can improve your Postgres query performance. We also look at partitionwise-aggregates and their impact on query planning time and query execution time. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. Benchmarking partitioning options in Postgres Enabling partition-wise operations in Postgres enable_partition_pruning enable_partitionwise…
Continue readingOptimizing bulk loads in Postgres, and how COPY helps with cache performance
10 August, 2023In episode 80 of “5mins of Postgres” we're going to talk about optimizing bulk load performance in Postgres and how the COPY ring buffer works. We're comparing different methods of INSERTs and show why COPY is the fastest option. We are also looking at pgbench and pgbuffercache to show client side vs server side performance. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. Using COPY for bulk loading data into…
Continue readingHandling outliers in BRIN indexes with the new multi minmax operator class
03 August, 2023In E79 of “5mins of Postgres” we're going to talk about when good correlation is not enough, the cases where BRIN indexes can be worse than having no index at all. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. When using an index can be slower than not using one How do BRIN indexes work? What does Postgres do with a BRIN index? When to use BRIN indexes Outliers and BRIN indexes Find outliers in BRIN indexes…
Continue readingPartitioning in Postgres and the risk of high partition counts
27 July, 2023In E78 of “5mins of Postgres”, we're going to talk about partitioning in Postgres and how it can either kill performance or make it a lot better. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. Decreased Postgres performance by using partitioning Increased overhead due to longer query planning time When planning time is longer than execution time for a query Increased Postgres performance by using partitioning…
Continue readingHow Table Access Methods in Postgres may offer a future without Bloat
20 July, 2023In episode 77 of “5mins of Postgres”, we are going to take closer look at VACUUM and a potential future without bloat in your database thanks to table access methods. In detail, we are looking at Alexander Korotkov's OrioleDB. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. VACUUM and table bloat in Postgres A new way of “VACUUMing” your Postgres database No bloat with OrioleDB, undo logs as a design decision…
Continue readingHow to optimize correlated subqueries in Postgres
13 July, 2023In episode 76 of “5mins of Postgres”, we are going to talk about how to optimize subqueries in Postgres by understanding the Postgres planner better. We look at correlated vs. uncorrelated subqueries, as well as scalar subqueries vs. tabular subqueries. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. How Postgres subqueries are used in a query What is a subquery? Correlated subqueries vs. uncorrelated subqueries…
Continue readingPostgres performance with IN vs ANY
06 July, 2023In E75 of “5mins of Postgres” we're going to talk about a surprising case Kaarel Moppel ran into, where using "IN" is faster than "ANY". We investigate this edge case in detail and are also looking at Tom Lane's response, who is one of the main authors of a lot of Postgres planner logic, to Kaarel's case. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. A surprising performance problem with single-element lists…
Continue readingStoring and querying vector data in Postgres with pgvector
29 June, 2023In today’s episode 74 of “5mins of Postgres” we're going to talk about vectors in Postgres. We are having a look at the claim that vectors are the new JSON in Postgres, are talk about Andrew Kane’s pgvector, and what work is needed in the Postgres core to support vectors and ML and AI work in Postgres better. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. How Postgres has adapted to AI and ML use cases using…
Continue readingSpeed up Postgres with transaction pooling with prepared statements
22 June, 2023In E73 of “5mins of Postgres” we're talking about how to use transaction pooling with prepared statements to make Postgres up to 30% faster. We're looking at improvements to pgbouncer, PgCat, and other connection poolers. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. Enabling Prepared Statements with Transaction Pooling for 30% Faster Queries Session mode vs. transaction mode How transaction pooling with…
Continue readingHow to use HOT Updates in Postgres to make CLUSTER work better
15 June, 2023In E72 of “5mins of Postgres” we're gonna talk about how to use HOT updates to use CLUSTER more effectively, and how we can utilize CLUSTER to get the last bit of performance out of an index scan. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. Utilizing CLUSTER to speed up index scans in Postgres Understanding table sorting via the correlation column The drawbacks of Postgres CLUSTER Using HOT Updates for…
Continue readingHow Figma and Notion scaled Postgres
08 June, 2023In episode 71 of “5mins of Postgres” we're going to talk about the growing pains of database architecture at Figma as well as alternate architectures, such as the sharding architecture chosen by Notion. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. How Figma scaled Postgres by partitioning tables between servers Read replicas and new databases for new use cases Partitioning data in Postgres to have different…
Continue readingHow bulk loading with COPY can be 300% times faster in Postgres 16 Beta 1
01 June, 2023In episode 70 of “5mins of Postgres” I want to talk about Postgres 16 Beta 1 being released and how one of its improvements can allow you to bulk load about 300% times faster with COPY. The reason for this being an improvement to relation extension locks. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. Getting started with Postgres 16 Beta 1 Speeding up Postgres performance in PG16 Beta 1 Improving relation…
Continue readingTuning huge pages in Postgres
25 May, 2023In E69 of “5mins of Postgres” we're going to talk about tuning huge pages in Postgres. We’ll explain what the Translation Lookaside Buffer is and how, starting with Postgres 15, Postgres can help you calculate how many huge pages to allocate. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. What are huge pages in Postgres? What does huge pages in Postgres do? How to configure the right huge_pages setting for…
Continue readingA Postmortem on High Memory Usage with Prepared Statements
18 May, 2023In today’s episode 68 of “5mins of Postgres” we're going to talk about how using partitioning together with prepared statements can lead to out of memory problems. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. How partitioning together with prepared statements can cause high memory usage in Postgres Debugging an out of memory event in Postgres Recycling connections more often sqlcommenter and trace parent…
Continue readingForcing Join Order in Postgres Using Optimization Barriers
11 May, 2023In today’s episode 67 of “5mins of Postgres” I want to talk about forcing join order in Postgres. We’ll walk through 3 techniques: Adding OFFSET “0” to a sub-SELECT, using common table expressions, and working with the join_collapse_limit. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. How to force the join order in Postgres with an optimizer barrier Subquery pull-ups in Postgres Adding OFFSET “0” to a sub…
Continue readingPostgres 16: Logical decoding on standbys
04 May, 2023In E66 of “5mins of Postgres” we're talking about logical decoding on a standby in Postgres 16, which allows us to keep following the logical replication stream whilst a failover happens. We’ll also touch upon the new pg_log_standby_snapshot function. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. Using logical decoding on standbys in Postgres 16 logical decoding can not be used while in recovery Create a…
Continue readingPostgres 16: Running EXPLAIN on any query (even with $1 parameters!)
27 April, 2023In episode 65 of “5mins of Postgres” we're going to talk about explaining generic plans in Postgres 16. We're looking at generic plans vs. custom plans, the benefits of generic plans and the limitations of EXPLAIN (GENERIC_PLAN). Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. EXPLAIN (GENERIC_PLAN) in Postgres 16 What are generic plans in Postgres? The GENERIC_PLAN option for EXPLAIN in Postgres 16 Limitations…
Continue readingPostgres 16: Buffer cache hit ratio and I/O times in pg_stat_io
20 April, 2023This is E64 of “5mins of Postgres”. Today, we talk about two improvements to the new pg_stat_io view in Postgres 16: tracking shared buffer hits and tracking I/O time. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. Tracking shared buffer hits and I/O times in pg_stat_io Track shared buffer hits in pg_stat_io Normal I/O operations Track I/O times in pg_stat_io What we have discussed in this episode of 5mins of…
Continue readingVacuum Cost Limit and Parallel Aggregate improvements in Postgres 16
13 April, 2023In episode 63 of “5mins of Postgres” we look at two new exciting features in Postgres 16: Updating the cost limit on the fly and using aggregate functions in parallel. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. Postgres 16: Enhancements for vacuum and parallel aggregate Updating vacuum cost limit on the fly in Postgres 16 Avoid stopping VACUUM and having to start a manual VACUUM Using two aggregate…
Continue readingPL/Rust 1.0 and its trusted language mode
06 April, 2023In E62 of “5mins of Postgres” we talk about PL/Rust, its 1.0 release and how it implements trusted language support in Postgres. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. PL/Rust: Running custom Rust code in Postgres PL/Rust trade-offs and benefits Initially compiling PL/Rust Trusted Languages in Postgres Using specific Rust crates postgrestd - The Postgres Standard Library, a rework of the Rust standard…
Continue readingCollations in Postgres
30 March, 2023In episode 61 of “5mins of Postgres” we’re talking about collations in Postgres. We’ll specifically take a look at glibc and ICU collations, and problems that can arise when using different operating system releases. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. Working with collations OS releases and database collation Collation versioning in Postgres What we have discussed in this episode of 5mins of…
Continue readingPostgres connection pooling: Comparing PgCat and pgbouncer
23 March, 2023In episode 60 of “5mins of Postgres” we’re talking about PgCat, a new connection pooler for Postgres, and how it compares to pgbouncer, which a lot of folks currently use. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. PgCat - A new Postgres connection pooler How Instacart adopted PgCat for Postgres connection pooling Postgres query load balancing in Ruby on Rails Comparing pgbouncer, Odyssey and PgCat How Rust…
Continue readingUUIDs vs Serial for Primary Keys - what's the right choice?
16 March, 2023In E59 of “5mins of Postgres” we’re talking about UUIDs vs Serials for primary keys. In our last episode, we talked about what happens if you use a regular four byte integer for your primary key and you then run out of space. Today, I want to talk about the scenario where you have made the right choice to use bigints in your system, but you're trying to decide whether you want to use UUIDs going forward. Or the other way around, if you are currently using UUIDs, and you're unsure if that's the…
Continue readingHandling integer sequence overflow without downtime in Postgres
09 March, 2023In episode 58 of 5mins of Postgres we're going to talk about handling integer sequence overflow in Postgres without downtime. We show how you can avoid integer overflow and how you can fix it once you've ran into it. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. What happens when a sequence reaches max value in Postgres? How to avoid integer sequence overflow in Postgres How to fix Postgres integer overflow…
Continue readingHow Postgres DBAs can use pg_stat_io
01 March, 2023In today’s episode 57 of 5mins of Postgres I want to talk about the pg_stat_io view and the everyday DBA perspective on why it matters. We’ll go through tracking activity in Postgres with pg_stat_io and talk about getting insights into extends, evictions, reuses, and more. The view is a new feature in the upcoming Postgres 16 release that was committed about three weeks ago. This was committed by Andres Freund, and was authored by Melanie Plageman. Share this episode: Click here to share this…
Continue readingUnlogged tables in Postgres: Is their data truly gone after a crash?
23 February, 2023In today’s E56 of 5mins of Postgres we’re talking about unlogged tables, reasons to use them for increased write performance, reduced vacuum impact and less total WAL, and reasons to not use them, because data is lost when Postgres crashes. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. What is an unlogged table in Postgres? Benefits of unlogged tables Disadvantages of unlogged tables Benchmarking unlogged…
Continue readingAvoid Postgres performance cliffs with MultiXact IDs and foreign keys
16 February, 2023In episode 55 of 5mins of Postgres we talk about performance cliffs encountered with MultiXact transaction IDs. We’ll also look at foreign key relationships and the Postgres VACUUM and autovacuum process. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. Avoiding performance cliffs with MultiXact IDs and foreign keys Postgres locking with foreign key relationships Understanding MultiXact in Postgres Caching…
Continue readingNever run VACUUM FULL: How to run pg_repack on Amazon RDS and Aurora
09 February, 2023In today’s E54 of 5mins of Postgres we talk about why you shouldn't use and how to use the alternative, pg_repack, on Amazon RDS and Aurora. We walk through pg_repack’s benefits, how to install it, and how it helped making one of our tables over 10x smaller. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. Why you should use pg_repack over VACUUM FULL in Postgres Problems with VACUUM FULL in Postgres Scenarios…
Continue readingTracing Locks in Postgres using eBPF programs
02 February, 2023In episode 53 of 5mins of Postgres, we talk about tracing Postgres locks using eBPF programs. Specifically, we’re looking at pg_lock_tracer and pw_lw_lock_tracer by Jan Nidzwetzki. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. pg_lock_tracer: An overview What is an eBPF program (Berkeley Packet Filter)? What does pg_lock_tracer do? How to install pg_lock_tracer Using pg_lw_lock_tracer to see trace points in…
Continue readingPostgres 16: Surviving without a superuser & reserved_connections
26 January, 2023In episode 52 of 5mins of Postgres, we talk about the createrole_self_grant option in Postgres 16, which lets us create a role that can administer the database but not break out of it. We also talk about the pg_use_reserved_connections role. All in all, Postgres 16 and above will make it easier to manage roles consistently and make sure that you grant the right permissions to the right users. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and…
Continue readingUsing Memoize to speed up joins between two Postgres tables
19 January, 2023This is episode 51 of 5mins of Postgres, and today we're going to talk about how to use Memoize to speed up joins between two tables. We’re looking at presentations from Bruce Momjian and Lukas Eder and quickly explain a bug with Memoize in Postgres versions earlier than 14.4. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. What is the Memoize plan node in Postgres? Benchmarking Postgres Memoize Postgres Memoize…
Continue readingUsing pgbench to load data faster, and the random_normal function in PG16
12 January, 2023Today, in episode 50 of 5mins of Postgres, we're going to talk about two things. We're going to talk about Kaarel Moppel's post on how to generate lots of test data with Postgres, using pgbench to load data fast and faster, and we're going to talk about the new "random_normal" function that was recently added to Postgres 16. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. Why you would want to test a large table…
Continue readingSpeed up Postgres queries with UNIONs and subquery pull-up
22 December, 2022Today, we're gonna talk about Postgres UNION. We'll look into the differences of Postgres UNION vs. OR and are investigating if using UNION is always the better choice. Quick answer: It is not. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. Postgres UNION Performance Using UNION vs. OR in Postgres Is UNION always faster than OR? Understanding Postgres' UNION ALL Postgres UNION and subqueries What we have…
Continue readingBenchmarking multi-column, covering and hash indexes in Postgres
15 December, 2022Today, we talk about benchmarking multi-column vs. multiple combined indexes in Postgres, and whether B-Tree or hash indexes are a better fit. We also look into cases and workloads where different indexes are better than other ones. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel. Benchmarking indexes in Postgres Postgres benchmarking with UNLOGGED tables Benchmarking Postgres index options Benchmarking a…
Continue readingRoaring Bitmaps and pgfaceting: Fast counting across large datasets in Postgres
08 December, 2022In E47 of 5mins of Postgres, we talk about counting and faceting large result sets using Roaring Bitmaps in Postgres. We are looking at the pg_roaringbitmap extension and the pgfaceting extension which help us improve our query performance from 222 seconds to 155 milliseconds. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Faster Postgres query performance with pgfaceting…
Continue readingCreating custom extensions on Amazon RDS and Aurora with pg_tle
01 December, 2022In today's episode 46 of 5mins of Postgres, we talk about trusted language extensions and how to use them on Amazon RDS and Aurora, as well as the new pg_tle extension. We also take a look at pg_tle's support for the "password check hook". Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Trusted language extensions for Postgres Postgres CREATE EXTENSION pg_tle: Postgres…
Continue readingPostgreSQL ANY vs IN lists: Bind parameters and performance
24 November, 2022Today, we look at the difference between PostgreSQL's ANY and IN operators, how they differ in performance, and why you might use one or the other. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. PostgreSQL security: ANY operator vs. IN lists PostgreSQL's ANY operator and bind parameters PostgreSQL performance: ANY operator vs. IN lists Allow hash lookup for IN clauses with…
Continue reading5mins of Postgres E44: Reducing replication lag with maintenance_io_concurrency in Postgres 15
17 November, 2022Today, we're talking about reducing replication lag by setting the maintenance_io_concurrency setting in Postgres 15. We're also talking about true asynchronous I/O in Postgres and how to use "recovery prefetch". Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Using maintenance_io_concurrency to reduce replication lag How to use maintenance_io_concurrency in Postgres 1…
Continue reading5mins of Postgres E43: Logical replication in Postgres 15 & why the REPLICA IDENTITY matters
10 November, 2022Today, we're going to talk about logical replication improvements in Postgres 15. In particular, we’ll talk about improvements to data filtering, why the REPLICA IDENTITY is important, and how logical replication deals with errors. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Postgres 15 logical replication improvements Logical replication in Postgres 15: Defining which…
Continue reading5mins of Postgres E42: A surprising case of very large integers causing a Sequential Scan
03 November, 2022Today, we talk about a surprising case of a very large integer causing a Sequential Scan in Postgres. There are cases when Postgres decides to not use an index, but instead opts for a Sequential Scan. This can cause some very real performance problems. Let’s have a look at why it does this and ways to resolve this issue! Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel…
Continue reading5mins of Postgres E41: Tuning shared_buffers for OLTP and data warehouse workloads & is 25% of RAM a good choice?
27 October, 2022In this episode of 5mins of Postgres, we’re going to talk about tuning shared_buffers, benchmarking with pgbench, TPROC-C and TPROC-H, and whether using 25% of RAM is a good choice. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Tuning shared_buffers in Postgres Benchmarking Postgres performance Benchmarking Postgres with pgbench Using pg_buffercache TPROC-C and TPROC-H…
Continue reading5mins of Postgres E40: How to debug slow Postgres UPDATEs and bloated tables with auto_explain & page_inspect
20 October, 2022Today, we talk about why Postgres UPDATEs are getting slower over time, HOT updates, auto_explain, the pageinspect extension, and reasons why VACUUM won’t remove dead rows from a table. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Debugging slow Postgres UPDATEs and bloated tables Understanding Postgres queries with auto_explain Using log_min_duration and log_timing…
Continue reading5mins of Postgres E39: Postgres 15 release, and 6 tips for managing indexes
13 October, 2022Today, we're going to talk about the Postgres 15 release, and six findings to improve how you manage your Postgres indexes. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Postgres 15 is released Managing Postgres indexes Each individual index has storage and write costs Postgres can choose different ways to query indexed data Combined indexes versus multi-column indexes in…
Continue reading5mins of Postgres E38: When to use BRIN indexes in Postgres, tuning pages_per_range and determining correlation with pg_stats
06 October, 2022In this article and video, we talk about tuning a Postgres BRIN index and how to decide whether to use BRIN based on pg_stats statistics and the correlation column. We also talk about the impact block ranges can have and a visual model on how to think about BRIN indexes. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. How to tune BRIN indexes to speed up Postgres performance…
Continue reading5mins of Postgres E37: New in Postgres 15: Logical replication column filters & database-wide ICU collations
29 September, 2022Today, we talk about new features in Postgres 15, including logical replication improvements and database wide ICU locales. In case you're not aware, Postgres 15 rc1 is around the corner and we can expect the final release of Postgres 15 within the next couple of weeks. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Using column lists in logical replication to filter data…
Continue reading5mins of Postgres E36: Optimizing Postgres query throughput in Python using pipeline mode and psycopg 3.1
22 September, 2022Today, we're going to talk about the new psycopg driver for Python. We're also going to talk about pipeline mode in psycopg and how it works. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. psycopg 3.1 for Python and Postgres Pipeline mode in psycopg 3.1 and Postgres Using connection.pipeline in psycopg Getting 2x - 10x better Postgres performance with pipeline mode What we…
Continue reading5mins of Postgres E35: How to run EXPLAIN on normalized queries from pg_stat_statements using plan_cache_mode
15 September, 2022In today’s episode we're going to talk about how to run EXPLAIN on a normalized query from pg_stat_statements and how to get the generic query plan. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Postgres EXPLAIN plans and pg_stat_statements What is pg_stat_statements in Postgres Can we use EXPLAIN by passing null? Using plan_cache_mode in Postgres to force the query planner…
Continue reading5mins of Postgres E34: Making the most of Parallel VACUUM and why SQL/JSON was pushed out to Postgres 16
08 September, 2022In this episode, we're walking through parallel VACUUMing in Postgres 13 and newer. We're also going to talk about the unfortunate fact that SQL/JSON won't be in Postgres 15. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. VACUUM and Parallel VACUUM in Postgres Postgres VACUUM: The 4 phases Parallel VACUUM in Postgres Understanding VACUUM parameters in Postgres The max_worker…
Continue reading5mins of Postgres E33: Postgres on Kubernetes, choosing the right operator, and handling major version upgrades
01 September, 2022Today, we're gonna talk about Postgres on Kubernetes, how it has improved over the years and two improvements to Postgres operators in Kubernetes in recent times. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Postgres and Kubernetes Postgres Operators Importing an Existing Postgres Database to Kubernetes with CloudNativePG Postgres Upgrades with the PGO operator What we…
Continue reading5mins of Postgres E32: Benchmarking and improving query network latency using tc, COPY and pipeline mode
25 August, 2022Today, we're gonna talk about network latency, how it impacts your query performance and how to improve it by using pipeline mode or COPY in Postgres. We also take a look at traffic control with the Linux tc tool. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Improving network latency in Postgres Using tc (traffic control) in Linux and Postgres Using \timing in psql COPY vs…
Continue reading5mins of Postgres E31: Postgres security patch release, spotting vulnerable extensions and securing the public schema
18 August, 2022Today, we walk through the latest Postgres patch releases and a security bug that got fixed. We'll also talk about extension security in Postgres, as well as the public schema and a change in Postgres 15 to make it more secure. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Latest PostgreSQL patches Extension scripts replace objects not belonging to the extension: bug fix…
Continue reading5mins of Postgres E30: Postgres performance puzzle, Linux readahead and tuning effective_io_concurrency
11 August, 2022Today, we're going to talk about an interesting Postgres performance puzzle by Jeremy Schneider, Linux readahead and how to tune effective_io_concurrency. The default Linux readahead setting assumes sequential scans ahead, but you might want to change it, together with effective_io_concurrency, which tunes how much Postgres is telling the Linux Kernel to look ahead. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive…
Continue reading5mins of Postgres E29: Postgres lock conflicts on Amazon Aurora & tuning max_standby_streaming_delay and hot_standby_feedback
04 August, 2022Today, we're talking about Postgres lock conflicts on Amazon Aurora and tuning and , why you should implement retry logic with Aurora and how you can avoid query conflicts on Postgres with Amazon Aurora's fast clones. We’ll also look at an effective setup from the Twitch team for these settings. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. The difference between Amazon…
Continue reading5mins of Postgres E28: Row Level Security in Postgres, security invoker views and why LEAKPROOF functions matter
28 July, 2022Today, we're going to talk about Postgres Row Level Security, the BYPASSRLS attribute, security invoker views, and Postgres performance problems when using LEAKPROOF functions and Row Level Security policies. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Using Postgres Row Level Security for multi-tenant databases Row Level Security in Postgres and the BYPASSRLS attribute…
Continue reading5mins of Postgres E27: Postgres 16 - Make subquery alias optional in FROM clause
21 July, 2022Today, we are talking about a change in Postgres 16, that removes the alias requirement for subqueries in FROM. This may seem like a very obscure problem, but it's actually a very common issue for people that either migrate to Postgres, for example from Oracle to Postgres, or who are new to Postgres and run into this error. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel…
Continue reading5mins of Postgres E26: New in Postgres 15 - NULL handling in UNIQUE constraints & Parallel DISTINCT
14 July, 2022Today, we are talking about two new features in Postgres 15. First of all, we're going to talk about the new unique constraint NULLS NOT DISTINCT option, and then we'll talk about the SELECT DISTINCT performance improvements. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. UNIQUE constraints and NULL values The behavior in older Postgres versions Rejecting duplicate NULL…
Continue reading5mins of Postgres E25: Postgres lock monitoring, LWLocks and the log_lock_waits setting
07 July, 2022Today, we are talking about Postgres locking. When it is concerning, what lightweight locks are, as well as the setting in Postgres. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Postgres lock monitoring with the pg_locks view Counting how many locks are not granted Finding the blocking process when a query is waiting for a lock Understanding lightweight locks (LWLocks…
Continue reading5mins of Postgres E24: Tuning the Postgres statistics target & understanding selectivity for spatial indexes
30 June, 2022Today, we are talking about tuning the Postgres statistics target, why it is 100 and how that number was derived, and Postgres selectivity, specifically selectivity for spatial indexes. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Tuning the Postgres statistics target Changing the Postgres statistics target on a per column basis Selectivity in Postgres Selectivity for…
Continue reading5mins of Postgres E23: Fuzzy text search & case-insensitive ICU collations in Postgres
23 June, 2022Today, we're going to talk about fuzzy text search in Postgres with LIKE/ILIKE, trigrams, levenshtein distances, as well as case-insensitive pattern matching. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Fuzzy text search in Postgres Searching with LIKE/ILIKE Searching with regular expressions Full-Text search in Postgres Searching with trigrams Using the levenshtein…
Continue reading5mins of Postgres E22: Reducing AWS Aurora I/O costs with table partitioning & understanding partition pruning
16 June, 2022Today, we're going to talk about using partitioning to reduce AWS Aurora I/O costs, pg_partman, and how partition pruning speeds up query performance. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Using Postgres partition pruning to reduce AWS Aurora I/O costs Improving Postgres query performance and reducing I/O costs with partition pruning Partition Pruning in PostgreSQL…
Continue reading5mins of Postgres E21: Server-side backup compression with LZ4 and Zstandard in Postgres 15, and the removal of exclusive backup mode
09 June, 2022In today’s episode 21, we're going to talk about backup improvements in Postgres 15. Postgres 15 beta1 came out a couple of weeks ago and we’re looking into LZ4 and Zstandard compression, as well as the removal of the exclusive backup mode. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Removal of the exclusive backup mode in Postgres 15 Using Server-Side LZ4 Backup…
Continue reading5mins of Postgres E20: An important bug in Postgres 14 with REINDEX / CREATE INDEX CONCURRENTLY, and using the amcheck extension
02 June, 2022Today, we're going to talk about an important indexing bug that was discovered in Postgres 14 and how you can verify if this affects you with the amcheck extension. Share this episode: Click here to share this episode on twitter and subscribe to our YouTube channel. REINDEX / CREATE INDEX CONCURRENTLY bug in Postgres 14 Understanding the amcheck extension in Postgres Using the bt_index_check function in amcheck for Postgres What we have discussed in this episode of 5mins of Postgres Transcript…
Continue reading5mins of Postgres E19: Speeding up sort performance in Postgres 15
26 May, 2022Today, we're going to talk about four specific improvements made in Postgres 15 that help speeding up sort performance: Sorting a single column Reducing memory consumption by using a different memory allocator New specialized sort routines for common data types Replacing the algorithm that's used for splitting up sorts to disk when they exceed the "work_mem". Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and…
Continue reading5mins of Postgres E18: Partition pruning, prepared statements and generic vs custom query plans
19 May, 2022In today’s episode 18, we'll talk about partition pruning and prepared statements in Postgres and run a performance test using the pgbench tool. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Performance of prepared statements with partitioning in Postgres Understanding prepared statements in Postgres Partition pruning in Postgres What we have discussed in this episode of…
Continue reading5mins of Postgres E17: Demystifying Postgres for application developers: A mental model for tables and indexes
12 May, 2022Today, we're going to talk about demystifying database performance for developers. This blog post by Christopher Winslett talks about how to think about your database as an application developer and what kind of mental model you can have to think effectively about how to use indexes and other data structures in your database. We will talk about Postgres Tables, Indexes, Index Cardinality, Table scans, and more! Share this episode: Click here to share this episode on twitter, or sign up for our…
Continue reading5mins of Postgres E16: Incremental Materialized Views with pg_ivm and Looking Ahead to Postgres 15 Beta 1
05 May, 2022Today, we're going to talk about incremental view maintenance, as well as the upcoming Postgres 15 beta release. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Using Incremental Materialized Views with pg_ivm Performance of Incremental Materialized Views with pg_ivm A first look at Postgres 15 Beta 1 What we have discussed in this episode of 5mins of Postgres Transcript Let…
Continue reading5mins of Postgres E15: Tuning Postgres' Deterministic Query Planner, Extended Statistics and Join Collapse Limits
28 April, 2022Today, we'll talk about optimizing with the Postgres deterministic query planner, have a look at using extended statistics, and show how to tune Join collapse times. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Optimizing with Postgres’ deterministic query planner Setting costs for page access Using Extended Statistics in Postgres CREATE STATISTICS and the dependencies…
Continue reading5mins of Postgres E14: HOT Updates vs Bottom-Up Index Deletion in Postgres 14
21 April, 2022Today, we're going to talk about bottom-up index deletion for B-tree indexes, Postgres pages, Postgres page splits in B-Tree indexes, the bt_page_items function, and how Bottom-Up Index Deletions compare to HOT updates. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Comparing HOT Updates and Bottom-Up Index Deletion Understanding Postgres pages Postgres page splits in B-Tree…
Continue reading5mins of Postgres E13: New SQL/JSON and JSON_TABLE features in Postgres 15
14 April, 2022In today's video, we take a look at the SQL/JSON features in Postgres 15. Postgres 15 will most likely be released in September or October, so it's still quite a way until we can actually use this on a production server. However, a couple of days ago, the Postgres 15 feature freeze occurred, which means we now have a good sense for which functionality will make it into this next Postgres release. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and…
Continue reading5mins of Postgres E12: The basics of tuning VACUUM and autovacuum
07 April, 2022Today, we're going to talk about tidying up with VACUUM. Now, as the Postgres Weekly newsletter put it, we can't go a month without covering VACUUM in some way or another. If you recall a few weeks ago, we last talked about "dead tuples not yet removable" and what is essentially a very specific VACUUM problem that causes VACUUM to stall and not make progress. You can find the episodes about this here: 5mins of Postgres E7: Autovacuum, dead tuples not yet removable, and the Postgres xmin horizon…
Continue readingPostgres INSERT ON CONFLICT and how it compares to MERGE in Postgres 15
31 March, 2022Today, in episode 11 of our series, we are taking a look at the MERGE command and how it differs from Postgres' INSERT ON CONFLICT command. Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. MERGE in Postgres 15 How the MERGE command works in Postgres Postgres' INSERT ON CONFLICT command: Why does it exist? What we have discussed in this episode of 5mins of Postgres Transcript…
Continue reading5mins of Postgres E10: max_wal_size, Postgres full page writes and UUID vs BIGINT primary keys
24 March, 2022In today’s episode 10, we'll take a look at max_wal_size, full-page writes and why you might want to choose BIGINT instead of a UUID for your primary key column. Share this episode: Click here to post to twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Tuning max_wal_size in PostgreSQL What is max_wal_size Optimizing max_wal_size and checkpoint_timeout The impact of full-page writes in Postgres Using UUID vs BIGINT data types for…
Continue reading5mins of Postgres E9: PostGIS vs GPUs, tuning parallel queries, and automating Citus extension benchmarks with HammerDB
17 March, 2022In this episode, we'll talk about PostGIS versus GPU performance for spatial queries and also take a look at using HammerDB and custom automation scripts to benchmark the Citus extension. Share this episode: Click here to post to twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Transcript Looking at PostGIS vs. GPU for Performance and Spatial Joins The importance of automating performance benchmarks Using HammerDB to drive a TPC-C…
Continue reading5mins of Postgres E8: Postgres Auditing: Table triggers with supabase vs the pgAudit extension
10 March, 2022Today, we'll take a look at Postgres auditing. We'll look at two different approaches: Using triggers in Postgres Using the pgAudit extension Share this episode: Click here to post to twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Transcript Using triggers in Postgres for Postgres Auditing Using the pgAudit extension for Postgres Auditing What we have discussed in this episode of 5mins of Postgres Transcript Let's jump in! Using…
Continue reading5mins of Postgres E7: Autovacuum, dead tuples not yet removable, and the Postgres xmin horizon
03 March, 2022Today, we'll talk about autovacuum, dead tuples not yet removable, and what the xmin horizon in Postgres does. Share this episode: Click here to post to twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Transcript Vacuuming dead tuples not yet removable in Postgres When vacuum doesn’t remove dead rows from a table 1) Long running transactions 2) Replication slots 3) Prepared transactions What we have discussed in this episode of…
Continue reading5mins of Postgres E6: Optimizing Postgres Text Search with Trigrams and GiST indexes
24 February, 2022Today, we're looking at optimizing Postgres text search with trigrams and take a closer look at GiST indexes. Share this episode: Click here to post to twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Transcript Improving Postgres text search performance with trigrams Understanding the siglen parameter How GiST indexes work What we have discussed in this episode of 5mins of Postgres Transcript Let's have a look together! Improving…
Continue reading5mins of Postgres E5: Finding the root cause of high CPU utilization in Postgres with EXPLAIN and the Linux perf command
17 February, 2022Today, we'll take a look at a hairy Postgres incident and how we can use Postgres EXPLAIN and the Linux perf command to debug high CPU utilization in Postgres. Share this episode: Click here to post to twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Transcript Debugging a 100% CPU utilization spike with EXPLAIN and perf Comparing the old system vs. the new system Going one step deeper: Linux system level tools Profiling with the…
Continue reading5mins of Postgres E4: Writing your own custom Postgres aggregates with Rust or raw SQL
10 February, 2022Today we'll take a look at how to write custom aggregates in Postgres using either Rust or raw SQL. Share this episode: Click here to post to twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Transcript Using pgx to write a Rust extension for Postgres that runs custom aggregate code Using raw SQL to implement harmonic mean and geometric mean in Postgres Using aggregation in Postgres to refine hyperfunctions design What we have…
Continue reading5mins of Postgres E3: Postgres performance cliffs with large JSONB values and TOAST
03 February, 2022Today, we're going to talk about JSONB and how JSONB performance can behave badly when the JSONB value exceeds the two kilobyte limit where Postgres starts storing data in TOAST. Share this episode: Click here to post to twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel. Transcript How Postgres stores data: Large JSON value query performance PostgreSQL physical storage Understanding JSONB performance What we have discussed in this…
Continue reading5mins of Postgres E2: Using unnest(..), generate_series(), and PostGIS
27 January, 2022Today, we're gonna look at a few interesting aspects on how to work with data sets in Postgres. First of all, we'll take look at using for performing bulk operations on a larger number of values. Second, we'll take a look at and how to generate sample data if you do not have large amounts of input data. Last, but not least, we'll take a look at a PostGIS community example, where somebody had a particular challenge, indexing PostGIS data, and we'll take a look at how a PostGIS core team member…
Continue reading5mins of Postgres E1: Using Postgres statistics to improve bad query plans, pg_hint_plan extension
20 January, 2022Hello, and welcome to 5 minutes of Postgres! Today, we are starting a new weekly video series where we'll walk through interesting articles about Postgres from the last week, as well as evergreen blog posts from the past, and provide context and our personal perspective on it. We are planning to publish a new episode of this series every Thursday, so keep an eye out! We'll always embed the video first, but below it you can find a transcription in case you want to read through what we discussed…
Continue reading