Ask HN: How do I profile my Postgres database?

[vsroy]

I am running a t3.micro instance -- and doing about 8 queries / second. Not many, but it seems like queries are consistently taking 1-10 seconds to respond, and as a result, the site is going down.

I'm having a hard time telling what the problem is. Are some of my queries really slow and inefficiently designed? Is is it just that my database is too small and I need to upgrade it?

Is there something I can attach to my database that can easily tell me what the bottleneck is? (Disk, CPU, memory)

[andrelaszlo]

The first thing I'd do is to turn on the slow query log (or lower the threshold). Once you have a few logged, run an explain/analyze on them and see if it's an issue with the queries.

On the instance itself, you can check memory usage (free) and cpu usage (top).

Most of the popular graphical clients for PG (I've used pgadmin a little) have some way to show current resource usage as well. PG keeps quite a lot of statistics by default, the manual is quite good: https://www.postgresql.org/docs/current/performance-tips.htm...

Check the EC2 dashboard for your instance as well, to see if any other resource usage is high.

[vsroy]

Thanks for the help. After upgrading my instance, all the query times dropped dramatically. The thing is, I did not even upgrade the instance that much (micro -> medium).

I'm guessing some important bottleneck was being hit, but I have no idea what it was (maybe 1GB RAM was causing spillover to disk in a query or something? like that?).

It seems important to understand what bottleneck I hit -- but frankly, I have no idea.