PostgreSQL Tutorials

This document provides an introduction to tuning PostgreSQL and EDB Postgres Advanced Server (EPAS), versions 10 through 13. The system used is the RHEL family of linux distributions, version 8. These are only general guidelines and actual tuning det ...

Like most of the database vendors provide a way to bulk copy from files, EnterpriseDB has a nice tool at hand called EDB*Loader for EDB Postgres Advanced Server. It is a high-performance bulk data loader that provides an interface compatible with sql ...

This article discusses connections to PostgreSQL database servers. It first reviews the possible states for a connection and then shows how to identify and terminate connections that are lying idle and consuming resources. States of a connectio ...

This article offers tips for improving Online Analytical Processing (OLAP) performance for PostgreSQL. Sizing up the Situation Schema Planning Database Tuning Hardware Selection As the world’s most advanced open-source database, Post ...

This article discusses GUC parameters that can be used for PostgreSQL tuning and how to configure them for improved performance.

This article defines a probe and provides instructions for creating custom probes for PostgreSQL using EDB Postgres Enterprise Manager (PEM).

In PostgreSQL, we already support parallelism of a SQL query which leverages multiple cores to execute the query faster.  Vacuum is one of the most critical utility operations which helps in controlling bloat, one of the major problems for PostgreSQL DBAs.  So, vacuum needs to run really fast to reduce the bloat as early as possible. In PG13, parallel vacuum is introduced which allows for parallel vacuuming of multiple indexes corresponding to a single table.

This article looks at parameters that can be used to help manage memory in PostgreSQL. Recommended settings for each parameter are also provided.

<p>This article introduces multicolumn indexes and explains how they can by used to make large queries run faster.</p>

<p>This article looks at unlogged tables in PostgreSQL and reviews performance differences between unlogged tables and ordinary and temporary tables, as well as crash recovery.</p>

<p>This article explains how to get started using pgAdmin 4 as a management tool for PostgreSQL.</p>

<p>This article discusses parameters that can be used to configure performance in PostgreSQL. After introducing ways of measuring database speed, it reviews the parameters and ways to optimize performance with them.</p>

<p>This article describes a problem in PostgreSQL where logical replication can cause a server to run out of memory. It explains the cause of the problem, demonstrates the severity of the problem with an example, then provides a solution introduced in PostgreSQL 13, the logical_decoding_work_mem parameter.</p>

<p>While working with PostgreSQL the two basic requirements is to create a database and set up a few users. This will help us in eliminating the need for reinstallation, if we mess up the default set of databases or users that already exist, while trying to learn and build our understanding.</p>

<p>This article covers how benchmark tests can be used to demonstrate the effect of table partitioning on performance.</p>

<p>With the addition of declarative partitioning in PostgreSQL 10, it only made sense to extend the existing pgbench benchmarking module to create partitioned tables. A recent commit of patch by Fabien Coelho in PostgreSQL 13 has made this possible.&nbsp;</p>

<p>Most people know that Postgres allows the creation of indexes on expressions. This is helpful if you need index lookups of expressions used in where clauses. However, there is another benefit to expression indexes, and that is optimizer statistics. Not only do expression indexes allow rapid lookups of matching expressions, but they also provide optimizer statistics, which improve row estimates and hence query plans.</p>

<p>When data are naturally aligned, CPU can perform read and write to memory efficiently. Hence, each data type in PostgreSQL has a specific alignment requirement. When multiple attributes are stored consecutively in a tuple, padding is inserted before an attribute so that it begins from the required aligned boundary. A better understanding of these alignment requirements may help minimizing the amount of padding required while storing a tuple on disk, thus saving disk space.</p>

<p>Parallel access methods&nbsp;are introduced in PostgreSQL since v 9.6. Still, I could not help but notice that every now and then there are complaints&nbsp;about the parallel sequential scan that is not getting selected or it is degrading the performance of a query. &nbsp;So, I decided to write this blog to cater to more practical scenarios and specifically focus on its less talked about aspect -- where parallel sequential scan would (should) not improve the performance.</p>

<p>In my previous blog post, I showed how statistics generated on expression indexes can be used to produce more accurate row counts and potentially better plans. While I did show more accurate row counts via explain, I did not show changed query plans. I plan to do so in this blog post.</p>