Most people who deal with relational databases think of transaction commits as binary operations — the query is running and not yet committed, then it is completed and committed. However, internally, there are many stages to a commit:

• Write commit record to the write-ahead log
• Flush the write-ahead log to durable storage
• Update the clog (slide 57)
• Transfer to replicas
• Mark the commit as visible to other sessions (ProcArrayEndTransaction() updates PGPROC)
• Communicate commit to the client

These steps are implemented in RecordTransactionCommit().

What is interesting is that this process can be interrupted at anytime, by perhaps a server crash or network failure. For example:

• The commit could be flushed to disk but not yet visible to other clients
• The commit could be replicated (and visible to queries on replicas) but not visible to queries on the primary
• Queries could appear committed to other sessions before the client issuing the query receives notification

Continue Reading »

If constraints in general have caught your interest, our interactive learning portal has a whole section on the use of non-spatial constraints, even a video walkthrough!

In our last installment, we covered the use of CHECK constraints to enforce data quality at an object level.

However, spatial data quality usually involves higher order relationships between geometries. Just as a strong non-spatial model will enforce foreign key relationships, spatial constraints can be used to enforce spatial relationships.

A hierarchical query is an SQL query that handles hierarchical model data such as the structure of organizations, living species, and a lot more. All important database engines including PostgreSQL, Oracle, DB2 and MS SQL offer support for this type of query.

However, in some cases hierarchical queries can come with a price tag. This is especially true if trees are deep, complex and therefore require some effort by the database engine to handle all this data. Simple queries are usually not a problem but if trees grow out of proportion “WITH RECURSIVE” (ANSI SQL) and “CONNECT BY” (the old Oracle implementation) might start to be an issue.

ltree: Speeding up tree structures

The ltree module is part of the standard PostgreSQL contrib package which can be found on most servers and can therefore be loaded as a normal extension. It should also be provided by most cloud services including Amazon RDS, AWS and Microsoft Azure.

Ltree implements a data type ltree for representing labels of data stored in a hierarchical tree-like structure. This blog will show how this module can be used to speed up some quite common use cases.

Preparing sample data

The first thing you have to do is to make sure that the ltree extension is loaded into your database server. CREATE EXTENSION will do the job. Then I have created a simple hierarchical structure that has a parent and a child value. This is the standard approach to store this kind of data:

CREATE EXTENSION ltree;

CREATE TABLE t_orga
(
parent_code text,
child_code text,
UNIQUE (parent_code, child_code)
); 

In the next step I have created some test data. Note that ltree can handle labels – please don’t use strings full of special characters here (more on that a little later):

INSERT INTO t_orga
VALUES (NULL, 'A'),
('A', 'B'),
('B', 'C'),
('B', 'D'),
('D', 'E'),
('C', 'F'),
('C', 'G'),
('E', 'H'),
('F', 'I'),
('I', 'J'),
('G', 'K'),
('K', 'L'),
('L', 'M')
;

test=# SELECT * FROM t_orga ;
 parent_code | child_code
-------------+------------
      

Having presented at two online conferences in the past two weeks, presenting at two this coming week, and presenting at many EDB-sponsored webinars, I have learned a few things about online presentations that might be helpful for Postgres organizers, speakers, and attendees:

For Organizers: With no need for speakers and attendees to travel to online conferences, there are many more potential speakers available, and many more potential attendees than for in-person conferences. However, the technical challenges of hosting an online conference are significant because producing and consuming content can require multiple platforms that must be integrated seamlessly for a positive user experience. The content production platform, e.g. Zoom, is often different than the consumption platform, e.g. YouTube, Facebook Live. If the user experience is poor, people will leave because they are not bound to a physical location like an in-person event. Adjusting to the time zones of speakers and attendees can be complex — doing a 24-hour conference like Precona Live (organizer tips) solves many of the time zone problems, but requires moderators from many time zones. The moderator's job is much more extensive for online conferences since they control access, deal with technical problems, and manage the all-important chat channel. For online conferences, chat is the best way to promote attendee engagement. If chat is done well, user engagement during presentations can be even better than in-person conferences.

For Speakers: Just like for organizers, speakers have more technical challenges than in-person conferences — it is harder to engage the audience, more things can go wrong, and attendees can more easily leave. As a speaker, I have a checklist that I always references before each presentation:

• Mute phone, chat, email, and upgrade notifications
• Use a laptop on AC power with wired Ethernet, for reliability
• Have a count-up clock to keep track of the talk duration
• Use a headset so you don't lean toward

The term “Window Functions” never really give much away in terms of the capability and various options they provide. So, it made sense to explore these and while doing so, I thought it’s worth sharing. Turns out, Window functions are quite useful and can simplify writing complicated SQL queries.

When fetching data, you could fetch a tabular list of individual tuples or group those with aggregate functions. However, there are times when we need to include aggregate values in individual tuples; e.g. a list of products with each row containing category-wise average price, or perhaps, there is a need to calculate value from the previous or the next tuple. You can imagine that SQL becomes rather complicated.

Definition of Window Functions

“Window functions give us the ability to perform calculations over a set of rows that are related to the current query row.”

This is similar to the ability that aggregate functions provide except that where aggregate functions return a single row per each group, Window functions return all rows.

Let’s dig straight into the Window functions.

Setting Up The Environment

Let’s create an employee and a department table with department ID as a foreign key in the employee table.

CREATE TABLE dept
(
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

CREATE TABLE emp
(
    emp_id SERIAL PRIMARY KEY, 
    emp_name VARCHAR(50) NOT NULL,
    salary_amount decimal(8,2),
    dept_id integer references dept(dept_id)
);

Time to populate the data into the department table.

INSERT INTO dept(dept_name) 
VALUES 
('Finance'),
('Human Resource'),
('IT');

Let’s populate the employee table with a list of famous scientists, their departments and their salaries. The departments and salaries are assigned using random functions.

INSERT INTO emp (emp_name, salary_amount, dept_id) 
VALUES
('Albert Einstein', FLOOR(RANDOM() * 5 + 1) * 1500, FLOOR(RANDOM() * 3 + 1)),
('Blaise Pascal', FLOOR(RANDOM() * 5 + 1) * 1500, FLOOR(RANDOM() * 3 + 1)),
('Caro

The PostGIS Team is pleased to release PostGIS 2.3.11. This is the last bug fix release of the PostGIS 2.3 series. Please upgrade to 2.4 or higher if you want to continue receiving bug fixes.

• source download
• NEWS
• PDF docs en

If you come across any issues, feel free to report via our ticket tracker https://trac.osgeo.org/postgis or mailing list with details as described here. For security issues, send reports to security@postgis.net.

HighGo Software Inc. (Canada) is pleased to announce the first GitHub community release of MongoDB Logical Decoding plugin v1.0.6 (wal2mongo), which can be used to replicate PostgreSQL database changes to an output format that can be directly fed into the mongo client tool to achieve logical replication between PostgreSQla and MongoDB. Wal2mongo plugin is useful for a case where PostgreSQL is used as the main raw data source to collect data from outside world but MongoDB is used internally for data analytics purposes. Manual data migration between PostgreSQL and MongoDB poses a lot of potential problem and having a logical decoding plugin like wal2mongo can help reduce the data migration complexity between the two databases.

Wal2mongo plugin produces outputs that can be understood by MongoDB without further parsing. For this reason, it can be used with the pg_recvlogical front end tool to achieve a very simple logical replication between the two databases. Simply feed the outputs of pg_recvlogical to the mongo client tool like this.

pg_recvlogical -S slot -f - --start | mongo

Both source and binary releases (for CentOS6, 7) of wal2mongo plugin can be found here

Feature Highlight

• Rich support in data type translation between PostgreSQL and MongoDB
• Support complex data type translation such as arrays, XML, JSON, timestamp …etc
• Support database WAL changes caused by INSERT, UPDATE, and DELETE clauses.
• Support 2 output modes: regular output mode and transaction output mode (for multi-document transaction control)
• Support MongoDB version 3.6, 4.0 and above
• Support multi-publisher translation mode
• Support change stream identification using a combination of cluster name, database name and replication slot name such * that when multiple PostgreSQL servers using the same decoding plugin, the destination MongoDB server is able to distinguish the source of the change stream
• simple, lightweight and feature-rich.
• Can be paired with HighGo replication application to ac

At a company where most all people have some Postgres expertise you can easily learn something new from your coworkers every day about Postgres. In my first week I saw a question in our internal slack that I could guess an answer to, but it wasn't definitive.

There is still significant confusion about characters sets, encodings, and collations. This is because in the real, non-digital world, we usually treat languages, their characters, and ordering as unified, but in the digital world, they are treated separately, and their distinction can be hard to visualize.

These two posted queries illustrate collation in a very creative way. The first query, SELECT chr(i) FROM generate_series(1, 50000) i ORDER BY chr(i) COLLATE "C", outputs characters in their "C" binary order, with ASCII as the first 128 characters, successive groups of languages following, and ending with pictographic languages.

The second query, SELECT chr(i) FROM generate_series(1, 50000) i ORDER BY chr(i) COLLATE "en_US.utf8", outputs the same 50,000 characters in "United States English" UTF8 order. The output starts with pictographic languages, not ASCII. The Latin alphabet appears, but not until line 19068. What is interesting is that there are 118 symbols grouped together that look like 'a', 'a' with diacritics, or have 'a' as part of their symbol. Then 'b' appears with a group of 38 symbols that look like or use 'b', and so on through the Latin alphabet. (If you highlight a character and paste it into a search box, Google will tell you about that Unicode symbol.)

Continue Reading »

Generating simple data sets

Before we get started I want to introduce my favorite set-returning functions which can help you to generate sample data:

test=# SELECT * FROM generate_series(1, 10) AS x;
x
----
1
2
3
4
5
6
7
8
9
10
(10 rows)

All we do here is simply to generate a list from 1 to 10 and print it on the screen. Let us play around with windowing a bit now: There are two cases we got to keep in mind. If the OVER-clause is empty it means that the entire data set is used. If we use ORDER BY it is only the data set up to the point in the sorted list. The following listing contains an example:

test=# SELECT *,
array_agg(x) OVER (),
array_agg(x) OVER (ORDER BY x)
FROM generate_series(1, 10) AS x;
x | array_agg | array_agg
----+------------------------+------------------------
1 | {1,2,3,4,5,6,7,8,9,10} | {1}
2 | {1,2,3,4,5,6,7,8,9,10} | {1,2}
3 | {1,2,3,4,5,6,7,8,9,10} | {1,2,3}
4 | {1,2,3,4,5,6,7,8,9,10} | {1,2,3,4}
5 | {1,2,3,4,5,6,7,8,9,10} | {1,2,3,4,5}
6 | {1,2,3,4,5,6,7,8,9,10} | {1,2,3,4,5,6}
7 | {1,2,3,4,5,6,7,8,9,10} | {1,2,3,4,5,6,7}
8 | {1,2,3,4,5,6,7,8,9,10} | {1,2,3,4,5,6,7,8}
9 | {1,2,3,4,5,6,7,8,9,10} | {1,2,3,4,5,6,7,8,9}
10 | {1,2,3,4,5,6,7,8,9,10} | {1,2,3,4,5,6,7,8,9,10}
(10 rows)

As you can see the last column keeps accumulating more values.

PostgreSQL: ROWS BETWEEN … PRECEDING …. AND … FOLLOWING

Often it is necessary to limit the set of data used by the aggregation function. ROWS BETWEEN … PRECEDING … AND … FOLLOWING allows you to do exactly that. The following example shows how this works:

test=# SELECT *, array_agg(x) OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM generate_series(1, 10) AS x;
x | array_agg
----+-----------
1 | {1,2}
2 | {1,2,3}
3 | {2,3,4}
4 | {3,4,5}
5 | {4,5,6}
6 | {5,6,7}
7 | {6,7,8}
8 | {7,8,9}
9 | {8,9,10}
10 | {9,10}
(10 rows)

What you see is that the data fed to array_agg is seriously restricted. But, the restriction we are using here is a static one. The constants are hardwired. In some cases you might need

Understanding the relationship between LSN and WAL file names.

WAL, LSN and File Names

PostgreSQL stores changes that is going to apply to data into the Write Ahead Logs (WALs), that usually are 16 MB each in size, even if you can configure your cluster (starting from version 11) to different sizes.
PostgreSQL knows at which part of the 16 MB file (named segment) it is by an offset that is tied to the Log Sequence Number (LSN). Let’s see those in action.
First of all, let’s get some information about the current status:

testdb=> SELECT pg_current_wal_lsn(),
          pg_walfile_name( pg_current_wal_lsn() );;
-[ RECORD 1 ]------|-------------------------
pg_current_wal_lsn | C/CE7BAD70
pg_walfile_name    | 000000010000000C000000CE

The server is currently using the WAL file named 000000010000000C000000CE. It is possible to see the relationship between the LSN, currently C/CE7BAD70 and the WAL file name as follows. The LSN is made up by three pieces: X/YYZZZZZZ where:

• X represents the middle part of the WAL file name, one or two symbols;
• YY represents the final part of the WAL file name;
• ZZZZZZ are six symbols that represents the offset within the file name.
  Therefore, given the LSN C/CE7BAD70 we can assume that the middle part of the WAL file name will be C and the last part will be CE, both zero padded to 8 symbols, so respectively 0000000C and 000000CE. Concatenated togehter, they provide us with a file name that ends with 0000000C000000CE. The initial part of the filename is still missing, and that is the timeline the server is running on, in this case 1, zero padded as the other parts, so 00000001 that provides us the final name 000000010000000C000000CE.
  To summarize, the following is the correspondance between the single parts:

LSN  ->              C  /     CE      7BAD70
WAL  -> 00000001 0000000C 000000CE

Please consider that the above example is just to show you the concept, but it is better to use the function pg_walfile_n

You might have seen that there is a pg_lsn data type:

test=> \dTS pg_lsn
              List of data types
   Schema   |  Name  |       Description
------------+--------+-------------------------
 pg_catalog | pg_lsn | PostgreSQL LSN datatype

Continue Reading »

Constraints are used to ensure that data in the database reflects the assumptions of the data model.

• Do foreign keys match up to corresponding keys? (REFERENCES)
• Are mandatory columns filled in? (NOT NULL)
• Are unique values columns in fact unique? (UNIQUE)
• Do other data quality rules pass? (CHECK)

Why enforce data quality rules in the database, and not at the application tier? Because if you qualify all your data at the application tier, you have to duplicate all that logic whenever you add another application that writes to the database.

If you are lucky enough to write a system successful enough to be in production for more than a couple years, the odds of multiple write applications proliferating rapidly converge to 100%. Enforcing core data quality rules in the database is a nice way to ground your system with solid assumptions about data integrity.

Our interactive learning portal has a whole section on the use of non-spatial constraints, if you're interested. There's also a video.

What about spatial, though? Thanks to the magic of PostgreSQL run-time extensions, the geometry type is just another type and all the machinery of constraint checking works just as well for geometry as any other type.

Here are a few examples of simple and complex constraints that spatial database users like to enforce on the geometry data.

Heisenberg’s uncertainty principle

In quantum mechanics, the uncertainty principle […] asserts a fundamental limit to the precision with which the values for certain pairs of physical quantities of a particle, such as position, x, and momentum, p, can be predicted from initial conditions.

Uncertainty principle, Wikipedia

In layman’s terms, what Heisenberg’s principle states is that by measuring a property of a system, you may be altering that system itself: your observation becomes a distorted version of the reality.

In most cases, this distortion is negligible and we can simply ignore it. If we use a thermometer to measure someone’s temperature, some heat will be transferred from the person to the termometer, effectively lowering the person’s temperature. But it should not be noticeable, and well below the error margin of the thermometer.

But what happens when the measurement may not just affect, but rather completely ruin the measurement?

Where the potential lie is

You are probably resorting a lot to use Postgres’ EXPLAIN ANALYZE command when you want to optimize a query’s performance. You probably look at the query nodes, see which ones have the highest execution time and then try to optimize them. The costlier the node is, the biggest return of investment you get if you can optimize it. Obviously, a query optimization may change the query plan altogether, but you get the point: you want to know where most of the query execution time is going.

Now grab your favorite Postgres and run the following commands:

create table i1 as select i from generate_series(1,1000*1000) as i;
create table i2 as select i from generate_series(1,1000) as i;
analyze;
explain analyze select sum(i1.i * i2.i) from i1 inner join i2 using (i);

Note that the first analyze command is not related at all with the explain analyze command that follows it.

Run query. Note the time reported by explain analyze. Now run the query and note the execution time without explain analyze. You can

The functionality of using table partitions to speed up queries and make tables more manageable as data amounts grow has been available in Postgres for a long time already, with nicer declarative support available from v10 – so in general it’s a known technique for developers. But what is not so uniformly clear is the way how low-level partition management is done…as Postgres leaves it to users but no real standard tools or even concepts have emerged.

So it happened again that a customer approached me with a plan to use partitions for an action logging use case…but could not find a nice tool to his liking after a session of googling and was looking for some advice. Sure I know some tools…but my actual advice seemed quite unconventional and surprising for him: Why all the craziness with tools for this simple task? Why not throw together and use something simple yourself?

The problem with tools

Of course tools in a broader sense are necessary and great, we wouldn’t be here today without our stone axe and especially software developers thrive on skilful use of editors, programming languages, etc. But the thing with tools is that you should really know when you actually need to use one! And when it comes to simple, isolated tasks I’d argue that it’s better to delay picking one until you absolutely need one and understand the “problem space” on a sufficiently good level. As adopting some deeply integrated software tools also bring risks to the table – like the added additional complexity. When something then goes sour you’re usually up for some serious sweating / swearing as you quickly need to gain a deep understanding of the tool’s internals (as it was mostly set up some X months or years ago and who remembers all this stuff) – a tough thing to do when the production database is unable to process user queries and your manager is breathing down your neck.

Also most tools or products in the PostgreSQL realm are in the end just GitHub repos with code, but without the associated business model and sup

Event triggers are a very powerful mechanism to react to data structure changes in PostgreSQL.

Inspecting Command Tags and Events in event triggers

While preparing an example for a course of mine about event triggers, I thought I’ve never proposed a catch-all event trigger debugging use case. So, here it is.
Event Triggers are a powerful mechanism that PostgreSQL provides to react to database schema changes, like table or column addition and deletion, object creation, and so on. The official documentatio already presents a couple of example about dropping objects or rewriting tables, so my little example is about more common commands. I create the following function:

CREATE OR REPLACE FUNCTION
f_event_trigger_demo()
RETURNS EVENT_TRIGGER
AS
$code$
DECLARE
event_tuple record;
BEGIN
   RAISE INFO 'Event trigger function called ';
   FOR event_tuple IN SELECT *
                      FROM pg_event_trigger_ddl_commands()  LOOP
                      RAISE INFO 'TAG [%] COMMAND [%]', event_tuple.command_tag, event_tuple.object_type;
   END LOOP;
END
$code$
LANGUAGE plpgsql;

It is quite simple to understand what it does: every time the function is triggered, it asks or the tuples out of the special function pg_event_trigger_ddl_commands(), that provides one tuple for every single command executed. Why multiple tuples? Because you could execute one command that explodes into different sub-commands.
Than, simply, the function does print the command tag and the object type.
Usually command tags are uppercase, while object types are lowercase.
The trigger can be created as follows:

testdb=# create event trigger tr_demo on ddl_command_end execute function f_event_trigger_demo();

It is now simple enough to test the trigger:

testdb=# create table foo();
INFO:  Event trigger function called 
INFO:  TAG [CREATE TABLE] COMMAND [table]
CREATE TABLE

testdb=# alter table foo add column i int default 0;
INFO:  Event trigger function called 
INFO:  TAG [ALTER TABLE] COMMAND [table]
ALTER TABLE

It’s time to test the new PostgreSQL 13 release!

PostgreSQL 13 beta 1 on FreeBSD via pgenv

Five days ago PostgreSQL 13 beta 1 has been released!
It’s time to test the new awesome version of our beloved database. Installing from source is quite trivial, but why not using pgenv to such aim?
Installing on my FreeBSD machine with pgenv is as simple as:

luca@miguel ~ % pgenv build 13beta1
...
PostgreSQL, contrib, and documentation installation complete.
pgenv configuration written to file /home/luca/git/pgenv/.pgenv.13beta1.conf
PostgreSQL 13beta1 built

Are you ready to test it?
Activate it and enjoy:

luca@miguel ~ % pgenv use 13beta1
...
server started
PostgreSQL 13beta1 started
Logging to /home/luca/git/pgenv/pgsql/data/server.log


luca@miguel ~ % psql -U postgres -c "SELECT version();" template1
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 13beta1 on x86_64-unknown-freebsd12.1, compiled by gcc (FreeBSD Ports Collection) 9.2.0, 64-bit
(1 row)

Enjoy!

Introduction

People who have worked with databases for a number of years will inevitably have encountered situations where some abandoned session caused table locks never to be released and basically ground a service to a halt. The most frequent culprit is an idle transaction on an open psql session behind a screen session on someone’s development server. This occurred so frequently that in 2016, Vik Fearing, Stéphane Schildknecht, and Robert Haas introduced idle_in_transaction_session_timeout into Postgres’ list of parameters in the release of version 9.6. However, an uncommitted idle transaction isn’t the only way to stick a wedge in the gears of a database system. Another one that we’ll discuss today involves prepared transactions, which have the potential to do the same thing.

What’s a Prepared Transaction?

Prepared transactions are Postgres’ implementation of two-phase commit. The aim of this feature is to synchronize the commit of several transactions as though they were one transaction. A possible use case for this is a system where there are several databases (think several intentories across geographic regions – something of that sort), and all need to be updated at the same time. The typical set of prepared transactions might look something like this:

1. Issue BEGIN on any relevant database/node
2. Do your CRUD
3. Issue PREPARE TRANSACTION 'any_custom_transaction_name' to disassociate the transaction from your pid and store it in the database
4. Once all databases/nodes have successfully finished the PREPARE TRANSACTION step, and you’re ready to commit, issue COMMIT PREPARED 'your_transaction_name' to commit the changes to disk.

If at any point there’s an error and the transactions need to be rolled back, a ROLLBACK PREPARED 'your_transaction_name' will roll back your transaction (this will need to be run on all relevant databases).

Where’s the Danger?

In the third step above, I hinted at the idea that a prepared transaction would be disassociated with your Postgres

Postgres already documents the ability to backup the database cluster using file system snapshots. Unfortunately, database clusters that use tablespaces often cannot use this method if the storage system doesn't support simultaneous snapshots across file systems.

However, simultaneous snapshots across file systems might not be a hard requirement for Postgres snapshot backups. It might be possible for snapshots to be non-simultaneous as long as the write-ahead log that spans the time frame between snapshots is included in the backup, and checkpoints do not happen during that time frame.

Internally, starting Postgres from a snapshot backup replays write-ahead log records to make a single file system snapshot consistent. Potentially, it could do the same for non-simultaneous snapshots of multiple file systems. However, documenting this, giving users a reliable list of steps to perform, and making sure it always works is probably too complex to justify.

pgBackRest is a well-known powerful backup and restore tool. Old backups and archives are removed by the expire command based upon the defined retention policy.

Since the latest version published last month, new features regarding retention have been committed. We’ll here first overview those changes and then make a tour of the retention policy options that should be available in the next release.

New features

Time-based retention

Add time-based retention for full backups:

The --repo-retention-full-type option allows retention of full backups based 
on a time period, specified in days.

The new option will default to 'count' and therefore will not affect current 
installations. Setting repo-retention-full-type to 'time' will allow the user 
to use a time period, in days, to indicate full backup retention. Using this 
method, a full backup can be expired only if the time the backup completed is 
older than the number of days set with repo-retention-full (calculated from 
the moment the 'expire' command is run) and at least one full backup meets the 
retention period. If archive retention has not been configured, then the default 
settings will expire archives that are prior to the oldest retained full backup. 

For example, if there are three full backups ending in times that are 25 days 
old (F1), 20 days old (F2) and 10 days old (F3), then if the full retention 
period is 15 days, then only F1 will be expired; F2 will be retained because 
F1 is not at least 15 days old.

This possibility opens a lot of opportunities. However, it’s not so obvious that at least one full backup over the time-period defined will be kept.

Expire a specific backup set

Add --set option to the expire command:

The specified backup set (i.e. the backup label provided and all of its 
dependent backups, if any) will be expired regardless of backup retention 
rules except that at least one full backup must remain in the repository.

The description is pretty simple, it will now be possible to expi

Currently, it is impossible to move tables, indexes, and entire tablespaces from one cluster to another — that is because each table and index file is bound to the cluster's infrastructure because of:

• Table and index definitions
• pg_xact (commit/abort/in-progress transaction status records)
• pg_multixact (used for multi-session row locking)

FYI, you can easily move tablespaces to new directories as long as it remains in the same cluster, and move tables and indexes between tablespaces.

So, how could it be made possible? Freeze can remove references to pg_xact and pg_multixact, assuming there are no active transactions during the freeze operation. Table and index definitions can be more complex, but it certainly seems possible. This requires more research.

Missing Piece: Failover of the Logical Replication Slot

Logical decoding capability has existed in PostgreSQL for the last several versions, and a lot more functionalities are added over time.  However, one of the very crucial functionalities is still missing in PostgreSQL, which prevents logical replication to be part of critical production environments where downstream expects the logical changes to be streamed over a database connection reliably – even after a high-availability failover.

The root of the problem is that the Logical Replication Slot which exists on the primary is not available in a physical standby.  Meanwhile, almost all PostgreSQL high-availability solutions depend on physical standbys. So if there is a failover, new primary (the old physical standby) won’t be aware of any slots which were existing with old primary. This leads to a completely broken logical replication (not continuous) downstream.  We can create a fresh slot on the new primary and set up a fresh replication, but if the original logical replication was lagging, this leads to data loss.

Designs and Discussions for Solving the Problem

This topic is not new. The historical PostgreSQL community discussion thread is available here. Craig Ringer blogged about it in 2016 following his efforts to provide a patch that should solve the problems. The idea was to write slot-related changes to the WAL logs so that the standby can recreate and maintain slots using the information passed through WAL logs.

Even though this approach of failover slot makes life easy for most of the scenarios, there were some notable objections because a lot more problems around it also needed to be addressed. Ideally, there should be a way to mark whether a slot is a “failover slot” so that the corresponding information will be written to WAL. But every standby which receives the WALs including the cascaded replicas will create and maintain slots. This will result in unnecessary overhead and WAL retention, maintaining old visio

This year, I was so excited about doing a workshop about optimizing Python & Django apps with Postgres superpowers for the PyCon 2020 conference.

Working with other developers on performance is something I always find amazing. So props to the Python people at Microsoft who encouraged my team to create a workshop on Postgres for PyCon 2020. Thank you to Nina Zakharenko, Dan Taylor, & Crystal Kelch.

Alas, we had to change our plans and find other ways to share the PostgreSQL workshop content that we had prepared. So I created a video on the topic of database performance for Django developers, to help teach you the PostgreSQL tips and tricks that have served me well in optimizing my Django apps. These tips are what I call “Postgres superpowers.”

Being myself a developer, and having worked with Django for years, I know that there are a few traps to avoid in order to have fast apps. In this video, I go through a few performance traps to avoid—as well as tools you can use to identify pain points.

Where to find this PyCon video on Django performance

You can find this video on Optimizing Python & Django performance in three different places:

• Microsoft PyCon 2020 board at aka.ms/pycon2020
• PyCon2020 YouTube channel: Optimizing your Python & Django apps with Postgres superpowers
• On this very blog post, below

On the Microsoft Board for PyCon2020, you will find a ton of useful videos created by my teammates on the Python team here at Microsoft. I found Nina’s talk on debugging especially interesting! And my Postgres teammate Sai Srirampur also created an (awesome) short demo on Building HTAP applications with Python and Postgres—in Azure.

But before you go watch my PyCon video, let me tell you a few things that I think you will find useful when watching.

Tools to help optimize Python

Django and Postgres have tools for you to use in order to see your SQL queries executed. In my experience, looking into executed SQL queries is the best way to make sure you don’t let your O

CREATE TABLE one_column_table (field INTEGER);

The above SQL creates ‘one_column_table’ with only a single column. But does the table actually have only one column?

Let’s query the pg_attribute catalog to find out how many columns our one_column_table has.

SELECT attname, attnum, atttypid::regtype from pg_attribute WHERE attrelid = 'one_column_table'::regclass;
 attname  | attnum | atttypid
----------+--------+----------
 tableoid |     -6 | oid
 cmax     |     -5 | cid
 xmax     |     -4 | xid
 cmin     |     -3 | cid
 xmin     |     -2 | xid
 ctid     |     -1 | tid
 field    |      1 | integer
(7 rows)

What?? As per the ‘pg_attribute’ catalog, the table we just created has seven columns.

Somehow PostgreSQL has added six extra columns to our table and all of these implicit columns have -ve ‘attnum’ value.

This post is about these implicit system columns, what data these fields hold, and what we can do with that data.
So let us look at the system columns one by one.

ctid

TID is an acronym for a tuple identifier (row identifier), which is a pair of values (block number, tuple index within the block). Since the data files in PostgreSQL are logically divided into fixed-size blocks (8k by default), the CTID column identifies the physical location of the two within its table.

So what we can do with this CTID column?

Knowing the exact row location of the tuple within the table’s data file can be handy for fixing the data corruption in some cases. but it has another very interesting use cases. This CTID column could be used to identify the unique ROW within the table if it has duplicate data.

For example

Suppose we accidentally inserted the duplicate data in our example ‘one_column_table’ table.

INSERT INTO one_column_table SELECT generate_series(1,5);
--insert the same data 2nd time
INSERT INTO one_column_table SELECT generate_series(1,5);
SELECT * FROM one_column_table;
 field 
-------
     1
     2
     3
     4
     5
     1
     2
     3
     4
     5
(10 rows)

So l