pghero

For 4) - consider PGHero[1] and PGTuner[2] instead of a full-time DBA. We use both in production and they work very well to help track down performance issues with Postgres. [1] https://github.com/ankane/pghero [2] https://pgtune.leopard.in.ua/ Edit: For the record, I have worked at a few small companies as the "SysAdmin" guy who did the whole compliment of servers, OS, storage, networking, VMs, DB, perf tuning, etc.

The screenshot section in the README seems to be empty. Would've been interesting to see that. There's many tools that do similar things like https://github.com/ankane/pghero or some tools here: https://gitlab.com/postgres-ai

PgHero looks great (props for linking to related projects!), though somehow not discussed here much previously. https://github.com/ankane/pghero https://news.ycombinator.com/item?id=41299148#41300220 (mentioned among the author's other helpful tools and Ruby gems) > helpful to identify slow queries in production, remove duplicate indexes, see missing indexes, keep an eye on table size, etc -- I haven't recently put in the effort to find a copy of SQL Sentry from back when the full-featured edition was briefly free but even the "always free" version was helpful working with MSSQL query plans. https://www.solarwinds.com/free-tools/plan-explorer (NOTE: Not sure how free-but-pushy it is these days, but years ago it wasn't bad.)

PgHero is also from him I believe. Very helpful to identify slow queries in production, remove duplicate indexes, see missing indexes, keep an eye on table size, etc. https://github.com/ankane/pghero

For our production PGSQL databases, we use a combination of PGTuner[0] to help estimate RAM requirements and PGHero[1] to get a live view of the running DB. Furthermore, we use ZFS with the built-in compression to save disk space. Together, these three utilities help keep our DBs running very well. [0] https://pgtune.leopard.in.ua [1] https://github.com/ankane/pghero

I am using https://github.com/ankane/pghero/ and this is one of its features with GUI

I'm a huge supporter of PGhero (https://github.com/ankane/pghero) for this reason. Sometimes a single index is all that's required, but if DB design isn't your forte it's hard to know where to put it.

If you are using rails, pghero is a great gem to get better visibility on your Postgres performance https://github.com/ankane/pghero

Also worth checking out for postgres users: https://github.com/ankane/pghero

Related. https://github.com/ankane/pghero

One thing that hasn't been mentioned yet, that I found when I started using Python and SQL together, is the importance of well-considered indices. ORM's aren't always too clever when they generate an index, and often (for repeated queries) you can dramatically cut down the processing time by thoughtfully generating an index. If I know I'm going to be asking the database a certain question a bunch of times I'll even generate a temporary index to speed up my analysis, and then delete it when I'm done. No sense running a SQL query for an hour if I can cut it down to 5 minutes with an index on the target column. EDIT: I know this only applies to PostgreSQL, and there are numerous alternatives, but a big shout-out to pghero which helped me identify a bunch of duplicate and missing indexes in one of my databases, saving both time and hard dri

I administer a medium-sized postgres db, around (now) 110GB of data, and recently had the opportunity to revisit the old .conf file when I redeployed that database to a different server. What I quickly realized was that years of tweaks I had implemented as I learned postgres from various (now outdated) online sources were making it difficult to optimize my new instance. I decided to start again from scratch. I discovered a handy little utility called pghero and set up it's internal monitoring: https://github.com/ankane/pghero I backed up my old .conf replaced it with a default and blew away all my old indices, and fired up the database. Of course, nothing worked well. But step by step, working with postgres' internal tools and pghero, I took a brand new look at every previous assumption and was much more strategic about where and when