PostgreSQL 13 helps 'fairness' of sort queries

A simple description of the new WITH TIES standard feature in PostgreSQL 13. | Continue reading


@2ndquadrant.com | 6 days ago

Tips for PostgreSQL bulk data loading

Sometimes, PostgreSQL databases need to import large quantities of data in a single or a minimal number of steps. This process can be sometimes unacceptably slow. In this article, we will cover some best practice tips for bulk importing data into PostgreSQL databases. | Continue reading


@2ndquadrant.com | 7 days ago

Advanced partition matching for partition-wise join

Yet another optimizer improvement for partitioned tables. Know more about partition-wise join and how advanced partition matching algorithm allows it to be used in more cases. | Continue reading


@2ndquadrant.com | 7 days ago

Running multiple PgBouncer instances with systemd

How can you run multiple instances of PgBouncer on a single host? Peter Eisentraut explores in this blog. | Continue reading


@2ndquadrant.com | 9 days ago

Postgres on ZFS (2017)

ZFS and Postgres are two words that are rarely used in the same sentence. It turns out ZFS complements Postgres perfectly, and we'll explore how. | Continue reading


@2ndquadrant.com | 12 days ago

PG Phriday: Things Postgres Could Improve – Part 1

Part 1 of the series on “10 Things #Postgres Could Improve” starts with examining Postgres XID wraparound complications in greater depth. | Continue reading


@2ndquadrant.com | 14 days ago

Oracle to PostgreSQL – Cursors and Common Table Expressions

In the next installment of the “Migration from Oracle to PostgreSQL” series, Kirk explores the ‘Common Table Expression’ (CTE) alternative to using cursors in PostgreSQL. | Continue reading


@2ndquadrant.com | 16 days ago

NSS on Windows for PostgreSQL Development

How do you get a working NSS on Windows to use for #PostgreSQL development? Andrew Dunstan shares how. | Continue reading


@2ndquadrant.com | 21 days ago

PostgreSQL 13: Don’t let slots kill your primary

Limiting replication slot size can save you primary database server from abrupt shutdown. Learn here how to protect your production systems against this problem. | Continue reading


@2ndquadrant.com | 27 days ago

Updating the PostgreSQL root.crt file for TLS client auth

Let’s say you have two PostgreSQL servers that connect to each other using postgres_fdw. They communicate over TLS and they use client certificates to authenticate. You have a Certificate Authority you have set up, and it has issued server and client certificates for each server. … | Continue reading


@2ndquadrant.com | 29 days ago

Oracle to PostgreSQL – Binary Objects

In continuation of the “Migration from Oracle to PostgreSQL” series, Kirk discusses in detail about handling binary data in PostgreSQL & how to make architectural choices coming from the Oracle perspective. | Continue reading


@2ndquadrant.com | 29 days ago

Oracle to PostgreSQL: Start with/Connect By

Kirk Roybal continues his ‘Migration from Oracle to PostgreSQL series’ by detailing the START WITH/CONNECT BY construct. | Continue reading


@2ndquadrant.com | 1 month ago

Why choose immutable application containers for Cloud Native Postgres products

Read why we chose immutable application containers for our CloudNative PostgreSQL and BDR products and relied on the kubernetes API for High Availability. | Continue reading


@2ndquadrant.com | 1 month ago

Unicode Normalization in PostgreSQL 13

Unicode equivalence Unicode is a complicated beast. One of its numerous peculiar features is that different sequences of codepoints can be equal. This is not the case in legacy encodings. In LATIN1, for instance, the only thing that is equal to ‘a’ is ‘a’ and the only thing that … | Continue reading


@2ndquadrant.com | 2 months ago

Barman Cloud – Part 2: Cloud Backup

In the first part of this blog, Jonathan explained how the barman-wal-archive command works. Now, assuming you followed those instructions, you have a properly configured PostgreSQL instance up and running. In this second part, I will show you how the barman-cloud-backup command … | Continue reading


@2ndquadrant.com | 2 months ago

Bulk Transactions with RESTful CRUD API Using PostgreSQL and Spring Boot

Learn how to develop and use bulk operation CRUD API in Spring Boot with PostgreSQL database, how JPA provides the support for bulk operations and more. | Continue reading


@2ndquadrant.com | 2 months ago

RESTful CRUD API Using PostgreSQL and Spring Boot – Part One

Take a deep dive into the creation of RESTful CRUD application using Spring Boot and its connectivity with PostgreSQL. Here’s the first of two-series blog by Muhammad Haroon. | Continue reading


@2ndquadrant.com | 2 months ago

Keep your libraries updated and nobody gets hurt

Sometimes skipping an update or two for a library means that we can trigger unexpected breakages in other software that are very hard to troubleshoot. | Continue reading


@2ndquadrant.com | 2 months ago

Local Persistent Volumes and PostgreSQL Usage in Kubernetes

Can I use PostgreSQL in Kubernetes and expect to achieve performance results of the storage that are comparable to traditional installations on bare metal or VMs? In this article I go through the benchmarks we did in our own Private Cloud based on Kubernetes 1.17 to test the perf … | Continue reading


@2ndquadrant.com | 2 months ago

Oracle to PostgreSQL: Reasons to Migrate

PostgreSQL has quite a few compelling advantages, some of which are not found in any other database management system. This article will outline the ones that are most interesting to the Oracle developer seeking to do a migration or investigate alternatives for new development. T … | Continue reading


@2ndquadrant.com | 2 months ago

Oracle to PostgreSQL: Basic Architecture

This article provides the Oracle database administrator with equivalent PostgreSQL architecture knowledge. The process is a bit loose, but it is sufficient to bootstrap the concepts that are transferable and identify the ones that are not. | Continue reading


@2ndquadrant.com | 3 months ago

Understanding User Management in PgBouncer

PgBouncer is a popular connection proxy and pooler for PostgreSQL. As PgBouncer presents a PostgreSQL protocol interface to client applications, it also handles client authentication. For that, it maintains its own directory of users and passwords. That is sometimes a source of c … | Continue reading


@2ndquadrant.com | 3 months ago

How to Use Tree Machine Learning Model with 2UDA – PostgreSQL and Orange (Pt 4)

This article gives a step by step guide to utilizing Machine Learning capabilities with 2UDA. In this article, we will use examples of Animals to predict whether they are Mammals, Birds, Fish or Insects. Software versions We are going to use 2UDA version 11.6-1 to implement the M … | Continue reading


@2ndquadrant.com | 4 months ago

How to migrate from inheritance-based to declarative partitioning in PostgreSQL

Partitioning is one of the coolest features in the latest PostgreSQL versions. PostgreSQL 10 introduced native partitioning and more recent versions have continued to improve upon this feature. This article provides a guide to move from inheritance based partitioning to declarati … | Continue reading


@2ndquadrant.com | 4 months ago

A tale of password authentication methods in PostgreSQL

Let’s say you want to implement a password authentication method in a client/server protocol. How would you do that and what would be the possible problems? The following is the story of how this was done in PostgreSQL. password In the beginning, PostgreSQL only had the method th … | Continue reading


@2ndquadrant.com | 4 months ago

Backup manifests and pg_verifybackup in PostgreSQL 13

PostgreSQL 13 introduces two useful features to enhance automated validation of physical backups: backup manifests and a new tool called pg_verifybackup. In this short article I attempt to provide an overview of them. As you probably know if you are familiar with PostgreSQL and t … | Continue reading


@2ndquadrant.com | 4 months ago

Automated rapid switchover with a BDR database cluster in Kubernetes

Gabriele Bartolini explores how BDR and Kubernetes allow you to reach very high uptimes in a year for a database solution, thanks to the rapid switchover capability. | Continue reading


@2ndquadrant.com | 4 months ago

Using Random Forest Machine-Learning Model with 2UDA (Part 3)

This article gives a step by step guide to utilizing Machine Learning capabilities with 2UDA. In this article, we’ll use examples of Animals to predict whether they are Mammals, Birds, Fish or Insects. Software versions We’re going to use 2UDA version 11.6-1 to implement the Mach … | Continue reading


@2ndquadrant.com | 5 months ago

Developing PostgreSQL for Windows – Part 3

The third and final part of this multi-series blogs by Peter Eisentraut further explores PostgreSQL development in a Windows environment and how to automate the process. | Continue reading


@2ndquadrant.com | 5 months ago

Creating a PostgreSQL procedural language – Part 5 – Returning Results

This example will just be focusing on returning data from user defined functions, specifically returning a value as opposed to using OUT parameters.  The full code is on Github. To quickly review, PL/Julia calls jl_eval_string() to execute Julia code and captures the returning jl … | Continue reading


@2ndquadrant.com | 6 months ago

How to Protect Your PostgreSQL Databases from Cyberattacks with SQL Firewall

In today’s world, organizations increasingly face an unprecedented level of threat of cyberattacks against their information assets. Cyberattacks can come in many forms. One such attack is called SQL injection. With SQL injection, rogue players target the backend database of any … | Continue reading


@2ndquadrant.com | 6 months ago

PostgreSQL is the worlds’ best database

The title is not clickbait or hyperbole. I intend to prove that by virtue of both design and implementation that PostgreSQL is objectively and measurably a better database1 than anything currently available, with or without money considerations.How in the world can I claim and ju … | Continue reading


@2ndquadrant.com | 6 months ago

Developing PostgreSQL for Windows, Part 2

We discuss some techniques to test code changes on and for Windows if it’s not your primary development environment. | Continue reading


@2ndquadrant.com | 6 months ago

Creating a PostgreSQL Procedural Language – Part 4 – Handling Input Parameters

One method to handle input parameters with PL/Julia is to rewrite the body of the user defined function or stored procedure on the fly with the values declared as global variables. | Continue reading


@2ndquadrant.com | 6 months ago

How to deploy a multi-master BDR cluster in Kubernetes

Discover in this article and video how BDR can be easily and quickly deployed on Kubernetes thanks to our Cloud Native BDR Operator. | Continue reading


@2ndquadrant.com | 6 months ago

Creating a PostgreSQL Procedural Language – Part 3 – Executing User Code

A PostgreSQL procedural language handler needs to look up the body of the user defined function or stored procedure for the code to execute.  Remember that the C function executed to handle PL/Julia user defined functions and stored procedures is defined in the SQL file. Thus the … | Continue reading


@2ndquadrant.com | 6 months ago

Creating a PostgreSQL Procedural Language – Part 2 – Embedding Julia

Julia provides an API so that Julia functions can be called from C.  PL/Julia will use this C API to execute Julia code from its user defined functions and stored procedures. Julia’s documentation provides an example C program that starts up the Julia environment, evaluates the e … | Continue reading


@2ndquadrant.com | 7 months ago

Creating a PostgreSQL Procedural Language – Part 1 – Setup

Prepare a development environment for a new procedural language. | Continue reading


@2ndquadrant.com | 7 months ago

Backup and Recovery of Partial WAL Files in PostgreSQL

Being a PostgreSQL database administrator and managing backups on a daily basis, one of the new features of Barman 2.10 that I liked the most is the transparent management of .partial WAL files during recovery. And, most likely, you feel the same if you have been using Barman wit … | Continue reading


@2ndquadrant.com | 9 months ago

Sequential UUID Generators

sequential-uuids extension introduces generators of sequential UUIDs, addressing some of the common issues - random I/O patterns and WAL write amplification | Continue reading


@2ndquadrant.com | 10 months ago

Postgres 12: Using the linear distance operator to find the closest match

K-nearest neighbor answers the question of "What is the closest match?". PostgreSQL 12 can answer this question, and use indexes while doing it. | Continue reading


@2ndquadrant.com | 10 months ago

PostgreSQL 12: Foreign Keys and Partitioned Tables

Wherein I describe foreign keys in conjunction with partitioned tables in PostgreSQL 12. | Continue reading


@2ndquadrant.com | 11 months ago

Replication configuration changes in PostgreSQL 12

Historically, PostgreSQL’s replication configuration has been managed via a configuration parameters stored in a dedicated configuration file, recovery.conf, which has been present in PostgreSQL since the introduction of archive recovery in PostgreSQL 8.0. One of the major change … | Continue reading


@2ndquadrant.com | 11 months ago

Partitioning Enhancements in PostgreSQL 12

PostgreSQL12 brings dramatic improvements in partitioning in PostgreSQL databases. Read all about in Kirk’s in-depth blog. | Continue reading


@2ndquadrant.com | 11 months ago

A few special-case performance enhancements in PostgreSQL 12

With every new release of PostgreSQL, there are a range of performance enhancements. Some are system-wide and affect every user, but most are highly specific to a certain use case. In this post, I am going to briefly highlight three improvements in PG12 that speed up certain oper … | Continue reading


@2ndquadrant.com | 1 year ago

PostgreSQL 12: Partitioning is now faster

Many improvements have been made to partitioning in PostgreSQL 12. Here's some benchmark results so you can see what's been done. | Continue reading


@2ndquadrant.com | 1 year ago

Partitioning Enhancements in PostgreSQL 12

Declarative partitioning got some attention in the PostgreSQL 12 release, with some very handy features. There has been some pretty dramatic improvement in partition selection (especially when selecting from a few partitions out of a large set), referential integrity improvements … | Continue reading


@2ndquadrant.com | 1 year ago

PostgreSQL Interval, Date, Timestamp and Time Data Types

Declarations, Input, arithmetic, functions, constants and usage of PostgreSQL Interval, Date, Timestamp and Time data types. Thank you, Chicago. | Continue reading


@2ndquadrant.com | 1 year ago