Tag: PostgreSQL

Feed: Planet PostgreSQL. PostgreSQL’s built-in autovacuum – the housekeeper – is improving, version after version. It is becoming more capable while reducing its overhead and addressing edge cases. I think there is no PostgreSQL version that comes out without any autovacuum improvement, and no doubt that it is good enough for the majority of use cases. But still, that is far from what is a perfect fit for any specific environment. While working with many of the customer environments we keep seeing cases where the built-in logic is not sufficient. As I keep doing fixes for many Percona customers, I ... Read More

Feed: Planet PostgreSQL. Let’s go into details: tableoid The tableoid is the OID from the table where the row is originally coming from. In a simple table, this is just the OID of the table itself. oid=# CREATE TABLE show_oid (id INT); CREATE TABLE oid=# SELECT 'public.show_oid'::regclass::oid; oid ------- 79824 (1 row) oid=# INSERT INTO show_oid VALUES (1), (2); INSERT 0 2 oid=# SELECT tableoid, id FROM show_oid; tableoid | id ----------+---- 79824 | 1 79824 | 2 (2 rows) All rows are coming from the table with the oid = 79824. This changes once partitioning is ... Read More

Feed: Planet PostgreSQL. © Laurenz Albe 2022Even if you understand what a deadlock is, debugging deadlocks can be tricky. This article shows some techniques on how to figure out the cause of a deadlock.A simple deadlock exampleSetting the stageWe will test our techniques for debugging deadlocks with the following example: CREATE TABLE parent ( pid bigint PRIMARY KEY, pdata text NOT NULL ); CREATE TABLE child ( cid bigint PRIMARY KEY, pid bigint REFERENCES parent NOT NULL, cdata text NOT NULL ); INSERT INTO parent VALUES (1, 'no children yet'); The deadlockTo provoke the deadlock, we run the following transactions ... Read More

Feed: Planet PostgreSQL. 23 Jun 2022 I have a blog post up today at Crunchy Data on some of the mechanisms that underlie the PostgreSQL query planner, it’s pretty good if I do say so myself. I was motivated to write it by a conversation over coffee with my colleague Martin Davis. We were talking about a customer with an odd query plan case and I was explaining how the spatial statistics system worked and he said “you should do that up as a blog post”. And, yeah, I should. One of the things that is striking as you follow ... Read More

Feed: Recent Announcements. Amazon Aurora PostgreSQL-Compatible Edition now supports PostgreSQL major version 14 (14.3). PostgreSQL 14 includes performance improvements for parallel queries, heavily-concurrent workloads, partitioned tables, logical replication, and vacuuming. PostgreSQL 14 also improves functionality with new capabilities. For example, you can cancel long-running queries if a client disconnects and you can close idle sessions if they time out. Range types now support multiranges, allowing representation of non-contiguous data ranges, and stored procedures can now return data via OUT parameters. This release includes new features for Babelfish for Aurora PostgreSQL version 2.1. Please refer to Amazon Aurora PostgreSQL updates for more information ... Read More

Feed: Planet PostgreSQL. Minor version upgrades One result of our discussion is that minor upgrades (as example v14.0 to v14.1) are relatively easy to do, but might hold some surprises for anyone who does not pay attention to the details (e.g., the release notes). A good example is the recent release of PostgreSQL version 14.4. It is a minor upgrade, so no new features. But it requires extensive work on B-tree indexes. For the DBA it is not easy to figure out how much work this is, or how long the maintenance window needs to be. Lætitia points out that ... Read More

Feed: Planet PostgreSQL. QuerySet.explain() is very handy, but its restriction to methods that return QuerySets can limit your analysis. It’s possible to EXPLAIN most PostgreSQL statements, such as INSERT (from e.g. Model.save()).Below is a snippet of code containing a context manager to perform the same EXPLAIN on any query and output the query plan. This uses database instrumentation to modify the SQL and capture the explain result.Keep in mind that the ANALYZE option actually executes the query. So if you’re running something that modifies data, like Model.save(), QuerySet.delete(), etc., the changes are applied. This can also make it tricky to ... Read More

Feed: Planet PostgreSQL. Posted by Andreas 'ads' Scherbaum on Tuesday, June 21. 2022 As is a good tradition, the PostgreSQL Project participates in Google Summer of Code (GSoC). Last year we submitted 7 projects for 7 students - and got all 7 projects accepted. This year we got quite a few more good proposals from students, and more mentors are helping. Guess what? Google accepted all 12 proposals! Google modified the program again. For 2021 they cut the time for every project in half, to accommodate for the at-home work during the Covid-19 pandemic. This turned out to be suboptimal, and ... Read More

Feed: Planet PostgreSQL. Here’s the basic process to find and stop a query. Note you’ll need to connect as a user with adequate permissions to do so, such as an admin account.1. Find the pidPostgreSQL creates one process per connection, and it identifies each process with its operating system process ID, or pid. In order to cancel a query, you need to know the pid for the connection it’s running on.One way to find this out is with the pg_stat_activity view, which provides information about the live queries. For example, try this query:SELECT pid, state, backend_start, substr(query, 0, 100) q ... Read More

Feed: Planet PostgreSQL. When Disco was the hot new music, the people who built database management systems based their praxis around the then-correct assumption that hardware was extremely expensive, and hence that procuring things like more storage, let alone more machines on which they would run, could be counted on to require significant capital resources. This could only happen on the time scale of quarters, even if it was deemed worthwhile to fight the fights needed to procure them.The above conditions informed the practice of hardening single nodes against failure without adding significant hardware resources in the process. This practice ... Read More