PostgreSQL Tutorials

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

Recent Tutorials

Why I picked Postgres over Oracle, Part II

Jan KarremansJanuary 12, 2018

Continuing this short series of blog posts on some of my drivers for moving to Postgres from Oracle. Please read Part I of the series if you have not done so. It discussed the topics “History”, “More recently” and “The switch to Postgres”.

Why I picked Postgres over Oracle, Part I

Jan KarremansJanuary 12, 2018

I have started with databases quite early on in my career. RMS by Datapoint… was it really a database? Well, at least sort of. It held data in a central storage, but it was a typical serial “database”. Interestingly enough, some of this stuff is maintained up to today. (Talk about longevity!)

History of improvements in VACUUM in PostgreSQL

Robert M. HaasJanuary 5, 2018

In a recent blog post, I talked about why every system that implements MVCC needs some scheme for removing old row versions, and how VACUUM meets that need for PostgreSQL. In this post, I’d like to examine the history of VACUUM improvements in recent years, the state of VACUUM as it exists in PostgreSQL today, and how it might be improved in the future.

Why we need VACUUM to implement MVCC In PostgreSQL

Robert M. HaasDecember 18, 2017

Experienced PostgreSQL users and developers rattle off the terms “MVCC” and “VACUUM” as if everyone should know what they are and how they work, but in fact many people don’t.

How to use Xmin in PostgreSQL Queries

Bruce MomjianDecember 5, 2017

Someone at a conference mentioned they were using the invisible xmin column to perform updates. I was initially skeptical of this approach, but once he explained the purpose, it made sense.

Debugging Your PostgreSQL Database Binaries

Ranjeet DhumalNovember 15, 2017

Knowing how to debug is a critical aspect of every application development life cycle. Debugging database bianaries allow you to not only recognize that an exception has occurred, but also traverses through the database binaries execution until the culprit code can be located.

How to determine Single Master XDB replication lag

Richard YenOctober 27, 2017

I recently had a few customers ask me about using a query for monitoring SMR XDB lag, so they can keep tabs on replication progress and set up notifications.

Is PostgreSQL autovacuum too aggressive?

Ameen AbbasSeptember 17, 2017

I covered vacuum basics in my earlier post. In this post, I'm covering the importance of aggressive autovacuum. Database Administrators who just started their career might have doubts in their mind

How does VACUUM work in PostgreSQL

Richard YenAugust 24, 2017

I recently had a customer who was trying to figure out why his VACUUM-for-wraparound was taking so long–he had a 6TB database, and the VACUUM had been running for over 4 days.

How to run EDB Backup and Recovery with Amazon EFS

Adam WrightAugust 23, 2017

If your organization uses EDB Postgres in Amazon Web Services and you want to leverage advanced EDB Backup and Recovery Tool (BART) features such as incremental backup, one way to accomplish this is with Amazon Elastic File System (EFS)

What Happens When I Press Enter? -- EDB failover manager Switchover

Richard YenAugust 18, 2017

nterpriseDB Failover Manager (EFM) is a great tool to automate failover/switchover if you use Postgres' streaming replication feature. Not only do you get High Availability (HA), you can do so with just a few simple commands to make it all happen very quickly.

Using auth_method=hba in PgBouncer

Richard YenAugust 18, 2017

PgBouncer is a great tool for improving database performance with connection pooling. I've been using it for many years, since it first became available in 2007. Since then, several improvements have been implemented, including the ability to use auth_type=hba, which implements a PG-like HBA authentication method similar to the pg_hba.conf format we're all used to

Some numbers, oh, and pgAdmin 4 v1.6 was released.

Dave PageJuly 25, 2017

For those that aren't aware, pgAdmin 4 is the fourth complete rewrite of pgAdmin since the first code was written way back in 1998(!). One of the major aims of the technology change we made for the new version was to help attract new developers as we always found them extremely hard to find when we used C++ (we now use Python and Javascript).

How to Leverage Local Temporary Table for Global Temporary Table in EDB Postgres

Vibhor KumarJuly 6, 2017

In the past, I posted a blog on the concept of creating a Global Temporary Table (GTT) for migration from Oracle to EDB Postgres. In that blog I had shared the following characteristics a Global Temporary Table

Demonstration of using SQL Protect with that Postgres cluster

Timothy StewardJune 4, 2017

Quickly (less than five minutes) install and run EPAS 9.5/9.6 on RHEL 5/6 and demonstrate using SQL Protect with that Postgres cluster.

Quickstart guide on using pgPool

Timothy StewardJune 4, 2017

This guide is intended to get you up and started with a default installation of pgPool in front of a master and streaming replica. There is much more to pgPool that is not covered in this quickstart, the intent here is only to get you a simple configuration working as quickly as possible.