"Obviously querying over project_shorthand = '@unison/cloud' isn't going to be able to use an index, so isn't going to be the most performant query"
If you know you're going to be querying on username and project shorthand a lot you're just a handful more lines away from instantiating that as a database function which can take your shorthand apart with string functions and get back to a high-performance query. Functions can return rowsets which you can then further filter with WHERE clauses.
Although in that case I think I'd just take the first and second parameters separately anyhow, since
debug_contribution("sophie", "@unison/cloud")
and
debug_contributions("sophie", "unison", "cloud")
isn't a big enough difference to be worth writing string-parsing code. But do as you like.
The next logical step is to realize that using these views to drive your actual application has great potential to both vastly simplify the logic and make it super high performance. You can even take this so far as to make a view that spits out XML (or I guess json): you basically get SSR for free with easy to inspect/compose queries.
Or write the query as a function in code, that way, everyone can know it exists, call it easily, and change it along with any other changes to your logic or schema.
I implemented something similar for a 3M/day ad tech platform. We created a few materialized views for my boss, myself, and the DevOps team to monitor, instead of querying over 100 tables. I used stored procedures in PostgreSQL, which made it fast, efficient, and non-blocking also not to mention we avoided setting up complex Grafana/UI in admin dashboard, boss: i want to know x, y ,z, okay -> tableplus, -> export csv boom!
I was very confused about the term "Debug" view. We just called them views and used them normally. The main challenge was predicate pushdown which, though usually worked, was somewhat dependent on how smart the optimizer was. Also, things could get tricky when you start building views on top of views. Soon you are not certain why a simple looking query selecting from a simple looking view became slow until you see that it is views all the way down. As with most things, balance is the key.
To help understand why a complex query with many joins comes back empty, I like to provide helper debug functions that builds the query step by step. Each block checks one prerequisite and tells the user what’s missing:
```sql
FUNCTION debug_user(user_id):
IF NOT (SELECT FROM user WHERE user.user_id = user_id) THEN
-- user doesn’t exist
END IF;
IF NOT (SELECT FROM user
JOIN user_addr ON user_addr.user_id = user.user_id
WHERE user.user_id = user_id) THEN
-- user has no address
END IF;
-- keep adding joins until you find the break
```
because when you have a query that involves 6 relations, and you don't get results, it is nice to know why.
Counterpoint: you should not be connecting to your production database, you should not be running non-critical queries on production database servers, and you probably shouldn’t have permission to see all this data about your users.
Obviously your mileage may vary, your scale is your own, your trade offs are your own trade offs.
But be aware that there comes an operational scale where this is not an acceptable way - operationally, legally, privacy-wise - to investigate customer issues, and you’ll need different tricks.
I'm trying to work out why 'views can help you see whats going on in your database' has got to the front page of HN. Is it that we've had a whole generation grow up with ORMs and so only people over 50 know how to actually use a RDBMS?
15 comments
[ 2.7 ms ] story [ 36.6 ms ] threadIf you know you're going to be querying on username and project shorthand a lot you're just a handful more lines away from instantiating that as a database function which can take your shorthand apart with string functions and get back to a high-performance query. Functions can return rowsets which you can then further filter with WHERE clauses.
Although in that case I think I'd just take the first and second parameters separately anyhow, since
and isn't a big enough difference to be worth writing string-parsing code. But do as you like.See an alert? Just query the view and you should have all the client/job/whatever IDs you need to trace the problem.
```sql FUNCTION debug_user(user_id):
```because when you have a query that involves 6 relations, and you don't get results, it is nice to know why.
Obviously your mileage may vary, your scale is your own, your trade offs are your own trade offs.
But be aware that there comes an operational scale where this is not an acceptable way - operationally, legally, privacy-wise - to investigate customer issues, and you’ll need different tricks.