Debug persistence issues

Several tools can help diagnose database-related issues (transactions, locks, or SQL execution time).

1. Inspect the database

Use tools like DBeaver or pgAdmin to connect to PostgreSQL and inspect the data through SQL queries (command-line tools also work, but a UI tends to be easier). Useful queries include:

  • SELECT * FROM project

  • SELECT * FROM semantic_data

  • SELECT * FROM representation_data

  • SELECT * FROM databasechangelog (Liquibase metadata)

To check table sizes:

SELECT table_schema,
       table_name,
       pg_total_relation_size('"'||table_schema||'"."'||table_name||'"')
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY 3 DESC;

2. Monitor database activity

Run SELECT * FROM pg_stat_activity to list the PostgreSQL sessions. Look for clients named PostgreSQL JDBC Driver (the default Spring/Hikari pool name):

  • Multiple idle entries with SET application_name = 'PostgreSQL JDBC Driver' are the idle pool threads (expected).

  • Entries whose last query is COMMIT are threads that completed at least one transaction.

  • An idle client with a meaningful query may indicate a transaction that was never committed.

  • An active client stuck for a long time likely points to a slow query.

3. Log outbound SQL

3.1. Spring JDBC logs

Set logging.level.org.springframework.jdbc=debug to log JDBC lifecycle events, including transaction creation/commit and the SQL statements executed.

3.2. Datasource proxy logs

If you need parameter values, temporarily add the following dependency to sirius-web:

<dependency>
  <groupId>com.github.gavlyukovskiy</groupId>
  <artifactId>datasource-proxy-spring-boot-starter</artifactId>
  <version>1.9.0</version>
</dependency>

Then enable logging.level.net.ttddyy.dsproxy.listener=debug to log each statement with its bound parameters.

4. Profile expensive queries

Use PostgreSQL’s EXPLAIN with ANALYZE to understand how a statement is executed:

EXPLAIN (analyze, buffers, format text)
SELECT "...";

The output shows the time spent on each plan node, which helps pinpoint the bottleneck.

5. Enable verbose PostgreSQL logs

You can configure the Dockerized PostgreSQL instance to log every detail:

docker run -p 5438:5432 --name sirius-web-postgresql \
  -e POSTGRES_USER=dbuser -e POSTGRES_PASSWORD=dbpwd -e POSTGRES_DB=sirius-web-db \
  -itd postgres -c logging_collector=on -d 5

Decrease -d (debug level) if the log becomes too verbose. Logs are typically available under /var/lib/postgresql/data/log within the container.