sql: expose table on-disk size

[tbg]

As of #20627, the admin UI computes the approximate on-disk size of tables. However, it makes sense to expose this information programmatically via SQL. All that's needed for this is to find a place to expose it, and populate it with from (*adminServer).TableStats. This call isn't extremely cheap (has fan-out and needs to scan a subset of the meta entries), but caching could be added as necessary.

Perhaps there is a connection to be made with #20323. It seems reasonable that approximate on-disk size for a table would be exposed in SELECT .. FROM system.table_statistics. (cc @RaduBerinde).

@dianasaur323 for triage.

related: #86017

Jira issue: CRDB-5915

Epic: CRDB-24527

[dianasaur323]

@RaduBerinde and perhaps @tschottdorf do you mind giving an approximate time to do this? doesn't sound like it would take too long? SELECT .. FROM system.table_statistics makes sense to me.

[petermattis]

system.table_statistics is meant for powering query planning. It is possible that it could contain approximate disk size, but I see some impedance mismatch. For example, I believe that table only contains stats on the primary index right now while for the admin UI I imagine you'd want to include the approximate disk size of indexes too. Also, the refresh rate of system.table_statistics might be too low for the admin UI. Lastly, table statistics are only going to be populated on demand (via a CREATE STATISTICS) call for 2.0.

Adding a crdb_internal virtual table (or a column to an existing table) and powering it via a fan-out TableStats might be more tenable in the near term.

[dianasaur323]

@petermattis makes sense to me, although my understanding was that we don't do much in terms of documenting crdb_internal. @jseldess correct me if I'm wrong?

I think we are rapidly coming to the point where we have to consider exposing metrics in the admin UI also through either SQL or some other API for users trying to automate things based on metrics, so I'm a bit concerned about choosing one path here that we then have to change when we finally have time to think about how to expose these metrics in a way that is easily consumable by users in the format they want. What are your thoughts on that?

[jseldess]

That's was the basic agreement in the past, yes, @dianasaur323. @knz was involved in that decision. Could we add a SQL statement that queries that internal table?

[petermattis]

I think we are rapidly coming to the point where we have to consider exposing metrics in the admin UI also through either SQL or some other API for users trying to automate things based on metrics, so I'm a bit concerned about choosing one path here that we then have to change when we finally have time to think about how to expose these metrics in a way that is easily consumable by users in the format they want. What are your thoughts on that?

I'm in agreement that we should be able to access vs internal metrics via SQL. Adding SQL statements for each of these would likely result in an explosion of statements. I believe the hesitance in the past to document crdb_internal was to give ourselves freedom to make backwards incompatible changes. But we can either revisit that decision or design another set of virtual tables for which we promise to maintain compatibility.

[dianasaur323]

In this case, should we just do the crdb_internal virtual table approach for this specific metric and revisit when more people start asking for programmatic access to these metrics? Diana

…

On Thu, Dec 14, 2017 at 3:29 PM, Peter Mattis ***@***.***> wrote: I think we are rapidly coming to the point where we have to consider exposing metrics in the admin UI also through either SQL or some other API for users trying to automate things based on metrics, so I'm a bit concerned about choosing one path here that we then have to change when we finally have time to think about how to expose these metrics in a way that is easily consumable by users in the format they want. What are your thoughts on that? I'm in agreement that we should be able to access vs internal metrics via SQL. Adding SQL statements for each of these would likely result in an explosion of statements. I believe the hesitance in the past to document crdb_internal was to give ourselves freedom to make backwards incompatible changes. But we can either revisit that decision or design another set of virtual tables for which we promise to maintain compatibility. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#20712 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AFgDWJ98GfSydy0Qx24FFZgZnQQDAwEHks5tAYUtgaJpZM4RCNKm> .

-- Diana Hsieh diana@cockroachlabs.com 407-690-9048

[tbg]

I think the virtual table makes sense for now. Querying this programmatically is generally a little dangerous too because the work done in computing these quantities isn't trivial, so we shouldn't exactly advertise it.

But this also constrains us somewhat: if we make this a virtual table with strawman schema (table_id, approx_size_bytes), will any query to this table compute the stats for all tables? We should avoid that.

If that restriction makes things difficult, a more straightforward way is to make that a function (so that it can only be invoked for individual tableIDs).

[RaduBerinde]

With a little bit of work we can plumb a filter to the virtual table generator function so it only computes it for one table if there is a `WHERE table_id = x`

…

-Radu

On Thu, Dec 14, 2017 at 5:21 PM, Tobias Schottdorf ***@***.*** > wrote: I think the virtual table makes sense for now. Querying this programmatically is generally a little dangerous too because the work done in computing these quantities isn't trivial, so we shouldn't exactly advertise it. But this also constrains us somewhat: if we make this a virtual table with strawman schema (table_id, approx_size_bytes), will any query to this table compute the stats for *all* tables? We should avoid that. If that restriction makes things difficult, a more straightforward way is to make that a function (so that it can only be invoked for individual tableIDs). — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#20712 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/APxxeGuC0HCn_MW7fB4XOZrBGfJNvRgvks5tAZ9kgaJpZM4RCNKm> .

[dianasaur323]

@vivekmenezes do you mind seeing if anyone would have time to do this? it would be nice since it is a customer request.

[vivekmenezes]

https://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE

The entire page is a list of the various admin related functions that we could support. I'm in favor of us figuring out what we want to support and building a project around it rather than doing a one off feature request.

[tbg]

@vivekmenezes I think we should leverage the flexibility we have in crdb_internal to iterate quickly and stabilize things as they've been in for a while (moving them out of crdb_internal or coming up with some other scheme). It's hard to communicate to a user that we're not going to provide necessary functionality because we want to polish first.

[knz]

👍 on publicizing a set of vtables that we're willing to expose in docs and users. I would however use two separate namespaces though -- perhaps crdb_internal for those we want to expose and crdb_experimental as a "staging area" for features we're not sure about yet.

I'd suggest migrating most of those we have in crdb_internal to crdb_experimental, with the exception of those few the users want and we can document.

Also I think it's OK to have some redundancy between the namespaces.

[dianasaur323]

@knz that sounds like a good approach. cc @jseldess so that he is aware that we may have to account for time to document this