Skip to content(if available)orjump to list(if available)

You Should Add Debug Views to Your DB

al3rez

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!

jerf

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

ibejoeb

If you're using it regularly, you can make it a derived column and index it in most RDBSs. Then it'll work with predicate push-down and you can do other fancy things like store it on alternative partitions to optimize block reads when you're not using it.

The other obvious benefit is that it is no different in semantics than any other column, so there's no advance knowledge necessary for other users vs using a function.

ndriscoll

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.

null

[deleted]

sodapopcan

I get the sentiment but it is an odd one that we'd consider adding a name to a list of tables (that can be sorted and filtered) that we generally don't look at as much as our code* as more clutter than adding a SQL to our codebase.

* I realize this is not true for everyone

Ozzie_osman

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.

morkalork

This plus some automated assertion checks on whatever invariants you can think of makes for a very cheap and scalable canary.

See an alert? Just query the view and you should have all the client/job/whatever IDs you need to trace the problem.

vincekerrazzi

I agree. So I did. And over the span of a year I was the only one that used them. ¯\_(ツ)_/¯