Comment by lmm
3 years ago
> Even in your scenario you could identify schemas and tables that can be separated and moved into a different database or at maturity into a more scalable NoSQL variety.
How? There's nothing tracking or reporting that (unless database management instrumentation has improved a lot recently), SQL queries aren't versioned or typechecked. Usually what happens is you move a table out and it seems fine, and then at the end of the month it turns out the billing job script was joining on that table and now your invoices aren't getting sent out.
> Generally once you get to the point that is being described that means you have a very strong sense on the of queries you are making.
No, just the opposite; you have zillions of queries being run from all over the case and no idea what they all are, because you've taught everyone that everything's in this one big database and they can just query for whatever it is they need.
Every database I know of can generate query logs. Why not just log every query and do some statistical analysis on it?
> Every database I know of can generate query logs.
Every one I know of warns that it comes with significant performance implications and isn't intended to be used in production.
> Why not just log every query and do some statistical analysis on it?
And then what? If you know this table only gets queried a few times a month, what does that actually tell you that you can use?
It's resource intensive - but so is being in a giant tarpit/morass. Adding client query logging is cheaper and can be distributed. I just double checked, and neither Oracle nor Postgres warn 'never use it in production'
And if you have logs, you can see what actually gets queried, and by whom, and what doesn't get queried, and by whom.
That will also potentially let you start constructing views and moving actual underlying tables out of the way to where you can control them.
Which can let you untangle the giant spaghetti mess you're in.
But then, that's just me having actually done that a few times. You're welcome to complain about how it's actually unsolvable and will never get better, of course.
3 replies →