PostgreSQL | SQL Conjuror

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!

Facebook

Google

Twitter

Category: PostgreSQL

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

Recent Posts

Archives

Categories

Recent Posts

Archives

Categories

Tag Cloud