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

Query analysis and index optimization for auditlog database #106

Open
4 tasks
acn-dgopa opened this issue Apr 9, 2024 · 0 comments
Open
4 tasks

Query analysis and index optimization for auditlog database #106

acn-dgopa opened this issue Apr 9, 2024 · 0 comments
Assignees

Comments

@acn-dgopa
Copy link
Member

acn-dgopa commented Apr 9, 2024

Case

Querying data from the auditlog database is slow. Currently we have partitioned database per year. But there is enormous data that is logged.

Query analysis

Start by monitoring the database for slow-running queries. PostgresSql provides tools like pg_stat_statements and pgBadger for query analysis.

Index Optimization

Create indexes on columns used in WHERE clauses, join conditions, and order by clauses to improve query performance. Consider using composite indexes for queries involving multiple columns.

Query Rewriting

Rewrite queries to make them more efficient. this could involve limiting the data based on user, timeperiod.

Query Tuning

Adjust the query parameters such as work_mem, effective_cache_size, and random_page_cost based on the characteristics of the workload and hardware configuration.

Tasks

@acn-dgopa acn-dgopa added this to the Event Log - Part 2 milestone Apr 9, 2024
@acn-dgopa acn-dgopa self-assigned this Apr 12, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
Status: 📈 Backlog
Development

No branches or pull requests

1 participant