Neues vom PostgreSQL Planet

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.

A reader provided us a feedback about a wrong listing.

PostgreSQL 11 Server Side Programming Errata Corrige

I have already written about how my first book on PostgreSQL, named PostgreSQL 11 Server Side Programming Quick Start Guide, gained more attention.

Overview This article is an extended version atop of the previous article which was a kickstart for building an application using Spring Boot and PostgreSQL. There is no internal feature supported by Java which offers mapping between class objects and database tables; therefore, we use Object Relational Model (ORM). JPA is an abstraction above JDBC […]

Since SQL/MED (Management External Data) was implemented in PostgreSQL, hundreds of projects have emerged that try to connect PostgreSQL with other data sources. Just by doing a simple search on GitHub with the keys “postgres” + “fdw” you can figure that out.

My extensions and applications uses markdown format for documentation. There is new nice viewer - mandown - that allows viewing in terminal.

https://github.com/Titor8115/mandown

I have recently covered the importance of libpq environment variables and connection specification options. While most libpq options control how to connect to the Postgres server, there is one special option that can change variables on the server you connect to, e.g.:

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 confusion, so in this blog post I want to try to describe how […]

PostgreSQL Person of the Week Interview with Tomas Vondra: My name is Tomas Vondra, I live in Prague, and I’m a PostgreSQL user, developer, contributor and committer. I work for 2ndQuadrant, one of the companies contributing to PostgreSQL and providing services related to it, and I’m also involved in the local PostgreSQL community in various ways. Aside from that I do have various sports-related hobbies - cycling for example.

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 confusion, so in this blog post I want to try to describe how […]

Our latest release to the Citus open source extension to Postgres is Citus 9.3.

If you’re a regular reader of the Citus Blog, you already know Citus transforms Postgres into a distributed database, distributing your data and SQL queries across multiple servers. This post—heavily inspired by the internal release notes that lead engineer Marco Slot circulated internally—is all about what’s new & notable in Citus 9.3.

Postgres is a database software engine with a lot to love, and certainly a non-zero amount to hate. Rather than approaching the topic from a glib and clickbaity angle, let’s see what’s really lurking in those dark recesses, and how we may potentially address them. Though the original blog post calling out Postgres’ faults gave […]

I have previously covered the importance of libpq and environment variables. While you can specify discrete connection command-line parameters and environment variables, there is a catch-all setting that allows connection options to be specified in a single string, e.g.:

Transaction is the most basic concept of a database. Using begin and end command in PostgreSQL can start and commit a transaction. Of course, this is the most common PostgreSQL transaction. In addition, there are sub transaction, multi transaction, 2pc transaction concepts in PostgreSQL. In this blog, I will demonstrate the emergence scenario and kernel implementation of these PostgreSQL transactions.

pgbackrest is an amazing backup solution for PostgreSQL, quite frankly it is my favourite. And now fully supports FreeBSD too!

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 Machine Learning model. This version 11.6-1 combines: PostgreSQL […]

Have you ever watched a team of five-year-olds play soccer? The way the mass of children chases the ball around in a group? I think programmers do that too.

There’s something about working on a problem together that is so much more rewarding than working separately, we cannot help but get drawn into other peoples problems. There’s a lot of gratification to be had in finding a solution to a shared difficulty!

Even better, different people bring different perspectives to a problem, and illuminate different areas of improvement.

So, you have a partitioned table. And you want to change your mind. Re-partitioning is "easy" if you can take downtime -- just create a new table with a new name and copy all the data over. But what if we want to try to do it without downtime? Logical replication enhancements in PostgreSQL 13 brings us some new options for this!

But first a disclaimer -- this is definitely not pretty! And does not take into consideration things like foreign keys and similar. But sometimes a quick hack can be the best hack.

So let's go!

Most queries against a database are short lived. Whether you're inserting a new record or querying for a list of upcoming tasks for a user, you're not typically aggregating millions of records or sending back thousands of rows to the end user. A typical short lived query in Postgres can easily be accomplished in a few milliseconds or less.

Libpq is the Postgres connection library used by almost every non-JDBC application. It allows many connection parameters, which can be specified on the command line or embedded in applications:

$ psql -h myhost.com -d mydb

Continue Reading »

PostgreSQL is a really powerful database and offers many features to make SQL even more powerful. One of these impressive things is the concept of a composite data type. In PostgreSQL a column can be a fairly complex thing. This is especially important if you want to work with server side stored procedures or functions. However, there are some details people are usually not aware of when making use of stored procedures and composite types.