Development::Performance::DB Performance Bottlenecks Finding (draft)

Some ideas to help diagnose poor performance on Postgres SQL queries:

Enter docker container:

docker exec -ti cardano-rosetta-java-db-1 /bin/bash

Connect to psql inside of docker container:

PGPASSWORD=weakpwd#123_d psql -h localhost -p 5432 -d rosetta-java -U rosetta_db_admin

Schema list

SELECT schema_name FROM information_schema.schemata; or \dn

pin default schema

SET search_path TO mainnet;

list tables

\dt

rosetta-java=> \dt
                       List of relations
 Schema  |           Name           | Type  |      Owner
---------+--------------------------+-------+------------------
 mainnet | address                  | table | rosetta_db_admin
 mainnet | address_utxo             | table | rosetta_db_admin
 mainnet | block                    | table | rosetta_db_admin
 mainnet | cost_model               | table | rosetta_db_admin
 mainnet | cursor_                  | table | rosetta_db_admin
 mainnet | delegation               | table | rosetta_db_admin
 mainnet | epoch_param              | table | rosetta_db_admin
 mainnet | era                      | table | rosetta_db_admin
 mainnet | error                    | table | rosetta_db_admin
 mainnet | flyway_schema_history    | table | rosetta_db_admin
 mainnet | invalid_transaction      | table | rosetta_db_admin
 mainnet | local_epoch_param        | table | rosetta_db_admin
 mainnet | pool_registration        | table | rosetta_db_admin
 mainnet | pool_retirement          | table | rosetta_db_admin
 mainnet | protocol_params_proposal | table | rosetta_db_admin
 mainnet | rollback                 | table | rosetta_db_admin
 mainnet | stake_registration       | table | rosetta_db_admin
 mainnet | transaction              | table | rosetta_db_admin
 mainnet | transaction_size         | table | rosetta_db_admin
 mainnet | transaction_witness      | table | rosetta_db_admin
 mainnet | tx_input                 | table | rosetta_db_admin
 mainnet | withdrawal               | table | rosetta_db_admin
(22 rows)

Postgres Config File Location:

/var/lib/postgresql/data/postgresql.conf

Postgres SQL Performance Monitoring Options

---

1. Use pg_stat_statements (Best for Monitoring)

PostgreSQL provides a built-in extension pg_stat_statements that tracks query performance, including index usage.

Enable pg_stat_statements

1. Edit postgresql.conf (inside your container):

   shared_preload_libraries = 'pg_stat_statements'
   track_activity_query_size = 2048

2. Restart PostgreSQL:

   docker restart <postgres_container_name>

3. Run inside psql:

   CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Query to Check Queries Using Indexes

SELECT query, calls, total_exec_time, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

• shared_blks_hit > shared_blks_read → More index usage.
• If shared_blks_read is high, queries are scanning more blocks, possibly missing indexes.

---

2. Log Queries That Don't Use Indexes

If you want to log queries that perform sequential scans, enable logging.

Edit postgresql.conf

log_min_duration_statement = 500  # Log queries taking more than 500ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_statement = 'all'
log_temp_files = 0
log_autovacuum_min_duration = 0

Then restart PostgreSQL.

Filter Queries Using Sequential Scan

Once logging is enabled, find queries not using indexes:

cat /var/lib/postgresql/data/log/postgresql.log | grep 'Seq Scan'

If many queries are using Seq Scan, they are not hitting indexes.

---

3. Use EXPLAIN (ANALYZE, BUFFERS) Automatically

To analyze all queries dynamically:

CREATE OR REPLACE FUNCTION log_explain() RETURNS event_trigger AS $$
BEGIN
  RAISE NOTICE '%', (SELECT json_agg(row_to_json(t))
    FROM (
      SELECT query, json_agg(row_to_json(p)) AS plan
      FROM pg_stat_statements p, LATERAL (EXPLAIN (ANALYZE, BUFFERS) p.query) q
    ) t);
END;
$$ LANGUAGE plpgsql;

Then attach it to queries.

---

4. Use auto_explain (Automatic Logging of Query Plans)

This extension logs execution plans automatically.

Enable auto_explain

1. Edit postgresql.conf:

   shared_preload_libraries = 'auto_explain'
   auto_explain.log_min_duration = 500  # Log queries taking more than 500ms
   auto_explain.log_analyze = true
   auto_explain.log_buffers = true
   auto_explain.log_nested_statements = true

2. Restart PostgreSQL:

   docker restart <postgres_container_name>

3. Run in psql:

   LOAD 'auto_explain';

Now, PostgreSQL will log all slow queries with EXPLAIN ANALYZE, allowing you to detect if indexes are used.

---

5. Third-Party Monitoring Tools

If you want a UI-based approach, tools like pgAdmin, pganalyze, or pgwatch2 can track index usage visually.

---

Final Thoughts

For real-time monitoring, use pg_stat_statements.
For logging slow queries, enable auto_explain.
For detailed analysis, filter logs for Seq Scan.

Would you like help setting up any of these? 🚀