Enrique | SQL Conjuror

PostgreSQL – Vacuum Basics

By Enrique 22/03/2023 21/03/2024 PostgreSQL, Database Administration

To implement one of the ACID properties called “Isolation”, PostgreSQL provides MVCC (Multi-Version Concurrency Control). In this way, the maximum possible concurrency among current transactions is achievable. PostgreSQL does this by creating versions of each tuple when the tuple receives any modifications. For instance, say that a tuple received n concurrent modifications. The n versions of the same tuple will still be kept and will make the last committed modified tuple visible to other transactions. Now, this will lead to more disk space usage because you are having both visible and non-visible tuples. The good news is that PostgreSQL offers a few ways to reuse the non-visible tuples to make way for further write operations. Read More

PostgreSQL databases require periodic maintenance known as vacuuming.

PostgreSQL’s VACUUM command has to process each table on a regular basis for several reasons:

To recover or reuse disk space occupied by updated or deleted rows
To update data statistics used by the PostgreSQL query planner
To update the visibility map, which speeds up index-only scans
To protect against loss of very old data due to transaction ID wraparound or multixact ID wraparound

Let’s perform a test with the usage of VACUUM. We will create a sample table and run a few SQL statements that will generate non-visible tuples or dead tuples.

Connect to your database as a super use then run the command below:

$ psql -h localhost -U postgres
postgres=# CREATE EXTENSION pg_freespacemap;
CREATE

Let’s create a test table:

postgres=# CREATE TABLE test(t INT);

Let’s turn off autovacuum for a moment for demo purposes:

postgres=# ALTER TABLE test SET(autovacuum_enabled=off);
ALTER TABLE

Let’s observe how many pages this table will occupy in the disk when we insert 1,000 rows:

postgres=> INSERT INTO test VALUES(generate_series(1, 7000));
INSERT 0 7000
postgres=> ANALYZE test;
ANALYZE
postgres=> SELECT relpages FROM pg_class WHERE relname='test';
 relpages
----------
       31
(1 row)

PostgreSQL used thirty-one relpages to store 7000 integer records.

Using the pg_freespace() function, let’s verify the free space available in the relation test:

postgres=> SELECT sum(avail) freespace FROM pg_freespace('test');
 freespace
-----------
         0
(1 row)

Let’s update all the 1,000 records and gather the relpages and freespace map information again:

postgres=> UPDATE test SET t=7000;
UPDATE 7000
postgres=> ANALYZE test;
ANALYZE
postgres=> SELECT relpages FROM pg_class WHERE relname='test';
 relpages
----------
       62
(1 row)

postgres=> SELECT sum(avail) freespace FROM pg_freespace('test');
 freespace
-----------
         0
(1 row)

We’ve gained 31 additional pages by running the update on 7,000 tuples. In other words, the table size expanded by up to 50% in the disk storage upon updating the entire table.

Now, let’s gather the number of non-visible or dead tuples of the table. We can do this by using the pg_stat_user_tables view.

postgres=> SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname='test';
 n_dead_tup
------------
       7000
(1 row)

Let’s run the VACUUM then observe the amount of free space available for the relation ‘test’:

postgres=> VACUUM test;
VACUUM
postgres=> SELECT sum(avail) freespace FROM pg_freespace('test');
 freespace
-----------
    252288
(1 row)

After executing VACUUM command, it logically cleans up the dead tuples resulting in free space being available. Now, we are gonna insert additional 7,000 records into the table. Then let’s look at the relpages count and free information.

postgres=> INSERT INTO test VALUES(generate_series(1, 7000));
INSERT 0 7000
postgres=> ANALYZE test;
ANALYZE
postgres=> SELECT relpages FROM pg_class WHERE relname='test';
 relpages
----------
       62
(1 row)

postgres=> SELECT sum(avail) freespace FROM pg_freespace('test');
 freespace
-----------
      7488

(1 row)

From the previous SQL statement output, since we ran the VACUUM command before inserting records, we see that even after inserting additional 7,000 records into the table, the relpages did not increase. We can also see that the free space that was logically deleted by the previous VACUUM command has been properly used.

How about doing the same steps above without using the VACUUM command before inserting records? Let’s do that, then observe the disk usage.

postgres=> TRUNCATE test;
TRUNCATE TABLE
postgres=> ANALYZE test;
ANALYZE
postgres=> INSERT INTO test VALUES(generate_series(1, 7000));
INSERT 0 7000
postgres=> UPDATE test SET t=700;
UPDATE 7000
postgres=> INSERT INTO test VALUES(generate_series(1, 7000));
INSERT 0 7000
postgres=> ANALYZE test;
ANALYZE
postgres=> SELECT relpages FROM pg_class WHERE relname='test';
 relpages
----------
      93

postgres=> SELECT sum(avail) freespace FROM pg_freespace('test');
 freespace
-----------
         0
(1 row)

From the previous example, we can see that the table size increased by 50% when we perform an additional insert operation after updating the table. The reason for this is that the table has no available free space map, and it increased the table storage. In the prior example, after the insert operation has been completed, the total table size takes nine pages, and in this scenario, it takes 93 pages.

Now, we can validate that a table will reuse its dead tuple storage for any new incoming transactions when VACUUM operation is used. However, it is not recommended to run VACUUM explicitly on every insert/update/delete operation. It will cause I/O utilization to increase because VACUUM is a heavy process that scans all the tuples in a table. It is also not suggested to entirely stop VACUUM on any table; otherwise, you will have more dead tuples on your disk, which can result in ineffective disk utilization. The best practice is to run VACUUM process periodically. For example, we can run it once a day or once a week. We can either schedule it using crontab or pgBucket.

What Happens When We Execute VACUUM Command

When we run =VACUUM command on any table, it will ask the Postgres instance to transmit all the currently running SQL queries. Once the vacuum process sees that list, it will proceed to spot the dead tuples that are not in view from the running SQL queries. Afterwards, it will utilize the maintenance_work_mem to load the relation into memory where it can then identify dead tuples. Once all the dead tuples have been detected by the vacuum process, it will initiate an FSM(Free Space Map) for that relation.

Another duty of the vacuum process is to update each table row transaction status by confirming pg_clog and pg_subtrans. Each row header in contains xmin and xmax values. The xmin and xmax values relate to the transaction statuses: In progress, Committed, Aborted by citing the status bits from pg_clog.

xmin

The column that records the transaction id that created the row.

xmax

The column records the transaction id that expired the row, either through an UPDATE or DELETE.

Cheers!

PostgreSQL – TOAST (The Oversized-Attribute Storage Technique)

By Enrique 26/12/2022 21/03/2024 PostgreSQL, Database Administration, Database Design

TOAST stands for The Oversized-Attribute Storage Technique.
PostgreSQL uses a fixed page size (commonly 8 kB), and does not allow tuples to span multiple pages. Therefore, it is not possible to store very large field values directly. To overcome this limitation, large field values are compressed and/or broken up into multiple physical rows. Read More

Toast is an operation in PostgreSQL to handle large chunks of data to fit in page buffer. When the data surpass TOAST_TUPLE_THRESHOLD (2KB default), Postgres will squeeze the data, trying to fit in 2KB buffer size. If the compressing of the large column data does not lead to smaller block (<2KB), it will be split into smaller chunks.

Because of how the pointer system is implemented, most TOAST table column types limited to a max size of 1GB.

Almost every table you create has its own related (unique) TOAST table, which may or may not ever end up being utilized, depending on the size of rows you insert. A table with only fixed-width columns like integers may not have an associated toast table.

All of this is transparent to the user and enabled by default.

The benefit of TOAST table is that whenever data requires retrieving from TOAST table, it only needs to refer to TOAST table; otherwise, it does not refer to it unnecessarily.

How to find whether a TOAST table is correlated with your main tables?

You can easily select reltoastrelid from pg_class. Like any other table ObjectID is also assigned to TOAST Table.

SELECT relname,reltoastrelid 
FROM pg_class 
WHERE relname='Table_Name';

Cheers!

PostgreSQL – Check Table Level Locks

By Enrique 16/12/2022 16/12/2022 PostgreSQL, Database Administration, Troubleshooting

Here is a script to check table level locks

SELECT 
  act1.query as blocking_query, 
  act2.query as blocked_query, 
  l1.pid AS blocked_pid, 
  l2.pid AS blocking_pid, 
  l1.relation :: regclass 
FROM 
  pg_locks l1, 
  pg_locks l2, 
  pg_stat_activity act1, 
  pg_stat_activity act2 
WHERE 
  l1.granted = true 
  AND l2.granted = false 
  AND l1.pid = act1.pid 
  AND l2.pid = act2.pid 
  AND l1.relation = l2.relation;

Cheers!

PostgreSQL – Check Transactional Locks / Blocking

By Enrique 06/12/2022 06/12/2022 PostgreSQL, Database Administration

Here is a script to check which query is blocking who.

SELECT 
  bl.pid AS blocked_pid, 
  a.usename AS blocked_user, 
  ka.query AS current_or_recent_statement_in_blocking_process, 
  ka.state AS state_of_blocking_process, 
  now() - ka.query_start AS blocking_duration, 
  kl.pid AS blocking_pid, 
  ka.usename AS blocking_user, 
  a.query AS blocked_statement, 
  now() - a.query_start AS blocked_duration 
FROM 
  pg_catalog.pg_locks bl 
  JOIN pg_catalog.pg_stat_activity a ON a.pid = bl.pid 
  JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid 
  AND kl.pid != bl.pid 
  JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid 
WHERE 
  NOT bl.GRANTED;

Cheers!

PostgreSQL – Check Queries Running More Than 15 Minutes

By Enrique 21/11/2022 21/11/2022 PostgreSQL, Database Administration

Below is a script to check queries that are running for more than 15 minutes.

SELECT datname,query,state,query_start,
now() - query_start AS query_time
FROM pg_stat_activity
WHERE (now() - query_start) > interval '15 minutes';

Cheers!

PostgreSQL – Get the Size of All Databases

By Enrique 14/11/2022 14/11/2022 PostgreSQL, Database Administration

Here is a script to get the size of all your PostgreSQL databases.

SELECT 
  d.datname AS Name, 
  pg_catalog.pg_get_userbyid(d.datdba) AS Owner, 
  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_size_pretty(
    pg_catalog.pg_database_size(d.datname)
  ) ELSE 'No Access' END AS SIZE 
FROM 
  pg_catalog.pg_database d 
ORDER BY 
  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_database_size(d.datname) ELSE NULL END DESC;

Cheers!

PostgreSQL – Get the Top 20 Biggest Tables

By Enrique 14/11/2022 14/11/2022 PostgreSQL, Database Administration

Here is a script to get the top 20 biggest tables

SELECT 
  nspname || '.' || relname AS "relation", 
  pg_size_pretty(
    pg_relation_size(C.oid)
  ) AS "size" 
FROM 
  pg_class C 
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 
WHERE 
  nspname NOT IN (
    'pg_catalog', 'information_schema', 
    'pg_toast'
  ) 
ORDER BY 
  pg_relation_size(C.oid) DESC 
LIMIT 
  20;

Cheers!

PostgreSQL – Check Active Sessions

By Enrique 14/11/2022 14/11/2022 PostgreSQL, Database Administration

Here is a script that checks current active sessions in PostgreSQL

SELECT 
  datname, 
  usename, 
  application_name, 
  now() - backend_start AS "Session duration", 
  pid, 
  query 
FROM 
  pg_stat_activity 
WHERE 
  state = 'active' 
ORDER BY 
  "Session duration" DESC;

Cheers!

MySQL and PostgreSQL Equivalent Commands

By Enrique 13/10/2022 22/03/2023 PostgreSQL, MySQL, Database Administration

In this blog post, I’ve associated several PostgreSQL commands with what I know in MySQL. Below is a table of some of the frequently used commands.

MySQL	PostgreSQL	Description
mysqldump	pg_dumpall	extract a PostgreSQL database cluster into a script file
mysqldump	pg_dump	extract a PostgreSQL database into a script file or other archive file
N/A	pg_restore	restore a PostgreSQL database from an archive file created by pg_dump
N/A	pg_top	pg_top is a PostgreSQL interactive monitoring utility, similar to the Unix top utility, providing a dynamically updated view of activity within a PostgreSQL database
mysql	psql	PostgreSQL interactive terminal
Exit Mysql {quit\|exit\| [ctrl-c]}	Exit Postgres\q	quit psql,
show databases;	\l or \list	View all of the defined databases on the server
use [dbname];	\c [dbname]	Connect to a database
show tables;	\dt or \dt+	List all the tables in the current schema
describe [tablename];	\d [tablename]	Shows the columns, types, modifiers, indexes, and tables referenced by keys.
show create table [tablename];	No direct equivalent, use below command from shell: pg_dump -st tablename dbname	This will provide the sql used to create a table.
select * from mysql.user;	select * from pg_user; \du	Lists all database roles
show full processlist;	select * from pg_stat_activity;	Show what queries are currently running in the DB
show variables;	show all;	Displays the current setting of run-time parameters
show engine innodb status\G	SELECT * FROM pg_stat_activity; SELECT * FROM pg_stat_database; SELECT * FROM pg_stat_user_tables; SELECT * FROM pg_stat_user_indexes; SELECT * FROM pg_locks;	There is no central place in PostgreSQL to get all of the information obtained by running show engine InnoDB status in MySQL. However, there are a number of queries you can run to get roughly equivalent data.
show slave status\G	select * from pg_stat_replication; select now() – pg_last_xact_replay_timestamp() AS replication_delay;	Monitor replication
\G	\x	Sets or toggles expanded table formatting mode

Cheers!

PostgreSQL – Get The Top 10 Time-Consuming Queries in AWS RDS PostgreSQL and TencentDB for PostgreSQL

By Enrique 05/10/2022 05/10/2022 Database Administration, Troubleshooting

Here is a script to get the Top 10 Time-Consuming Queries (This will work for PostgreSQL version 13 and above)

We will make use of the pg_stat_statements view. This view will tell us which types of queries are slow and how often these queries are called.

In AWS RDS, this module is already installed by default. So, all you need to do is to create the extension before running the script.

create extension pg_stat_statements;

In Tencent DB for PostgreSQL, both module and the extension are enabled by default. So, there is no need to do anything before running the query.

Disable expanded display

\x

Script to get Top 10 Time Consuming Queries

SELECT 
  t2.rolname, 
  t3.datname, 
  queryid, 
  calls, 
  (
    total_plan_time + total_exec_time
  ) / 1000 as total_time_seconds, 
  (min_plan_time + min_exec_time) / 1000 as min_time_seconds, 
  (max_plan_time + max_exec_time) / 1000 as max_time_seconds, 
  (mean_plan_time + mean_exec_time) / 1000 as mean_time_seconds, 
  (
    stddev_plan_time + stddev_exec_time
  ) / 1000 as stddev_time_seconds, 
  rows, 
  shared_blks_hit, 
  shared_blks_read, 
  shared_blks_dirtied, 
  shared_blks_written, 
  local_blks_hit, 
  local_blks_read, 
  local_blks_dirtied, 
  local_blks_written, 
  temp_blks_read, 
  temp_blks_written, 
  blk_read_time / 1000 as blk_read_time_seconds, 
  blk_write_time / 1000 as blk_write_time_seconds 
FROM 
  pg_stat_statements t1 
  JOIN pg_roles t2 ON (t1.userid = t2.oid) 
  JOIN pg_database t3 ON (t1.dbid = t3.oid) 
WHERE 
  t2.rolname != 'rdsadmin' 
  AND queryid IS NOT NULL;

Cheers!

Facebook

Google

Twitter

Author: Enrique

PostgreSQL – Vacuum Basics

PostgreSQL – TOAST (The Oversized-Attribute Storage Technique)

PostgreSQL – Check Table Level Locks

PostgreSQL – Check Transactional Locks / Blocking

PostgreSQL – Check Queries Running More Than 15 Minutes

PostgreSQL – Get the Size of All Databases

PostgreSQL – Get the Top 20 Biggest Tables

PostgreSQL – Check Active Sessions

MySQL and PostgreSQL Equivalent Commands

PostgreSQL – Get The Top 10 Time-Consuming Queries in AWS RDS PostgreSQL and TencentDB for PostgreSQL

Recent Posts

Archives

Categories

Recent Posts

Archives

Categories

Tag Cloud