[HN Gopher] PgAssistant: OSS tool to help devs understand and op...
___________________________________________________________________
PgAssistant: OSS tool to help devs understand and optimize PG
performance
Author : justinclift
Score : 155 points
Date : 2025-02-12 15:01 UTC (7 hours ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| dewey wrote:
| 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
| justinclift wrote:
| I 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 wrote:
| Ah, 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...)
| ai-christianson wrote:
| > 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 wrote:
| A mp4 would be a much better choice instead of a gif and
| probably ~1.5MB.
| zimpenfish wrote:
| ffmpeg -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 wrote:
| Submit a PR?
| zimpenfish wrote:
| I'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 wrote:
| Maybe just open an issue and provide the ffmpeg command
| then
| zimpenfish wrote:
| Yeah, did that in the end.
| mjevans wrote:
| __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...'
|
| 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.
| kirici wrote:
| Is there any benefit in using libaom-av1 over libsvtav1
| nowadays?
| ai-christianson wrote:
| Good point.
| ggregoire wrote:
| > 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 wrote:
| They'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 wrote:
| I'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 wrote:
| They 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 wrote:
| Claude has been really good at all things Postgres related
| ellisv wrote:
| In my experience, LLMs are a lot better than my backend
| engineers who don't even try but not that much better than
| someone who's skimmed the docs.
|
| Typically they suggest 1) adding indexes and 2) refactoring the
| query. If you only provide the query then the model isn't aware
| of what indexes already exist. LLMs make assumptions about your
| data model that often don't hold (i.e. 3NF). Sometimes you have
| to give the LLM the latest docs because it's unaware of
| performance improvements and features for newer versions.
|
| In my view, it's better to look at the query plan and think
| yourself about how to improve the query but I also recognize
| that most people writing queries aren't going to do that.
|
| There are other tools (RIP OtterTune) that can tune database
| parameters. Not something I really see any generative model
| helping with.
| joshstrange wrote:
| They won't understand your schema and make the wrong
| assumptions _but_ as long as you are checking the results and
| can understand the final query they can be very helpful.
|
| Obviously there are tricks to let them better understand your
| schema but even using all of those it's going to make the wrong
| assumptions about some columns and how they are used.
| victorbjorklund wrote:
| LLM:s are really really good at creating queries. Shaving of
| the last ms from a complex query? yea, im sure an experienced
| query optimizer expert might beat it.
|
| But you get very far from letting the LLM run a few queries to
| gather info about the database and its use.
| znpy wrote:
| honest 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 wrote:
| 2nd 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
| edoceo wrote:
| The PostgreSQL docs are great and then watch the presentations
| from their conferences - very cool stuff.
|
| Then practice, docker makes this easy.
| lacoolj wrote:
| Most 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
|
| They probably have newer versions, this is just what's in my
| bookmarks
| jpitz wrote:
| Greg 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.
| scosman wrote:
| I've wanted to make this for years. I have a doc of saved
| pg_stat_statement and other queries saved.
| Olshansky wrote:
| Added this to the performance tuning section:
| https://github.com/Olshansk/postgres_for_everything?tab=read...
| cpursley wrote:
| Neat! Just added it to my "Postgres Is Enough" gist:
|
| https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...
| booleanbetrayal wrote:
| This 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.
___________________________________________________________________
(page generated 2025-02-12 23:00 UTC)