Hasura Console Data Screen has Very Long Load Time with Large Amounts of DB Tables

[mtate713]

Is your proposal related to a problem?

While using a self-hosted Docker instance of Hasura, the Data screen in the Hasura Console seems to take 20 minutes to load in the tables of databases

with 100+ tables. It sometimes hangs indefinitely and never loads any tables, tracked or untracked. The GraphQL engine is still able to process queries from the large databases, but it has become difficult to track new tables or add new table relationships.

Describe the solution you'd like

An update to the Hasura Console frontend so that it can handle large amounts of DB tables and is scalable to demand.

Describe alternatives you've considered

I have tried manually updating Hasura Metadata JSON to track new tables and relationships, but I rather use the Hasura Console to do these tasks. Especially when I am introducing this product to less technical users.

Thank you!

[BenoitRanque]

@mtate713 can you share any more details about your database? Especially interested in not only the size of the schema, but also whether your database has any data and if so how much. We've seen similar issues in the past with very large databases.

[mtate713]

Yes, I was experimenting with Hasura on a test Postgres database with a few (<5) schemas, but about 100+ tables in each schema. Each of the tables has about 1 year of daily archive data. The Data screen was able to load the initial database and schemas fast. But once I open one of the schemas it takes a very long time to load in the tables. Also when the tables do load in, if I click on one table the data and relationships in the table loads in fast. It seems like just loading in the list of schema tables is hanging up the Data screen. Is there any way that you can update that process in the Hasura Console?

[BenoitRanque]

@mtate713 Absolutely. We'd appreciate some help in identifying the culprit query, if that's ok with you.
Could you please create a support ticket mentioning this issue? We'll share a meeting link there so we can cooperate on finding the cause of the problem.

If you do not have a hasura cloud account, you can also email support [at] hasura [dot] io

[manasag]

Hi @mtate713 , we are looking into supporting these kind of bulk (or long running) tasks in Hasura CLI as an extension, which can be easier to manage and track due to command line interface. Will that be something that can work for your usecase? We can share some demo work for reference.

[mtate713]

Yes, if you can share an example of this extension in a docker instance of Hasura CLI, that would be very helpful. One recommendation I suggest for improving the Hasura Console is to have a way to store the untracked tables in the Metadata. It seems like the issue is because the Console is constantly pulling the list of untracked tables from the Database every time you do anything in the Data screen. I really love Hasura and what you all are doing with it! Thank you for reaching out!

[scriptonist]

@mtate713 Thanks for offering your help.

The following is a docker-compose file demonstrating a CLI plugin to track all tables in a Postgres database.

The cli service is the one which has the relevant information. Do let us know what you think.

version: '3.6'
services:
  postgres:
    image: postgres:12
    restart: always
    volumes:
    - db_data:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: postgrespassword
  graphql-engine:
    image: hasura/graphql-engine:v2.15.1
    ports:
    - "8080:8080"
    depends_on:
    - "postgres"
    restart: always
    environment:
      ## postgres database to store Hasura metadata
      HASURA_GRAPHQL_DATABASE_URL: postgres://postgres:postgrespassword@postgres:5432/postgres
      ## this env var can be used to add the above postgres database to Hasura as a data source. this can be removed/updated based on your needs
      PG_DATABASE_URL: postgres://postgres:postgrespassword@postgres:5432/postgres
      ## enable the console served by server
      HASURA_GRAPHQL_ENABLE_CONSOLE: "true" # set to "false" to disable console
      ## enable debugging mode. It is recommended to disable this in production
      HASURA_GRAPHQL_DEV_MODE: "true"
      HASURA_GRAPHQL_ENABLED_LOG_TYPES: startup, http-log, webhook-log, websocket-log, query-log
      ## uncomment next line to run console offline (i.e load console assets from server instead of CDN)
      # HASURA_GRAPHQL_CONSOLE_ASSETS_DIR: /srv/console-assets
      ## uncomment next line to set an admin secret
      # HASURA_GRAPHQL_ADMIN_SECRET: myadminsecretkey
    healthcheck:
      test: ["CMD", "curl", "-f", "http://localhost:8080/v1/version"]
      interval: 10s
      timeout: 10s
      retries: 10
  cli:
    image: alpine/curl:3.14
    depends_on:
    - "graphql-engine"
    command: sh -c "
      curl -LO https://github.com/hasura/graphql-engine/releases/download/v2.15.1/cli-hasura-linux-amd64
      && chmod +x cli-hasura-linux-amd64
      && mv cli-hasura-linux-amd64 /usr/local/bin/hasura
      && curl -LO https://github.com/scriptonist/hasura-cli-track/releases/download/v0.0.2/manifest.yaml 
      && cat manifest.yaml
      && hasura plugins install --manifest-file manifest.yaml track --skip-update-check
      && hasura init hasura --skip-update-check
      && export HASURA_GRAPHQL_ENDPOINT='http://graphql-engine:8080'
      && cd hasura
      && curl -LO https://raw.githubusercontent.com/allpwrfulroot/datasets/master/chinook.sql
      && hasura migrate create --sql-from-file chinook.sql chinook --database-name default --skip-update-check 
      && hasura migrate apply --database-name default --skip-update-check
      && hasura track tables --database-name default
      "

volumes:
  db_data:

[BenoitRanque]

Hi @mtate713, we've received your ticket regarding this issue, and shared a meeting link so we can look into this.
Despite our best efforts we cannot reproduce this yet, so we'd like your help in diagnosing this problem.

Can you confirm you've received our replies on the ticket?

[BenoitRanque]

@mtate713 I'd also like to know if the schemas have a lot of columns between all their tables?
You can check this using the following statement:

SELECT COUNT(*)
FROM information_schema.columns where table_schema = 'public';

Change the schema name as required.