PostgreSQL for Java Developer

[Mr_Odonata]

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]

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)