This guide provieds a comprehensive collection of SQL queries for monitoring and managing PostgreSQL and Greenlpum databases, covering session analysis, lock investigation, resource management, and metadata operations.

1. Active Connection Analysis

---

Monitor current client connections and their status:

-- Count active connections excluding current session
SELECT COUNT(*) AS connection_count
FROM pg_stat_activity
WHERE pid != pg_backend_pid();

-- Detailed session information
SELECT pid, usename, application_name, client_addr, state, query_start
FROM pg_stat_activity
WHERE pid != pg_backend_pid();

-- Lock overview with active queries
SELECT 
    lk.locktype,
    lk.database,
    cls.relname AS locked_object,
    lk.pid,
    lk.mode,
    lk.granted,
    act.query AS current_statement
FROM pg_locks lk
INNER JOIN pg_class cls ON lk.relation = cls.oid
INNER JOIN pg_stat_activity act ON lk.pid = act.pid
ORDER BY cls.relname;

2. Client Connection Status Monitoring

---

Identify connection states and execution times:

SELECT
    pid,
    CASE 
        WHEN waiting THEN 'blocked_waiting_for_lock'
        ELSE 'active_executing'
    END AS execution_status,
    NOW() - GREATEST(query_start, xact_start) AS duration,
    LEFT(query, 30) AS query_preview
FROM pg_stat_activity
WHERE pid != pg_backend_pid()
    AND state != 'idle'
    AND application_name NOT ILIKE 'pg_stats%'
ORDER BY duration DESC;

3. Lock Contention Investigation

---

Analyze lock holders and waiters with transaction details:

-- Identify lock conflicts (excluding indexes where relkind=0)
SELECT
    locker.pid,
    pc.relname AS table_name,
    locker.mode AS lock_mode,
    locker_act.application_name,
    MIN(locker_act.query_start) AS transaction_start,
    CASE 
        WHEN locker.granted THEN 'lock_acquired'
        ELSE 'waiting_for_lock'
    END AS lock_state,
    NOW() - MIN(locker_act.query_start) AS wait_duration,
    locker_act.query AS current_sql
FROM pg_locks locker
JOIN pg_stat_activity locker_act ON locker.pid = locker_act.pid
JOIN pg_class pc ON locker.relation = pc.oid
WHERE locker.pid != pg_backend_pid()
    AND pc.relkind != 'i'
    AND application_name NOT ILIKE 'pg_stats%'
GROUP BY locker.pid, pc.relname, locker.mode, locker_act.application_name, 
         locker.granted, locker_act.query
ORDER BY wait_duration DESC;

4. Transaction and Lock Relationship

---

Map transactions to their locked resources:

SELECT
    pc.relname AS locked_table,
    tans.pid,
    CASE 
        WHEN tans.granted THEN 'executing_with_lock'
        ELSE 'waiting_for_lock'
    END AS status,
    LEAST(tans.query_start, tans.xact_start) AS transaction_begin,
    NOW() - LEAST(tans.query_start, tans.xact_start) AS elapsed_time,
    psa.query AS active_query
FROM pg_locks tans
JOIN pg_locks pl ON tans.pid = pl.pid
JOIN pg_class pc ON pl.relation = pc.oid
JOIN pg_stat_activity psa ON tans.pid = psa.pid
WHERE tans.transactionid IS NOT NULL
    AND pc.relkind = 'r'
ORDER BY elapsed_time DESC;

5. Table-Specific Lock Analysis

---

Focus on locks for a particular table:

SELECT
    lk.locktype,
    lk.pid,
    lk.virtualtransaction,
    lk.transactionid,
    nsp.nspname AS schema_name,
    cls.relname AS table_name,
    lk.mode,
    lk.granted,
    CASE 
        WHEN lk.granted THEN 'lock_held'
        ELSE 'lock_requested'
    END AS lock_status,
    NOW() - GREATEST(act.query_start, act.xact_start) AS query_duration,
    act.query_start,
    LEFT(act.query, 50) AS sql_preview
FROM pg_locks lk
LEFT JOIN pg_class cls ON lk.relation = cls.oid
LEFT JOIN pg_namespace nsp ON cls.relnamespace = nsp.oid
JOIN pg_stat_activity act ON lk.pid = act.pid
WHERE lk.pid != pg_backend_pid()
    AND cls.relname = 'your_table_name' -- Replace with target table
ORDER BY act.query_start;

6. Real-Time SQL Execution Monitoring

---

Track currently executing statements:

-- Active query monitoring
SELECT
    pid,
    backend_start,
    NOW() - backend_start AS session_duration,
    query AS executing_sql
FROM pg_stat_activity
WHERE state = 'active'
    AND pid != pg_backend_pid()
ORDER BY session_duration DESC;

-- Terminate a long-running query
SELECT pg_cancel_backend(12345); -- Use actual PID

-- Forceful termination
SELECT pg_terminate_backend(12345);

7. Storage Size Analysis

---

Examine database and object sizes:

-- Top 20 largest tables and indexes
SELECT
    ns.nspname AS schema_name,
    cls.relname AS object_name,
    cls.relkind AS object_type,
    pg_size_pretty(pg_table_size(cls.oid)) AS table_size,
    pg_size_pretty(pg_indexes_size(cls.oid)) AS index_size,
    pg_size_pretty(pg_total_relation_size(cls.oid)) AS total_size
FROM pg_class cls
JOIN pg_namespace ns ON ns.oid = cls.relnamespace
WHERE ns.nspname NOT IN ('pg_catalog', 'information_schema')
    AND ns.nspname !~ '^pg_toast'
    AND cls.relkind IN ('r', 'i')
ORDER BY pg_total_relation_size(cls.oid) DESC
LIMIT 20;

-- Database size summary
SELECT 
    datname,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database;

8. Greenplum Distribution Key Analysis

---

Examine table distribution strategies:

SELECT
    sch.nspname AS schema_name,
    tbl.relname AS table_name,
    obj_description(tbl.oid) AS table_comment,
    string_agg(attr.attname, ', ' ORDER BY attr.attnum) AS distribution_keys
FROM pg_class tbl
JOIN pg_namespace sch ON tbl.relnamespace = sch.oid
LEFT JOIN gp_distribution_policy dpol ON tbl.oid = dpol.localoid
LEFT JOIN pg_attribute attr ON tbl.oid = attr.attrelid 
    AND attr.attnum = ANY(dpol.attrnums)
LEFT JOIN pg_inherits inh ON tbl.oid = inh.inhrelid
WHERE sch.nspname = 'public' -- Adjust schema name
    AND inh.inhrelid IS NULL
    AND attr.attnum > 0
GROUP BY sch.nspname, tbl.relname, tbl.oid, dpol.attrnums
ORDER BY tbl.relname;

9. Greenplum Resource Management

---

Monitor resource queues and workload:

-- Resource queue status
SELECT * FROM gp_toolkit.gp_resqueue_status;

-- Waiting resource queue locks
SELECT * FROM gp_toolkit.gp_locks_on_resqueue 
WHERE lorwaiting = 'true';

-- Query priority assignments
SELECT * FROM gp_toolkit.gp_resq_priority_statement;

-- Active sessions with resource queue details
SELECT
    act.pid,
    act.usename,
    rsq.rsqname AS queue_name,
    act.query,
    NOW() - act.query_start AS duration
FROM pg_stat_activity act
JOIN pg_roles rol ON act.usename = rol.rolname
JOIN pg_resqueue rsq ON rol.rolresqueue = rsq.oid
WHERE act.state = 'active';

10. Data Distribution Health Check

---

Identify data skew across segments:

SELECT
    seg_id,
    row_count,
    ROUND(row_count - AVG(row_count) OVER (), 0) AS skew_count
FROM (
    SELECT 
        gp_segment_id AS seg_id,
        COUNT(*) AS row_count
    FROM your_table -- Replace with target table
    GROUP BY gp_segment_id
) distribution
ORDER BY skew_count DESC;

11. Metadata and Schema Queries

---

Extract table structures and properties:

-- Table structure details
SELECT
    attr.attname AS column_name,
    typ.typname AS data_type
FROM pg_attribute attr
JOIN pg_class tbl ON attr.attrelid = tbl.oid
JOIN pg_type typ ON attr.atttypid = typ.oid
JOIN pg_namespace nsp ON tbl.relnamespace = nsp.oid
WHERE attr.attnum > 0
    AND NOT attr.attisdropped
    AND nsp.nspname = 'public'
    AND tbl.relname = 'your_table'
ORDER BY attr.attnum;

-- Tables missing statistics
SELECT * FROM gp_toolkit.gp_stats_missing;

-- Invalid segment information
SELECT * FROM gp_toolkit.gp_pgdatabase_invalid;

12. Partition Table Management

---

Inspect partitioned table structures:

SELECT
    tablename,
    partitiontablename,
    partitiontype,
    partitionboundary
FROM pg_partitions
WHERE tablename = 'partitioned_table'
ORDER BY partitionboundary DESC;

13. Data Import Operations

---

Execute bulk data loading:

-- Local file import with error logging
COPY target_table FROM '/path/to/datafile.txt' 
WITH (DELIMITER '|', FORMAT 'text')
LOG ERRORS INTO error_table SEGMENT REJECT LIMIT 100;

-- Remote data import via psql
psql -h remote_host -U username -d database -c 
"COPY target_table FROM STDIN WITH DELIMITER '|'" < /path/to/localfile.txt

14. Permission Management

---

Generate and manage access grants:

-- Generate SELECT grants for tables
SELECT 
    'GRANT SELECT ON ' || nsp.nspname || '.' || cls.relname || ' TO reporting_role;' AS grant_statement
FROM pg_class cls
JOIN pg_namespace nsp ON cls.relnamespace = nsp.oid
WHERE cls.relkind = 'r'
    AND nsp.nspname NOT IN ('pg_catalog', 'information_schema')
    AND NOT has_table_privilege('reporting_role', cls.oid, 'SELECT');

-- Schema-level permissions
SELECT DISTINCT
    'GRANT ALL ON SCHEMA ' || nspname || ' TO app_user;' AS schema_grant
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
    AND nspname != 'information_schema';

15. Query History Analysis

---

Review historical query performance:

-- Query execution history (requires gpmetrics schema)
SELECT
    username,
    db AS database_name,
    cost AS query_cost,
    tstart AS start_time,
    tfinish AS end_time,
    status,
    query_text,
    EXTRACT(EPOCH FROM (tfinish - tstart)) AS duration_seconds
FROM gpmetrics.queries_history
WHERE tfinish >= CURRENT_DATE - INTERVAL '1 day'
    AND EXTRACT(EPOCH FROM (tfinish - tstart)) > 10
ORDER BY duration_seconds DESC;

16. Database Maintenance

---

Address table bloat and optimize storage:

-- Identify bloated tables
SELECT * FROM gp_toolkit.gp_bloat_diag 
ORDER BY bdirelpages DESC, bdidiag;

-- Reclaim space (exclusive lock required)
VACUUM FULL bloated_table;

-- Redistribute data evenly
ALTER TABLE skewed_table SET WITH (REORGANIZE=true) 
DISTRIBUTED BY (distribution_column);

17. Utility Commands

---

Advanced psql operations and type handling:

-- Cross-database query execution
psql -tc "SELECT COUNT(*) FROM staging.events;" prod_db | psql -a analytics_db

-- Type casting examples
SELECT employee_id::text AS id_string FROM employees;

-- Find tables without primary keys
SELECT
    tab.table_schema,
    tab.table_name
FROM information_schema.tables tab
LEFT JOIN information_schema.table_constraints tco 
    ON tab.table_schema = tco.table_schema 
    AND tab.table_name = tco.table_name
    AND tco.constraint_type = 'PRIMARY KEY'
WHERE tab.table_type = 'BASE TABLE'
    AND tab.table_schema NOT IN ('pg_catalog', 'information_schema')
    AND tco.constraint_name IS NULL;

-- Change default search path
SHOW search_path;
ALTER DATABASE mydb SET search_path TO custom_schema, public;