Which tables should be auto vacuumed or auto analyzed – UPDATE

Some time ago I wrote blogpost which showed how to list tables that should be autovacuumed or autoanalyzed.

Query in there had one important problem – it didn't take into account per-table settings.

Continue reading Which tables should be auto vacuumed or auto analyzed – UPDATE

Waiting for PostgreSQL 13 – Add leader_pid to pg_stat_activity

On 6th of February 2020, Michael Paquier committed patch:

Add leader_pid to pg_stat_activity
 
This new field tracks the PID of the group leader used with parallel
query.  For parallel workers and the leader, the value is set to the
PID of the group leader.  So, for the group leader, the value is the
same as its own PID.  Note that this reflects what PGPROC stores in
shared memory, so as leader_pid is NULL if a backend has never been
involved in parallel query.  If the backend is using parallel query or
has used it at least once, the value is set until the backend exits.
 
Author: Julien Rouhaud
Reviewed-by: Sergei Kornilov, Guillaume Lelarge, Michael Paquier, Tomas
Vondra
Discussion: https://postgr.es/m/CAOBaU_Yy5bt0vTPZ2_LUM6cUcGeqmYNoJ8-Rgto+c2+w3defYA@mail.gmail.com

Continue reading Waiting for PostgreSQL 13 – Add leader_pid to pg_stat_activity

Which tables should be auto vacuumed or auto analyzed?

Recently I was in a situation where autovacuum couldn't keep up with changes. To solve the problem I finally decided to manually vacuum analyze all tables (manual vacuum/analyze is faster than one ran by autovacuum daemon).

But it irritated me that I didn't have ready way to check which tables are waiting for autovacuum to work on them.

So, I wrote it.

Continue reading Which tables should be auto vacuumed or auto analyzed?

Which schema is using the most disk space?

I was faced with interesting problem. Which schema, in my DB, uses the most disk space? Theoretically it's trivial, we have set of helpful functions:

  • pg_column_size
  • pg_database_size
  • pg_indexes_size
  • pg_relation_size
  • pg_table_size
  • pg_tablespace_size
  • pg_total_relation_size

But in some cases it becomes more of a problem. For example – when you have thousands of tables …

Continue reading Which schema is using the most disk space?

I have PostgreSQL, loaded some data, and have app using it. Now what?

I had to deal with this question, or some version of it, quite a few times. So, decided to write a summary on what one could (or should) do, after data is in database, and application is running. Namely – setup some kind of replication and backups.

What to use, how, and why? This is what this post is all about.

Continue reading I have PostgreSQL, loaded some data, and have app using it. Now what?

Waiting for 9.3 – Add \watch [SEC] command to psql.

On 4th of April, Tom Lane committed patch:

Add \watch [SEC] command to psql.
 
This allows convenient re-execution of commands.
 
Will Leinweber, reviewed by Peter Eisentraut, Daniel Farina, and Tom Lane

Continue reading Waiting for 9.3 – Add \watch [SEC] command to psql.

How much RAM is PostgreSQL using?

(disclaimer: all the data and examples in here are on Linux – the same data can be probably obtained on other systems too, it's just that I work on Linux and don't know other systems well).

This question pops occasionally in various places – PostgreSQL is using too much memory, why is that, and how can it be mitigated?

Before we can go to “optimizing", we should understand the problem. But do we? Both standard tools – ps and top – lie. How/why? Let's see.

Continue reading How much RAM is PostgreSQL using?

How to let clients to create new users?

Some (quite long) time ago, someone, somewhere (my memory is pretty fragile) asked a question. I don't have it exact, but the gist was: is it possible to give some users rights to create new users, without making them superusers, and forcing new users to have access only to one particular database.

After some discussion it was clear that the scenario was shared hosting with PostgreSQL, so the situation could look like this:

you are administrator of shared hosting service. One of services is PostgreSQL. You have client, named “depesz", and you want him to be able to create new users, but these users shouldn't be able to connect to any other database than depesz's db.

Is it doable?

Continue reading How to let clients to create new users?

Waiting for 9.0 – table and index sizes

On 19th of January Tom Lane committed really brilliant patch:

Log Message:
-----------
Add pg_table_size() and pg_indexes_size() to provide more user-friendly
wrappers around the pg_relation_size() function.
 
Bernd Helmle, reviewed by Greg Smith

Continue reading Waiting for 9.0 – table and index sizes

Waiting for 8.5 – VACUUM FULL change

Some time ago Josh Berkus wrote about possible changes in VACUUM FULL.

Now these changes came to life. By now, I mean 6th of January, when Takahiro Itagaki committed his patch:

Log Message:
-----------
Support rewritten-based full vacuum as VACUUM FULL. Traditional
VACUUM FULL was renamed to VACUUM FULL INPLACE. Also added a new
option -i, --inplace for vacuumdb to perform FULL INPLACE vacuuming.
 
Since the new VACUUM FULL uses CLUSTER infrastructure, we cannot
use it for system tables. VACUUM FULL for system tables always
fall back into VACUUM FULL INPLACE silently.
 
Itagaki Takahiro, reviewed by Jeff Davis and Simon Riggs.

Continue reading Waiting for 8.5 – VACUUM FULL change