When it comes to optimizing query performance, indexing is one of the most powerful tools available to database engineers. Both PostgreSQL and Microsoft SQL Server (or Azure SQL) use B-Tree indexes as their default indexing structure, but the way each system implements, maintains, and uses those indexes varies in subtle but important ways. In this blog post, we explore key areas where PostgreSQL and SQL Server diverge: how their B-Tree indexes implementations behave under the hood and how the...| pganalyze Blog
The Postgres EXPLAIN command is invaluable when trying to understand query performance. SQL is a declarative language, and the Postgres query planner will decide the most efficient way to execute a query. However, plan selection is based on statistics, configuration settings, and heuristics—not a crystal ball. Sometimes there's a substantial gap between what the planner thinks is most efficient and reality. In those situations, EXPLAIN can help Postgres users understand the planner's "reaso...| pganalyze Blog
If you're migrating from Oracle Database to PostgreSQL, you're likely accustomed to using hints to optimize queries. In Oracle, these are special directives embedded in SQL (like ) that steer the optimizer's execution plan. They can be extremely useful but also introduce complexity and “hint debt” over time. PostgreSQL takes a very different approach to query optimization. Rather than supporting built-in hints, the Postgres community, historically, has emphasized relying on its cost-based…| pganalyze Blog
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…| pganalyze Blog
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…| pganalyze Blog
DBAs and developers use pganalyze to identify the root cause of performance issues, optimize queries and to get alerts about critical issues. Sign up for free!| pganalyze
Postgres 18 introduces Asynchronous I/O (AIO) that can dramatically improve read performance, especially in the cloud. Learn how these changes and the new io_method setting work and see why our benchmark results show that io_uring is the recommended setting for maximizing I/O performance in Postgres 18 over the default setting 'worker'.| pganalyze
pganalyze lets you visualize your Postgres performance metrics and improve your database performance with sophisticated dashboards. Learn more!| pganalyze
We walk through different approaches for zero downtime Postgres upgrades, and give examples from GitLab, Instacart, and others.| pganalyze
We introduce the new pg_query release that supports turning SQL queries with Postgres 16 syntax into parse trees.| pganalyze
We walk through the incremental backup feature committed to the Postgres 17 development branch by Robert Haas. We show an example on our local machine.| pganalyze
We talk about querying system wide I/O statistics in Postgres, show several use cases for pg_stat_io, and talk about the future of I/O observability in Postgres.| pganalyze
pganalyze Lock Monitoring automatically detects locking/blocking queries as they happen, can alert you of production incidents, and keeps a history of past incidents.| pganalyze
How the Postgres planner breaks down a query into scans and how this impacts indexing choices| pganalyze
We are taking a look at Postgres 14, explaining nested loops, extended statistics for expressions, 64-bit transaction IDs, and more exciting Postgres patches from 2021.| pganalyze
We are looking at 4 methods: Insert One Record at a Time, Bulk Inserts with Active Record Import, PostgreSQL Copy with Activerecord-copy, Using Background Jobs.| pganalyze
Learn for which data types and operators GIN is best suited for and why updating GIN indexes can be more expensive thank you think.| pganalyze
We share how views differ from materialized views and show how using Postgres views makes querying aggregated data easier and (with materialized views) faster.| pganalyze
We take a behind the scenes look at the pganalyze Index Advisor and discuss a few recent improvements, such as utilizing selectivity information better.| pganalyze
We look into ML-based Postgres indexing with GitHub CoPilot and find out that relying on the Postgres planner and using a deterministic model is better.| pganalyze
Learn about Postgres operator classes, index types, how to match index definitions to queries, how to add and drop Postgres indexes safely on production, and more!| pganalyze
We look at 3 different methods for pagination with Django and Postgres and explain their benefits and tradeoffs so you can decide which is the best fit for you.| pganalyze
We walk through the different types of partitions available in Postgres and how to use the django-postgres-extra package to make partitioning happen in Django.| pganalyze
This article shows how PostGIS and GeoDjango can help you use spatial data to build location-aware web applications.| pganalyze
Learn how to create and implement a row level security policy in Rails, allowing to limit the database rows a user can access.| pganalyze
We take a look at whats new in Postgres 14, with idle and active connection scaling, memory monitoring, query IDs and more| pganalyze
We create domain types and composite types in PostgreSQL and show how to hook into the Rails Attributes API to help instantiate them as objects that Ruby can use.| pganalyze
In this article you can learn about pg_query 2.0 - the easiest way to parse Postgres queries. Read now!| pganalyze
Learn to optimize a Full Text Search implementation with Django and Postgres. Even on a small table, we bring query execution time down from 0.045s to 0.001s.| pganalyze
We set up database level checks on columns, create new composite data types, register new data types with psycopg2, and take a look at the Django Field class.| pganalyze
This article compares PostGIS in Rails with Geocoder and highlights areas where you'll want to (or need to) reach for one over the other. Learn more here.| pganalyze
Learn how to create and implement a row level security policy with Django middleware and Django signals, allowing to limit the database rows a user can access.| pganalyze
With Django's ORM, creating JSONB fields, using them in your models, inserting data into them, and querying from them is all possible. Learn how here.| pganalyze
Learn how to use SVG with React and d3, what SVG is, when to use it, handling, sizing, styling, layouting and interactivity in SVG and how to embed HTML in SVG.| pganalyze
Active Record lets you access raw SQL. We look at 5 business requests for data, translate them into SQL, then into Rails code to find the requested data.| pganalyze
This article shows how to optimize a Full Text Search implementation with Rails and PostgreSQL, taking a single query from 130ms to 7ms.| pganalyze
We look at how to utilize both, views and materialized views, within Ruby on Rails with a real-world sized dataset of hockey teams and their top scorers.| pganalyze
We look into the pg_trgm extension, seeing first what trigrams actually are, and then how we can practically use similarity functions and operators in our Rails apps.| pganalyze
We discuss what N+1 queries are, how they are easily produced in GraphQL, and how to solve them using the graphql-batch gem along with a few custom batch loaders.| pganalyze
DBAs and developers use pganalyze to identify the root cause of performance issues, optimize queries and to get alerts about critical issues. Sign up for free!| pganalyze
DBAs and developers use pganalyze to identify the root cause of performance issues, optimize queries and to get alerts about critical issues. Sign up for free!| pganalyze
DBAs and developers use pganalyze to identify the root cause of performance issues, optimize queries and to get alerts about critical issues. Sign up for free!| pganalyze