This guide includes queries for postgreql

misc/utility commands

Set pager off

\pset pager off

1. Check when vaccum was run

select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables;

2. Vaccum the table manually

VACUUM ANALYZE table_name;  

3. Find how much time vaccum will take

https://dba.stackexchange.com/questions/44657/how-much-time-will-a-vacuum-autovacuum-operation-take

4. Get the size of the database

SELECT
    pg_database.datname,
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
    FROM pg_database;

5. Get the size of each table

SELECT
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

It will show 3 columns:

• Table – The name of the table
• Size – The total size that this table takes
• External Size – The size that related objects of this table like indices take

6. Query taking maximum time

SELECT max(now() -xact_start) FROM pg_stat_activity WHERE state IN ('idle in transaction','active');

7. check the connection group by client address

 select client_addr, count(*) as connections from pg_stat_activity group by client_addr order by connections desc;

7.1 check the queries details from specific ip and it's state

select state, count(*) as connections, max(now() -xact_start) as max_query_time, avg(now() -xact_start) as avg_query_time, query, from pg_stat_activity WHERE client_addr='X.x.x.X' group by state, query order by connections desc;

7.2 Cancel the queries from specific IP and database

SELECT 
    pg_terminate_backend(pid) 
FROM 
    pg_stat_activity 
WHERE 
    -- don't kill my own connection!
    pid <> pg_backend_pid()
    -- don't kill the connections to other databases
    AND datname = 'database_name'
    AND client_addr='X.x.x.X'
    ;

8. Get the approximate row count for the table Postgres

SELECT reltuples AS approximate_row_count FROM pg_class WHERE relname = 'table_name';

Timescale

SELECT h.schema_name,
    h.table_name,
    h.id AS table_id,
    h.associated_table_prefix,
    row_estimate.row_estimate
   FROM _timescaledb_catalog.hypertable h
     CROSS JOIN LATERAL ( SELECT sum(cl.reltuples) AS row_estimate
           FROM _timescaledb_catalog.chunk c
             JOIN pg_class cl ON cl.relname = c.table_name
          WHERE c.hypertable_id = h.id
          GROUP BY h.schema_name, h.table_name) row_estimate
ORDER BY schema_name, table_name;

9. Get the parameters for the postgresql right

http://pgtune.leopard.in.ua/

10. About locks

https://www.pgcon.org/2017/schedule/attachments/449_PostgresLocking

11. Copy the queries to file

\COPY ('sql query') TO 'filepath/filename';

12. See the locks

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));

References

1. https://lob.com/blog/supercharge-your-postgresql-performance
2. https://www.postgresql.org/docs/9.5/static/sql-vacuum.html
3. https://wiki-bsse.ethz.ch/display/ITDOC/Check+size+of+tables+and+objects+in+PostgreSQL+database
4. https://www.postgresql.org/docs/current/routine-vacuuming.html

Managing transaction id wraparound

5. https://www.crunchydata.com/blog/managing-transaction-id-wraparound-in-postgresql
6. https://hevodata.com/learn/postgresql-vacuum-command/#2