Thread: Selecting tables from Browser

Selecting tables from Browser

From
Shirley Wang
Date:
Hello!

Our team is working on validating a solution for viewing many tables in the Catalog Browser. We've heard through user interviews that some schemas hold many tables (over 10000 in some cases) and when expanding tables, the browser crashes. In cases where people are able to view tables in the catalog browser, finding the relevant tables requires a lot of search/scrolling. 

We are testing these designs over the next few weeks. In particular we're looking to validate/invalidate if:
Terry would filter the list of tables before selecting tables
Terry has a curated schema list based on her permissions
Terry needs to reopen the object manager again after making a selection
Terry needs to collapse and expand the list of tables without opening the object manager
Terry needs to use the object manager once there are at least 20 tables

(Terry is our DBA persona)



Here are the screens:
01 Open 'Object Manager' by clicking on tables in the browser. (not attached to 'object manager', open to suggestions)
08.png
02 Most DBAs tend to know which tables they're working with on a high level. Here they can filter down the list.
09_01b.png
03 Alternatively, DBAs can just select the tables they need.
09_02.png

04 once selected, the tables appear in the browser
10.png

Part of our test is to learn how users expect to re-open the object manager as well. 

Feedback is welcome. We'll consolidate feedback from here with the feedback we get from user tests and share that out in a few weeks once the sessions are done.


Shirley & pgAdmin team

Re: Selecting tables from Browser

From
Robert Eckhardt
Date:
Shirley, 

I have a few questions.
  1. Why 20. It is a dunbar number and seems smallish but is there another reason?
  2. Since this is referred to as an Object Manager I assume the same thing will eventually be available for databases, schema, partitions, etc. How do permissions work currently to limit m view of these objects? (or do they)
  3. Do you think that this would look differently if you assumptions were based on a user who writes BI reports or some other non-DBA user? 
-- Rob

On Mon, Aug 21, 2017 at 12:20 PM, Shirley Wang <swang@pivotal.io> wrote:
Hello!

Our team is working on validating a solution for viewing many tables in the Catalog Browser. We've heard through user interviews that some schemas hold many tables (over 10000 in some cases) and when expanding tables, the browser crashes. In cases where people are able to view tables in the catalog browser, finding the relevant tables requires a lot of search/scrolling. 

We are testing these designs over the next few weeks. In particular we're looking to validate/invalidate if:
Terry would filter the list of tables before selecting tables
Terry has a curated schema list based on her permissions
Terry needs to reopen the object manager again after making a selection
Terry needs to collapse and expand the list of tables without opening the object manager
Terry needs to use the object manager once there are at least 20 tables

(Terry is our DBA persona)



Here are the screens:
01 Open 'Object Manager' by clicking on tables in the browser. (not attached to 'object manager', open to suggestions)
08.png
02 Most DBAs tend to know which tables they're working with on a high level. Here they can filter down the list.
09_01b.png
03 Alternatively, DBAs can just select the tables they need.
09_02.png

04 once selected, the tables appear in the browser
10.png

Part of our test is to learn how users expect to re-open the object manager as well. 

Feedback is welcome. We'll consolidate feedback from here with the feedback we get from user tests and share that out in a few weeks once the sessions are done.


Shirley & pgAdmin team


Re: Selecting tables from Browser

From
Shirley Wang
Date:
Hi Rob
On Mon, Aug 21, 2017 at 1:53 PM Robert Eckhardt <reckhardt@pivotal.io> wrote:
Shirley, 

I have a few questions.
  1. Why 20. It is a dunbar number and seems smallish but is there another reason?
The browser fits about 30 ish items right now on my smallish laptop screen. The question we asked ourselves is 'what is the most content we can show before the browser gets unwieldy?' 

You have thoughts on this?
 
  1. Since this is referred to as an Object Manager I assume the same thing will eventually be available for databases, schema, partitions, etc. How do permissions work currently to limit m view of these objects? (or do they)

Good point about the name implying further reach than just tables. We're going to change the dialog header to show 'Select tables for display', at least until we decide we want to include databases, schema, partitions.

I'm not sure how permissions works to limit the view, our assumption is that permissions does though. 

Dave P do you have more insight on what permissions can limit?
  1. Do you think that this would look differently if you assumptions were based on a user who writes BI reports or some other non-DBA user? 
Potentially, as you mentioned off the email thread, a DBA would be interested in a larger list of tables than someone who writes BI reports. 

That said, since this is a problem we hear from DBAs, and they feel the most pain around this, I think it's fine to focus on solving the problem for them. Solving their pains will also address the pains of people who write BI reports since they also feel the same issues at a lesser intensity

We will be testing with non-DBA users though too.
 

Re: Selecting tables from Browser

From
Robert Eckhardt
Date:


On Mon, Aug 21, 2017 at 7:17 PM, Shirley Wang <swang@pivotal.io> wrote:
Hi Rob
On Mon, Aug 21, 2017 at 1:53 PM Robert Eckhardt <reckhardt@pivotal.io> wrote:
Shirley, 

I have a few questions.
  1. Why 20. It is a dunbar number and seems smallish but is there another reason?
The browser fits about 30 ish items right now on my smallish laptop screen. The question we asked ourselves is 'what is the most content we can show before the browser gets unwieldy?' 

You have thoughts on this?

No thought better than 20. I was thinking a few hand full or a large enough number to cause performance issues. 
 
 
  1. Since this is referred to as an Object Manager I assume the same thing will eventually be available for databases, schema, partitions, etc. How do permissions work currently to limit m view of these objects? (or do they)

Good point about the name implying further reach than just tables. We're going to change the dialog header to show 'Select tables for display', at least until we decide we want to include databases, schema, partitions.

I'm not sure how permissions works to limit the view, our assumption is that permissions does though. 

Dave P do you have more insight on what permissions can limit?
  1. Do you think that this would look differently if you assumptions were based on a user who writes BI reports or some other non-DBA user? 
Potentially, as you mentioned off the email thread, a DBA would be interested in a larger list of tables than someone who writes BI reports. 

That said, since this is a problem we hear from DBAs, and they feel the most pain around this, I think it's fine to focus on solving the problem for them. Solving their pains will also address the pains of people who write BI reports since they also feel the same issues at a lesser intensity

We will be testing with non-DBA users though too.

I'm mostly curious about this because I only really know how I interacted with a DB in both roles. 

-- Rob
 
 


Re: Selecting tables from Browser

From
Dave Page
Date:


On Tue, Aug 22, 2017 at 12:17 AM, Shirley Wang <swang@pivotal.io> wrote:
Hi Rob
On Mon, Aug 21, 2017 at 1:53 PM Robert Eckhardt <reckhardt@pivotal.io> wrote:
Shirley, 

I have a few questions.
  1. Why 20. It is a dunbar number and seems smallish but is there another reason?
The browser fits about 30 ish items right now on my smallish laptop screen. The question we asked ourselves is 'what is the most content we can show before the browser gets unwieldy?' 

A lot more than the proposed 20. I regularly work with ~100 tables in a single schema, and having to go through an additional dialogue to find what I need would be hugely inconvenient. 

I will often learn about a new database by browsing through it as well, jumping from table to table as I discover relationships etc. I couldn't imagine doing that with a filtering dialogue getting in the way.
 

You have thoughts on this?
 
  1. Since this is referred to as an Object Manager I assume the same thing will eventually be available for databases, schema, partitions, etc. How do permissions work currently to limit m view of these objects? (or do they)

Good point about the name implying further reach than just tables. We're going to change the dialog header to show 'Select tables for display', at least until we decide we want to include databases, schema, partitions.

I'm not sure how permissions works to limit the view, our assumption is that permissions does though. 

Dave P do you have more insight on what permissions can limit?

Permissions don't limit what you would see here. They limit a roles ability to insert/update/delete data in tables, but not to examine the schema.
 
  1. Do you think that this would look differently if you assumptions were based on a user who writes BI reports or some other non-DBA user? 
Potentially, as you mentioned off the email thread, a DBA would be interested in a larger list of tables than someone who writes BI reports. 

That said, since this is a problem we hear from DBAs, and they feel the most pain around this, I think it's fine to focus on solving the problem for them. Solving their pains will also address the pains of people who write BI reports since they also feel the same issues at a lesser intensity

We will be testing with non-DBA users though too.

I think this is the wrong way to approach this problem. At the very least, the limit of 20 objects needs to have a much higher value, and be configurable.

I think it would be far better to implement searching of the tree as we had in pgAdmin 3 (and a number of users have requested we re-implement), and do partial branch loading on the tree, where we display maybe 30 items, then add a "Load more..." node at the end, that when click would be replaced with the next 30 items.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Selecting tables from Browser

From
Shirley Wang
Date:


On Tue, Aug 22, 2017 at 8:27 AM Dave Page <dpage@pgadmin.org> wrote:
On Tue, Aug 22, 2017 at 12:17 AM, Shirley Wang <swang@pivotal.io> wrote:
Hi Rob
On Mon, Aug 21, 2017 at 1:53 PM Robert Eckhardt <reckhardt@pivotal.io> wrote:
Shirley, 

I have a few questions.
  1. Why 20. It is a dunbar number and seems smallish but is there another reason?
The browser fits about 30 ish items right now on my smallish laptop screen. The question we asked ourselves is 'what is the most content we can show before the browser gets unwieldy?' 

A lot more than the proposed 20. I regularly work with ~100 tables in a single schema, and having to go through an additional dialogue to find what I need would be hugely inconvenient. 

I will often learn about a new database by browsing through it as well, jumping from table to table as I discover relationships etc. I couldn't imagine doing that with a filtering dialogue getting in the way.

Would you be able to describe more about your use case? What role are you taking on as you are browsing through the database, what are you trying to accomplish and why?
 
 

You have thoughts on this?
 
  1. Since this is referred to as an Object Manager I assume the same thing will eventually be available for databases, schema, partitions, etc. How do permissions work currently to limit m view of these objects? (or do they)

Good point about the name implying further reach than just tables. We're going to change the dialog header to show 'Select tables for display', at least until we decide we want to include databases, schema, partitions.

I'm not sure how permissions works to limit the view, our assumption is that permissions does though. 

Dave P do you have more insight on what permissions can limit?

Permissions don't limit what you would see here. They limit a roles ability to insert/update/delete data in tables, but not to examine the schema.

So as a Platform Administrator (which we're defining as someone in a very large company who works with many DBAs across multiple databases), I am not able to restrict what schemas different user groups can see?  

More specifically, if there are 500 schemas for an org, every user will be able to see them all in the browser and changing permissions will not impact what a DBA will see?
 
 
  1. Do you think that this would look differently if you assumptions were based on a user who writes BI reports or some other non-DBA user? 
Potentially, as you mentioned off the email thread, a DBA would be interested in a larger list of tables than someone who writes BI reports. 

That said, since this is a problem we hear from DBAs, and they feel the most pain around this, I think it's fine to focus on solving the problem for them. Solving their pains will also address the pains of people who write BI reports since they also feel the same issues at a lesser intensity

We will be testing with non-DBA users though too.

I think this is the wrong way to approach this problem. At the very least, the limit of 20 objects needs to have a much higher value, and be configurable.

I think it would be far better to implement searching of the tree as we had in pgAdmin 3 (and a number of users have requested we re-implement),

Implementing search will definitely add value for users, we've also heard the same requests from people during our interviews as well. However it doesn't solve for navigating the browser when there are enough tables to crash or significantly slow down the application.

20 objects is where we're starting from, it's definitely a risky assumption we're making and I'm not convinced it's the right number. But for user interviews its good to have a clear point of view on a design decision so we can test it and get feedback on it.

 
and do partial branch loading on the tree, where we display maybe 30 items, then add a "Load more..." node at the end, that when click would be replaced with the next 30 items.

I think that would make it difficult to get to zebra table. A partial load could be a solution, followed by some way to navigate to the tables towards the end of the alphabet.
 

Re: Selecting tables from Browser

From
Dave Page
Date:


On Tue, Aug 22, 2017 at 5:04 PM, Shirley Wang <swang@pivotal.io> wrote:


On Tue, Aug 22, 2017 at 8:27 AM Dave Page <dpage@pgadmin.org> wrote:
On Tue, Aug 22, 2017 at 12:17 AM, Shirley Wang <swang@pivotal.io> wrote:
Hi Rob
On Mon, Aug 21, 2017 at 1:53 PM Robert Eckhardt <reckhardt@pivotal.io> wrote:
Shirley, 

I have a few questions.
  1. Why 20. It is a dunbar number and seems smallish but is there another reason?
The browser fits about 30 ish items right now on my smallish laptop screen. The question we asked ourselves is 'what is the most content we can show before the browser gets unwieldy?' 

A lot more than the proposed 20. I regularly work with ~100 tables in a single schema, and having to go through an additional dialogue to find what I need would be hugely inconvenient. 

I will often learn about a new database by browsing through it as well, jumping from table to table as I discover relationships etc. I couldn't imagine doing that with a filtering dialogue getting in the way.

Would you be able to describe more about your use case? What role are you taking on as you are browsing through the database, what are you trying to accomplish and why?

Typically a support type role I guess - think 3rd line DBA support, someone coming in to troubleshoot a specific issue and starting by familiarising themselves with the database in question.
 
 
 

You have thoughts on this?
 
  1. Since this is referred to as an Object Manager I assume the same thing will eventually be available for databases, schema, partitions, etc. How do permissions work currently to limit m view of these objects? (or do they)

Good point about the name implying further reach than just tables. We're going to change the dialog header to show 'Select tables for display', at least until we decide we want to include databases, schema, partitions.

I'm not sure how permissions works to limit the view, our assumption is that permissions does though. 

Dave P do you have more insight on what permissions can limit?

Permissions don't limit what you would see here. They limit a roles ability to insert/update/delete data in tables, but not to examine the schema.

So as a Platform Administrator (which we're defining as someone in a very large company who works with many DBAs across multiple databases), I am not able to restrict what schemas different user groups can see?  

Nope. PostgreSQL would need row level security features on the system catalogs to do that, which it doesn't have. Whilst you can restrict users from using objects (executing functions, IUD on tables etc), you cannot stop them seeing the schema as the catalogs all allow read access.
 

More specifically, if there are 500 schemas for an org, every user will be able to see them all in the browser and changing permissions will not impact what a DBA will see?

Correct - that's why pgAdmin 3 had the database and schema restriction options, the former of which was added to pgAdmin 4 a release or two back. We offer them for convenience though, NOT as security measures.
 
 
 
  1. Do you think that this would look differently if you assumptions were based on a user who writes BI reports or some other non-DBA user? 
Potentially, as you mentioned off the email thread, a DBA would be interested in a larger list of tables than someone who writes BI reports. 

That said, since this is a problem we hear from DBAs, and they feel the most pain around this, I think it's fine to focus on solving the problem for them. Solving their pains will also address the pains of people who write BI reports since they also feel the same issues at a lesser intensity

We will be testing with non-DBA users though too.

I think this is the wrong way to approach this problem. At the very least, the limit of 20 objects needs to have a much higher value, and be configurable.

I think it would be far better to implement searching of the tree as we had in pgAdmin 3 (and a number of users have requested we re-implement),

Implementing search will definitely add value for users, we've also heard the same requests from people during our interviews as well. However it doesn't solve for navigating the browser when there are enough tables to crash or significantly slow down the application.

True. If that is something users actually see, have you investigated the root cause to ensure it's not a side effect of some bad code (e.g. something that is unnecessarily O(2N))?
 

20 objects is where we're starting from, it's definitely a risky assumption we're making and I'm not convinced it's the right number. But for user interviews its good to have a clear point of view on a design decision so we can test it and get feedback on it.

I will object to any behavioural change that requires me to use a filtering dialogue for databases similar to what I typically work with; I've never seen any performance issues in this area, and it would unnecessarily add additional steps into my workflow, that would significantly slow me down if I have to keep changing the filtering every time I find I need to look at an object that isn't currently shown.

Granted, I do not have 10K tables in any of my databases; but I do use databases with >2K functions in them quite successfully (as we all do - they're in pg_catalog).
 

 
and do partial branch loading on the tree, where we display maybe 30 items, then add a "Load more..." node at the end, that when click would be replaced with the next 30 items.

I think that would make it difficult to get to zebra table. A partial load could be a solution, followed by some way to navigate to the tables towards the end of the alphabet.
 

Good point.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Selecting tables from Browser

From
Shirley Wang
Date:
We've talked to 3 people for feedback on the workflow.

In summary:
  • The designs don't match up to the interactions users expect when selecting a table. They saw more information that what they expected/needed to, and that lead to confusion
  • Users do need to be able to filter through tables.
  • Databases and schemas can be restricted to a user. Only seeing what objects each person has access to (or, at the very least, an indicator that some objects are restricted), would help DBAs narrow down their search for the correct object.
    • One interviewee mentioned each user [developer] has their own schema mapped to their ID
Update designs by:
  • Only display objects that users have permissions to. In other words, don't display objects a user cannot read or edit.
  • Include a visual indicator for objects that are read only
  • Add a filter that would search through all objects without needing to expand the object. Then display only the relevant object that includes the search string.


We'll send updated designs sometime in the next few weeks.

Jing+Shirley

On Wed, Aug 23, 2017 at 4:59 AM Dave Page <dpage@pgadmin.org> wrote:
On Tue, Aug 22, 2017 at 5:04 PM, Shirley Wang <swang@pivotal.io> wrote:


On Tue, Aug 22, 2017 at 8:27 AM Dave Page <dpage@pgadmin.org> wrote:
On Tue, Aug 22, 2017 at 12:17 AM, Shirley Wang <swang@pivotal.io> wrote:
Hi Rob
On Mon, Aug 21, 2017 at 1:53 PM Robert Eckhardt <reckhardt@pivotal.io> wrote:
Shirley, 

I have a few questions.
  1. Why 20. It is a dunbar number and seems smallish but is there another reason?
The browser fits about 30 ish items right now on my smallish laptop screen. The question we asked ourselves is 'what is the most content we can show before the browser gets unwieldy?' 

A lot more than the proposed 20. I regularly work with ~100 tables in a single schema, and having to go through an additional dialogue to find what I need would be hugely inconvenient. 

I will often learn about a new database by browsing through it as well, jumping from table to table as I discover relationships etc. I couldn't imagine doing that with a filtering dialogue getting in the way.

Would you be able to describe more about your use case? What role are you taking on as you are browsing through the database, what are you trying to accomplish and why?

Typically a support type role I guess - think 3rd line DBA support, someone coming in to troubleshoot a specific issue and starting by familiarising themselves with the database in question.
 
 
 

You have thoughts on this?
 
  1. Since this is referred to as an Object Manager I assume the same thing will eventually be available for databases, schema, partitions, etc. How do permissions work currently to limit m view of these objects? (or do they)

Good point about the name implying further reach than just tables. We're going to change the dialog header to show 'Select tables for display', at least until we decide we want to include databases, schema, partitions.

I'm not sure how permissions works to limit the view, our assumption is that permissions does though. 

Dave P do you have more insight on what permissions can limit?

Permissions don't limit what you would see here. They limit a roles ability to insert/update/delete data in tables, but not to examine the schema.

So as a Platform Administrator (which we're defining as someone in a very large company who works with many DBAs across multiple databases), I am not able to restrict what schemas different user groups can see?  

Nope. PostgreSQL would need row level security features on the system catalogs to do that, which it doesn't have. Whilst you can restrict users from using objects (executing functions, IUD on tables etc), you cannot stop them seeing the schema as the catalogs all allow read access.
 

More specifically, if there are 500 schemas for an org, every user will be able to see them all in the browser and changing permissions will not impact what a DBA will see?

Correct - that's why pgAdmin 3 had the database and schema restriction options, the former of which was added to pgAdmin 4 a release or two back. We offer them for convenience though, NOT as security measures.
 
 
 
  1. Do you think that this would look differently if you assumptions were based on a user who writes BI reports or some other non-DBA user? 
Potentially, as you mentioned off the email thread, a DBA would be interested in a larger list of tables than someone who writes BI reports. 

That said, since this is a problem we hear from DBAs, and they feel the most pain around this, I think it's fine to focus on solving the problem for them. Solving their pains will also address the pains of people who write BI reports since they also feel the same issues at a lesser intensity

We will be testing with non-DBA users though too.

I think this is the wrong way to approach this problem. At the very least, the limit of 20 objects needs to have a much higher value, and be configurable.

I think it would be far better to implement searching of the tree as we had in pgAdmin 3 (and a number of users have requested we re-implement),

Implementing search will definitely add value for users, we've also heard the same requests from people during our interviews as well. However it doesn't solve for navigating the browser when there are enough tables to crash or significantly slow down the application.

True. If that is something users actually see, have you investigated the root cause to ensure it's not a side effect of some bad code (e.g. something that is unnecessarily O(2N))?
 

20 objects is where we're starting from, it's definitely a risky assumption we're making and I'm not convinced it's the right number. But for user interviews its good to have a clear point of view on a design decision so we can test it and get feedback on it.

I will object to any behavioural change that requires me to use a filtering dialogue for databases similar to what I typically work with; I've never seen any performance issues in this area, and it would unnecessarily add additional steps into my workflow, that would significantly slow me down if I have to keep changing the filtering every time I find I need to look at an object that isn't currently shown.

Granted, I do not have 10K tables in any of my databases; but I do use databases with >2K functions in them quite successfully (as we all do - they're in pg_catalog).
 

 
and do partial branch loading on the tree, where we display maybe 30 items, then add a "Load more..." node at the end, that when click would be replaced with the next 30 items.

I think that would make it difficult to get to zebra table. A partial load could be a solution, followed by some way to navigate to the tables towards the end of the alphabet.
 

Good point.


--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company