PosgreSQL - Bulk Updating PostgreSQL Table Using CSV and Temporary Table

-------------- Bulk Updating PostgreSQL Table Using CSV and Temporary Table-----------------------------

Introduction

In this document, we walk through a practical approach to bulk update records in a PostgreSQL table using a CSV file. This method is particularly useful when dealing with large datasets (millions of rows) where direct updates may be inefficient or error-prone.

 The scenario involves updating columns in a target table using data provided in a CSV file.

Step 1: Create a Temporary Staging Table

We first create a temporary table to load the CSV data. Temporary tables are session-specific and automatically dropped when the session ends, making them safe for intermediate operations.

 CREATE TEMP TABLE tmp_target_table (

    id bigint,

    column_a varchar(45),

    column_b bigint

);

Step 2: Load CSV Data Using \copy Command

Since the CSV file resides on the client machine, we use the \copy command in psql, which reads the file from the client side.

 \copy tmp_target_table

FROM 'C:/path/to/your/file.csv'

DELIMITER ','

CSV HEADER;

Step 3: Validate Loaded Data

Always verify the data count after loading.

 

SELECT count(*) FROM tmp_target_table;

Step 4: Verify Matching Records with Target Table

Before performing updates, ensure that all IDs exist in the target table.

 

SELECT count(*)

FROM tmp_target_table s

JOIN schema_name.target_table t

ON t.id = s.id;

 

If needed, check for any missing records:

 

SELECT s.*

FROM tmp_target_table s

LEFT JOIN schema_name.target_table t

ON t.id = s.id

WHERE t.id IS NULL;

Step 5: Perform Bulk Update

Now update the target table using the staging table.

 

UPDATE schema_name.target_table t

SET

    column_a = s.column_a,

    column_b = s.column_b

FROM tmp_target_table s

WHERE t.id = s.id;

Step 6: Post-Update Validation

Finally, confirm that updates were applied correctly.

 

SELECT count(*)

FROM schema_name.target_table t

JOIN tmp_target_table s

ON t.id = s.id;

Key Benefits of This Approach

• Efficient for millions of records

• Minimal locking compared to row-by-row updates

• Easy validation before applying changes

• Safe rollback if wrapped inside a transaction

Best Practices

Always test in a lower environment first.

Wrap the update inside a transaction for safety:

 BEGIN;

 -- update statement

 COMMIT;

 Consider indexing the staging table if join performance is slow:

 CREATE INDEX idx_tmp_target_table_id ON tmp_target_table(id);

 Run VACUUM ANALYZE after large updates if needed.

Conclusion

Using a temporary table with the \copy command is one of the most reliable and performant ways to perform bulk updates in PostgreSQL. This method ensures data integrity while handling large volumes efficiently.

PostgreSQL - Performance Tuning(Riyaz Alam)

PostgreSQL Query Performance Tuning Guide

Introduction

This document provides a practical guide for PostgreSQL query performance tuning, monitoring database activity, identifying bottlenecks, and maintaining database health. The queries included here help database administrators and developers quickly diagnose performance issues and take corrective actions.

Performance Tuning Approach

1. Identify the top queries consuming resources.

2. Check for missing indexes.

3. Optimize queries to perform more work efficiently.

4. Monitor sessions, wait events, and dead tuples.

5. Maintain tables using VACUUM and ANALYZE.

1. Top Active Queries with Client IP, Wait Events, and Duration

SELECT

  usename,

  datname,

  pid,

  client_addr,

  state,

  wait_event_type,

  wait_event,

  now() - query_start AS age,

  query

FROM pg_stat_activity

WHERE state <> 'idle'

  AND query NOT ILIKE '%pg_stat_activity%'

ORDER BY age DESC

LIMIT 10;

2. Top Active Queries (Simplified View)

SELECT

  query,

  state,

  pid, 

  age(clock_timestamp(), query_start) AS age

FROM pg_stat_activity

WHERE state <> 'idle'

  AND query NOT LIKE '% FROM pg_stat_activity %'

ORDER BY age DESC

LIMIT 10;

3. Find Idle in Transaction Sessions

SELECT

    pid,

    usename,

    application_name,

    client_addr,

    state,

    query,

    state_change,

    now() - state_change AS idle_duration

FROM pg_stat_activity

WHERE state = 'idle in transaction'

  AND now() - state_change > interval '5 minutes'

ORDER BY idle_duration DESC;

Check PostgreSQL Activity for Specific Operations

SELECT pid, state, query

FROM pg_stat_activity

WHERE query ILIKE '%repack%';

Check Idle Sessions

SELECT

  query,

  state,

  pid, 

  age(clock_timestamp(), query_start) AS age

FROM pg_stat_activity

WHERE state = 'idle'

  AND query NOT LIKE '%FROM pg_stat_activity%';

Terminate Idle Sessions

SELECT pg_terminate_backend(pid)

FROM pg_stat_activity

WHERE state = 'idle'

  AND pid <> pg_backend_pid()

  AND usename <> 'postgres';

4. Validate Dead Tuples

SELECT

  schemaname,

  relname,

  last_autoanalyze,

  last_autovacuum,

  last_vacuum,

  last_analyze,

  n_dead_tup

FROM pg_stat_all_tables

WHERE n_dead_tup > 0

ORDER BY n_dead_tup DESC

LIMIT 20;

Generate VACUUM Commands

SELECT

  'VACUUM ANALYZE ' || schemaname || '.' || relname || ';'

FROM pg_stat_all_tables

WHERE n_dead_tup > 0

ORDER BY n_dead_tup DESC

LIMIT 20;

5. Check Number of Sessions by State

SELECT count(*), state

FROM pg_stat_activity

GROUP BY state;

Active Sessions by User, Application, and IP

SELECT

    usename AS username,

    client_addr AS client_ip,

    application_name,

    COUNT(*) AS session_count

FROM pg_stat_activity

WHERE state = 'active'

GROUP BY usename, client_addr, application_name

ORDER BY session_count DESC;

Terminate Long Running Sessions (Example: > 3 Minutes)

SELECT pg_terminate_backend(pid)

FROM pg_stat_activity

WHERE (state = 'active' AND now() - query_start > interval '3 minutes')

   OR state = 'idle in transaction'

   OR state = 'idle';

6. Check Wait Events

SELECT

  count(*),

  usename,

  wait_event_type,

  wait_event

FROM pg_stat_activity

GROUP BY usename, wait_event_type, wait_event

ORDER BY 1;

7. Take Explain Plan for Long Running Queries

Use EXPLAIN ANALYZE only for SELECT queries. Avoid using it for INSERT, UPDATE, or DELETE in production.

 

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)

SELECT * FROM your_table;

8. Identify Missing Indexes (Magic Query)

SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan,

       seq_tup_read / seq_scan AS avg_rows_per_scan

FROM pg_catalog.pg_stat_user_tables

WHERE seq_scan > 0

ORDER BY seq_tup_read DESC;

9. Tables with Heavy DML Activity (Write IOPS)

SELECT

  relname,

  seq_scan,

  n_live_tup,

  n_tup_ins,

  n_tup_upd,

  n_tup_hot_upd,

  n_tup_del,

  last_vacuum,

  last_autovacuum,

  last_analyze,

  pg_relation_size(relid) AS size_bytes,

  pg_size_pretty(pg_relation_size(relid)) AS size_readable

FROM pg_stat_all_tables

ORDER BY n_tup_del DESC;

10. Aggressive Autovacuum Tuning Example

ALTER TABLE schema_name.table_name SET (autovacuum_vacuum_scale_factor = 0.0);

ALTER TABLE schema_name.table_name SET (autovacuum_vacuum_threshold = 10000);

ALTER TABLE schema_name.table_name SET (autovacuum_analyze_scale_factor = 0.0);

ALTER TABLE schema_name.table_name SET (autovacuum_analyze_threshold = 10000);

Find Tables Needing Aggressive Vacuum

SELECT schemaname, relname,

last_vacuum, last_analyze, vacuum_count, analyze_count,

last_autoanalyze, last_autovacuum, autovacuum_count, autoanalyze_count,

n_dead_tup

FROM pg_stat_all_tables

WHERE n_dead_tup > 10000

ORDER BY n_dead_tup DESC

LIMIT 50;

11. Check Last Analyze Time for a Table

SELECT relname, last_analyze

FROM pg_stat_all_tables

WHERE schemaname = 'your_schema'

  AND relname = 'your_table';

Conclusion

This guide provides essential queries for PostgreSQL performance tuning, monitoring sessions, identifying missing indexes, and maintaining database health. Regular monitoring combined with proper indexing and vacuum strategy ensures optimal database performance.

 ============To check the locking and blocking===============

Query to be used mostly -----

SELECT

    activity.pid                AS blocked_pid,

    activity.usename            AS blocked_user,

    activity.application_name   AS blocked_app,

    activity.client_addr        AS blocked_client,

    activity.wait_event_type,

    activity.wait_event,

    now() - activity.query_start AS blocked_duration,

    activity.query              AS blocked_query,

    blocking.pid                AS blocking_pid,

    blocking.usename            AS blocking_user,

    blocking.application_name   AS blocking_app,

    blocking.client_addr        AS blocking_client,

    blocking.query              AS blocking_query,

    now() - blocking.query_start AS blocking_query_duration

FROM pg_stat_activity AS activity

JOIN pg_stat_activity AS blocking

  ON blocking.pid = ANY (pg_blocking_pids(activity.pid))

ORDER BY blocked_duration DESC;

--------------------------------------------------------------------------------------------------------------
1. To check the locking and blocking(Main query to use) 

SELECT

COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,

now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid,

blockeda.query as blocked_query, blockedl.mode as blocked_mode,

blockinga.pid AS blocking_pid, blockinga.query as blocking_query,

blockingl.mode as blocking_mode

FROM pg_catalog.pg_locks blockedl

JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid

JOIN pg_catalog.pg_locks blockingl ON(( (blockingl.transactionid=blockedl.transactionid) OR

(blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)) AND blockedl.pid != blockingl.pid)

JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid AND blockinga.datid = blockeda.datid

WHERE NOT blockedl.granted AND blockinga.datname = current_database();

-[ RECORD 1 ]----+-------------------------------------------------------------------------------

locked_item      | transactionid

waiting_duration | 04:01:41.317819

blocked_pid      | 25192

blocked_query    | SELECT DISTINCT t.schemaname, t.tablename                                     +

                 |   FROM pg_catalog.pg_publication_tables t                                     +

                 |  WHERE t.pubname IN ('znwuserspublication')

blocked_mode     | ShareLock

blocking_pid     | 13747

blocking_query   | INSERT INTO nwdev.nsNWCustomerUsageAggregate as a                             +

                 |  VALUES ($1, to_jsonb($2::jsonb), to_jsonb($3::jsonb), $4, $5, $6, $7, $8, $9)

blocking_mode    | ExclusiveLock

prod=>

====================How to Kill long-running PostgreSQL query================

2. How to Kill long-running PostgreSQL query

In case you don't want a query to continue running inside the database, you can use the pid (process ID) from the pg_stat_activity

or pg_locks views to terminate the running process.

pg_cancel_backend(pid) will attempt to gracefully kill a running query process.

pg_terminate_backend(pid) will immediately kill the running query process, but potentially have side affects across additional

queries running on your database server. The full connection may be reset when running pg_terminate_backend.

pg_cancel_backend('pid')

select pg_terminate_backend('pid')

6419

3780

3779

select pg_terminate_backend('6419');

pg_terminate_backend('3780');

pg_terminate_backend('3779');

========================Dynamic Query to kill multiple sessions===================

3. Dynamic Query to kill multiple sessions

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session where status='INACTIVE';

SELECT pg_terminate_backend(pid)

FROM pg_stat_activity

WHERE datname = 'Database_Name'

AND pid <> pg_backend_pid()

AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') 

AND state_change < current_timestamp - INTERVAL '15' MINUTE;

SELECT pg_terminate_backend(pid) FROM pg_stat_activity

WHERE datname = 'databasename'

AND pid <> pg_backend_pid()

AND state in ('idle');

====IMP - To kill all the active session in the database -

https://www.dbvis.com/thetable/how-to-kill-all-connections-to-a-database-in-postgresql/#:~:text=Dropping%20All%20Active%20Connections%20to%20a%20PostgreSQL%20Database&text=1%20SELECT%20pg_terminate_backend(pid)%202,want%20to%20close%20sessions%20for

SELECT

 pg_terminate_backend(pid)

FROM

 pg_stat_activity

WHERE

 datname = 

'QUARANTINE'

AND

 leader_pid 

IS NULL

;

-------------------To monitor an activity

SELECT pid,

       usename,

       datname,

       state,

       query_start,

       now() - query_start AS duration,

       query

FROM pg_stat_activity

WHERE query ILIKE '%DROP%';

=====================Locking and Blocking=======================

4. . To find the locks in the database.

select

relname as relation_name,

query,

pg_locks.*

from pg_locks

join pg_class on pg_locks.relation = pg_class.oid

join pg_stat_activity on pg_locks.pid = pg_stat_activity.pid

======================How to Find blocked query and blocking queries=============

5. . How to Find blocked query and blocking queries

SELECT

activity.pid,

activity.usename,

activity.query,

blocking.pid AS blocking_id,

blocking.query AS blocking_query

FROM pg_stat_activity AS activity

JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

Output -

prod-> JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

 pid | usename | query | blocking_id | blocking_query

-----+---------+-------+-------------+----------------

(0 rows)

================== How to View the Locks on Tables using pg_lock===============

6.  How to View the Locks on Tables using pg_lock

select * from pg_locks;

        How to find locks with table names and queries

select relname as relation_name, query, pg_locks.* from pg_locks

join pg_class on pg_locks.relation = pg_class.oid

join pg_stat_activity on pg_locks.pid = pg_stat_activity.pid;

==========================================================================

======To kill all locked sessions in PostgreSQL, you can follow these steps==========

1.  Identify Locked Sessions-

SELECT pg_stat_activity.pid, pg_stat_activity.query, pg_locks.locktype, pg_locks.mode

FROM pg_stat_activity

JOIN pg_locks ON pg_stat_activity.pid = pg_locks.pid

WHERE pg_locks.granted = 'f';

2. Terminate the Locked Sessions

SELECT pg_terminate_backend(pg_stat_activity.pid)

FROM pg_stat_activity

JOIN pg_locks ON pg_stat_activity.pid = pg_locks.pid

WHERE pg_locks.granted = 'f';

PostgreSQL - Table Bloat

 WITH constants AS (

  -- define some constants for sizes of things

  -- for reference down the query and easy maintenance

  SELECT 

    current_setting('block_size'):: numeric AS bs, 

    23 AS hdr, 

    8 AS ma

), 

no_stats AS (

  -- screen out table who have attributes

  -- which dont have stats, such as JSON

  SELECT 

    table_schema, 

    table_name, 

    n_live_tup :: numeric as est_rows, 

    pg_table_size(relid):: numeric as table_size 

  FROM 

    information_schema.columns 

    JOIN pg_stat_user_tables as psut ON table_schema = psut.schemaname 

    AND table_name = psut.relname 

    LEFT OUTER JOIN pg_stats ON table_schema = pg_stats.schemaname 

    AND table_name = pg_stats.tablename 

    AND column_name = attname 

  WHERE 

    attname IS NULL 

    AND table_schema NOT IN (

      'pg_catalog', 'information_schema'

    ) 

  GROUP BY 

    table_schema, 

    table_name, 

    relid, 

    n_live_tup

), 

null_headers AS (

  -- calculate null header sizes

  -- omitting tables which dont have complete stats

  -- and attributes which aren't visible

  SELECT 

    hdr + 1 +(

      sum(

        case when null_frac <> 0 THEN 1 else 0 END

      )/ 8

    ) as nullhdr, 

    SUM(

      (1 - null_frac)* avg_width

    ) as datawidth, 

    MAX(null_frac) as maxfracsum, 

    schemaname, 

    tablename, 

    hdr, 

    ma, 

    bs 

  FROM 

    pg_stats CROSS 

    JOIN constants 

    LEFT OUTER JOIN no_stats ON schemaname = no_stats.table_schema 

    AND tablename = no_stats.table_name 

  WHERE 

    schemaname NOT IN (

      'pg_catalog', 'information_schema'

    ) 

    AND no_stats.table_name IS NULL 

    AND EXISTS (

      SELECT 

        1 

      FROM 

        information_schema.columns 

      WHERE 

        schemaname = columns.table_schema 

        AND tablename = columns.table_name

    ) 

  GROUP BY 

    schemaname, 

    tablename, 

    hdr, 

    ma, 

    bs

), 

data_headers AS (

  -- estimate header and row size

  SELECT 

    ma, 

    bs, 

    hdr, 

    schemaname, 

    tablename, 

    (

      datawidth +(

        hdr + ma -(

          case when hdr % ma = 0 THEN ma ELSE hdr % ma END

        )

      )

    ):: numeric AS datahdr, 

    (

      maxfracsum *(

        nullhdr + ma -(

          case when nullhdr % ma = 0 THEN ma ELSE nullhdr % ma END

        )

      )

    ) AS nullhdr2 

  FROM 

    null_headers

), 

table_estimates AS (

  -- make estimates of how large the table should be

  -- based on row and page size

  SELECT 

    schemaname, 

    tablename, 

    bs, 

    reltuples :: numeric as est_rows, 

    relpages * bs as table_bytes, 

    CEIL(

      (

        reltuples * (

          datahdr + nullhdr2 + 4 + ma - (

            CASE WHEN datahdr % ma = 0 THEN ma ELSE datahdr % ma END

          )

        )/(bs - 20)

      )

    ) * bs AS expected_bytes, 

    reltoastrelid 

  FROM 

    data_headers 

    JOIN pg_class ON tablename = relname 

    JOIN pg_namespace ON relnamespace = pg_namespace.oid 

    AND schemaname = nspname 

  WHERE 

    pg_class.relkind = 'r'

), 

estimates_with_toast AS (

  -- add in estimated TOAST table sizes

  -- estimate based on 4 toast tuples per page because we dont have

  -- anything better.  also append the no_data tables

  SELECT 

    schemaname, 

    tablename, 

    TRUE as can_estimate, 

    est_rows, 

    table_bytes + (

      coalesce(toast.relpages, 0) * bs

    ) as table_bytes, 

    expected_bytes + (

      ceil(

        coalesce(toast.reltuples, 0) / 4

      ) * bs

    ) as expected_bytes 

  FROM 

    table_estimates 

    LEFT OUTER JOIN pg_class as toast ON table_estimates.reltoastrelid = toast.oid 

    AND toast.relkind = 't'

), 

table_estimates_plus AS (

  -- add some extra metadata to the table data

  -- and calculations to be reused

  -- including whether we cant estimate it

  -- or whether we think it might be compressed

  SELECT 

    current_database() as databasename, 

    schemaname, 

    tablename, 

    can_estimate, 

    est_rows, 

    CASE WHEN table_bytes > 0 THEN table_bytes :: NUMERIC ELSE NULL :: NUMERIC END AS table_bytes, 

    CASE WHEN expected_bytes > 0 THEN expected_bytes :: NUMERIC ELSE NULL :: NUMERIC END AS expected_bytes, 

    CASE WHEN expected_bytes > 0 

    AND table_bytes > 0 

    AND expected_bytes <= table_bytes THEN (table_bytes - expected_bytes):: NUMERIC ELSE 0 :: NUMERIC END AS bloat_bytes 

  FROM 

    estimates_with_toast 

  UNION ALL 

  SELECT 

    current_database() as databasename, 

    table_schema, 

    table_name, 

    FALSE, 

    est_rows, 

    table_size, 

    NULL :: NUMERIC, 

    NULL :: NUMERIC 

  FROM 

    no_stats

), 

bloat_data AS (

  -- do final math calculations and formatting

  select 

    current_database() as databasename, 

    schemaname, 

    tablename, 

    can_estimate, 

    table_bytes, 

    round(

      table_bytes /(1024 ^ 2):: NUMERIC, 

      3

    ) as table_mb, 

    expected_bytes, 

    round(

      expected_bytes /(1024 ^ 2):: NUMERIC, 

      3

    ) as expected_mb, 

    round(bloat_bytes * 100 / table_bytes) as pct_bloat, 

    round(

      bloat_bytes /(1024 :: NUMERIC ^ 2), 

      2

    ) as mb_bloat, 

    table_bytes, 

    expected_bytes, 

    est_rows 

  FROM 

    table_estimates_plus

) -- filter output for bloated tables

SELECT 

  databasename, 

  schemaname, 

  tablename, 

  can_estimate, 

  est_rows, 

  pct_bloat, 

  mb_bloat, 

  table_mb 

FROM 

  bloat_data -- this where clause defines which tables actually appear

  -- in the bloat chart

  -- example below filters for tables which are either 50%

  -- bloated and more than 20mb in size, or more than 25%

  -- bloated and more than 1GB in size

WHERE 

  (

    pct_bloat >= 0 

    AND mb_bloat >= 0

  ) --   OR ( pct_bloat >= 10 AND mb_bloat >= 100 )

  --- where  tablename='generalledgerdetail'

ORDER BY 

  mb_bloat DESC 

limit 

  20;

PostgreSQL - Index Bloat in GB

WITH btree_index_atts AS (

    SELECT 

        nspname, 

        indexclass.relname AS index_name, 

        indexclass.reltuples, 

        indexclass.relpages, 

        indrelid, 

        indexrelid,

        indexclass.relam,

        tableclass.relname AS tablename,

        regexp_split_to_table(indkey::text, ' ')::smallint AS attnum,

        indexrelid AS index_oid

    FROM pg_index

    JOIN pg_class AS indexclass ON pg_index.indexrelid = indexclass.oid

    JOIN pg_class AS tableclass ON pg_index.indrelid = tableclass.oid

    JOIN pg_namespace ON pg_namespace.oid = indexclass.relnamespace

    JOIN pg_am ON indexclass.relam = pg_am.oid

    WHERE pg_am.amname = 'btree' 

      AND indexclass.relpages > 0

      AND nspname NOT IN ('pg_catalog','information_schema')

),

index_item_sizes AS (

    SELECT

        ind_atts.nspname, 

        ind_atts.index_name, 

        ind_atts.reltuples, 

        ind_atts.relpages, 

        ind_atts.relam,

        indrelid AS table_oid, 

        index_oid,

        current_setting('block_size')::numeric AS bs,

        8 AS maxalign,

        24 AS pagehdr,

        CASE 

            WHEN max(coalesce(pg_stats.null_frac,0)) = 0 THEN 2

            ELSE 6

        END AS index_tuple_hdr,

        SUM((1 - coalesce(pg_stats.null_frac, 0)) * coalesce(pg_stats.avg_width, 1024)) AS nulldatawidth

    FROM pg_attribute

    JOIN btree_index_atts AS ind_atts 

        ON pg_attribute.attrelid = ind_atts.indexrelid 

       AND pg_attribute.attnum = ind_atts.attnum

    JOIN pg_stats 

        ON pg_stats.schemaname = ind_atts.nspname

       AND (

            (pg_stats.tablename = ind_atts.tablename AND pg_stats.attname = pg_catalog.pg_get_indexdef(pg_attribute.attrelid, pg_attribute.attnum, TRUE)) 

            OR 

            (pg_stats.tablename = ind_atts.index_name AND pg_stats.attname = pg_attribute.attname)

        )

    WHERE pg_attribute.attnum > 0

    GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9

),

index_aligned_est AS (

    SELECT 

        maxalign, bs, nspname, index_name, reltuples,

        relpages, relam, table_oid, index_oid,

        COALESCE (

            CEIL (

                reltuples * (

                    6 + maxalign 

                    - CASE 

                        WHEN index_tuple_hdr % maxalign = 0 THEN maxalign

                        ELSE index_tuple_hdr % maxalign

                      END

                    + nulldatawidth 

                    + maxalign 

                    - CASE 

                        WHEN nulldatawidth::integer % maxalign = 0 THEN maxalign

                        ELSE nulldatawidth::integer % maxalign

                      END

                )::numeric / (bs - pagehdr::NUMERIC) + 1

            ), 

            0

        ) AS expected

    FROM index_item_sizes

),

raw_bloat AS (

    SELECT 

        current_database() AS dbname, 

        nspname, 

        pg_class.relname AS table_name, 

        index_name,

        bs * (index_aligned_est.relpages)::bigint AS totalbytes, 

        expected,

        CASE

            WHEN index_aligned_est.relpages <= expected THEN 0

            ELSE bs * (index_aligned_est.relpages - expected)::bigint 

        END AS wastedbytes,

        CASE

            WHEN index_aligned_est.relpages <= expected THEN 0

            ELSE bs * (index_aligned_est.relpages - expected)::bigint * 100 

                 / (bs * (index_aligned_est.relpages)::bigint) 

        END AS realbloat,

        pg_relation_size(index_aligned_est.table_oid) AS table_bytes,

        stat.idx_scan AS index_scans

    FROM index_aligned_est

    JOIN pg_class ON pg_class.oid = index_aligned_est.table_oid

    JOIN pg_stat_user_indexes AS stat ON index_aligned_est.index_oid = stat.indexrelid

),

format_bloat AS (

    SELECT 

        dbname AS database_name, 

        nspname AS schema_name, 

        table_name, 

        index_name,

        ROUND(realbloat) AS bloat_pct, 

        ROUND(wastedbytes / (1024^3)::NUMERIC, 3) AS bloat_gb,

        ROUND(totalbytes / (1024^3)::NUMERIC, 3) AS index_gb,

        ROUND(table_bytes / (1024^3)::NUMERIC, 3) AS table_gb,

        index_scans

    FROM raw_bloat

)

-- Final result: indexes with >30% bloat and >0.01 GB wasted

SELECT *

FROM format_bloat

WHERE bloat_pct > 30 AND bloat_gb > 0.01

ORDER BY bloat_gb DESC;

 

Postgres - Index Bloat in GB -formatted

 WITH btree_index_atts AS (

    SELECT 

        nspname, 

        indexclass.relname AS index_name, 

        indexclass.reltuples, 

        indexclass.relpages, 

        indrelid, 

        indexrelid,

        indexclass.relam,

        tableclass.relname AS tablename,

        regexp_split_to_table(indkey::text, ' ')::smallint AS attnum,

        indexrelid AS index_oid

    FROM pg_index

    JOIN pg_class AS indexclass ON pg_index.indexrelid = indexclass.oid

    JOIN pg_class AS tableclass ON pg_index.indrelid = tableclass.oid

    JOIN pg_namespace ON pg_namespace.oid = indexclass.relnamespace

    JOIN pg_am ON indexclass.relam = pg_am.oid

    WHERE pg_am.amname = 'btree' 

      AND indexclass.relpages > 0

      AND nspname NOT IN ('pg_catalog','information_schema')

),

index_item_sizes AS (

    SELECT

        ind_atts.nspname, 

        ind_atts.index_name, 

        ind_atts.reltuples, 

        ind_atts.relpages, 

        ind_atts.relam,

        indrelid AS table_oid, 

        index_oid,

        current_setting('block_size')::numeric AS bs,

        8 AS maxalign,

        24 AS pagehdr,

        CASE 

            WHEN max(coalesce(pg_stats.null_frac,0)) = 0 THEN 2

            ELSE 6

        END AS index_tuple_hdr,

        SUM((1 - coalesce(pg_stats.null_frac, 0)) * coalesce(pg_stats.avg_width, 1024)) AS nulldatawidth

    FROM pg_attribute

    JOIN btree_index_atts AS ind_atts 

        ON pg_attribute.attrelid = ind_atts.indexrelid 

       AND pg_attribute.attnum = ind_atts.attnum

    JOIN pg_stats 

        ON pg_stats.schemaname = ind_atts.nspname

       AND (

            (pg_stats.tablename = ind_atts.tablename AND pg_stats.attname = pg_catalog.pg_get_indexdef(pg_attribute.attrelid, pg_attribute.attnum, TRUE)) 

            OR 

            (pg_stats.tablename = ind_atts.index_name AND pg_stats.attname = pg_attribute.attname)

        )

    WHERE pg_attribute.attnum > 0

    GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9

),

index_aligned_est AS (

    SELECT 

        maxalign, bs, nspname, index_name, reltuples,

        relpages, relam, table_oid, index_oid,

        COALESCE (

            CEIL (

                reltuples * (

                    6 + maxalign 

                    - CASE 

                        WHEN index_tuple_hdr % maxalign = 0 THEN maxalign

                        ELSE index_tuple_hdr % maxalign

                      END

                    + nulldatawidth 

                    + maxalign 

                    - CASE 

                        WHEN nulldatawidth::integer % maxalign = 0 THEN maxalign

                        ELSE nulldatawidth::integer % maxalign

                      END

                )::numeric / (bs - pagehdr::NUMERIC) + 1

            ), 

            0

        ) AS expected

    FROM index_item_sizes

),

raw_bloat AS (

    SELECT 

        current_database() AS dbname, 

        nspname, 

        pg_class.relname AS table_name, 

        index_name,

        bs * (index_aligned_est.relpages)::bigint AS totalbytes, 

        expected,

        CASE

            WHEN index_aligned_est.relpages <= expected THEN 0

            ELSE bs * (index_aligned_est.relpages - expected)::bigint 

        END AS wastedbytes,

        CASE

            WHEN index_aligned_est.relpages <= expected THEN 0

            ELSE bs * (index_aligned_est.relpages - expected)::bigint * 100 

                 / (bs * (index_aligned_est.relpages)::bigint) 

        END AS realbloat,

        pg_relation_size(index_aligned_est.table_oid) AS table_bytes,

        stat.idx_scan AS index_scans

    FROM index_aligned_est

    JOIN pg_class ON pg_class.oid = index_aligned_est.table_oid

    JOIN pg_stat_user_indexes AS stat ON index_aligned_est.index_oid = stat.indexrelid

),

format_bloat AS (

    SELECT 

        dbname AS database_name, 

        nspname AS schema_name, 

        table_name, 

        index_name,

        ROUND(realbloat) AS bloat_pct, 

        ROUND(wastedbytes / (1024^3)::NUMERIC, 3) AS bloat_gb,

        ROUND(totalbytes / (1024^3)::NUMERIC, 3) AS index_gb,

        ROUND(table_bytes / (1024^3)::NUMERIC, 3) AS table_gb,

        index_scans

    FROM raw_bloat

)

-- Final simplified selection

SELECT 

    table_name,

    index_name,

    bloat_pct,

    bloat_gb,

    index_gb

FROM format_bloat

WHERE bloat_pct > 30 

  AND bloat_gb > 0.01

ORDER BY bloat_gb DESC

LIMIT 20;

AI - Difference between Gen AI | AI Agent | Agentic AI

                         Difference between Gen AI | AI Agent | Agentic AI

Generative AI	Generative AI is a type of artificial intelligence that that creates new contents such as text, images, audio or video based on on pattern learnt from existing data.
AI Agent	AI Agent is a program that takes input, think and act to complete a task using tool, memory and knowledge. It is autonomous but narrow. It is task specific and does not span multiple or evolving goals.
Agentic AI	Agentic AI is a system where one or more AI agents work autonomously, often over long task, making decisions, using tools and even other agent to reach the goal.

 

System Type	Gen AI (LLM-only)	AI Agent	Agentic AI
Task Capability	Answers based on pre-trained knowledge only	Takes input, decides, and completes a task	Handles multi-step goals with planning and coordination
Tool Usage	No external tools	Uses tools to complete a task	Uses multiple tools, may call other agents
Autonomous Decisions	No decision-making	Makes decisions to complete the task	Plans, decides, and adapts over time