Postgres Monitoring, Database Optimization and more

EXPLAIN (ANALYZE, BUFFERS) and interpreting shared buffers hit in Nested Loops

21 September, 2023

In 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…

Postgres 16 is released!

14 September, 2023

In 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…

How partition-wise joins and aggregates improve query performance

17 August, 2023

In 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…

Optimizing bulk loads in Postgres, and how COPY helps with cache performance

10 August, 2023

In 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…

Handling outliers in BRIN indexes with the new multi minmax operator class

03 August, 2023

In 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…

Partitioning in Postgres and the risk of high partition counts

27 July, 2023

In 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…

Introducing pganalyze VACUUM Advisor: Workload-aware autovacuum tuning for Postgres

25 July, 2023

VACUUM in Postgres is a fact of life - every Postgres installation out there today, including those on AWS Aurora and Google AlloyDB, has to run autovacuum on each table at least every 2 billion transactions to perform freezing. And if you'd like to keep the active portion of your tables in memory, a frequent vacuuming cycle ensures tables stay small, and queries remain fast, with minimal bloat. Oftentimes, when your database is still small, you don't have to think about VACUUM and autovacuum…

How Table Access Methods in Postgres may offer a future without Bloat

20 July, 2023

In 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…

How to optimize correlated subqueries in Postgres

13 July, 2023

In 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…

Postgres performance with IN vs ANY

06 July, 2023

In 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…

Storing and querying vector data in Postgres with pgvector

29 June, 2023

In 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…

Speed up Postgres with transaction pooling with prepared statements

22 June, 2023

In 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…

How to use HOT Updates in Postgres to make CLUSTER work better

15 June, 2023

In 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…

How Figma and Notion scaled Postgres

08 June, 2023

In 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…

How bulk loading with COPY can be 300% times faster in Postgres 16 Beta 1

01 June, 2023

In 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…

Tuning huge pages in Postgres

25 May, 2023

In 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…

A Postmortem on High Memory Usage with Prepared Statements

18 May, 2023

In 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…

Forcing Join Order in Postgres Using Optimization Barriers

11 May, 2023

In 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…

Waiting for Postgres 16: Logical decoding on standbys

04 May, 2023

In 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…

Waiting for Postgres 16: Running EXPLAIN on any query (even with $1 parameters!)

27 April, 2023

In 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…

Waiting for Postgres 16: Buffer cache hit ratio and I/O times in pg_stat_io

20 April, 2023

This 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…

Vacuum Cost Limit and Parallel Aggregate improvements in Postgres 16

13 April, 2023

In 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…

PL/Rust 1.0 and its trusted language mode

06 April, 2023

In 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…

Collations in Postgres

30 March, 2023

In 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…

Postgres connection pooling: Comparing PgCat and pgbouncer

23 March, 2023

In 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…

UUIDs vs Serial for Primary Keys - what's the right choice?

16 March, 2023

In 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…

Handling integer sequence overflow without downtime in Postgres

09 March, 2023

In 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…

How Postgres DBAs can use pg_stat_io

01 March, 2023

In 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…

Unlogged tables in Postgres: Is their data truly gone after a crash?

23 February, 2023

In 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…

Avoid Postgres performance cliffs with MultiXact IDs and foreign keys

16 February, 2023

In 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…

Waiting for Postgres 16: Cumulative I/O statistics with pg_stat_io

14 February, 2023

One of the most common questions I get from people running Postgres databases at scale is: How do I optimize the I/O operations of my database? Historically, getting a complete picture of all the I/O produced by a Postgres server has been challenging. To start with, Postgres splits its I/O activity into writing the WAL stream, and reads/writes to the data directory. The real challenge is understanding second-order effects around writes: Typically the write to the data directory happens after the…

Never run VACUUM FULL: How to run pg_repack on Amazon RDS and Aurora

09 February, 2023

In 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…

Tracing Locks in Postgres using eBPF programs

02 February, 2023

In 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…

Postgres 16: Surviving without a superuser & reserved_connections

26 January, 2023

In 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…

Using Memoize to speed up joins between two Postgres tables

19 January, 2023

This 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…

Using pgbench to load data faster, and the random_normal function in PG16

12 January, 2023

Today, 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…

Speed up Postgres queries with UNIONs and subquery pull-up

22 December, 2022

Today I want to start by saying: Thank you! This is episode 49 of 5mins of Postgres, and almost a year ago we started with the first episode. And back then, I wouldn't have believed if you told me we would get a thousand subscribers on our YouTube channel and this many people listening and commenting to each episode.So I really appreciate everyone's time in watching and learning more about Postgres and giving me a good reason to look at what's new with Postgres every week. This is our last…

Benchmarking multi-column, covering and hash indexes in Postgres

15 December, 2022

Today, 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…

Roaring Bitmaps and pgfaceting: Fast counting across large datasets in Postgres

08 December, 2022

In 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…

Lock monitoring in Postgres: Find blocking queries in a lock tree with pganalyze

01 December, 2022

Postgres databases power many mission critical applications, and applications expect consistent query performance. If even a single query takes longer than expected, it can lead to unhappy users, or delayed background processes. We can use to debug a slow query, but there is one Postgres problem it won't tell us about: Blocked queries. You may also know this as "blocked sessions" from other database systems. This is when one query holds a lock on a table and the other is waiting for those locks…

Creating custom extensions on Amazon RDS and Aurora with pg_tle

01 December, 2022

In 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…

5mins of Postgres E45: IN lists vs ANY operator: Bind parameters and performance

24 November, 2022

Today, we're going to talk about the difference between the ANY and the IN operator in Postgres, 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. Postgres security: IN lists vs. ANY operator Using the ANY operator and bind parameters Postgres performance: IN lists vs. ANY operator Allow hash lookup for IN…

5mins of Postgres E44: Reducing replication lag with maintenance_io_concurrency in Postgres 15

17 November, 2022

Today, 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…

5mins of Postgres E43: Logical replication in Postgres 15 & why the REPLICA IDENTITY matters

10 November, 2022

Today, 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…

5mins of Postgres E42: A surprising case of very large integers causing a Sequential Scan

03 November, 2022

Today, 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…

5mins of Postgres E41: Tuning shared_buffers for OLTP and data warehouse workloads & is 25% of RAM a good choice?

27 October, 2022

In 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…

5mins of Postgres E40: How to debug slow Postgres UPDATEs and bloated tables with auto_explain & page_inspect

20 October, 2022

Today, 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…

5mins of Postgres E39: Postgres 15 release, and 6 tips for managing indexes

13 October, 2022

Today, 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…

5mins of Postgres E38: When to use BRIN indexes in Postgres, tuning pages_per_range and determining correlation with pg_stats

06 October, 2022

In 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…

5mins of Postgres E37: New in Postgres 15: Logical replication column filters & database-wide ICU collations

29 September, 2022

Today, 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…

5mins of Postgres E36: Optimizing Postgres query throughput in Python using pipeline mode and psycopg 3.1

22 September, 2022

Today, 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…

5mins of Postgres E35: How to run EXPLAIN on normalized queries from pg_stat_statements using plan_cache_mode

15 September, 2022

In 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…

5mins of Postgres E34: Making the most of Parallel VACUUM and why SQL/JSON was pushed out to Postgres 16

08 September, 2022

In 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…

5mins of Postgres E33: Postgres on Kubernetes, choosing the right operator, and handling major version upgrades

01 September, 2022

Today, we're gonna talk about Postgres on Kubernetes, how it has improved over the years and two improvements to operators 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 Using PostgreSQL Operators Importing an Existing Postgres Database to Kubernetes with CloudNativePG Postgres Upgrades with the PGO operator What we have discussed in…

5mins of Postgres E32: Benchmarking and improving query network latency using tc, COPY and pipeline mode

25 August, 2022

Today, 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…

5mins of Postgres E31: Postgres security patch release, spotting vulnerable extensions and securing the public schema

18 August, 2022

Today, 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…

5mins of Postgres E30: Postgres performance puzzle, Linux readahead and tuning effective_io_concurrency

11 August, 2022

Today, 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…

5mins of Postgres E29: Postgres lock conflicts on Amazon Aurora & tuning max_standby_streaming_delay and hot_standby_feedback

04 August, 2022

Today, 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…

5mins of Postgres E28: Row Level Security in Postgres, security invoker views and why LEAKPROOF functions matter

28 July, 2022

Today, 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…

5mins of Postgres E27: Waiting for Postgres 16 - Make subquery alias optional in FROM clause

21 July, 2022

Today, 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…

5mins of Postgres E26: New in Postgres 15 - NULL handling in UNIQUE constraints & Parallel DISTINCT

14 July, 2022

Today, 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…

5mins of Postgres E25: Postgres lock monitoring, LWLocks and the log_lock_waits setting

07 July, 2022

Today, 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…

5mins of Postgres E24: Tuning the Postgres statistics target & understanding selectivity for spatial indexes

30 June, 2022

Today, 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…

5mins of Postgres E23: Fuzzy text search & case-insensitive ICU collations in Postgres

23 June, 2022

Today, 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…

5mins of Postgres E22: Reducing AWS Aurora I/O costs with table partitioning & understanding partition pruning

16 June, 2022

Today, 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…

5mins of Postgres E21: Server-side backup compression with LZ4 and Zstandard in Postgres 15, and the removal of exclusive backup mode

09 June, 2022

In 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…

5mins of Postgres E20: An important bug in Postgres 14 with REINDEX / CREATE INDEX CONCURRENTLY, and using the amcheck extension

02 June, 2022

Today, 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…

A balanced approach to automatic Postgres indexing: The new version of the pganalyze Index Advisor

01 June, 2022

Historically, index creation, tuning and maintenance has been the task of database administrators who had a detailed understanding of the different queries used by applications. The fast-moving pace of modern application development, combined with a move to the cloud, has shifted the responsibility of indexing to application developers - without giving them the right tools. Application developers today spend a lot of time manually creating indexes for their Postgres queries, reviewing database…

An automatic indexing system for Postgres: How we built the pganalyze Indexing Engine

01 June, 2022

Indexing is hard. Most people who have worked with databases will agree that regardless of how you do it, creating the right indexes on your database requires thought and detailed analysis. It's not something to be taken lightly. If you don't create the right indexes, your queries will be slow, and if you create too many indexes, your writes on busy tables will be slowed down significantly. But even if you know how to do it, indexing takes a lot of effort. And this has gotten more complex with…

5mins of Postgres E19: Speeding up sort performance in Postgres 15

26 May, 2022

Today, 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…

Design at pganalyze: How we use design principles to develop new features and products

24 May, 2022

At pganalyze, we know attention and time is very valuable and being respectful of it will allow everyone on the team to think deeply and do high-quality work. That’s why we run only a few standing meetings. It’s one actually. A weekly meeting on Tuesdays. Most of our active working time goes into product development, infrastructure improvements, and working with customers. Apart from that, it is on each team member to organize and schedule working sessions, support triages, or workshops. We are…

5mins of Postgres E18: Partition pruning, prepared statements and generic vs custom query plans

19 May, 2022

In 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…

5mins of Postgres E17: Demystifying Postgres for application developers: A mental model for tables and indexes

12 May, 2022

Today, 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…

5mins of Postgres E16: Incremental Materialized Views with pg_ivm and Looking Ahead to Postgres 15 Beta 1

05 May, 2022

Today, 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…

The Unexpected Find That Freed 20GB of Unused Index Space

04 May, 2022

How to free space without dropping indexes or deleting data Every few months we get an alert from our database monitoring to warn us that we are about to run out of space. Usually we just provision more storage and forget about it, but this time we were under quarantine, and the system in question was under less load than usual. We thought this is a good opportunity to do some cleanups that would otherwise be much more challenging. To start from the end, we ended up freeing more than 70GB of un…

5mins of Postgres E15: Tuning Postgres' Deterministic Query Planner, Extended Statistics and Join Collapse Limits

28 April, 2022

Today, 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…

5mins of Postgres E14: HOT Updates vs Bottom-Up Index Deletion in Postgres 14

21 April, 2022

Today, 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…

5mins of Postgres E13: New SQL/JSON and JSON_TABLE features in Postgres 15

14 April, 2022

In 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…

5mins of Postgres E12: The basics of tuning VACUUM and autovacuum

07 April, 2022

Today, 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…

How Postgres Chooses Which Index To Use For A Query

01 April, 2022

Using Postgres sometimes feels like magic. But sometimes the magic is too much, such as when you are trying to understand the reason behind a seemingly bad Postgres query plan. I've often times found myself in a situation where I asked myself: "Postgres, what are you thinking?". Staring at an EXPLAIN plan, seeing a , and being puzzled as to why Postgres isn't doing what I am expecting. This has lead me down the path of reading the Postgres source, in search for answers. Why is Postgres choosing…

5mins of Postgres E11: MERGE in Postgres 15, and how it compares to INSERT ON CONFLICT

31 March, 2022

Today, in episode 11 of our series, we are taking a look at the command and how it differs from the 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 Why there is an INSERT ON CONFLICT command in Postgres What we have discussed in this episode of 5mins of Postgres Transcript Let's get started. MERGE in…

5mins of Postgres E10: max_wal_size, Postgres full page writes and UUID vs BIGINT primary keys

24 March, 2022

In 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…

5mins of Postgres E9: PostGIS vs GPUs, tuning parallel queries, and automating Citus extension benchmarks with HammerDB

17 March, 2022

In 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…

5mins of Postgres E8: Postgres Auditing: Table triggers with supabase vs the pgAudit extension

10 March, 2022

Today, 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…

5mins of Postgres E7: Autovacuum, dead tuples not yet removable, and the Postgres xmin horizon

03 March, 2022

Today, 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…

5mins of Postgres E6: Optimizing Postgres Text Search with Trigrams and GiST indexes

24 February, 2022

Today, 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…

5mins of Postgres E5: Finding the root cause of high CPU utilization in Postgres with EXPLAIN and the Linux perf command

17 February, 2022

Today, 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…

5mins of Postgres E4: Writing your own custom Postgres aggregates with Rust or raw SQL

10 February, 2022

Today 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…

5mins of Postgres E3: Postgres performance cliffs with large JSONB values and TOAST

03 February, 2022

Today, 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…

5mins of Postgres E2: Using unnest(..), generate_series(), and PostGIS

27 January, 2022

Today, 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…

5mins of Postgres E1: Using Postgres statistics to improve bad query plans, pg_hint_plan extension

20 January, 2022

Hello, 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…

Postgres in 2021: An Observer's Year In Review

07 January, 2022

Every January, the pganalyze team takes time to sit down to reflect on the year gone by. Of course, we are thinking about pganalyze, our customers and how we can improve our product. But, more importantly, we always take a bird's-eye view at what has happened in our industry, and specifically in the Postgres community. As you can imagine: A lot! So we thought: Instead of trying to summarize everything, let's review what happened with the Postgres project, and what is most exciting from our…

The Fastest Way To Load Data Into Postgres With Ruby on Rails

14 December, 2021

Data migration is a delicate and sometimes complicated and time-consuming process. Whether you are loading data from a legacy application to a new application or you just want to move data from one database to another, you’ll most likely need to create a migration script that will be accurate, efficient, and fast to help with the process — especially if you are planning to load a huge amount of data. There are several ways you can load data from an old Rails app or other application to Rails. In…

Understanding Postgres GIN Indexes: The Good and the Bad

02 December, 2021

Adding, tuning and removing indexes is an essential part of maintaining an application that uses a database. Oftentimes, our applications rely on sophisticated database features and data types, such as JSONB, array types or full text search in Postgres. A simple B-tree index does not work in such situations, for example to index a JSONB column. Instead, we need to look beyond, to GIN indexes. Almost 15 years ago to the dot, GIN indexes were added in Postgres 8.2, and they have since become an…

Using PostgreSQL Views in Django

16 November, 2021

At my first job, we worked with a lot of data. I quickly found that when there's a lot of data, there are bound to be some long, convoluted SQL queries. Many of ours contained multiple joins, conditionals, and filters. One of the ways we kept the complexity manageable was to create views for common queries. Views in PostgreSQL allow you to query against the results of another query. Views can be composed of columns from one or more tables or even other views, and they are easy to work with in a…

How we deconstructed the Postgres planner to find indexing opportunities

02 November, 2021

Everyone who has used Postgres has directly or indirectly used the Postgres planner. The Postgres planner is central to determining how a query gets executed, whether indexes get used, how tables are joined, and more. When Postgres asks itself "How do we run this query?”, the planner answers. And just like Postgres has evolved over decades, the planner has not stood still either. It can sometimes be challenging to understand what exactly the Postgres planner does, and which data it bases its…

A better way to index your Postgres database: pganalyze Index Advisor

23 September, 2021

When you run an application with a relational database attached, you will no doubt have encountered this question: Which indexes should I create? For some of us, indexing comes naturally, and B-tree, GIN and GIST are words of everyday use. And for some of us it’s more challenging to find out which index to create, taking a lot of time to get right. But what unites us is that creating and tweaking indexes is part of our job when we use a relational database such as Postgres in production. We need…

Using Postgres CREATE INDEX: Understanding operator classes, index types & more

12 August, 2021

Most developers working with databases know the challenge: New code gets deployed to production, and suddenly the application is slow. We investigate, look at our APM tools and our database monitoring, and we find out that the new code caused a new query to be issued. We investigate further, and discover the query is not able to use an index. But what makes an index usable by a query, and how can we add the right index in Postgres? In this post we’ll look at the practical aspects of using the…

Efficient Pagination in Django and Postgres

20 July, 2021

You could say most web frameworks take a naive approach to pagination. Using PostgreSQL’s COUNT, LIMIT, and OFFSET features works fine for the majority of web applications, but if you have tables with a million records or more, performance degrades quickly. Django is an excellent framework for building web applications, but its default pagination method falls into this trap at scale. In this article, I’ll help you understand Django’s pagination limitations and offer three alternative methods…

PostgreSQL Partitioning in Django

08 July, 2021

Postgres 10 introduced partitioning to improve performance for very large database tables. You will typically start to see the performance benefits with tables of 1 million or more records, but the technical complexity usually doesn’t pay off unless you’re dealing with hundreds of gigabytes of data. Though there are several advantages to partitioning, it requires more tables, which can become cumbersome to work with, especially if you change your data structure in the future. Please note: If you…

Using GeoDjango and PostGIS in Django

24 June, 2021

Spatial data is any geographic data that contains information related to the earth, such as rivers, boundaries, cities, or natural landmarks. It describes the contours, topology, size, and shape of these features. Maps are a common method of visualizing spatial data, which is typically represented in vector or raster form. In this article, I’ll introduce you to spatial data in PostgreSQL and Django. You’ll see how to use PostGIS and GeoDjango to create, store, and manipulate geographic data…

Using Postgres Row-Level Security in Ruby on Rails

25 May, 2021

Securing access to your Postgres database is more important than ever. With applications growing more complex, often times using multiple programming languages and frameworks within the same app, it can be challenging to ensure access to customer data is handled consistently. For example, if you are building a SaaS application where different companies use the application, you don't want users of Company A to see the data of users in Company B by accident. Sure, you could use create a separate…

A look at Postgres 14: Performance and Monitoring Improvements

21 May, 2021

The first beta release of the upcoming Postgres 14 release was made available yesterday. In this article we'll take a first look at what's in the beta, with an emphasis on one major performance improvement, as well as three monitoring improvements that caught our attention. Before we get started, I wanted to highlight what always strikes me as an important unique aspect of Postgres: Compared to most other open-source database systems, Postgres is not the project of a single company, but rather…

Creating Custom Postgres Data Types in Rails

22 April, 2021

Postgres ships with the most widely used common data types, like integers and text, built in, but it's also flexible enough to allow you to define your own data types if your project demands it. Say you're saving price data and you want to ensure that it’s never negative. You might create a type that you could then use to define columns on multiple tables. Or maybe you have data that makes more sense grouped together, like GPS coordinates. Postgres allows you to create a type to hold that data…

Introducing pg_query 2.0: The easiest way to parse Postgres queries

18 March, 2021

The query parser is a core component of Postgres: the database needs to understand what data you're asking for in order to return the right results. But this functionality is also useful for all sorts of other tools that work with Postgres queries. A few years ago, we released pg_query to support this functionality in a standalone C library. pganalyze uses pg_query to parse and analyze every SQL query that runs on your Postgres database. Our initial motivation was to create pg_query for checking…

Efficient Postgres Full Text Search in Django

24 February, 2021

In this article, we'll take a look at making use of the built-in, natural language based Postgres Full Text Search in Django. Internet users have gotten increasingly discerning when it comes to search. When they type a keyword into your website's search bar, they expect to find logically ranked results, including related matches and misspellings. Because users are used to these sophisticated search systems, developers have to build applications that use more than simple queries. Postgres Full…

Creating Custom Postgres Data Types in Django

15 December, 2020

Postgres allows you to define custom data types when the default types provided don't fit your needs. There are many situations where these custom data types come in handy. For example, if you have multiple columns in several tables that should be an between 0 and 255, you could use a custom data type so that you only have to define the constraints once. Or, if you have complex data - like metadata about a file - and you want to save it to a single column instead of spreading it across several…

PostGIS vs. Geocoder in Rails

01 October, 2020

This article sets out to compare PostGIS in Rails with Geocoder and to highlight a couple of the areas where you'll want to (or need to) reach for one over the other. I will also present some of the terminology and libraries that I found along the way of working on this project and article as I set out to understand PostGIS better and how it is integrated with Rails. If you are interested in learning how to work with geospatial data with PostGIS in Django I recommend having a look at our blog…

Lessons Learned from Running Postgres 13: Better Performance, Monitoring & More

21 September, 2020

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 Smaller Indexes with B-Tree Deduplication Extended Statistics Improvements in Postgres 1…

Using Postgres Row-Level Security in Python and Django

13 August, 2020

Postgres introduced row-level security in 2016 to give database administrators a way to limit the rows a user can access, adding an extra layer of data protection. What's nice about RLS is that if a user tries to select or alter a row they don't have access to, their query will return 0 rows, rather than throwing a permissions error. This way, a user can use , and they will only receive the rows they have access to with no knowledge of rows they don't. Most examples of RLS limit row access by…

Using Postgres JSONB Fields in Django

30 July, 2020

I remember the first time I built user preferences into an app. At first, users just needed to be able to opt in or out of our weekly emails. "No big deal," I thought, "I'll just add a new field on the Users table." For a while, that was fine. A few weeks later, my boss asked me if we could let users opt into push notifications. Fine, that's just one more column on the database. Can't hurt, right? You probably see where this is going. Within months, my user table had 40 columns, and while…

Building SVG Components in React

09 July, 2020

React is well known as a great tool for building complex applications from HTML and CSS, but that same approach can also be used with SVG to build sophisticated custom UI elements. In this article, we'll give a brief overview of SVG, when to use it (and when not to), and how to use it effectively in a React application. We'll also briefly touch on how to integrate with d3 (which comes in very useful when working with SVG). We relied heavily on SVG to build the charting updates we launched…

Advanced Active Record: Using Subqueries in Rails

24 June, 2020

Active Record provides a great balance between the ability to perform simple queries simply, and also the ability to access the raw SQL sometimes required to get our jobs done. In this article, we will see a number of real-life examples of business needs that may arise at our jobs. They will come in the form of a request for data from someone else at the company, where we will first translate the request into SQL, and then into the Rails code necessary to find those records. We will be covering…

Introducing New Charts & Date Picker in pganalyze

29 April, 2020

Clear and flexible presentation of data is the bread and butter of a monitoring service. A good one will display the right data, but a great one can guide you toward meaningful insights. Visual representation of data in a clear and concise way can help you make decisions quickly. Today we're releasing multiple updates to pganalyze that will help you get to insights more effectively, and keep your database running smoothly. Date range selection as a first-class concept Consistent charts across…

Full Text Search in Milliseconds with Rails and PostgreSQL

16 April, 2020

Imagine the following scenario: You have a database full of job titles and descriptions, and you’re trying to find the best match. Typically you’d start by using an ILIKE expression, but this requires the search phrase to be an exact match. Then you might use trigrams, allowing spelling mistakes and inexact matches based on word similarity, but this makes it difficult to search using multiple words. What you really want to use is Full Text Search, providing the benefits of ILIKE and trigrams…

Effectively Using Materialized Views in Ruby on Rails

16 January, 2020

It's every developer's nightmare: SQL queries that get large and unwieldy. This can happen fairly quickly with the addition of multiple joins, a subquery and some complicated filtering logic. I have personally seen queries grow to nearly one hundred lines long in both the financial services and health industries. Luckily Postgres provides two ways to encapsulate large queries: Views and Materialized Views. In this article, we will cover in detail how to utilize both views and materialized views…

Introducing Automated Postgres EXPLAIN Visualization & Insights

16 December, 2019

Today, we’re excited to introduce you to the next evolution of pganalyze. We updated our logo and overall brand, worked on our documentation to help you understand Postgres and its internals better and, most importantly, we’re proud to announce a new key feature on our platform: Automated EXPLAIN Visualization & Insights. Automatic Collection of Query Plans Automatic Visualization of Postgres EXPLAIN Plans pganalyze EXPLAIN Insights The new pganalyze brand Offering this functionality to you is a…

Similarity in Postgres and Rails using Trigrams

19 November, 2019

You typed "postgras", did you mean "postgres"? Use the best tool for the job. It seems like solid advice, but there's something to say about keeping things simple. There is a training and maintenance cost that comes with supporting an ever growing number of tools. It may be better advice to use an existing tool that works well, although not perfect, until it hurts. It all depends on your specific case. Postgres is an amazing relational database, and it supports more features than you might…

Efficient GraphQL queries in Ruby on Rails & Postgres

24 September, 2019

GraphQL puts the user in control of their own destiny. Yes, they are confined to your schema, but beyond that they can access the data in any which way. Will they ask only for the "events", or also for the "category" of each event? We don't really know! In REST based APIs we know ahead of time what will be rendered, and can plan ahead by generating the required data efficiently, often by eager-loading the data we know we'll need. In this article, we will discuss what N+1 queries are, how they…

Postgres Connection Tracing, Wait Event Analysis & Vacuum Monitoring go into GA on pganalyze

14 April, 2019

We’re excited to announce the general availability of three new pganalyze features: Connection Tracing, Wait Event Analysis, as well as Vacuum Monitoring. These features have been developed based on the feedback of hundreds of customers monitoring their production Postgres databases using pganalyze. Thanks so much for consistently taking the time to provide us with valuable information on how you’d like to see pganalyze evolve! Postgres Connection Tracing & Wait Event Analysis One of the most…

Postgres 11: Monitoring JIT performance, Auto Prewarm & Stored Procedures

04 October, 2018

Everyone’s favorite database, PostgreSQL, has a new release coming out soon: Postgres 11 In this post we take a look at some of the new features that are part of the release, and in particular review the things you may need to monitor, or can utilize to increase your application and query performance. Just-In-Time compilation (JIT) in Postgres 11 Just-In-Time compilation (JIT) for query execution was added in Postgres 11. It's not going to be enabled for queries by default, similar to parallel…

Postgres Log Monitoring with pganalyze: Introducing Log Insights 2.0

24 July, 2018

TLDR: We recently released substantial improvements to our Log Insights feature, including up to 30 day history, support for Heroku Postgres, as well as support for monitoring the log files of PostgreSQL servers running on-premise. How pganalyze parses Postgres log files Its now been a bit over a year since we first released the log monitoring functionality in pganalyze, and we would like to share a major update with you today. Before diving in, a quick review how the pganalyze collector works…

Postgres Log Monitoring 101: Deadlocks, Checkpoint Tuning & Blocked Queries

12 February, 2018

Those of us who operate production PostgreSQL databases have many jobs to do - and often there isn't enough time to take a regular look at the Postgres log files. However, often times those logs contain critical details on how new application code is affecting the database due to locking issues, or how certain configuration parameters cause the database to produce I/O spikes. This post highlights three common performance problems you can find by looking at, and automatically filtering your…

Visualizing & Tuning Postgres Autovacuum

28 November, 2017

In this post we'll take a deep dive into one of the mysteries of PostgreSQL: VACUUM and autovacuum. The Postgres autovacuum logic can be tricky to understand and tune - it has many moving parts, and is hard to understand, in particular for application developers who don't spend all day looking at database documentation. But luckily there are recent improvements in Postgres, in particular the addition of pg_stat_progress_vacuum in Postgres 9.6, that make understanding autovacuum and VACUUM…

Whats New in Postgres 10: Monitoring Improvements

04 October, 2017

Postgres 10 has been stamped on Monday, and will most likely be released this week, so this seems like a good time to review what this new release brings in terms of Monitoring functionality built into the database. In this post you'll see a few things that we find exciting about the new release, as well as some tips on what to adjust, whether you use a hosted Postgres monitoring tool like pganalyze, or if you've written your own scripts. New "pg_monitor" Monitoring Role Most users of Postgres…

Introducing Log Insights: Realtime Analysis of Postgres Logs

07 June, 2017

After significant development effort, we're excited to introduce you to a new part of pganalyze that we believe every production Postgres database needs: pganalyze Log Insights UPDATE: We released pganalyze Log Insights 2.0 - read more about it in our article: Postgres Log Monitoring with pganalyze: Introducing Log Insights. In the past you used generic log management systems and setup your own filtering and altering rules, which required a lot of manual effort, as well as knowledge of all…

Monitoring PostgreSQL 9.5 & Improved Weekly Reports

06 July, 2015

Last week the first official alpha version of PostgreSQL 9.5 was released. Whilst the stable release is still 2-3 months away, now is a good time to review what is upcoming, and which changes and improvements we can expect. Here is an overview of the most important changes for monitoring tools: pg_stat_statements gets new columns min_time, max_time, mean_time & stddev_time - making it much easier to identify outliers in the query statistics New pg_stat_ssl view that shows active SSL connections…

Introducing pg_query: Parse PostgreSQL queries in Ruby

17 June, 2014

In this article we'll take a look at the new pg_query Ruby library. pg_query is a Ruby library I wrote to help you parse SQL queries and work with the PostgreSQL parse tree. We use this extension inside pganalyze to provide contextual information for each query and find columns which might need an index. At the end of this article you'll also find monitor.rb - a ready-to-use example that filters pg_stat_statements output and restricts it to only show a specific table. Existing Solutions to Parse…

Announcing The All-New Database Check-Up

20 March, 2014

We’ve just launched our new version of Database Check-Up - allowing you to see more quickly what could be relevant to look at in your database. In addition we’ve also revamped the detail pages of queries, tables, indices and config settings to match the new style: Improved Check-Up: Config Settings When working with other people's PostgreSQL databases, we’ve seen a lot of things, from fsync=off (which you really only want if you don’t care about your data or have no writes) to simple…