> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/graphprotocol/graph-node/llms.txt
> Use this file to discover all available pages before exploring further.

# Schema Generation

> How Graph Node transforms GraphQL schemas into PostgreSQL table definitions

## Overview

Graph Node converts GraphQL schemas into PostgreSQL table structures following a systematic set of rules. Understanding this transformation is essential for optimizing subgraph performance and troubleshooting storage issues.

## Core Schema Generation Rules

<Accordion title="Namespace Isolation">
  Each subgraph's data is stored in a dedicated PostgreSQL namespace named `sgdNNNN`. The mapping between namespace names and deployment IDs is maintained in the `deployment_schemas` table.
</Accordion>

<Accordion title="Entity Type Mapping">
  Each entity type in the GraphQL schema becomes a table with a structure that mirrors the type's field declarations.
</Accordion>

<Accordion title="Enum Types">
  Enums in the GraphQL schema are created as PostgreSQL enum types and used directly in table definitions.
</Accordion>

<Accordion title="Interface Handling">
  Interfaces are not stored in the database. Only concrete types that implement interfaces are stored as tables.
</Accordion>

## Table Structure

### Mutable Entities (Standard @entity)

<CodeGroup>
  ```graphql GraphQL Schema theme={null}
  type Account @entity {
    id: ID!
    owner: Bytes!
    balance: BigInt!
    created: Int!
  }
  ```

  ```sql Generated Table theme={null}
  CREATE TABLE sgd42.account(
      vid         int8 serial primary key,
      id          text not null,
      owner       bytea not null,
      balance     numeric not null,
      created     int4 not null,
      block_range int4range not null
  )
  ```
</CodeGroup>

<Accordion title="System Columns">
  * **`vid`**: Internal version identifier used to uniquely identify specific entity versions
  * **`block_range`**: Enables time-travel queries by tracking which blocks each entity version is valid for
</Accordion>

<Note>
  The `block_range` column enables querying historical state at any block height. See the time-travel queries documentation for details.
</Note>

### Immutable Entities

Entities declared with `@entity(immutable: true)` use a simpler structure:

<CodeGroup>
  ```graphql GraphQL Schema theme={null}
  type Transfer @entity(immutable: true) {
    id: ID!
    from: Bytes!
    to: Bytes!
    value: BigInt!
  }
  ```

  ```sql Generated Table theme={null}
  CREATE TABLE sgd42.transfer(
      vid    int8 serial primary key,
      id     text not null,
      from   bytea not null,
      to     bytea not null,
      value  numeric not null,
      block$ int not null,
      UNIQUE(id)
  )
  ```
</CodeGroup>

<Accordion title="Optimizations for Immutable Entities">
  **Block Range Simplification**: Since immutable entities never change, the upper bound of `block_range` is always infinite. Instead of storing this, we use:

  * **`block$`**: Single integer column (not a range)
  * **Visibility check**: Simplified to `block$ <= B` instead of range containment

  **Unique Constraint**: Since each entity has only one version, we add `UNIQUE(id)`

  **Index Type**: Can use simple BTree indexes instead of expensive GiST indexes
</Accordion>

### Timeseries Entities

Timeseries entities use the same structure as immutable entities:

<CodeGroup>
  ```graphql GraphQL Schema theme={null}
  type PricePoint @entity(timeseries: true) {
    id: Int8!
    timestamp: Timestamp!
    price: BigDecimal!
    volume: BigInt!
  }
  ```

  ```sql Generated Table theme={null}
  CREATE TABLE sgd42.price_point(
      vid       int8 serial primary key,
      id        int8 not null,
      timestamp timestamp not null,
      price     numeric not null,
      volume    numeric not null,
      block$    int not null,
      UNIQUE(id)
  )
  ```
</CodeGroup>

<Note>
  The only difference from immutable entities is that timeseries entities must have a `timestamp` attribute.
</Note>

### Aggregation Entities

Aggregations are represented by multiple tables, one per interval:

<CodeGroup>
  ```graphql GraphQL Schema theme={null}
  type TradeStats @aggregation(intervals: ["hour", "day"]) {
    id: Int8!
    timestamp: Timestamp!
    volume: BigInt! @aggregate(fn: "sum", arg: "volume")
    trades: Int8! @aggregate(fn: "count")
  }
  ```

  ```sql Generated Tables theme={null}
  -- Hour interval table
  CREATE TABLE sgd42.trade_stats_hour(
      id        int8 not null,
      timestamp timestamp not null,
      volume    numeric not null,
      trades    int8 not null,
      block$    int not null
  )

  -- Day interval table
  CREATE TABLE sgd42.trade_stats_day(
      id        int8 not null,
      timestamp timestamp not null,
      volume    numeric not null,
      trades    int8 not null,
      block$    int not null
  )
  ```
</CodeGroup>

<Note>
  Aggregation tables are never updated, only appended to. They do not support mutable entities.
</Note>

## Type Mapping

### ID Column Types

<Accordion title="Supported ID Types">
  * **`ID`**: Stored as `text` (alias for `String` for historical reasons)
  * **`String`**: Stored as `text`
  * **`Bytes`**: Stored as `bytea`
</Accordion>

### Primitive Types

<CodeGroup>
  ```yaml GraphQL to SQL Type Mapping theme={null}
  GraphQL Type    → PostgreSQL Type
  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  String          → text
  Int             → int4
  Int8            → int8
  BigInt          → numeric
  BigDecimal      → numeric
  Bytes           → bytea
  Boolean         → boolean
  Timestamp       → timestamp
  ```
</CodeGroup>

### Reference Types

<Accordion title="Entity References">
  When an attribute references another entity, the column type matches the referenced entity's `id` type.

  **No Foreign Keys**: Graph Node does not use foreign key constraints. This allows:

  * Storing entities that reference entities created later
  * Avoiding constraint checking overhead during indexing

  **Trade-offs**:

  * Foreign key violations only detected at query time
  * References may return no results if target entity doesn't exist
</Accordion>

<CodeGroup>
  ```graphql Entity References theme={null}
  type Account @entity {
    id: ID!
    owner: Bytes!
  }

  type Token @entity {
    id: ID!
    holder: Account!  # References Account.id
  }
  ```

  ```sql Generated Columns theme={null}
  -- Account table
  id text not null

  -- Token table
  id     text not null
  holder text not null  -- Same type as Account.id
  ```
</CodeGroup>

### Enum Types

<CodeGroup>
  ```graphql GraphQL Enum theme={null}
  enum Status {
    PENDING
    ACTIVE
    COMPLETED
  }

  type Order @entity {
    id: ID!
    status: Status!
  }
  ```

  ```sql Generated Enum and Column theme={null}
  -- Create enum type
  CREATE TYPE sgd42.status AS ENUM ('PENDING', 'ACTIVE', 'COMPLETED');

  -- Use in table
  CREATE TABLE sgd42.order(
      vid         int8 serial primary key,
      id          text not null,
      status      sgd42.status not null,
      block_range int4range not null
  )
  ```
</CodeGroup>

### Array Types

List types in GraphQL become PostgreSQL array types:

<CodeGroup>
  ```graphql Array Fields theme={null}
  type Entity @entity {
    id: ID!
    tags: [String!]!
    values: [BigInt!]!
  }
  ```

  ```sql Array Columns theme={null}
  CREATE TABLE sgd42.entity(
      vid         int8 serial primary key,
      id          text not null,
      tags        text[] not null,
      values      numeric[] not null,
      block_range int4range not null
  )
  ```
</CodeGroup>

<Note>
  Nested arrays like `[[String]]` are not allowed in GraphQL schemas. Arrays only contain primitive types or entity references.
</Note>

## Indexing Strategy

<Note>
  Graph Node builds indexes extensively due to not knowing query patterns ahead of time. This leads to significant overindexing, but both reducing overindexing and supporting custom indexes are open development areas.
</Note>

### Indexes for Mutable Entities

<Accordion title="Exclusion Index">
  **Purpose**: Ensures that different versions of the same entity have disjoint block ranges.

  ```sql theme={null}
  CREATE EXCLUDE INDEX ON sgd42.entity 
  USING gist (id WITH =, block_range WITH &&)
  ```

  This prevents overlapping versions of the same entity ID.
</Accordion>

<Accordion title="BRIN Index">
  **Purpose**: Speeds up operations with good data locality (e.g., reversion).

  ```sql theme={null}
  CREATE INDEX ON sgd42.entity 
  USING brin (
      lower(block_range), 
      COALESCE(upper(block_range), 2147483647), 
      vid
  )
  ```

  Particularly effective for tables where entities are never updated or deleted.
</Accordion>

### Indexes for Immutable/Timeseries Entities

<CodeGroup>
  ```sql Unique ID Index theme={null}
  CREATE UNIQUE INDEX ON sgd42.transfer (id)
  ```

  ```sql BRIN Block Index theme={null}
  CREATE INDEX ON sgd42.transfer 
  USING brin (block$, vid)
  ```
</CodeGroup>

### Attribute Indexes

Graph Node creates indexes for each attribute with a naming convention:

<Accordion title="Index Naming: attr_N_M">
  * **N**: Number of the entity type in the GraphQL schema
  * **M**: Number of the attribute within that type

  Example: `attr_3_1` for the second attribute of the fourth entity type (zero-indexed for attributes, but entity types start from 0)
</Accordion>

<CodeGroup>
  ```sql Primitive Type Index theme={null}
  -- BTree index for primitive attributes
  CREATE INDEX attr_2_3 ON sgd42.account 
  USING btree (balance)
  ```

  ```sql Reference Type Index theme={null}
  -- GiST index for entity references
  CREATE INDEX attr_5_1 ON sgd42.token 
  USING gist (holder, block_range)
  ```
</CodeGroup>

### String Attribute Indexes

Large string attributes require special handling:

<Accordion title="String Prefix Indexing">
  **Problem**: PostgreSQL limits individual index entry sizes.

  **Solution**: Index only the prefix of string attributes.

  ```sql theme={null}
  CREATE INDEX attr_1_2 ON sgd42.entity 
  USING btree (left(name, STRING_PREFIX_SIZE))
  ```

  **Query Generation**: Automatically adds prefix clauses to queries:

  ```sql theme={null}
  -- User query looking for name = 'Hamming'
  SELECT * FROM entity 
  WHERE left(name, STRING_PREFIX_SIZE) = 'Hamming'
    AND name = 'Hamming'
  ```

  The prefix clause allows index usage while the full comparison ensures correctness.
</Accordion>

## Known Issues and Limitations

<Accordion title="Array Performance">
  **Issue**: Storing arrays as PostgreSQL array attributes can have catastrophically bad performance.

  **Condition**: Arrays with unbounded or large sizes.

  **Impact**: Slow queries and storage bloat.

  **Mitigation**: Keep array sizes small and bounded.
</Accordion>

<Accordion title="Overindexing">
  **Issue**: Extensive indexing leads to:

  * Large storage requirements for indexes
  * Slower write operations

  **Status**: Open issue - reducing overindexing is an active development area.

  **Mitigation**: Manual index optimization (see Custom Indexes below).
</Accordion>

<Accordion title="Index Usability for Sorting">
  **Issue**: BTree indexes on single attributes aren't always usable for sorting.

  **Reason**: Graph Node adds `id` to all `ORDER BY` clauses for deterministic ordering.

  ```sql theme={null}
  -- User query
  ORDER BY name

  -- Actual SQL
  ORDER BY name, id
  ```

  A single-column index on `name` cannot efficiently support this multi-column sort.

  **Future**: PostgreSQL 13's incremental sorting may help.
</Accordion>

<Accordion title="Custom Indexes">
  **Issue**: No built-in support for custom indexes makes it hard to transfer manually created indexes between subgraph versions.

  **Convention**: Manually created indexes should start with `manual_` prefix.

  ```sql theme={null}
  CREATE INDEX manual_account_owner_balance 
  ON sgd42.account (owner, balance)
  ```

  **Status**: Custom index support is an open development area.
</Accordion>

## Best Practices

### Schema Design

<CodeGroup>
  ```graphql Prefer Immutable When Possible theme={null}
  # Use immutable for entities that never change
  type Transfer @entity(immutable: true) {
    id: ID!
    from: Bytes!
    to: Bytes!
    value: BigInt!
  }

  # Benefits:
  # - Simpler storage structure
  # - More efficient indexes
  # - Better query performance
  ```

  ```graphql Limit Array Sizes theme={null}
  # Avoid unbounded arrays
  type BadExample @entity {
    id: ID!
    allTransfers: [Bytes!]!  # Could grow indefinitely
  }

  # Better approach: Use relationships
  type Transaction @entity {
    id: ID!
    transfers: [Transfer!]! @derivedFrom(field: "transaction")
  }

  type Transfer @entity(immutable: true) {
    id: ID!
    transaction: Transaction!
    # ... other fields
  }
  ```
</CodeGroup>

### Performance Optimization

1. **Use immutable entities** for data that never changes
2. **Avoid large arrays** in entity fields
3. **Consider manual indexes** for common query patterns (prefix with `manual_`)
4. **Monitor index usage** and remove unused indexes
5. **Use appropriate ID types** (Bytes for addresses, String for human-readable IDs)