Recently I've seen case like:| select * from depesz;
In previous post in the series I wrote about how to interpret single line in explain analyze output, it's structure, and later on described all basic data-getting operations (nodes in explain tree).| select * from depesz;
On 29th of July 2025, David Rowley committed patch:| select * from depesz;
Got interested recently in speed of pg_dump. Specifically, if, over the years, it has became faster, and if yes, how much.| select * from depesz;
On 8th of April 2025, Daniel Gustafsson committed patch:| select * from depesz;
On 7th of April 2025, Álvaro Herrera committed patch: Allow NOT NULL constraints to be added as NOT VALID This allows them to be added without scanning the table, and validating them afterwards without holding access exclusive lock on the table after any violating rows have been deleted or fixed. Doing ALTER TABLE … Continue reading "Waiting for PostgreSQL 18 – Allow NOT NULL constraints to be added as NOT VALID"| select * from depesz;
On 5th of April 2025, Álvaro Herrera committed patch: Add modern SHA-2 based password hashes to pgcrypto. This adapts the publicly available reference implementation on https://www.akkadia.org/drepper/SHA-crypt.txt and adds the new hash algorithms sha256crypt and sha512crypt to crypt() and gen_salt() respectively. Author: Bernd Helmle <mailings@oopsware.de> Reviewed-by: Japin Li <japinli@hotmail.com> Discussion: https://postgr.es/m/c763235a2757e2f5f9e3e27268b9028349cef659.camel@oopswar...| select * from depesz;
On 4th of April 2025, Andrew Dunstan committed patch: Non text modes for pg_dumpall, correspondingly change pg_restore pg_dumpall acquires a new -F/--format option, with the same meanings as pg_dump. The default is p, meaning plain text. For any other value, a directory is created containing two files, globals.data and map.dat. The first contains SQL … Continue reading "Waiting for PostgreSQL 18 – Non text modes for pg_dumpall, correspondingly change pg_restore"| select * from depesz;
On 5th of March 2025, Andrew Dunstan committed patch: Allow json{b}_strip_nulls to remove null array elements An additional paramater ("strip_in_arrays") is added to these functions. It defaults to false. If true, then null array elements are removed as well as null valued object fields. JSON that just consists of a single null is not … Continue reading "Waiting for PostgreSQL 18 – Allow json{b}_strip_nulls to remove null array elements"| select * from depesz;
On 11st of December 2024, Masahiko Sawada committed patch: Add UUID version 7 generation function. This commit introduces the uuidv7() SQL function, which generates UUID version 7 as specified in RFC 9652. UUIDv7 combines a Unix timestamp in milliseconds and random bits, offering both uniqueness and sortability. In our implementation, the 12-bit sub-millisecond … Continue reading "Waiting for PostgreSQL 18 – Add UUID version 7 generation function."| select * from depesz;
I once wrote about this problem, but given that we now have DO blocks and procedures I can make nicer, easy to use example. Over the years there have been many improvements to how long ALTER TABLE can take. You can now (in some cases) change datatype without rewrite or add default value. Regardless how … Continue reading "How to ALTER tables without breaking application?"| select * from depesz;
Every now and then I see something like this: select u.* from users u where 0 = (select count(*) from addresses a where a.user_id = u.id); and it kinda pains me. So figured, I'll write about what is the problem with it, and how to avoid such constructs. Lets consider what went through someones mind … Continue reading "SQL best practices – don’t compare count(*) with 0"| select * from depesz;
Based on checking logs, and my own personal needs I added more categories of keywords to pgdoc.link: environment variables, like: PGHOST config file names, like: pgpass functions from some contrib modules, like: akeys, or cube_ll_coord various keyword-like functions (?), like: current_user. This happened thanks to bug report by Benjie Gillam This brought the total number … Continue reading "Changes on pgdoc.link"| select * from depesz;
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 timestamp truncated to hour. Basically, storing somewhere result of: … Continue reading "Grouping data into array of sums – fun with custom aggregates"| select * from depesz;
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 🙂| select * from depesz;
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 called array_reverse_n() that … Continue reading "Waiting for PostgreSQL 18 – Add SQL function array_reverse()"| select * from depesz;
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 way to search PostgreSQL documentation"| select * from depesz;
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 picked bad plan"| select * from depesz;
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”)"| select * from depesz;
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 foreign keys, but … Continue reading "Waiting for PostgreSQL 18 – Add temporal FOREIGN KEY contraints"| select * from depesz;
On 3rd of March 2025, Tomas Vondra committed patch:| select * from depesz;
On 21st of February 2025, Robert Haas committed patch:| select * from depesz;
On 7th of February 2025, Peter Eisentraut committed patch:| select * from depesz;
On 14th of February 2025, Nathan Bossart committed patch:| select * from depesz;
On 11st of February 2025, Nathan Bossart committed patch:| select * from depesz;
On 22nd of January 2025, Tom Lane committed patch:| select * from depesz;
This question happens every now and then in one of PostgreSQL support places.| select * from depesz;
On 16th of January 2025, Dean Rasheed committed patch:| select * from depesz;
On 6th of January 2025, Nathan Bossart committed patch:| select * from depesz;
On 11st of December 2024, David Rowley committed patch:| select * from depesz;
On 27th of November 2024, Peter Eisentraut committed patch:| select * from depesz;
On 18th of December 2024, Michael Paquier committed patch:| select * from depesz;
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).| select * from depesz;
On 16th of January 2024, Peter Eisentraut committed patch:| select * from depesz;
Ever been in situation where you had to sort data that is partially text, and partially numerical?| select * from depesz;
On 14th of December 2022, Peter Eisentraut committed patch:| select * from depesz;