5mins 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. 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 Transcript Let's dive in. Performance of prepared statements with partitioning in…
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. We talk about a…
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. 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's take a look! Using Incremental Materialized Views with pg_ivm So, first of all, this is a new extension…
Continue readingThe Unexpected Find That Freed 20GB of Unused Index Space
04 May, 2022How 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…
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. Optimizing with Postgres’ deterministic query planner Setting costs for page access Using Extended Statistics in Postgres CREATE STATISTICS and the dependencies statistics type in Postgres Tuning Join Collapse Limits in Postgres What we have discussed in this episode…
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. Comparing HOT Updates and Bottom-Up Index Deletion Understanding Postgres pages Postgres page splits in B-Tree indexes Bottom-Up Index deletion in Postgres to avoid page splits Using the bt_page_items function B…
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. SQL/JSON constructor syntax in…
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 readingHow Postgres Chooses Which Index To Use For A Query
01 April, 2022Using 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…
Continue reading5mins of Postgres E11: MERGE in Postgres 15, and how it compares to INSERT ON CONFLICT
31 March, 2022Today, 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. 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 Postgres 15 Earlier this week, the command was merged into the Postgres 15 branch. , for those of you who…
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. 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 primary keys What we have discussed in this episode of 5mins of Postgres Transcript Let's dive in! Tuning…
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. Transcript Looking at PostGIS vs. GPU for Performance and Spatial Joins The importance of automating performance benchmarks Using HammerDB to drive a TPC-C or TPC-H benchmark What we have discussed in this episode of 5mins of Postgres Transcript Let's dive in…
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. 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 triggers in Postgres for Postgres Auditing Today’s episode was inspired by a blog post by Oliver Rice on…
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. 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 Transcript Let's get started! Vacuuming dead tuples not yet removable in Postgres We'll…
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. 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 Postgres text search performance with trigrams Trigrams in general are a way of splitting up a string…
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. 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 Linux perf utility set CPU Profiling with run_xcpu by Tanel Poder What we have discussed in this episode…
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. 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 discussed in this episode of 5mins of Postgres Transcript Let's get started! Using pgx to write a Rust…
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. Transcript How Postgres stores data: Large JSON value query performance PostgreSQL physical storage Understanding JSONB performance What we have discussed in this episode of 5mins of Postgres Transcript Let's dive right in! How Postgres stores data: Large JSON value…
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 readingPostgres in 2021: An Observer's Year In Review
07 January, 2022Every 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…
Continue readingThe Fastest Way To Load Data Into Postgres With Ruby on Rails
14 December, 2021Data 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…
Continue readingUnderstanding Postgres GIN Indexes: The Good and the Bad
02 December, 2021Adding, 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…
Continue readingUsing PostgreSQL Views in Django
16 November, 2021At 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…
Continue readingHow we deconstructed the Postgres planner to find indexing opportunities
02 November, 2021Everyone 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…
Continue readingA better way to index your Postgres database: pganalyze Index Advisor
23 September, 2021When 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…
Continue readingUsing Postgres CREATE INDEX: Understanding operator classes, index types & more
12 August, 2021Most 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…
Continue readingEfficient Pagination in Django and Postgres
20 July, 2021You 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…
Continue readingPostgreSQL Partitioning in Django
08 July, 2021Postgres 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…
Continue readingUsing GeoDjango and PostGIS in Django
24 June, 2021Spatial 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…
Continue readingUsing Postgres Row-Level Security in Ruby on Rails
25 May, 2021Securing 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…
Continue readingAn early look at Postgres 14: Performance and Monitoring Improvements
21 May, 2021The 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…
Continue readingCreating Custom Postgres Data Types in Rails
22 April, 2021Postgres 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…
Continue readingIntroducing pg_query 2.0: The easiest way to parse Postgres queries
18 March, 2021The 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…
Continue readingEfficient Postgres Full Text Search in Django
24 February, 2021In 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…
Continue readingCreating Custom Postgres Data Types in Django
15 December, 2020Postgres 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…
Continue readingPostGIS vs. Geocoder in Rails
01 October, 2020This 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…
Continue readingLessons Learned from Running Postgres 13: Better Performance, Monitoring & More
21 September, 2020Postgres 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…
Continue readingUsing Postgres Row-Level Security in Python and Django
13 August, 2020Postgres 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…
Continue readingUsing Postgres JSONB Fields in Django
30 July, 2020I 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…
Continue readingBuilding SVG Components in React
09 July, 2020React 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…
Continue readingAdvanced Active Record: Using Subqueries in Rails
24 June, 2020Active 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…
Continue readingIntroducing New Charts & Date Picker in pganalyze
29 April, 2020Clear 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…
Continue readingFull Text Search in Milliseconds with Rails and PostgreSQL
16 April, 2020Imagine 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…
Continue readingEffectively Using Materialized Views in Ruby on Rails
16 January, 2020It'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…
Continue readingIntroducing Automated Postgres EXPLAIN Visualization & Insights
16 December, 2019Today, 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…
Continue readingSimilarity in Postgres and Rails using Trigrams
19 November, 2019You 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…
Continue readingEfficient GraphQL queries in Ruby on Rails & Postgres
24 September, 2019GraphQL 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…
Continue readingPostgres Connection Tracing, Wait Event Analysis & Vacuum Monitoring go into GA on pganalyze
14 April, 2019We’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…
Continue readingNew in Postgres 11: Monitoring JIT performance, Auto Prewarm & Stored Procedures
04 October, 2018Everyone’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…
Continue readingPostgres Log Monitoring with pganalyze: Introducing Log Insights 2.0
24 July, 2018TLDR: 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…
Continue readingPostgres Log Monitoring 101: Deadlocks, Checkpoint Tuning & Blocked Queries
12 February, 2018Those 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…
Continue readingVisualizing & Tuning Postgres Autovacuum
28 November, 2017In 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…
Continue readingWhats New in Postgres 10: Monitoring Improvements
04 October, 2017Postgres 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…
Continue readingIntroducing Log Insights: Realtime Analysis of Postgres Logs
07 June, 2017After 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…
Continue readingMonitoring PostgreSQL 9.5 & Improved Weekly Reports
06 July, 2015Last 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…
Continue readingIntroducing pg_query: Parse PostgreSQL queries in Ruby
17 June, 2014In 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…
Continue readingAnnouncing The All-New Database Check-Up
20 March, 2014We’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…
Continue reading