Hacker News Reader

Comment by dntbnmpls | Hacker News Reader

> 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.

View on HN

> 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.

Let me rephrase: many may troubleshoot in dev/staging, but that's not the right thing to do. Query execution plans can change completely based on data volume and even with the data itself. Databases use detailed statistics about the data to influence the planner's decision.

Hence, tuning a query on an environment without production data can lead to query optimizations that are either irrelevant or totally wrong in production. I have seen this countless times in my professional experience.

> Other than for small toy databases, I don't see how this would work for large databases.

Since it uses cloning at the volume/fs layer, the size of the database is irrelevant --only the rate of changes matter.

> You can test, optimize, etc queries on non-production servers.

No, you cannot, as I mentioned above. It's a waste of effort and can only lead to the false illusion that your query is good --and then beat you in production.

> Let me rephrase: many may troubleshoot in dev/staging, but that's not the right thing to do.

Maybe we have a different definition of "troubleshoot". Also, dev and/or staging should have similar data/specs/etc to production.

> Query execution plans can change completely based on data volume and even with the data itself.

Yes. That's why you could have your production server generate/log actual query plans for queries that are causing you problems. You could even set performance (cpu,io,bandwidth,etc) thresholds for your server to log. Of course that's in addition to the profile/monitoring data you have on the server.

> Hence, tuning a query on an environment without production data can lead to query optimizations that are either irrelevant or totally wrong in production. I have seen this countless times in my professional experience.

Well then your issue is that your dev/staging setup is poor. How or what do you even develop, test and troubleshoot? Might as well develop, test and troubleshoot straight on production.

> Since it uses cloning at the volume/fs layer, the size of the database is irrelevant --only the rate of changes matter.

Size still does matters. And as I mentioned, it still has a problem with security.

> No, you cannot, as I mentioned above. It's a waste of effort and can only lead to the false illusion that your query is good --and then beat you in production.

"waste of effort"? Developing, testing, troubleshooting, etc should be done off production. It's only on the rare cases that you should troubleshoot on production. This is best practices and basic security.

Unless you are working on internal office setup which isn't facing the outside world and where security and uptime doesn't matter. Your comment reminds me of people saying using "admin/root" account for everything is fine because everything else is "waste of effort". Do you even have a dev/staging setup? What do you use that for?

Everyone's professional experience is different, but I've never heard anyone claim "many may troubleshoot in dev/staging, but that's not the right thing to do". Not the right thing to do?

In my professional experience, the testing/troubleshooting is done on dev/staging. Once we feel everything is up to snuff, code gets pushed to production. If there are issues on production, we try to replicate it on dev/staging/etc and troubleshoot it. Most of the time, we find the bug/issue. On the rarest of occasions do we have to troubleshoot directly on production which serve our company and especially our clients who have SLAs with us. Your cavalier attitude about production is something I've yet to come across. But if it works for you, then I guess that's all that matters.

Ideally, you would be absolutely right and I would not disagree with you. But I strongly do, based on many years of experience in the field.

Out of all the customers we help with their Postgres infrastructure, and that includes several $B companies, I have found none to have either: * Dev environments dimensioned similarly to production. Actually, this typically neither happens on staging and sometimes not even in QA. * All except QA, if any, have the same data as production.

So in practical terms, I haven't seen it is possible to troubleshoot on dev/test/staging/QA environments, least to have a copy of the full database. This is reasonable, too: how all developers may have 5TB of data on their laptops? (leaving aside all privacy and data protection considerations).

That's why having a tool, Joe, to light provision production-like environments, that are disposable, where you can troubleshoot your queries, is for me a very nice approach to this problem, and the one I recommend.

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.

Reply on news.ycombinator.com