Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Postgres Index Optimization and Benchmarking #863

Closed
tolgaOzen opened this issue Nov 22, 2023 · 41 comments · Fixed by #984 · May be fixed by #878
Closed

Postgres Index Optimization and Benchmarking #863

tolgaOzen opened this issue Nov 22, 2023 · 41 comments · Fixed by #984 · May be fixed by #878
Assignees
Labels
area/database Issues related with database configurations 💎 Bounty go Pull requests that update Go code 💰 Rewarded

Comments

@tolgaOzen
Copy link
Member

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

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 tolgaOzen added area/database Issues related with database configurations go Pull requests that update Go code 💎 Bounty labels Nov 22, 2023
@tolgaOzen
Copy link
Member Author

/bounty $400

Copy link

algora-pbc bot commented Nov 22, 2023

💎 $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
Copy link
Contributor

saifxd7 commented Nov 22, 2023

/attempt #863

Options

@b4s36t4
Copy link

b4s36t4 commented Nov 22, 2023

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

@tolgaOzen
Copy link
Member Author

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

@bolleyboll
Copy link

bolleyboll commented Nov 22, 2023

/attempt #863

Options

Copy link

algora-pbc bot commented Nov 22, 2023

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
Copy link
Contributor

Gmin2 commented Nov 24, 2023

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
Copy link
Member Author

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
Copy link
Contributor

Gmin2 commented Nov 24, 2023

@saifxd7 are you still working on this ?

@saifxd7
Copy link
Contributor

saifxd7 commented Nov 25, 2023

Yes @Min2who, I'm working on it!

Copy link

algora-pbc bot commented Nov 26, 2023

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

@tolgaOzen
Copy link
Member Author

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
Copy link
Member Author

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

@Gmin2
Copy link
Contributor

Gmin2 commented Nov 26, 2023

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

@tolgaOzen
Copy link
Member Author

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

@tolgaOzen tolgaOzen assigned Gmin2 and unassigned saifxd7 Nov 26, 2023
@tolgaOzen
Copy link
Member Author

Hello @Min2who, are there any updates?

@Gmin2
Copy link
Contributor

Gmin2 commented Nov 30, 2023

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 Gmin2 removed their assignment Dec 3, 2023
@Gmin2
Copy link
Contributor

Gmin2 commented Dec 3, 2023

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

@Rutik7066
Copy link

Rutik7066 commented Dec 3, 2023

/attempt #863

Options

@Rutik7066
Copy link

Hi @tolgaOzen could you please assign to me

@tolgaOzen
Copy link
Member Author

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

@Rutik7066
Copy link

Thanks @tolgaOzen

@Rutik7066
Copy link

Rutik7066 commented Dec 4, 2023

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

@ayewo
Copy link

ayewo commented Dec 5, 2023

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

Copy link

algora-pbc bot commented Dec 5, 2023

@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
Copy link
Member Author

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

@tolgaOzen
Copy link
Member Author

Hello @ayewo, is there any progress?

@mohanish2504
Copy link
Contributor

mohanish2504 commented Dec 12, 2023

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

Copy link

algora-pbc bot commented Dec 12, 2023

@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
Copy link

ayewo commented Dec 13, 2023

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
Copy link

Hi @ayewo are you working on this?

@mohanish2504
Copy link
Contributor

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

@ayewo
Copy link

ayewo commented Dec 18, 2023

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

@Rutik7066
Copy link

Hey @ayewo can we collaborate?

@tolgaOzen
Copy link
Member Author

Hello @ayewo, is there any progress?

@ayewo
Copy link

ayewo commented Dec 29, 2023

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

@tolgaOzen
Copy link
Member Author

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

@tolgaOzen tolgaOzen assigned mohanish2504 and unassigned ayewo Dec 29, 2023
@mohanish2504
Copy link
Contributor

Thanks @tolgaOzen.

Copy link

algora-pbc bot commented Dec 30, 2023

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

Copy link

algora-pbc bot commented Jan 2, 2024

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area/database Issues related with database configurations 💎 Bounty go Pull requests that update Go code 💰 Rewarded
Projects
Status: Q4 2022 – Oct-Dec
8 participants