HNHacker News
TopNewBestAskShowJobs

samokhvalov

828 karma · joined October 27, 2019

nik@postgres.ai

https://Postgres.AI

https://twitter.com/samokhvalov

https://gitlab.com/NikolayS

https://github.com/NikolayS

submissionscomments
samokhvalov··on Harmful Workload Generator for PostgreSQL
Well, this tool is extremely useful when you want, for example, to test your monitoring system or other observability tools to understand how "bad" cases will be really visible in them.

Of course, it should not be used in production.

Also, it's a good idea to use it for training of DBA / DBRE / SRE engineers to practice various cases and improve their diagnostics skills.

Regular benchmarks could have something like this embedded, I think. Postgres standard tool, pgbench, doesn't have it now. But I doubt "harmful workload" would be really useful when we test systems to see their max throughput / lowest latencies for particular workload.

samokhvalov··on JOINs the hard way: comparing MongoDB and Postgres
One size fits [almost] all.
samokhvalov··on Show HN: Visual SQL
Looks good!

Worth noting, the idea of "visual SQL" appeared long, long ago, it was called "Query by Example" https://en.wikipedia.org/wiki/Query_by_Example.

Congratulation on the launch and thank you for having PostgreSQL support. (Side-question: any reasons why you have mirrored Postgres logo?)

samokhvalov··on Joe, a Postgres query optimization bot
Cool!

Please join our Community Slack 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.

samokhvalov··on Joe, a Postgres query optimization bot
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 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.

samokhvalov··on Joe, a Postgres query optimization bot
Not yet.

Is there any particular interest? If so, I'd love to explore how it could work, let's connect.

samokhvalov··on Joe, a Postgres query optimization bot
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?

samokhvalov··on Joe, a Postgres query optimization bot
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.

samokhvalov··on Joe, a Postgres query optimization bot
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 (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?

samokhvalov··on Database Lab – Full-size staging databases as a service
Sure. Thin cloning is a way to obtain a clone of data that looks fully independent (you can modify, add, delete data), but physically multiple clones share physically the same data -- except those blocks that were changed. This is implemented using copy-on-write -- see, for example, https://en.wikipedia.org/wiki/Copy-on-write#In_computer_stor...
samokhvalov··on Database Lab – Full-size staging databases as a service
Thank you! I'll check out aquameta, looks interesting.
samokhvalov··on Database Lab – Full-size staging databases as a service
Well, not really. Two key differences:

1) dump/restore approach is one of "thick cloning" methods. It will take a really significant amount of time. Roughly, thick cloning speed for modern hardware and networks is somewhat ~1 TiB / hour just for transferring over network and writing to disk. Additionally, in the case of dump/restore, the timing will depend on the number of indexes that need to be built, materialized views to be refreshed, etc.

2) Dump/restore is "logical copying", it means that physical structure is not preserved. Logically, you'll get the same data. Physically – a completely different database. Bloat is gone, indexes are "fresh and good", and so on.

With Database Lab, unless you're on RDS, initial copying is preferably done using some "physical" methods (pg_basebackup, or just rsync, or – the best option – restoration from WAL-G/pgBackRest/Barman/etc backups). The physical structure is preserved. You have an identical copy of the original database. Then you request "thin clones", it really takes a couple of seconds for a multi-terabyte database. And can do various kinds of performance troubleshooting and experiments before you deploy your changes to production. Request 5 thin clones and check your idea 5 times, each time adjusting it a little bit. This is what usually really needed in development.

samokhvalov··on Database Lab – Full-size staging databases as a service
There are various approaches here, and there are some FOSS tools that you can use.

Some links:

- https://blog.taadeem.net/english/2019/01/03/8_anonymization_... – description of methods, and a tool for Postgres, postgresql_anonymizer

- https://habr.com/en/company/yandex/blog/485096/ – not for Postgres, it's for ClickHouse (open-source DBMS for analytics) but covers the topic very well.

samokhvalov··on Database Lab – Full-size staging databases as a service
Hi! Postgres.ai founder here.

This is requested quite often – for example, if we copy the database from production, sometimes it's needed to remove all personal data not to break regulations.

It is possible in Database Lab, but it's not a very user-friendly feature yet. Briefly, the process is as follows.

The "sync" Postgres instance is configured to be a production replica (better using WAL shipping from the WAL archive). Then, periodically, a new snapshot is created, currently it's done using this Bash script: https://gitlab.com/postgres-ai/database-lab/-/blob/master/sc.... (We are going to make it a part of the database-lab server in the upcoming releases).

Here https://gitlab.com/postgres-ai/database-lab/-/blob/master/sc... you can place any data transformations, so the final snapshot that will be used for thin cloning has adjusted data sets. For example, all personal data is removed or obfuscated.

Of course, if you do this, you need to keep in mind that physically, you'll have a different database. It may affect some kinds of testing (for example, troubleshooting bloat issues or some cases of index performance degradation). There are various choices to be made here. If interested, we'll be happy to help, please join our community Slack which is mentioned in the docs and README.

samokhvalov··on Announcing RDS/Aurora Snapshot Export to S3
Great news. But unfortunately, it is not the original data snapshot as the title says, it's Apache Parquet format. So this is useful for analytical tasks only.

For operational tasks, still, the only option (besides RDS cloning) for Postgres is pg_dump or pg_transport, losing physical layout.

samokhvalov··on Postgres Health Check and SQL Performance Analysis
No, it was a single real-world database.

Just in case if it looks surprisingly that Postgres can deal with such numbers of objects, I suggest checking this out: https://www.pgcon.org/2013/schedule/attachments/283_Billion_...

samokhvalov··on Postgres Health Check and SQL Performance Analysis
One single database. Yes, quite unusual case of multi-tenant database. We needed to use checkup there a while ago so made some optimizations.

I agree with you, the statement is not 100% clear. We will double-check it (and maybe will simulate the case in CI), and will improve the statement. Thank you.

samokhvalov··on Postgres Health Check and SQL Performance Analysis
Hi, the founder of Postgres.ai here.

The footprint is very minimalistic. I have 15+ years of Postgres DBA experience, and what this tool does basically is what I usually do myself with performing Postgres health checks under heavy load. But in automated fashion :)

We very carefully choose approaches and queries that we run on production servers. And it is used under heavy loads (dozens of thousands of TPS) daily.

There are certain places that can be heavy. For example, if you have 1 million indexes (yes, it happens, sometimes), the SELECT query for bloat analysis will be slow. Actually, with default settings, the tool limits itself setting `statement_timeout = '30s'` (can be adjusted using CLI option `--statement-timeout`). So, in databases with a huge number of objects, you should expect that F004 and F005 reports will be missing.

← PreviousPage 4 of 4