[CALCITE-6728] Lazy loading of database tables and schemas

• Type: Wish
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.38.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: Ulrich Kramer
• Created: 11/Dec/24 15:01
• Updated: 19/Dec/24 13:49

Description

We are facing the issue that our Database contains 500000 schemas with up to 500000 tables each.

Caching all these schemas and tables is not an option
1. It will require a lot of memory
2. The eviction of the cache must happen quite often since it's likely that every second one of these table is changed.

We worked around this issue by introducing some kind of lazy loading. We did this in the following way:

Instead of having pairs of methods to lookup tables and schemas (e.g. getTable and getTableNames), we introduced a new Lookup interface, which bundles these two methods

public interface Lookup<T> {
  /**
   * Returns an entity with a given name, or null if not found.
   * The name is matched case sensitive.
   *
   * @param name Name
   * @return Entity, or null
   */
  @Nullable  T get(String name) ;

  /**
   * Returns a named entity with a given name ignoring the case, or null if not found.
   *
   * @param name Name
   * @return Entity, or null
   */
  @Nullable Named<T> getIgnoreCase(String name) ;

  /**
   * Returns the names of the entities in matching pattern.
   *
   * @return Names of the entities
   */
  Set<String> getNames(LikePattern pattern);
}

and modified the Schema interface accordingly

public interface Schema {

  /**
   * Returns a lookup object to find tables
   *
   * @return Lookup
   */
  Lookup<Table> tables();

  /**
   * Returns a lookup object to find schemas
   *
   * @return Lookup
   */
  Lookup<? extends Schema> subSchemas();
  ...
}

Most of the changes can be found in this commit.

With theses changes we have been able to manage the huge amount of schemas and tables.

Here are my questions related to my wish/proposal:

• Does it make sense to provide a PR wich includes these modifications although they might break compatibility with older versions?
• Does someone else have a great idea to introduce lazy loading with a smaller change?

Issue Links

• is related to: CALCITE-690 Calcite must not call Schema.getTableNames if caching is disabled
• is related to: CALCITE-911 Add a variant of CalciteSchema that does not cache sub-objects
• is related to: CALCITE-1742 Create a read-consistent view of CalciteSchema for each statement compilation
• is related to: CALCITE-5687 lazy get scheme
• is related to: CALCITE-6268 Support implementing custom JdbcSchema
• is related to: CALCITE-4654 CloneSchema doesn't cache
• links to: GitHub Pull Request #4100