Neues vom PostgreSQL Planet

I released pspg 4.6.0 There are few interesting features against lastly mentioned here 4.3.0 release: 

Recently on twitter, i have seen a poll by Qovery asking how hard is it to create a database like PostgreSQL. With no surprise, people mostly voted it as “Very hard”. However, the word “like” in this question is very ambiguous, as rightly said in one of the comments by Dave Cramer, who is one of the very active contributors and committers of PostgreSQL JDBC driver.

Connection pooling and management is one of those things most people ignore far too long when it comes to their database. When starting out, you can easily get by without it. With 1 or 2 application servers spawning 5-10 connections, even the tiniest of Postgres servers can handle such.

Postgres has a where, when, and what set of options to control database server log messages. One frequent request is to allow log message to be split into multiple files or sent to multiple servers.

As part of my work on the CTO Team here at EDB, I do daily benchmarking of the PostgreSQL development branch with 500 concurrent users in order to make sure performance does not go down and to identify when in case it does. This also helps us determine which features improve performance for this TPROC-C workload. [Continue reading...]

You have a huge table, and it is necessary to change a column's data type, but your database has to keep running with no downtime. What do you do?

Here's one way to perform this change, in as unobtrusive a manner as possible while your table keeps serving users, by avoiding long DDL table locks and leveraging procedural transaction control.

Video from my short talk at this year's #FOSDEM #PostgreSQL 🐘 devroom 👇

I don’t usually post about upcoming PostgreSQL features and rather concentrate on tools available / versions released… but this feature got me excited and will certainly be a huge relief for real-life usage, especially for beginners! I had almost lost hope we might see this day – but after many years of wishing for it, one of the features most requested by normal business users/application developers has landed in the repository. Kudos to the author and reviewer!

There are many advantages to using bind variables in SQL statements, especially in conditional statements. Using bind variables can save the trouble to parse and optimize the query in every execution. There are also disadvantages such as bind variable will make optimizer lost its prediction accuracy and cause performance decrease. In PostgreSQL are represented by $ sign preceded with a number, just like ‘$n’. Have you ever wondered how to pass and process these parameters in Postgres? In general, a query with bind variables will be transformed to a query tree with Param nodes.

Last time we got acquainted with the structure of an important component of the shared memory — the buffer cache. A risk of losing information from RAM is the main reason why we need techniques to recover data after failure. Now we will discuss these techniques.

The log

Sadly, there's no such thing as miracles: to survive the loss of information in RAM, everything needed must be duly saved to disk (or other nonvolatile media).

Postgres has a lot of settings — 329 in Postgres 13. Most of these settings control one specific thing, and the defaults are fine for most use-cases. Synchronous_commit is one of the settings that isn't simple and controls perhaps too many things, though there are operational restrictions which prevent it from being split into separate settings.

Some time ago James Courtney reported missing functionality. Specifically, when one uses auto-explain, logged explains contain query text. So, when such explain is then pasted on explain.depesz.com, it stands to reason that it should be able to extract the query on its own, without having to manually extract it and put it in query box. … Continue reading "Changes on explain.depesz.com – extracted query from auto-explain plans"

Additional Contributors: David Christensen, Jonathan Katz, and Stephen Frost

In part 1 of this blog mini-series on machine learning, we looked at how to setup PostgreSQL so that we can perform regression analysis on our data using TensorFlow from within the database server using the pl/python3 procedural language. We also looked at some of the basics of pl/python3 as a primer for what is to come in this and part 3 of the series.

Query parallelism is supported in PostgreSQL since quite a while now. People in the PG community call it "Parallel query", but by now it is not limited to just SELECT queries. Index build leverages multiple cores; and even utilities like VACUUM now make use of parallelism. Furthermore, community is working on parallelizing COPY and INSERTs.                                                                                  I was interested to do kind-of "sanity" check of this capability specifically on ARM64 platform. Let's see how it goes.

PostgreSQL allows end users to store BLOBs (binary large objects) in the database. Many people use these functions to handle data directly in SQL. There has long been a discussion about whether this is a good thing or not. We expect that particular discussion to go on indefinitely. However, if you are in the “pro BLOB” camp, we want to share some insights into how binary data can be handled in PostgreSQL with maximum efficiency.

Postgres makes extensive use of operating system shared memory, and I have already written a presentation about it. The Postgres documentation gives specific instructions on how to determine the amount of shared memory allocated, specifically for sizing huge pages.

PostgreSQL Person of the Week Interview with Devrim Gündüz: I’m a community person. Ex-Turkey, now Londoner. Started using Linux in 1996, and PostgreSQL in 1998. Love to travel, listen to music, and enjoy life with friends. Started working around 2000. Joined PostgreSQL community around 1999. Working for EnterpriseDB since 2010, and also running two PostgreSQL companies in Turkey and in the UK.

One of the most critical topics to understand when administering a PostgresSQL database is the concept of transaction IDs (TXID) and that they can be exhausted if not monitored properly. However, this blog post isn’t going to go into the details of what it TXID exhaustion actually is. The Routine Vacuuming section of the documentation is probably one of the most important to read and understand so I will refer you there.

One of the most critical topics to understand when administering a PostgresSQL database is the concept of transaction IDs (TXID) and that they can be exhausted if not monitored properly. However, this blog post isn’t going to go into the details of what it TXID exhaustion actually is. The Routine Vacuuming section of the documentation is probably one of the most important to read and understand so I will refer you there.

Last month we released Citus 10 and we’ve received an overwhelming amount of positive feedback on the new columnar compression and single node Citus features, as well as the news that we’ve Seiten