Joe the editor?
Can we just pick sensible and unique names? A simple search for 'Joe Software' would have shown that the name is taken.
Joe, a Postgres query optimization bot
21–30 of 40 posts
Re: Joe, a Postgres query optimization bot
#22I've used Joe quite a bit, and all I can say is that it's a great idea that solves a noticeable problem: troubleshoot your queries. It may not look like is a hard challenge: but the reality is that most troubleshoot them in production, which means that: a) you need access to production, when you shouldn't; b) you run queries in production, potentially affecting -sometimes noticeably- production workloads. Joe provisi…
If I'm reading this correctly, Joe uses docker running on the same host as your regular production db, and provisions a snapshot, and a new docker image to mount it. So yes, your production DB data is 'safe' but if your query uses lots of CPU, or lots of Disk IO, then its still going to fight the other images on the system for resources. IE, a Full Table scan of a 2TB table is going to affect the other docker systems…
Joe uses Docker and ZFS (or LVM+ext4, alternatively) on a separate machine, where data is transferred, normally, from archives (optionally, being transferred constantly, if there is a small Postgres "sync" server with "restore_command" configured), stored on ZFS, periodically snapshotted and prepared (and at this point, we can anonymize it, see https://gitlab.com/postgres-ai/database-lab/-/blob/master/sc...), and snapshotted again. Such snapshots of prepared PGDATA can be done periodically. And from there, we do thin provisioning, in a few seconds.
All this happens on a fully separated machine, not affecting production nodes by any means.
Re: Joe, a Postgres query optimization bot
#23I've used Joe quite a bit, and all I can say is that it's a great idea that solves a noticeable problem: troubleshoot your queries. It may not look like is a hard challenge: but the reality is that most troubleshoot them in production, which means that: a) you need access to production, when you shouldn't; b) you run queries in production, potentially affecting -sometimes noticeably- production workloads. Joe provisi…
If I'm reading this correctly, Joe uses docker running on the same host as your regular production db, and provisions a snapshot, and a new docker image to mount it. So yes, your production DB data is 'safe' but if your query uses lots of CPU, or lots of Disk IO, then its still going to fight the other images on the system for resources. IE, a Full Table scan of a 2TB table is going to affect the other docker systems…
[deleted]
Re: Joe, a Postgres query optimization bot
#24Earlier quoted context omitted.
Hi, Postgres.ai founder here. Production is not affected, the chatbot works with thin clones provided by Database Lab https://gitlab.com/postgres-ai/database-lab/ . In general, you are right. If, in your case, using the "raw" databases is unacceptable, it is better to anonymize data first (of course, the physical layout will change in this case). However, Joe doesn't reveal the data. Engineers see only EXPLAIN (ANALY…
> The question you're raising is broader: let's think how many times we send some personal data to our colleagues in Slack, how is it controlled now? are we okay with that? Emails, tokens, and so on. People's existing tendency to expose critical data via Slack shouldn't be reinforced, and coming from a product that handles sensitive data by design, it's disheartening to see this line of thinking here. I would hope yo…
Thank you for very detailed feedback!!
> People's existing tendency to expose critical data via Slack shouldn't be reinforced
I agree with you. However, depending on company's policy, raw SQL, even with some concrete values, and EXPLAIN ANALYZE plans may be considered as acceptable for using them in Slack.
If it is considered harmful, we have a way to inject anonymization step into the snapshot preparation tool -- see https://gitlab.com/postgres-ai/database-lab/-/blob/master/sc... (we're thinking how to improve usability here, not losing flexibility). For example, you can run PostgreSQL Anonymizer (https://gitlab.com/dalibo/postgresql_anonymizer). Of course, the physical layout will change, so troubleshooting of, say, VACUUM commands would be affected. But rows cardinality, normally, won't change, so for large enough tables, it totally makes sense do go this route, for SQL troubleshooting&optimization tasks.
> Great! Now make that troubleshooting accessible via an authenticated URL and I'm in.
We are thinking about the GUI version actively, so stay tuned.
And on CLI version as well, but CLI will work in psql (which I'm personally a big fan of, spent 15 years there or so). This means that all the data will be present.
In the case of GUI, of course, we are able to work on meta-level, similarly to the Slack version.
> If I were to use this tool, I would envision the ideal version of it being self-hosted and able to function independent from any other service.
I hear you well, and it generally aligns with our team's vision. Thank you again for awesome feedback!
> As for "improv[ing] the level of collaboration of developers and DBAs", great that you're thinking about that, but that's my problem to solve, not Joe's. And depending on my team's existing solutions, this may even rule out Joe simply due to our current tool set.
Could you provide some examples of such solutions?
Re: Joe, a Postgres query optimization bot
#25Joe the editor? Can we just pick sensible and unique names? A simple search for 'Joe Software' would have shown that the name is taken.
...and lo, thereby the world ended.
Re: Joe, a Postgres query optimization bot
#26Earlier quoted context omitted.
Yeah, seriously. I wonder how they thought slack and chat interface would be a better than a CLI app...
If you are using a PG client already you are probably not the target audience. It's easier to tell someone to copy paste a query into Slack to get their weekly insights than teaching them how to set up pgcli or some other gui app. You'd also have to create tightly scoped permissions for everyone who only needs it from time to time. I'd probably set up something like https://github.com/getredash/redash for these cases…
I do use command line clients, because I feel they are more available and convenient than GUI tools, but I'm not a PostgreSQL optimization wizard so I could be in the target audience. My customers probably are.
Re: Joe, a Postgres query optimization bot
#27I've used Joe quite a bit, and all I can say is that it's a great idea that solves a noticeable problem: troubleshoot your queries. It may not look like is a hard challenge: but the reality is that most troubleshoot them in production, which means that: a) you need access to production, when you shouldn't; b) you run queries in production, potentially affecting -sometimes noticeably- production workloads. Joe provisi…
> but the reality is that most troubleshoot them in production
I find this hard to believe. Maybe on the very rare occasion where the problem is tied to production and irreproducible elsewhere, but my experience has been that most troubleshooting happens on development, testing, staging, etc servers.
> and gives you a full copy of the database for you, with real data
Other than for small toy databases, I don't see how this would work for large databases. Not to mention the huge security issue.
You can test, optimize, etc queries on non-production servers. If the problem is production specific, it most likely is more server specific and not query optimization related.
Re: Joe, a Postgres query optimization bot
#28Any plans to support Citus data?
Re: Joe, a Postgres query optimization bot
#29I've used Joe quite a bit, and all I can say is that it's a great idea that solves a noticeable problem: troubleshoot your queries. It may not look like is a hard challenge: but the reality is that most troubleshoot them in production, which means that: a) you need access to production, when you shouldn't; b) you run queries in production, potentially affecting -sometimes noticeably- production workloads. Joe provisi…
> but the reality is that most troubleshoot them in production I find this hard to believe. Maybe on the very rare occasion where the problem is tied to production and irreproducible elsewhere, but my experience has been that most troubleshooting happens on development, testing, staging, etc servers. > and gives you a full copy of the database for you, with real data Other than for small toy databases, I don't see ho…
Query plans are dependent upon actual data itself. Plan performance issues can be difficult to replicate in test envs.
AFAICT, this product relies on LVM snapshots/copy on write : so is ideal diagnosing, trying out ideas on large databases.
Re: Joe, a Postgres query optimization bot
#30Earlier quoted context omitted.
> The question you're raising is broader: let's think how many times we send some personal data to our colleagues in Slack, how is it controlled now? are we okay with that? Emails, tokens, and so on. People's existing tendency to expose critical data via Slack shouldn't be reinforced, and coming from a product that handles sensitive data by design, it's disheartening to see this line of thinking here. I would hope yo…
Thank you for very detailed feedback!! > People's existing tendency to expose critical data via Slack shouldn't be reinforced I agree with you. However, depending on company's policy, raw SQL, even with some concrete values, and EXPLAIN ANALYZE plans may be considered as acceptable for using them in Slack. If it is considered harmful, we have a way to inject anonymization step into the snapshot preparation tool -- se…
> Could you provide some examples of such solutions?
Well, if we're already doing our collaborating via Discord or Microsoft Teams or whatever then a Slack-based tool is at a significant disadvantage.