6 ms·

Joe, a Postgres query optimization bot

174 points by samokhvalov 7y ago

deleted 7y ago
[deleted]
akartasov 7y ago
I've just tried the live demo in the Database Lab Community Slack. It looks amazing! Keep it up!
cwp 7y ago
So... this thing gives full read/write/ddl access to production data ...via Slack? That is a security nightmare. Hard pass.
- lgessler 7y ago
  > Every time when an engineer starts communicating with Joe, a new full-size copy of the database is provisioned. It looks like users get their own disposable copy. Nothing's run on the prod copy.
  - avianlyric 7y ago
    Doesn’t change the fact it’s gonna put production data into Slack. Say goodbye to Access Control, and hello to a GDPR/security nightmare.
- itake 7y ago
  Yeah, seriously. I wonder how they thought slack and chat interface would be a better than a CLI app...
  - dewey 7y ago
    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 https://github.com/getredash/redash for these cases though, it's also very user friendly.
    - SahAssar 7y ago
      It's for SQL query optimization, not OLAP or similar, so it seems to me like the target audience probably already knows about the psql cli.
    - pmontra 7y ago
      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.
  - stansler 7y ago
    Joe running in Slack is indeed a way to simplify and speed-up SQL optimization workflow for developers, as it takes seconds to get initial query execution plans and optimization recommendations. >... With a Slack interface, so you don't need to bother with connection, provisioning, credentials and decommissioning. It's worth noticing that Joe is a use case of Database Lab (https://gitlab.com/postgres-ai/database-lab https://gitlab.com/postgres-ai/database-lab) and it's still possible to use Database Lab features like thin-clones provisioning of production-sized databases and fast data state reset with Database Lab client CLI (https://postgres.ai/docs/database-lab/6_cli_reference https://postgres.ai/docs/database-lab/6_cli_reference) for purposes of SQL optimization without Slack integration. If a user has sufficient access to the data they can provision thin-clone with Database Lab client CLI and use psql to work with the clone. Also, we have plans to add support of recommendations and statistics in CLI and REST API to the support of various messaging platforms in the future.
- samokhvalov 7y ago
  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/ 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 (ANALYZE, BUFFERS) plans. It may be considered acceptable. Of course, there are ways to retrieve particular values with some tricks like "explain analyze ... limit (select value from...)"), but this will be noticeable. Massive data leaks are not possible, Joe filters the output, presenting only metadata -- the whole idea was to provide the data for backend engineers, many of those don't have access to production data. 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. At the same time, Joe does boost the development speed because it becomes easier to troubleshoot SQL performance and discuss it with colleagues, collecting reliable facts. What would you say if it would be: 1. A chatbot for on-prem Mattermost? 2. A specialized GUI, SaaS or standalone?
  - diminish 7y ago
    Joe sounds great. Theoretically you could reveal certain type information by writing specialized attack queries for checking certain information, for example: WHERE username='joe' AND is_admin IS true or WHERE salary > 100000
  - ysavir 7y ago
    > 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 your product makes it harder for me to compromise data, not easier. > At the same time, Joe does boost the development speed because it becomes easier to troubleshoot SQL performance and discuss it with colleagues, collecting reliable facts. Great! Now make that troubleshooting accessible via an authenticated URL and I'm in. Especially because I may want to revisit a particular explain sometime down the line, and simple anchor-tag based list of bookmarkable past queries is easier to navigate than having to use Slack searches. 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. If there was then an option to add a Slack hook that notified people, that would be good. But it shouldn't be central to the product, and the information should strictly travel from the self-hosted service to slack via hooks, exposing only the URLs/titles of result pages. No need to expose the queries or DB structure. I think the current Slack implementation is a cool proof of concept, but this isn't a product I would seriously consider until it is able to perform its tasks in isolation. 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.
- SahAssar 7y ago
  It's for SQL query optimization, so you probably wouldn't run it on prod.
  - aspyct 7y ago
    Well you sure need a representative dataset to optimize your queries. Often, that means a replica of the production server, with real PII data in there.
    - SahAssar 7y ago
      I usually try to use mocked data as far as possible, but I guess the last validating step usually involves real data. Perhaps this can be used to optimize up to a certain point and then you can validate against production data as a last step.
- hamandcheese 7y ago
  A slack interface is probably a more auditable environment out of the box. Strong auditability is arguably more important than hard security guarantees.
  - wolco 7y ago
    Until you hit a certain # of messages. File works better.
kalimatas 7y ago
Just learned about Database Lab. Amazing! Thanks for sharing!
ahachete 7y ago
I'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 provisions a database clone using ZFS snapshot (I believe LVM support was added recently) and gives you a full copy of the database for you, with real data. You can test the queries, explain plans, create indexes to test if you need. And when you are done, it goes away. With a Slack interface, so you don't need to bother with connection, provisioning, credentials and decommissioning. Give it a try, it is a very good tool!
- briffle 7y ago
  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 running on this same server.
  - samokhvalov 7y ago
    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/scripts/create_zfs_snapshot.sh#L173 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.
  - deleted 7y ago
    [deleted]
alexandernst 7y ago
But why a Slack app instead of a PG module of some sort...?
mpol 7y ago
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.
- IAmEveryone 7y ago
  ...and lo, thereby the world ended.
tarun_anand 7y ago
Any plans to support Citus data?
- samokhvalov 7y ago
  Not yet. Is there any particular interest? If so, I'd love to explore how it could work, let's connect.
fb03 7y ago
How hard would it be to implement the Discord Bot protocol? I'd be willing to help on this front, and it'd be a nice way for me to get my hands wet on Go too. Thanks in Advance.
- samokhvalov 7y ago
  Cool! Please join our Community Slack https://database-lab-team-slack-invite.herokuapp.com/ https://database-lab-team-slack-invite.herokuapp.com/ and ping me there -- let's discuss it. We're refactoring the main code of Joe right now, to improve its extensibility. The main purpose is to simplify the process of adding new protocols.
m2mdas2 7y ago
Semi related, SQL Server have query store feature which records query insights on production db and in SSMS you can see the query statistics on various metrics like resource uses, execution counts etc along with query plan applied. I haven't dug deep into SQL server yet, but query store suggestions are good enough for now.
- samokhvalov 7y ago
  We call it macro-analysis -- the analysis of SQL workload as a whole, with call counts, timing, memory-related metrics, etc. Currently, Joe helps with micro analysis: you take just one SQL query, don't think about others and solve the task of optimizing this particular query. For "macro", Postgres ecosystem has pg_stat_statements (and our tool postgres-checkup https://gitlab.com/postgres-ai/postgres-checkup https://gitlab.com/postgres-ai/postgres-checkup for automated health checks builds reports on top of it), pgBadger to analyze logs, auto_explain to capture execution plans. Building the "bridge" between macro- and macro-analyses is a very interesting topic. I'm going to give a talk about this particular topic at the next PGCon.

174 points by samokhvalov 7y ago

deleted 7y ago

[deleted]

akartasov 7y ago

I've just tried the live demo in the Database Lab Community Slack. It looks amazing! Keep it up!

cwp 7y ago

So... this thing gives full read/write/ddl access to production data ...via Slack? That is a security nightmare. Hard pass.

lgessler 7y ago
> Every time when an engineer starts communicating with Joe, a new full-size copy of the database is provisioned. It looks like users get their own disposable copy. Nothing's run on the prod copy.
- avianlyric 7y ago
  Doesn’t change the fact it’s gonna put production data into Slack. Say goodbye to Access Control, and hello to a GDPR/security nightmare.
itake 7y ago
Yeah, seriously. I wonder how they thought slack and chat interface would be a better than a CLI app...
- dewey 7y ago
  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 https://github.com/getredash/redash for these cases though, it's also very user friendly.
  - SahAssar 7y ago
    It's for SQL query optimization, not OLAP or similar, so it seems to me like the target audience probably already knows about the psql cli.
  - pmontra 7y ago
    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.
- stansler 7y ago
  Joe running in Slack is indeed a way to simplify and speed-up SQL optimization workflow for developers, as it takes seconds to get initial query execution plans and optimization recommendations. >... With a Slack interface, so you don't need to bother with connection, provisioning, credentials and decommissioning. It's worth noticing that Joe is a use case of Database Lab (https://gitlab.com/postgres-ai/database-lab https://gitlab.com/postgres-ai/database-lab) and it's still possible to use Database Lab features like thin-clones provisioning of production-sized databases and fast data state reset with Database Lab client CLI (https://postgres.ai/docs/database-lab/6_cli_reference https://postgres.ai/docs/database-lab/6_cli_reference) for purposes of SQL optimization without Slack integration. If a user has sufficient access to the data they can provision thin-clone with Database Lab client CLI and use psql to work with the clone. Also, we have plans to add support of recommendations and statistics in CLI and REST API to the support of various messaging platforms in the future.
samokhvalov 7y ago
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/ 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 (ANALYZE, BUFFERS) plans. It may be considered acceptable. Of course, there are ways to retrieve particular values with some tricks like "explain analyze ... limit (select value from...)"), but this will be noticeable. Massive data leaks are not possible, Joe filters the output, presenting only metadata -- the whole idea was to provide the data for backend engineers, many of those don't have access to production data. 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. At the same time, Joe does boost the development speed because it becomes easier to troubleshoot SQL performance and discuss it with colleagues, collecting reliable facts. What would you say if it would be: 1. A chatbot for on-prem Mattermost? 2. A specialized GUI, SaaS or standalone?
- diminish 7y ago
  Joe sounds great. Theoretically you could reveal certain type information by writing specialized attack queries for checking certain information, for example: WHERE username='joe' AND is_admin IS true or WHERE salary > 100000
- ysavir 7y ago
  > 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 your product makes it harder for me to compromise data, not easier. > At the same time, Joe does boost the development speed because it becomes easier to troubleshoot SQL performance and discuss it with colleagues, collecting reliable facts. Great! Now make that troubleshooting accessible via an authenticated URL and I'm in. Especially because I may want to revisit a particular explain sometime down the line, and simple anchor-tag based list of bookmarkable past queries is easier to navigate than having to use Slack searches. 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. If there was then an option to add a Slack hook that notified people, that would be good. But it shouldn't be central to the product, and the information should strictly travel from the self-hosted service to slack via hooks, exposing only the URLs/titles of result pages. No need to expose the queries or DB structure. I think the current Slack implementation is a cool proof of concept, but this isn't a product I would seriously consider until it is able to perform its tasks in isolation. 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.
SahAssar 7y ago
It's for SQL query optimization, so you probably wouldn't run it on prod.
- aspyct 7y ago
  Well you sure need a representative dataset to optimize your queries. Often, that means a replica of the production server, with real PII data in there.
  - SahAssar 7y ago
    I usually try to use mocked data as far as possible, but I guess the last validating step usually involves real data. Perhaps this can be used to optimize up to a certain point and then you can validate against production data as a last step.
hamandcheese 7y ago
A slack interface is probably a more auditable environment out of the box. Strong auditability is arguably more important than hard security guarantees.
- wolco 7y ago
  Until you hit a certain # of messages. File works better.

kalimatas 7y ago

Just learned about Database Lab. Amazing! Thanks for sharing!

ahachete 7y ago

I'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 provisions a database clone using ZFS snapshot (I believe LVM support was added recently) and gives you a full copy of the database for you, with real data. You can test the queries, explain plans, create indexes to test if you need. And when you are done, it goes away. With a Slack interface, so you don't need to bother with connection, provisioning, credentials and decommissioning. Give it a try, it is a very good tool!

briffle 7y ago
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 running on this same server.
- samokhvalov 7y ago
  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/scripts/create_zfs_snapshot.sh#L173 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.
- deleted 7y ago
  [deleted]

alexandernst 7y ago

But why a Slack app instead of a PG module of some sort...?

mpol 7y ago

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.

IAmEveryone 7y ago
...and lo, thereby the world ended.

tarun_anand 7y ago

Any plans to support Citus data?

samokhvalov 7y ago
Not yet. Is there any particular interest? If so, I'd love to explore how it could work, let's connect.

fb03 7y ago

How hard would it be to implement the Discord Bot protocol? I'd be willing to help on this front, and it'd be a nice way for me to get my hands wet on Go too. Thanks in Advance.

samokhvalov 7y ago
Cool! Please join our Community Slack https://database-lab-team-slack-invite.herokuapp.com/ https://database-lab-team-slack-invite.herokuapp.com/ and ping me there -- let's discuss it. We're refactoring the main code of Joe right now, to improve its extensibility. The main purpose is to simplify the process of adding new protocols.

m2mdas2 7y ago

Semi related, SQL Server have query store feature which records query insights on production db and in SSMS you can see the query statistics on various metrics like resource uses, execution counts etc along with query plan applied. I haven't dug deep into SQL server yet, but query store suggestions are good enough for now.

samokhvalov 7y ago
We call it macro-analysis -- the analysis of SQL workload as a whole, with call counts, timing, memory-related metrics, etc. Currently, Joe helps with micro analysis: you take just one SQL query, don't think about others and solve the task of optimizing this particular query. For "macro", Postgres ecosystem has pg_stat_statements (and our tool postgres-checkup https://gitlab.com/postgres-ai/postgres-checkup https://gitlab.com/postgres-ai/postgres-checkup for automated health checks builds reports on top of it), pgBadger to analyze logs, auto_explain to capture execution plans. Building the "bridge" between macro- and macro-analyses is a very interesting topic. I'm going to give a talk about this particular topic at the next PGCon.