PostgreSQL Tutorials

How to solve common and tricky PostgreSQL issues covering Go, Python, Java, Dockers, Kubernetes, Django, and other technologies

Recent Tutorials

How to create and refresh data for materialized views in PostgreSQL

Richard YenNovember 20, 2019

Most relational database systems provide the functionality to create a VIEW, which basically acts like a shortcut or macro.

How to benchmark partition table performance

Beena EmersonNovember 19, 2019

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.

Everything you need to know about PostgreSQL triggers

Ranjeet DhumalNovember 18, 2019

A “trigger” is defined as any event that sets a course of action in a motion. In PostgreSQL, if you want to take action on specific database events, such as INSERT, UPDATE, DELETE, or TRUNCATE, then trigger functionality can be useful as it will invoke the required function on defined events.

How to Use Postgres Unions

Richard YenNovember 18, 2019

Whether we remember it or not, almost all of us learned set theory in elementary school. We drew Venn diagrams with overlapping circles and learned to articulate what the overlap means, making use of a strange set of operators to formulate equations around them. With SQL, there are occasions where we might want to use these concepts from set theory, whether it’s to concatenate two data sets or to extract information about two sets’ relationships. For this, PostgreSQL provides syntax for set operations: UNION, INTERSECT, and EXCEPT. In this article, we will focus on the UNION operator.

Stored procedures in PostgreSQL: How to create a stored procedure and invoke it?

Moumita RayNovember 15, 2019

A stored procedure is basically a set of precompiled SQL and procedural statements (declarations, assignments, loops, etc.) that is stored on the database server and can be invoked using the SQL interface to perform a special operation.

Connecting PostgreSQL using psql and pgAdmin

Amit SharmaNovember 15, 2019

This is a two-part article for beginners who have installed the most advanced open source database, PostgreSQL, and are now looking to connect to it. Since terminal/command line and pgAdmin are the most favored ways for connecting to PostgreSQL, I explain the basics of using both methodologies.

Query Plan Improvement with Expression Indexes in PostgreSQL

Bruce MomjianNovember 7, 2019

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.

How to improve PostgreSQL performance by fitting more tuples in a data page

Kuntal GhoshNovember 7, 2019

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.

PgBouncer Logs Rotation in Linux and Windows Tutorial

Raghavendra RaoNovember 7, 2019

Before doing a deep dive into the subject, a short outline about PgBouncer, it is a lightweight connection pooler for PostgreSQL that dramatically reduces the processing time and resources for maintaining a large number of client connections to one or more databases. Typically used to increase the number of user connections that can be handled in a high-performance environment. For more details on Installing/Configuring PgBouncer refer to the documentation.

When Parallel Sequential Scan Does Not Improve Performance of Postgres

Rafia SNovember 7, 2019

Parallel access methods are introduced in PostgreSQL since v 9.6. Still, I could not help but notice that every now and then there are complaints about the parallel sequential scan that is not getting selected or it is degrading the performance of a query. 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.

Postgres AT TIME ZONE Explained

Bruce MomjianNovember 7, 2019

I saw AT TIME ZONE used in a query, and found it confusing. I read the Postgres documentation and was still confused, so I played with some queries and finally figured it out. I then updated the Postgres documentation to explain it better, and here is what I found.

Using Materialized Views and Foreign Data Wrappers Together

Bruce MomjianNovember 7, 2019

You might know that Postgres supports materialized views and foreign data wrappers (fdw). Briefly, materialized views allow for queries to be materialized and refreshed on demand. Foreign data wrappers allow data to be pulled from foreign data sources, like Nosql stores and other Postgres servers.

Column Storage Intervals in Postgres Explained

Bruce MomjianNovember 7, 2019

Postgres uses native CPU alignment to store values in a row. This allows blocks to be copied unchanged from disk into shared buffers and accessed as local variables, as outlined in this presentation.

PostgreSQL unique constraint null: Allowing only one Null

Bruce MomjianNovember 7, 2019

While the SQL standard allows multiple nulls in a unique column, and that is how Postgres behaves, some database systems (e.g. MS SQL) allow only a single null in such cases. Users migrating from other database systems sometimes want to emulate this behavior in Postgres. Fortunately, this can be done.

How to improve Row Estimates with Expression Indexes in Postgres

Bruce MomjianNovember 7, 2019

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.

Are Hash Indexes Faster than Btree Indexes in Postgres?

Amit KapilaNovember 7, 2019

PostgreSQL have supported Hash Index for a long time, but they are not much used in production mainly because they are not durable. Now, with the next version of PostgreSQL, they will be durable. The immediate question is how do they perform as compared to Btree indexes.

Parallel Hash Joins in PostgreSQL Explained

Thomas MunroNovember 7, 2019

PostgreSQL 9.6 and 10 can use all three join strategies in parallel query plans, but they can only use a partial plan on the outer side of the join. As of commit 18042840, assuming nothing irreparably busted is discovered in the next few months, PostgreSQL 11 will ship with Parallel Hash. Partial plans will be possible on both sides of a join for the first time.

How to connect Postgres with LDAP (with StartTLS)

Richard YenNovember 7, 2019

I recently got a few support cases from customers seeking to connect Postgres with LDAP (usually with some form of SSL/TLS encryption, to ensure security). I spent a bit of time trying to create a consistently reproducible environment where LDAP could be used to authenticate PostgreSQL connections and wanted to write it down somewhere.

How to move Tablespces to a different directory in Postgres

Bruce MomjianNovember 7, 2019

Tablespaces are designed to allow Postgres clusters to be spread across multiple storage devices. Create tablespace creates a symbolic link in the pg_tblspc directory in the cluster's data directory pointing to the newly-created tablespace directory.

How to use pg_dump and pg_restore in multi-host enviorment

Bruce MomjianNovember 7, 2019

You have probably looked at logical dumps as supported by pg_dump and restores by pg_restore or, more simply, psql. What you might not have realized are the many options for dumping and restoring when multiple computers are involved.

pgbouncer auth_query and auth_user pro tips

Richard YenNovember 6, 2019

Anyone running a database in a production environment with over a hundred users should seriously consider employing a connection pooler to keep resource usage under control. PgBouncer is one such tool, and it’s great because it’s lightweight and yet has a handful of nifty features for DBAs that have very specific needs.

PostgreSQL Toast and Working with BLOBs/CLOBs Explained

Eric McCormackNovember 6, 2019

Almost every table you create has its own associated (unique) TOAST table, which may or may not ever end up being used, depending on the size of rows you insert. A table with only fixed-width columns like integers may not have an associated toast table.

Why you should use Connection Pooling when setting Max_connections in Postgres

Richard YenNovember 6, 2019

PostgreSQL is “The World’s Most Advanced Open Source Database,” and I believe it. In my 10+ years of working with it, it’s been solid, serving up data to SaaS loads of over 1000 queries per second, rarely going down, surviving all manner of accusations of corruption (which turned out to be software engineering error) and performance degradation (which turned out to be user error).

Indexing Documents for Full Text Search in PostgreSQL

Dave PageNovember 6, 2019

During a conversation with $BOSS a couple of days ago the topic of the new Full Text Search in MongoDB came up. He (knowing of, but presumably having never worked with FTS in PostgreSQL) suggested I might create a tool like the one the MongoDB guys were proudly showing off in their keynote video from their recent conference to make it easy to index and search HTML documents on disk.

Reverse Proxying to pgAdmin

Dave PageNovember 6, 2019

Reverse proxying requests to a pgAdmin server is becoming more and more popular if posts to the mailing lists are to be taken as an indicative measure; more often than not when using pgAdmin in a container (of which there have now been over 10 million pulls)!

How to Install Postgres on Docker

Tushar AhujaNovember 6, 2019

Docker is an open-source platform where we can create, deploy, and run applications by using containers. Docker is similar to virtual machines (VM), but in VM you need to create a virtual operating system, while Docker allows applications to use the system kernel.

How to manage Replication and Failover in Postgres Version 12 without recovery.conf file

Tushar AhujaNovember 6, 2019

With PostgreSQL v12, “recovery.conf” is no longer valid. Even if someone were to create a recovery.conf file manually and keep it under the data directory, the server is not going to start and will throw the following error

How to Replacing httpd self signed SSL certificates with trusted CA signed certificates in Postgres Enterprise Manager

Ankit ShuklaJuly 22, 2019

In this blog post we will go trough the steps to replace httpd self signed SSL certificates with trusted CA signed cerficates in Postgres Enterprise Manager

The Challenges of Setting max_connections and Why You Should Use a Connection Pooler

Richard YenJuly 15, 2019

PostgreSQL is “The World’s Most Advanced Open Source Database,” and I believe it. In my 10+ years of working with it, it’s been solid, serving up data to SaaS loads of over 1000 queries per second, rarely going down, surviving all manner of accusations of corruption (which turned out to be software engineering error) and performance degradation (which turned out to be user error)

Why Vendor Lock-in is not a binary decission

Bruce MomjianJuly 1, 2019

One of the major attractions of Postgres is the ability to stop using database software controlled by a single vendor. Single-vendor control means a single entity controls the software, tools, training, and support. There are sometimes options from other vendors, but they are usually hampered because the source code is closed.

How to install PostGIS on EDB Postgres Advanced Server

Phil AllsoppMay 8, 2019

PostGIS is a spatial extender for Postgres and adds more geometric types and spatial functionality to Postgres. You can use PostGIS to easily calculate distances between geometric objects, examples of which are points, lines, and polygons; you can easily calculate areas, whether one or more points sit within any geometric shapes.

Installing and running EDB Postgres Advanced Server cluster on RHEL 6 or RHEL 7

Haris IftikharMarch 27, 2019

Quickly (less than five minutes) install and run a default EPAS 11 cluster on RHEL 6 or RHEL 7

How can cloud database service free you from Cracle cloud service

Jamie WattMarch 12, 2019

These days, I have the good fortune of leading an amazing cluster of teams as the head of EnterpriseDB's Global Support Services. I talk to customers every day about who we are, what sets us apart, and how we can help to make you successful once you've dipped your feet in the Postgres world.

How to Configure pgBouncer to use Postgres Enterprise Manager(PEM) Agent

Vishal SawaleFebruary 22, 2019

This video provides detailed information about using pgBouncer as a connection pooler for limiting the number of connections from the PEM Agent towards the Postgres Enterprise Manager™ (PEM) server on non-Windows machine. The video is comprised of three configuration steps

EPAS 11 on Centos 7 - Using RPMs on Disconnected Networks

EDB TeamFebruary 11, 2019

This article will show you how to quickly set up an epas11 cluster on a disconnected network. This is a good practice to set up sandbox and dev boxes to play with epas11. In production environments, we recommend working with your System Administrator to set up Centos repositories correctly so that you will get most up to date Linux packages.

EPAS Using RPMs on Disconnected Networks

EDB TeamFebruary 1, 2019

If you are installing EDB on RHEL/CentOS machines, RPMs are the way to go. There are many, many reasons for this, a few of which are: It's the standard way to install on RHEL derivatives It uses consistent installation paths It's easily integrated into Puppet, Chef, and other provisioning tools

EPAS 10 using RPMs on Disconnected Networks  

EDB TeamJanuary 21, 2019

For some Advanced Server components that include or support pgAgent or PostGIS, you may be required to install the EPEL (Extra Packages for Enterprise Linux ) repository. If your platform has yum access to the EPEL repository, you can assume superuser. If your platform has yum access to the EPEL repository, you can assume superuser privileges and enter:

A Curious Case of Split WAL Files

Richard YenJanuary 14, 2019

I’ve come across this scenario maybe twice in the past eight years of using Streaming Replication in Postgres: replication on a standby refuses to proceed because a WAL file has already been removed from the primary server, so it executes Plan B by attempting to fetch the relevant WAL file using restore_command (assuming that archiving is set up and working properly), but upon replay of that fetched file, we hear another croak: “No such file or directory.”

How to Deal with the Case of Stuck TransactionIDs in PostgreSQL

Richard YenJanuary 9, 2019

Postgres is up and running, and things are humming along. Replication is working, vacuums are running, and there are no idle transactions in sight. You poke around your logs and make sure things are clean, but you notice a little warning

Can modified tuples be accessed using a statement-level trigger in Postgres?

Ashutosh SharmaDecember 6, 2018

"Transition tables for Triggers" introduced in PostgreSQL V10 which allows us to create transition tables using the REFERENCING clause in the CREATE TRIGGER statement to store the old and new version of tuples.

Data Alignment in PostgreSQL

Kuntal GhoshDecember 6, 2018

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.

Partition Pruning During ExecutionPartition Pruning During Execution

Beena EmersonNovember 26, 2018

Partitioning in Postgresql eases handling large volumes of data. This feature has greatly improved with the introduction of declarative partitioning in PostgreSQL 10, paving way for better query optimization and execution techniques on partitioned tables.

Switchover/Failover and Session Migration

Bruce MomjianOctober 5, 2018

I have already covered switchover and failover. In the case of failover, the old primary is offline so there are no options for migrating clients from the old primary to the new primary. If there is a switchover, there are options for client migration. Assuming you are using streaming replication, only one server can accept writes

How to properly sign rows to provide integrity in PostgreSQL

Bruce MomjianOctober 2, 2018

With the rsa keys created in my previous blog entry, we can now properly sign rows to provide integrity and non-repudiation, which we did not have before. To show this, let's create a modified version of the previous schema by renaming the last column to signature:

Parallel index-only scans in PostgreSQL

Rafia SJuly 31, 2018

In the previous blog, we saw that parallel index scans leads to significantly improves the performance of quite a few TPC-H queries. It is customary to analyze if its sister operator, namely index-only scan will benefit similarly when parallelized.

How to Enable SSL authentication for an EDB Postgres Advanced Server.

Sunil NarainJuly 17, 2018

SSL (Secured Sockets Layer) also known as TLS (Transport Layer Security) is a standard security technology for establishing encrypted connection between a server and a client. SSL uses a combination of public key and symmetric key encryption to secure connection between two machines communicating over the Internet or an internal network.

Using force_parallel_mode Correctly in PostgreSQL

Robert M. HaasJuly 12, 2018

I admit it: I invented force_parallel_mode. I believed then, and still believe now, that it is valuable for testing purposes. Certainly, testing using force_parallel_mode=on or force_parallel_mode=regress has uncovered many bugs in PostgreSQL's parallel query support that would otherwise have been very difficult to find.

EDB's Recommendation for Pgpool II Usage

Ahsan HadiMay 23, 2018

he Pgpool II Community releases a major version every year and minor versions several times in a year. In the last few releases, a lot of emphasis and focus was given to improving the existing functionality of pgpool II, making it more stable, robust and able to handle complex real-world scenarios.

Pgpool Support Metrics for EPAS Database

Ahsan HadiApril 30, 2018

One question that comes up very often is which version of Pgpool II is supported with which database server? I would like to address this question in this post.

Why I picked Postgres over Oracle, Part III

Jan KarremansJanuary 30, 2018

This is the final episode of this short series of blog posts on some of my drivers for moving to Postgres from Oracle. Please do read Part I and Part II of the series if you have not done so. It discussed the topics “History”, “More recently”, “The switch to Postgres”, “Realization”, “Pricing”, “Support” and “Extensibility”.