7 ms·
PgAssistant: OSS tool to help devs understand and optimize PG performance
- dewey 2y agoThe 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 https://github.com/ankane/pghero or some tools here: https://gitlab.com/postgres-ai https://gitlab.com/postgres-ai
- justinclift 2y agoI think it's probably just a large file, as I remember thinking the same thing but it was loaded when I returned to the browser tab with the readme later on.
- dewey 2y agoAh, you are right. I'm on a slow connection right now and it's a 22MB gif. (https://github.com/nexsol-technologies/pgassistant/tree/main/media https://github.com/nexsol-technologies/pgassistant/tree/main...)
- ai-christianson 2y ago> and it's a 22MB gif Whoa... that's a lot of data for a README! But demos are pretty important, so I guess it's worth it.
- dewey 2y agoA mp4 would be a much better choice instead of a gif and probably ~1.5MB.
- zimpenfish 2y agoffmpeg -y -i pgassistant.gif -c:v libx265 -q 55 -tag:v hvc1 -movflags '+faststart' -pix_fmt yuv420p pgassistant.mp4 That takes the GIF down to 1338973 bytes (1.3M) with (to my eyes) little loss of readability.
- ai-christianson 2y agoSubmit a PR?
- zimpenfish 2y agoI'm trying but it turns out that it's impossible to submit a useful PR with a video embed in `README.md` because the only way it works is by uploading the video through the web editor[0] and using a static user-images.githubusercontent.com URL. [0] Which puts it under your account, obvs., and is therefore not that helpful for a PR.
- ai-christianson 2y agoMaybe just open an issue and provide the ffmpeg command then
- zimpenfish 2y agoYeah, did that in the end.
- mjevans 2y ago__264516 Feb 12 11:44 pgassistant.gif 22782965 Feb 12 11:46 pgassistant.gif.raw.gif _2120322 Feb 12 11:55 pgassistant.gif.av1-20.mp4 __245780 Feb 12 11:56 pgassistant.gif.av1-55.mp4 wget -O pgassistant.gif.raw.gif 'https://github.com/nexsol-technologies/pgassistant/blob/main/media/pgassistant.gif?raw=true https://github.com/nexsol-technologies/pgassistant/blob/main...' ffmpeg -h encoder=libaom-av1 ffmpeg -i pgassistant.gif.raw.gif -c:v libaom-av1 -crf 20 -cpu-used 8 -movflags '+faststart' -pix_fmt yuv420p pgassistant.gif.av1-20.mp4 ffmpeg -i pgassistant.gif.raw.gif -c:v libaom-av1 -crf 55 -cpu-used 8 -movflags '+faststart' -pix_fmt yuv420p pgassistant.gif.av1-55.mp4 A place to start from at least, note the 264516 gif is what's currently on the landing page, with the wget command to grab the raw file.
- ai-christianson 2y agoGood point.
- cpachmann 2y ago[dead]
- ggregoire 2y ago> OpenAI helper: If you have an OpenAI account, the interface can query OpenAI to help you understand your query plan and optimize your SQL queries How good are LLMs at optimizing queries? Do they just give basic advices like "try adding an index there" or can they do more? I guess it would need to cross the results of the explain analyze with at least the DDLs of the table (including partitions, indexes, triggers, etc), and like the sizes of the tables and the rate of reads/writes of the tables in some cases, to be able to "understand" and "reason" about what's going on and offer meaningful advices (e.g. proposing design changes for the tables). I don't see how a generic LLM would be able to do that, but maybe another type of model trained for this task and with access to all the information it needs?
- ComputerGuru 2y agoThey’re non-deterministic and YMMV by design. No one can answer that question. It might save you a hundred million with some genius optimization it lucked into or, more likely, it’ll give you a seemingly equivalent query that actually returns subtly different results (easy to do with query optimizations when eg you rely on inherent/default sort order for joins with groups).
- VWWHFSfQ 2y agoI've used them off and on for basic stuff like "tell me what this query does". They're usually pretty good at that. Sometimes it will make suggestions that are meaningful improvements, most of the time not. Unless you specifically ask about something like "would this be better done with a left join lateral/correlated subquery?" But you kinda have to already know what you're going for and then it can be helpful.
- akx 2y agoThey can be useful. I just recently used Claude to make a query 10x faster, though I did have to back-and-forth a bit, so you do still need to know what you're doing.
- parthdesai 2y agoClaude has been really good at all things Postgres related
- znpy 2y agohonest question: does anybody know a good book/tutorial/source/whatever on becoming a postgresql DBA ? most of the material i see is written for people that want to write applications that work with postgresql, not on how to proficiently manage postgresql itself.
- oa335 2y ago2nd Quadrant (bought by Enterprise DB several years ago) had a bunch of excellent YouTube tutorials on common tasks. Some of them may still be available on YouTube. EDB has a fairly comprehensive set of self-paced trainings for sale. I went through them and thought they were really good. Postgres documentation is excellent though, and though the docs are long, reading through it carefully should give you almost all the information you need for most database tasks. https://www.postgresql.org/docs/current/admin.html https://www.postgresql.org/docs/current/admin.html
- edoceo 2y agoThe PostgreSQL docs are great and then watch the presentations from their conferences - very cool stuff. Then practice, docker makes this easy.
- lacoolj 2y agoMost of what I've learned has come from PG docs and various blogs on "we found this crazy thing costing us 100x memory/processing/response time/etc" But there is a great book on the internals (very long, in depth, but not unreadable if you have a good foundation already) - https://edu.postgrespro.com/postgresql_internals-14_en.pdf https://edu.postgrespro.com/postgresql_internals-14_en.pdf They probably have newer versions, this is just what's in my bookmarks
- jpitz 2y agoGreg Smith wrote a book about postgres high performance that does go into the internals a bit and how to analyze performance problems. If you want to be a DBA, this will probably wind up needing to be in your back pocket at some point.
- andatki 2y ago
- deleted 2y ago[deleted]
- scosman 2y agoI’ve wanted to make this for years. I have a doc of saved pg_stat_statement and other queries saved.
- Olshansky 2y agoAdded this to the performance tuning section: https://github.com/Olshansk/postgres_for_everything?tab=readme-ov-file#performance-tuning https://github.com/Olshansk/postgres_for_everything?tab=read...
- OJFord 2y agoI'm surprised that both this list and the linked 'very comprehensive list' are missing pgbadger.
- cpursley 2y agoNeat! Just added it to my "Postgres Is Enough" gist: https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb#performance-tuning https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...
- bertrandhartwig 2y agoThanks !
- booleanbetrayal 2y agoThis reminds me of a less feature-rich version of PgAnalyze, but it's always nice to have alternative OSS options for this sort of optimization work.
- sgarland 2y agoYour screenshot for issues found show indices being created with CREATE INDEX. If someone runs that on a prod DB with a large table, they’re gonna have a bad time. You should have it suggest CREATE INDEX CONCURRENTLY. Also, if I’m being honest, it’s a little off-putting to see a tool giving advice on Postgres and not differentiating between those two.
- j45 2y agoAlways lots to learn, appreciate the insight. It’s interesting that Postgres doesn’t seem to default some of these settings since they are clearly advisable as most cases.
- sgarland 2y agoI’ve long held that Postgres is more powerful than MySQL in terms of flexibility and options, but you really ought to have thoroughly read and understood the manual to run it in prod. In comparison, MySQL does a fantastic job out of the box (modulo buffer pool sizing and sync_array_size, and tbf Postgres is equally bad at the former, and the latter doesn’t matter until larger scale), but is somewhat limited in what you can do with it (emphasis on somewhat – it’s still quite adequate for nearly anyone). That’s why I’m forever salty that all of the tech influencers and bloggers hyped up Postgres so much. Yes, it’s a great RDBMS, but the frontend devs eagerly jumping into it haven’t the slightest clue what they’re getting themselves into.
- bertrandhartwig 2y agoThank you for this advice but the tool is designed to help dev ... before to run on production.
- sgarland 2y agoAnd you don’t think someone would copy/paste what they did on dev or stage onto prod?
- 2y ago
- libsofhn 2y ago[flagged]
- justinclift 2y agoIn what way?
- cwillu 2y agoTroll account is trolling.