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!

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!

Elastic Cloud Monitoring and Alerting

By Enrique 13/09/2022 24/03/2024 Elasticsearch, Database Administration

In this blog post, I will show you how to:

Enable logging and monitoring that will let us monitor our deployments in Kibana.
Integrate Elastic Alert with PagerDuty.

Enabling Logging and Monitoring

In Production, the best practice is to send our deployment logs and metrics to a dedicated monitoring deployment. Monitoring indexes logs and metrics into Elasticsearch and these indexes consume storage, memory, and CPU cycles like any other index. We can avoid affecting other production deployments and view the logs and metrics, even when production deployment is unavailable, by using a separate monitoring deployment.We need a minimum of three monitoring nodes to make monitoring highly available. Read More

Steps:

The Monitoring deployment must have the same major version and must be in the same region as your production deployments. Once the monitoring deployment has been setup, below are the steps to enable monitoring and logging.

Go to one of your deployments then go to Logs and Metrics.

2. Choose your Monitoring deployment where you want to ship data to. Choose Logs and Metrics. Click Save.

3. Repeat these steps for all your deployments.

Viewing Cluster Listing

Let’s view our clusters.

Steps:

1. Go to your Monitoring deployment.

2. Open Kibana.

3. Go to Stack Monitoring, which is under the Management section. When you open Stack Monitoring for the first time, you will be asked to acknowledge the creation of these default rules. They are initially configured to detect and notify on various conditions across your monitored clusters. You can view notifications for: Cluster health, Resource utilization, and Errors and exceptions for Elasticsearch in real time.

4. Click on one cluster to see its overview.

Review and Modify Existing Stack Monitoring Rules

Elastic Stack monitoring feature provides Kibana alerting rules, which is an out-of-the-box monitoring feature. These rules are preconfigured based on the best practices recommended by Elastic, but we can modify them to meet our requirements

Steps:

Go to Alerts and rules > Manage rules.

2. You may choose to edit or retain their default values.

Setting up Alerts Using PagerDuty Connector and Action

The PagerDuty connector uses the v2 Events API to trigger, acknowledge, and resolve PagerDuty alerts.

Creating PagerDuty Service and Intergration

Create a service and add integrations to begin receiving incident notifications.

Steps:

In Pager Duty, go to Services -> Service Directory and click New Service. On the next screen you will be guided through several steps.

2. Name: Enter a Name and Description based on the function that the service provides and click Next to continue.

3. Assign: Select Generate a new Escalation Policy or Select an existing Escalation Policy. Click Next to continue.

4. Integrations: Select the integration(s) you use to send alerts to this service from the search bar, dropdown, or from the list of our most popular integrations. In this case, we will select Elastic Alerts.