@Marvin are there any best practices on how to optimize the self-hosted prefect postgresql database? Opening the "Runs" tab takes minutes, and I feel like I need to actively delete old runs on a schedule. But I would like to know the best practices.

Optimizing the performance of a self-hosted Prefect PostgreSQL database can indeed be crucial, especially if you're experiencing slow performance when accessing the "Runs" tab. Here are some best practices and considerations based on available documentation and issues: 1. Database Maintenance: - Regularly delete old or unnecessary flow runs to reduce database size. This can be automated through scheduled scripts or Prefect flows. - Consider setting up a retention policy for runs, which automatically deletes runs older than a certain threshold. 2. Database Configuration: - Adjust PostgreSQL configuration settings like

shared_buffers

,

work_mem

, and

maintenance_work_mem

to better suit your workload. These can have a significant impact on performance. - Ensure that your PostgreSQL instance is using the latest stable version, as performance improvements are often included in updates. 3. Connection Pooling: - Use connection pooling to manage database connections efficiently. Prefect's configuration allows you to control the connection pool size via

sqlalchemy_pool_size

. 4. Timeouts and Performance: - Configure appropriate timeouts for database interactions using

PREFECT_SERVER_DATABASE_TIMEOUT

and

PREFECT_API_DATABASE_TIMEOUT

to prevent long-running queries from affecting performance. - Monitor and address any timeout issues, as highlighted in this GitHub issue. 5. Hardware and Scaling: - Ensure your database server has sufficient resources (CPU, memory, I/O) to handle the expected load. - Consider scaling vertically (upgrading server resources) or horizontally (sharding or adding replicas) if necessary. 6. Monitoring and Logs: - Regularly monitor database performance metrics and logs to identify bottlenecks or issues. - Use tools like

pg_stat_activity

to monitor active queries and identify slow-running ones. For more detailed guidance, you can refer to the Prefect documentation on self-hosting and database settings. These resources provide additional context and settings you may adjust to optimize performance.

@Marvin I also see a bunch of deadlocks in the database logs:

xxx:prefect@prefect:[27289]:DETAIL:  Process 27289 waits for ShareLock on transaction 25117713; blocked by process 27282.
	Process 27282 waits for ShareLock on speculative token 192 of transaction 25117714; blocked by process 27289.
	Process 27289: INSERT INTO task_run (flow_run_id, task_key, dynamic_key, flow_run_run_count, empirical_policy, task_inputs, tags, labels, name, run_count, expected_start_time, start_time, total_run_time, id, created, updated) VALUES ($1::UUID, $2::VARCHAR, $3::VARCHAR, $4::INTEGER, $5, $6, $7::JSONB, $8::JSONB, $9::VARCHAR, $10::INTEGER, $11::TIMESTAMP WITH TIME ZONE, $12::TIMESTAMP WITH TIME ZONE, $13::INTERVAL, $14::UUID, $15::TIMESTAMP WITH TIME ZONE, $16::TIMESTAMP WITH TIME ZONE) ON CONFLICT (id) DO UPDATE SET flow_run_id = $17::UUID, task_key = $18::VARCHAR, dynamic_key = $19::VARCHAR, flow_run_run_count = $20::INTEGER, empirical_policy = $21, task_inputs = $22, tags = $23::JSONB, labels = $24::JSONB, name = $25::VARCHAR, run_count = $26::INTEGER, expected_start_time = $27::TIMESTAMP WITH TIME ZONE, start_time = $28::TIMESTAMP WITH TIME ZONE, total_run_time = $29::INTERVAL, id = $30::UUID, updated = $31::TIMESTAMP WITH TIME ZONE WHERE task_run.state_timestamp < $32::TIMESTAMP WITH TIME ZONE
	Process 27282: UPDATE task_run SET state_id=$1::UUID, state_type=$2::state_type, state_name=$3::VARCHAR, state_timestamp=$4::TIMESTAMP WITH TIME ZONE, updated=now() WHERE task_run.id = $5::UUID AND (task_run.state_timestamp IS NULL OR task_run.state_timestamp < $6::TIMESTAMP WITH TIME ZONE)

Deadlocks in PostgreSQL, especially in the context of Prefect, can be challenging but there are some strategies to mitigate them: 1. Understanding Deadlocks: - Deadlocks occur when two or more transactions block each other by holding locks that the other transactions need. This can often happen with concurrent updates or inserts. 2. Transaction Management: - Ensure that transactions are as short as possible. The longer a transaction runs, the more likely it is to block other transactions. - Review your transaction isolation levels. Prefect uses the

READ_COMMITTED

isolation level by default, which is generally a good balance between consistency and performance. 3. Locking Strategies: - Avoid locking rows in the same order in different transactions. This can help prevent circular waits. - Consider using advisory locks if you need to coordinate complex operations. 4. Database Configuration: - Increase the

max_locks_per_transaction

parameter in PostgreSQL if necessary. This setting controls the number of locks that can be held by a single transaction. - Monitor and tune your PostgreSQL settings related to locks and concurrency. 5. Application Logic: - Review the application logic to ensure that transactions are not holding locks longer than necessary. - Consider batching updates or inserts to minimize the number of transactions. 6. Monitoring and Analysis: - Use PostgreSQL's

pg_stat_activity

and

pg_locks

views to monitor active queries and locks. - Analyze deadlock logs to understand the specific queries and transactions involved. For more detailed information on handling transactions and deadlocks, you can refer to the Prefect documentation on transactions and related GitHub issues discussing deadlocks and multiprocessing challenges. By implementing these strategies, you should be able to reduce the incidence of deadlocks and improve the overall performance of your Prefect-managed PostgreSQL database.