Was asked recently about optimization of interesting case. There was table like: =$ CREATE TABLE input_data ( category_id INT8, object_id INT8, interaction_ts timestamptz, interaction_type TEXT, interaction_count INT4 ); And there was a code that was grouping it all by sum()ing interaction_count per category, object, interaction_type, and...
Checking logs for pgdoc.link I noticed that some people where searching for system views, like pg_stat_activity, or pg_stat_all_tables in Pg 9.3. Now, these will work. This increased total number of known keywords from 1840 to 1883. Not much, but it's a progress 🙂
On 1st of November 2024, Michael Paquier committed patch: Add SQL function array_reverse() This function takes in input an array, and reverses the position of all its elements. This operation only affects the first dimension of the array, like array_shuffle(). The implementation structure is inspired by array_shuffle(), with a subroutine...
PostgreSQL documentation is, generally speaking, great. But it isn't the easiest thing to search in. Over the years I memorized urls to certain docs, but there is a limit to it. What's more, there are certain inconsistencies. For example – most pages that describe program have name that starts with app-. But not all. Some … Continue reading "New...
We recently hit an interesting case where planner picked wrong plan. Kinda. And figured it could be used to show how to deal with such cases. So, we have some databases on PostgreSQL 14 (yes, I know, we should upgrade, but it is LONG project to upgrade, so it's not really on the table now). … Continue reading "Case study: optimization of weirdly...
Amazing. Awesome. Well, but what is it? We could store json data in Pg since PostgreSQL 9.2 – so it's been there for over 12 years now. How is the new shiny thing different? What does it allow you to do? Let's see if I can shed some light on it… For starters: SQL/JSON is … Continue reading "SQL/JSON is here! (kinda “Waiting for Pg 17”)"
On 17th of September 2024, Peter Eisentraut committed patch: Add temporal FOREIGN KEY contraints Add PERIOD clause to foreign key constraint definitions. This is supported for range and multirange types. Temporal foreign keys check for range containment instead of equality. This feature matches the behavior of the SQL standard temporal...
On 17th of September 2024, Peter Eisentraut committed patch: Add temporal PRIMARY KEY and UNIQUE constraints Add WITHOUT OVERLAPS clause to PRIMARY KEY and UNIQUE constraints. These are backed by GiST indexes instead of B-tree indexes, since they are essentially exclusion constraints with = for the scalar parts of the key and && for … Continue...
We had a case recently where one of our DBs failed over to a new primary. To this server (old primary, and then new primary) we had connection from some kind of CDC tool (Debezium, I think). The thing is that while there was failover, this tool read (using logical decoding) changes on old primary … Continue reading "What tables were touched...
I've been to PGConf.dev recently, and one of the talks was about collations. The whole talk was interesting (to put it mildly), but the thing that stuck with me is that we really shouldn't be using default collation provider (libc with locale based collation), unless it's really needed, because we're wasting performance. But how much … Continue...
I thought about it for quite some time, whether I should write about it, and how. That's why there is delay since: On 6th of April 2024, Alexander Korotkov committed patch: Implement ALTER TABLE ... MERGE PARTITIONS ... command This new DDL command merges several partitions into the one partition of the target table. … Continue reading "Waiting...
This is not the usual Waiting for post, but something should be said. Back in March/April of 2022 Andrew Dunstan committed a series of patches that added support for lots of really interesting features from SQL/JSON standard. While I'm not avid user of json in database, I was very, very happy. Wrote couple of blogposts … Continue reading "Waiting...
On 3rd of April 2024, Tom Lane committed patch: Invent SERIALIZE option for EXPLAIN. EXPLAIN (ANALYZE, SERIALIZE) allows collection of statistics about the volume of data emitted by a query, as well as the time taken to convert the data to the on-the-wire format. Previously there was no way to investigate this without actually … Continue...
On 1st of April 2024, Masahiko Sawada committed patch: Add new COPY option LOG_VERBOSITY. This commit adds a new COPY option LOG_VERBOSITY, which controls the amount of messages emitted during processing. Valid values are 'default' and 'verbose'. This is currently used in COPY FROM when ON_ERROR option is set to ignore. If 'verbose' …...
On 30th of March 2024, Dean Rasheed committed patch: Add support for MERGE ... WHEN NOT MATCHED BY SOURCE. This allows MERGE commands to include WHEN NOT MATCHED BY SOURCE actions, which operate on rows that exist in the target relation, but not in the data source. These actions can execute UPDATE, DELETE, or … Continue reading "Waiting for...
On 17th of March 2024, Dean Rasheed committed patch: Add RETURNING support to MERGE. This allows a RETURNING clause to be appended to a MERGE query, to return values based on each row inserted, updated, or deleted. As with plain INSERT, UPDATE, and DELETE commands, the returned values are based on the new contents … Continue reading "Waiting...
Recently someone asked on Slack about what is transaction wraparound. Full answer is a bit too much for slack reply, but I can try to explain it in here. So, every row in PostgreSQL containst two hidden columns: xmin, and xmax. You can see them: =$ CREATE TABLE wrapit (a int4); CREATE TABLE =$ … Continue reading "What the hell is transaction...
On 16th of January 2024, Alexander Korotkov committed patch: Add new COPY option SAVE_ERROR_TO Currently, when source data contains unexpected data regarding data type or range, the entire COPY fails. However, in some cases, such data can be ignored and just copying normal data is preferable. This commit adds a new option SAVE_ERROR_TO, …...
On 16th of January 2024, Peter Eisentraut committed patch: Support identity columns in partitioned tables Previously, identity columns were disallowed on partitioned tables. (The reason was mainly that no one had gotten around to working through all the details to make it work.) This makes it work now. Some details on the behavior: … Continue...
On 4th of January 2024, Tom Lane committed patch: In plpgsql, allow %TYPE and %ROWTYPE to be followed by array decoration. This provides the useful ability to declare a variable that is an array of the type of some other variable or some table column. Quan Zongliang, Pavel Stehule Discussion:...
On 4th of January 2024, Peter Eisentraut committed patch: ALTER TABLE command to change generation expression This adds a new ALTER TABLE subcommand ALTER COLUMN ... SET EXPRESSION that changes the generation expression of a generated column. The syntax is not standard but was adapted from other SQL implementations. This command causes …...
On 20th of December 2023, Robert Haas committed patch: Add support for incremental backup. To take an incremental backup, you use the new replication command UPLOAD_MANIFEST to upload the manifest for the prior backup. This prior backup could either be a full backup or another incremental backup. You then use BASE_BACKUP with the INCREMENTAL …...
Very long time ago (in PostgreSQL 9.2) we got ability to get, from EXPLAIN, information how long Pg spent on I/O operations. Reads and writes. Over the years situation has changed. Originally it looked like this: I/O Timings: read=15.792 write=26.804 This told us that Pg spend 15.8ms reading from disk, and 26.8ms writing. In Pg … Continue reading...
Couple of months ago I wrote how to do human sort in Pg by using arrays, and splitting string. This works, but Matt mentioned in comments that it could be done with ICU collations. So I looked into it … First, I figured I'll write longer blogpost about collations, but to be honest – I … Continue reading "Human/version sort in PostgreSQL –...
On 16th of October 2023, Alexander Korotkov committed patch: Add support event triggers on authenticated login This commit introduces trigger on login event, allowing to fire some actions right on the user connection. This can be useful for logging or connection check purposes as well as for some personalization of environment. Usage details...
On 29th of August 2023, Daniel Gustafsson committed patch: Allow \watch queries to stop on minimum rows returned When running a repeat query with \watch in psql, it can be helpful to be able to stop the watch process when the query no longer returns the expected amount of rows. An example would be … Continue reading "Waiting for PostgreSQL 17 –...
On 26th of August 2023, Michael Paquier committed patch: Generate new LOG for "trust" connections under log_connections Adding an extra LOG for connections that have not set an authn ID, like when the "trust" authentication method is used, is useful for audit purposes. A couple of TAP tests for SSL and authentication need … Continue reading...
On 23rd of August 2023, Nathan Bossart committed patch: Add to_bin() and to_oct(). This commit introduces functions for converting numbers to their equivalent binary and octal representations. Also, the base conversion code for these functions and to_hex() has been moved to a common helper function. Co-authored-by: Eric Radman Reviewed-by:...
Ever been in situation where you had to sort data that is partially text, and partially numerical? Like invoice numbers: prefix-9, prefix-10, prefix-11, other-5, other-20 ? Normally you can't do order by as you will get them in wrong order: other-20 ⇒ other-5 ⇒ prefix-10 ⇒ prefix-11 ⇒ prefix-9. Can something be done with it? … Continue reading...
This problem happened recently to couple of people on various Pg support channels, so I figured I can write a bit more about it, so that in future I have a place where I can refer people to 🙂 Before we will go any further, please consider reading my earlier explanation on what wal is, … Continue reading "Why is my WAL directory so large?"
This might not interest many of you, but I recently heard about at least two people that stumbled upon the problems I did, so I figured I can write about problems we discovered, and how we solved them (or not). When we began our journey, the latest Pg was 14.x, that's why we're upgrading to … Continue reading "A tale about (incomplete) upgrade...
psql, the database client for PostgreSQL has, since forever, support for variables. These let you write certain queries in a way that is safe even when getting params from “outside". Let's see what can be done with it… To use them, we first need to know how to set them. For this, we have multiple … Continue reading "Variables in psql, how to use...
On 7th of April 2023, Tom Lane committed patch: Add array_sample() and array_shuffle() functions. These are useful in Monte Carlo applications. Martin Kalcher, reviewed/adjusted by Daniel Gustafsson and myself Discussion: https://postgr.es/m/9d160a44-7675-51e8-60cf-6d64b76db831@aboutsource.net Arrays are supported in PostgreSQL since...
Not everyone knows, but at since PostgreSQL 9.6, we have some built-in roles. Of course, there is always superuser (usually called postgres), but I'm not talking about it. I'm talking about magical roles that have names starting with pg_. Over the years we got more and more of them: pg_signal_backend (since PostgreSQL 9.6) pg_monitor (since …...
On 11st of February 2023, Andres Freund committed patch: Add pg_stat_io view, providing more detailed IO statistics Builds on 28e626bde00 and f30d62c2fc6. See the former for motivation. Rows of the view show IO operations for a particular backend type, IO target object, IO context combination (e.g. a client backend's operations on permanent...