Postgres Index Optimization and Benchmarking

[tolgaOzen]

Introduction

This document outlines the process of optimizing indexes in a Postgres database and conducting before-and-after benchmarks to evaluate the performance impact of these optimizations on specific check queries.

Repository File References

• Migrations: Located in internal/storage/postgres/migrations
• Queries: Located in internal/engines

Optimization Process

Initial Analysis

• Description: Assess the current indexing strategy and identify potential areas for improvement.
• Tools Used: [List any analysis tools or scripts used]
• Findings: Summarize the key findings from the initial analysis.

Index Optimization

• Optimization Strategies: Detail the specific index optimizations implemented.
  • Strategy 1: [Description]
  • Strategy 2: [Description]
  • ...
• Implementation Details: Provide SQL commands or scripts used for the optimization.

Benchmarking Setup

Environment Details

• Postgres Version: [Version]
• Operating System: [OS]
• Hardware Specifications: [Hardware details]

Benchmarking Methodology

• Queries Tested: List the check queries used for benchmarking, as found in the internal/engines folder.
  • Query 1: SQL statement
  • Query 2: SQL statement
  • ...
• Procedure: Describe the benchmarking process, including how many times each query is run and how metrics are collected.

Benchmark Results

Before Optimization

• Query 1:
  • Average Execution Time: [Time]
  • Additional Metrics: [Any other relevant metrics]
• Query 2:
  • Average Execution Time: [Time]
  • Additional Metrics: [Any other relevant metrics]
• ...

After Optimization

• Query 1:
  • Average Execution Time: [Time]
  • Additional Metrics: [Any other relevant metrics]
• Query 2:
  • Average Execution Time: [Time]
  • Additional Metrics: [Any other relevant metrics]
• ...

Analysis and Conclusions

• Performance Improvements: Highlight the key improvements in query performance post-optimization.
• Recommendations: Offer any further recommendations based on the benchmarking results.

Appendix

• Additional Notes: [Any other notes or observations]
• Scripts/Commands Used: Provide any scripts or commands used during the optimization and benchmarking process.

[tolgaOzen]

/bounty $400

[algora-pbc]

💎 $400 bounty created by Permify
🙋 If you'd like to work on this issue, comment below to get assigned
👉 To claim this bounty, submit a pull request that includes the text /claim #863 somewhere in its body
📝 Before proceeding, please make sure you can receive payouts in your country
💵 Payment arrives in your account 2-5 days after the bounty is rewarded
💯 You keep 100% of the bounty award
🙏 Thank you for contributing to Permify/permify!

[saifxd7]

/attempt #863

Options

• Cancel my attempt

[b4s36t4]

if anyone want to do, I found this tool https://github.com/ankane/dexter might be helpful.

[tolgaOzen]

@saifxd7, I have assigned this task to you. Happy hacking 🚀

[bolleyboll]

/attempt #863

Options

• Cancel my attempt

[algora-pbc]

Note: The user @saifxd7 is already attempting to complete issue #863 and claim the bounty. If you attempt to complete the same issue, there is a chance that @saifxd7 will complete the issue first, and be awarded the bounty. We recommend discussing with @saifxd7 and potentially collaborating on the same solution versus creating an alternate solution.

[Gmin2]

Hi @tolgaOzen i have initially seen the issues and have made the initial analysis of the database . Should i share how i would approach for optimizing the database?

[tolgaOzen]

Hello @Min2who, @saifxd7 is currently dealing with the issue. You can share it with him or you can collaborate with him on the development

[Gmin2]

@saifxd7 are you still working on this ?

[saifxd7]

Yes @Min2who, I'm working on it!

[algora-pbc]

💡 @saifxd7 submitted a pull request that claims the bounty. You can visit your bounty board to reward.

[tolgaOzen]

Hello @saifxd7, firstly, we have reviewed your PR and changing old migration files is dangerous for those who previously used Permify and should not be done. Additionally, since we want this issue to be analyzed more thoroughly, we would like to assign one more person to elaborate on it.

[tolgaOzen]

Hello @Min2who, if you still want to work on this issue. I can assign it to you.

[Gmin2]

i would like to work in it.If you can assign me

[tolgaOzen]

@Min2who, I'm assigning you. Happy hacking 🚀

[tolgaOzen]

Hello @Min2who, are there any updates?

[Gmin2]

Hello @Min2who, are there any updates?

Hey @tolgaOzen was travelling for a bit. I will submit my progress in a week. Is it fine ?

[Gmin2]

Hey @tolgaOzen i have unassigned from this issue. If anyone interested can give it a try !

[Rutik7066]

/attempt #863

Options

• Cancel my attempt

[tolgaOzen]

Okay @Min2who, thank you. I am assigning you, @Rutik7066. Happy hacking 🚀

[Rutik7066]

Thanks @tolgaOzen

[Rutik7066]

Hi @tolgaOzen you can assign this to other person as well. Thanks

[ayewo]

Throwing my hat in the ring with an /attempt #863

[algora-pbc]

@ayewo: The Permify.co team prefers to assign a single contributor to the issue rather than let anyone attempt it right away. We recommend waiting for a confirmation from a member before getting started.

[tolgaOzen]

Hi @ayewo, I'm assigning you. Happy hacking 🚀

[tolgaOzen]

Hello @ayewo, is there any progress?

[mohanish2504]

I will like to add my name in queue @tolgaOzen.

I have good experience with Postgres indexes.

references:

1. https://medium.com/@mohanishp1/optimizing-time-series-queries-b9fe7715ccea
2. https://medium.com/@mohanishp1/indexing-a-blessing-in-vertical-scaling-e037c3063b05

/attempt #863

[algora-pbc]

@mohanish2504: Another person is already attempting this issue. Please don't start working on this issue unless you were explicitly asked to do so.

[ayewo]

Hey @tolgaOzen

Thanks for the status check. I fell ill and I’m still recovering. Hopefully I’ll be able to continue this week.

[Rutik7066]

Hi @ayewo are you working on this?

[mohanish2504]

Hey @tolgaOzen I would like to contribute to this issue if @ayewo is not working.
@ayewo please let us know.

[ayewo]

I'm feeling much better and have already started working on this!

[Rutik7066]

Hey @ayewo can we collaborate?

[tolgaOzen]

Hello @ayewo, is there any progress?

[ayewo]

@tolgaOzen progress has been slow. No qualms if you'd like to assign this to someone else.

[tolgaOzen]

Hi @ayewo, okay, I am assigning @mohanish2504.

[mohanish2504]

Thanks @tolgaOzen.

[algora-pbc]

💡 @mohanish2504 submitted a pull request that claims the bounty. You can visit your bounty board to reward.

[algora-pbc]

🎉🎈 @mohanish2504 has been awarded $400! 🎈🎊