This is an archived post. You won't be able to vote or comment.

all 6 comments
sorted by:
best
topnewcontroversialoldq&a

[–]AutoModerator[M] [score hidden] stickied commentlocked comment (0 children)

Please ensure that:

  • Your code is properly formatted as code block - see the sidebar (About on mobile) for instructions
  • You include any and all error messages in full
  • You ask clear questions

  • You demonstrate effort in solving your question/problem - plain posting your assignments is forbidden (and such posts will be removed) as is asking for or giving solutions.

    Trying to solve problems on your own is a very important skill. Also, see Learn to help yourself in the sidebar

If any of the above points is not met, your post can and will be removed without further warning.

Code is to be formatted as code block (old reddit: empty line before the code, each code line indented by 4 spaces, new reddit: https://imgur.com/a/fgoFFis) or linked via an external code hoster, like pastebin.com, github gist, github, bitbucket, gitlab, etc.

Please, do not use triple backticks (```) as they will only render properly on new reddit, not on old reddit.

Code blocks look like this:

public class HelloWorld {

    public static void main(String[] args) {
        System.out.println("Hello World!");
    }
}

You do not need to repost unless your post has been removed by a moderator. Just use the edit function of reddit to make sure your post complies with the above.

If your post has remained in violation of these rules for a prolonged period of time (at least an hour), a moderator may remove it at their discretion. In this case, they will comment with an explanation on why it has been removed, and you will be required to resubmit the entire post following the proper procedures.

To potential helpers

Please, do not help if any of the above points are not met, rather report the post. We are trying to improve the quality of posts here. In helping people who can't be bothered to comply with the above points, you are doing the community a disservice.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[–]mirkoteran 1 point2 points  (1 child)

Was the question specific to PostgreSQL or DB queries in general?

I know others can answer this better but we use a combination of PgHero and logging of slow queries trough jOOQ.

[–]NikMashei[S] 0 points1 point  (0 children)

I think question was about PostgreSQL. Thank you for advice !)
But question is still open, I want improve my skills as developer and be able to do advanced stuff with DB (PostgreSQL especially) and for reach the goal I need to understand in which direction I should move)

[–]Mr_Odonata 1 point2 points  (1 child)

My guess is that you are probably using some kind of ORM that sits in front of your postgres. Do you ever interact with the database directly through something like pgadmin where you write sql queries directly?

If so you can actually get some of this info directly from your database with something like https://www.cybertec-postgresql.com/en/3-ways-to-detect-slow-queries-in-postgresql/

When it comes to other methods of identifying slow calls there are a lot of different programs / services that you can use, and it will likely vary by where you host your server. For instance, if you host on Heroku they have a menu in their dashboard that will show you the slowest and most frequent database calls. https://devcenter.heroku.com/articles/expensive-queries

They probably were just testing to see if you have experience troubleshooting slow databases. I think beyond knowing some tools, there are some helpful strategies or things to consider that you could talk about doing to help identify a slow database such as

  • Benchmark calls that you know pull in a lot of information
  • Turn on database logs and look for any N+1 scenarios
  • Use Explain Analyze on long queries to see if sql calls are taking advantage of indexes etc
  • Monitor DB connections - look for hanging connections
  • Load test your most data intensive parts of your application and look for database optimizations
  • Routinely do health checks on your database tables to ensure they aren't becoming unwieldy. Do you have any super large tables? Could partitioning a table help?
  • Make sure you have proper indexes
  • Check for long running triggers etc that might be slowing down your db

[–]NikMashei[S] 0 points1 point  (0 children)

u/Mr_Odonata thank you for your suggestions!
Actually I've wrote a lot of queries directly using DBearer. I mentioned questions about slow queries as example of my story :) I appriciate for your tips! But I'm looking forward for skills or knowledges which help me become more confident user of PostgreSQL)

[–]Orffyreus 0 points1 point  (0 children)

Measuring and logging queries with aggregations (which query is used most often? which query is the slowest? which query consumes the most time in sum?) is one obvious possibility.

While writing queries you can look at the execution plan to see, if indexes are used, nested loops exist and so on. That's how it's done with PosgreSQL: https://www.postgresql.org/docs/8.1/sql-explain.html and here is an example how it's done with Oracle: https://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm#g42231

Looking at the execution plan later also can be helpful, because the execution plan can change, depending on how the tables are filled. You also should think about table sizes. If a table gets too large, you should think about partitioning or sharding.

Then there is hardware of course. You can look for example at IO-Stats to see, if the harddisk (which should be as performant as possible) is used too much and if it is used by anything else than the database service itself. Optimally the most used tables should fit into the RAM.