Unconference - Stream 2 - 11:15-12:15 - PGCon 2022

"Optimize your PostgreSQL queries and achieve better performance by avoiding over-engineering, simplifying table-based checks, and leveraging tuple stores, index scans, and memoization."

Key takeaways

Table-based checks can be done faster and more simply.
Avoid over-engineering and focus on simplicity.
Use tuple stores for performance benefits.
Remove SPI as a first step towards improving performance.
Trigger handling can be complex and time-consuming.
Row-level triggers are heavy and may not be necessary.
Statement-level triggers can be used to handle some cases.
The executor function is complex and has overhead.
The planner can be bypassed in some cases.
The mini planner can be used for simple cases.
Consider memoizing the output of the mini planner.
The optimizer can be used to optimize plans.
Simple scans can be used for sequential scans.
Tuple routing can be used for partitioned tables.
Reference integrity checks can be complex and time-consuming.
Foreign keys can be used to enforce referential integrity.
Index scans can be used for sequential scans.
The executor can be bypassed for some operations.
Row-level triggers can be used for referential integrity.

Unconference - Stream 2 - 11:15-12:15 - PGCon 2022

More talks