Unconventional PostgreSQL Optimizations (hakibenita.com)

Article summary

The article discusses unconventional PostgreSQL optimization techniques, including using constraint_exclusion, function-based indexes, and virtual generated columns. These techniques can help improve query performance and reduce storage costs. The article also highlights the importance of considering the trade-offs between query performance and storage costs. By using these techniques, developers can optimize their PostgreSQL databases for better performance and efficiency.

Main themes

• PostgreSQL optimization
• constraint_exclusion
• function-based indexes
• virtual generated columns
• MERGE statement
• upserts
• hash indexes
• storage costs
• query performance

What commenters say

• Some commenters argue that virtual columns and hash indexes are useful features, but feel like they are bolted on and not fully integrated into the PostgreSQL ecosystem.
• Others believe that stored generated columns are a better approach than virtual columns, but this may increase storage usage.
• There is disagreement about the usefulness of the MERGE statement, with some commenters finding it more readable, but others pointing out its limitations and potential for unique constraint violations.
• Some commenters prefer using INSERT ... ON CONFLICT instead of MERGE for upserts, due to its guarantee of either inserting or updating, even in the presence of concurrent inserts.
• The use of COPY INTO for batch inserts is suggested as a fast approach, but may not work well with conflicts.
• Hash indexes are considered superior to creating a unique hash column and precomputing the hash, due to their performance and storage advantages.