Troubleshooting PostgreSQL

To figure out what is going on in the system, the following queries may be useful.
Find the most time-consuming queries, like this:
SELECT (SELECT datname
FROM pg_database
WHERE dbid = oid),
query, calls, total_time
FROM pg_stat_statements AS x
ORDER BY total_time DESC

SELECT schemaname, relname, n_tup_ins,
n_tup_upd, n_tup_hot_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC;

test=# CREATE FUNCTION mysum(int, int)
RETURNS int AS ' SELECT $1 + $2 '
LANGUAGE 'sql'

test=# SELECT granted, transactionid, mode, pid
FROM pg_locks
WHERE transactionid = 1812

The following query detects locks that have not been granted:
test=# SELECT locktype, granted, transactionid,
mode
FROM pg_locks WHERE granted = 'f';

Logically, this transaction has to wait for the first transaction. In this case, it is obvious what is going on, but how can it be checked if you don't know?
The first thing to check is always pg_stat_activity. It will reveal what is going on and which operations are active:
test=# \x
Expanded display is on.
test=# SELECT pid, query, waiting

Prepared queries try to solve exactly that problem. The idea is that the backend process will cache the plan and reuse it just in case it is needed again. The beauty is that sending the parameters instead of the complete query can be enough. Usually, prepared statements are happening behind the scenes when desired (this is usually done through the driver if the driver is prepared for it). However, you can also prepare queries manually

test=# \d pg_stat_user_indexes
View "pg_catalog.pg_stat_user_indexes"

SELECT schemaname, relname, seq_scan, seq_tup_read,
idx_scan, seq_tup_read / seq_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC;
The query provides us with schemaname and relname to identify the table inside the database. The seq_scan field will return the number of times the table has been read sequentially. During those sequential scans, the database had to read seq_tup_read rows. The idx_scan field informs us about the number of index scans, and finally, the average number of rows needed by seq_scan is displayed.

table in PostgreSQL can hold up to 1600 columns