Shaun Thomas’s recent post about client-side loops as an SQL anti-pattern is well worth a read if you’re relatively new to SQL-based application development. It’s reminded me of another SQL coding anti-pattern that I see quite a lot: the naïve read-modify-write cycle. Here I’ll e … | Continue reading
This post looks at full-text search, i.e. the ability to index and search in large amounts of text data. The same infrastructure (especially the indexes) may be useful for indexing semi-structured data like JSONB documents etc. but that’s not what this benchmark is focused on. | Continue reading
In the first part of this blog series, I’ve presented a couple of benchmark results showing how PostgreSQL OLTP performance changed since 8.3, released in 2008. In this part I plan to do the same thing but for analytical / BI queries, processing large amounts of data. There’s a n … | Continue reading
A couple years ago (at the pgconf.eu 2014 in Madrid) I presented a talk called “Performance Archaeology” which showed how performance changed in recent PostgreSQL releases. I did that talk as I think the long-term view is interesting and may give us insights that may be very valu … | Continue reading
PostgreSQL logs can contain really useful information for performance troubleshooting and auditing - provided logging has been configured properly. Here’s how you can get the best out of Postgres logs. | Continue reading
A simple description of the new WITH TIES standard feature in PostgreSQL 13. | Continue reading
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
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
How can you run multiple instances of PgBouncer on a single host? Peter Eisentraut explores in this blog. | Continue reading
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
Part 1 of the series on “10 Things #Postgres Could Improve” starts with examining Postgres XID wraparound complications in greater depth. | Continue reading
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
How do you get a working NSS on Windows to use for #PostgreSQL development? Andrew Dunstan shares how. | Continue reading
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
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
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
Kirk Roybal continues his ‘Migration from Oracle to PostgreSQL series’ by detailing the START WITH/CONNECT BY construct. | Continue reading
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
We discuss some techniques to test code changes on and for Windows if it’s not your primary development environment. | Continue reading
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
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
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
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
Prepare a development environment for a new procedural language. | Continue reading
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
sequential-uuids extension introduces generators of sequential UUIDs, addressing some of the common issues - random I/O patterns and WAL write amplification | Continue reading
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
Wherein I describe foreign keys in conjunction with partitioned tables in PostgreSQL 12. | Continue reading
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