Repository: ponder-sh/ponder
Pull request #844 (2024-04-29 20:33:13)

`PoolConfig` option


Patch:
diff --git a/.changeset/slimy-ghosts-build.md b/.changeset/slimy-ghosts-build.md
new file mode 100644
index 00000000..d26e3515
--- /dev/null
+++ b/.changeset/slimy-ghosts-build.md
@@ -0,0 +1,5 @@
+---
+"@ponder/core": patch
+---
+
+Added a `poolConfig` option to `ponder.config.ts`. This option overrides the default [`PoolConfig`](https://node-postgres.com/apis/pool) used when constructing the `node-postgres` connection pool.
diff --git a/docs/pages/docs/api-reference/config.mdx b/docs/pages/docs/api-reference/config.mdx
index 17f5ef07..2e92fb30 100644
--- a/docs/pages/docs/api-reference/config.mdx
+++ b/docs/pages/docs/api-reference/config.mdx
@@ -91,7 +91,7 @@ The `contracts` field is an object where each key is a contract name containing
 | **endBlock**      |    `number \| undefined`     | **Default: `undefined`**. Block number to stop syncing events. If this field is specified, the contract will not be indexed in realtime. This field can be used alongside `startBlock` to index a specific block range.  |
 | **maxBlockRange** |    `number \| undefined`     | The maximum block range that Ponder will use when calling `eth_getLogs` during the historical sync. If not provided, Ponder uses a sane default based on the network.                                                    |
 
-```ts filename="ponder.config.ts" {17-20}
+```ts filename="ponder.config.ts" {14-19}
 import { createConfig } from "@ponder/core";
 import { http } from "viem";
 
@@ -152,43 +152,49 @@ For more details, see the guide on [production deployments](/docs/production/dep
   <summary><p>Example `ponder.config.ts` using SQLite</p></summary>
   <p />
 
-  ```ts filename="ponder.config.ts" {4-7}
-  import { createConfig } from "@ponder/core"
+```ts filename="ponder.config.ts" {4-7}
+import { createConfig } from "@ponder/core";
+
+export default createConfig({
+  database: {
+    kind: "sqlite",
+    directory: "./.ponder/sqlite2",
+  },
+  // ... more config
+});
+```
 
-  export default createConfig({
-    database: {
-      kind: "sqlite",
-      directory: "./.ponder/sqlite2",
-    },
-    // ... more config
-  });
-  ```
 </details>
 
 ### Postgres
 
-| field                |         type          |                                                                                                     |
-| :------------------- | :-------------------: | :-------------------------------------------------------------------------------------------------- |
-| **kind**             |     `"postgres"`      |                                                                                                     |
-| **connectionString** | `string \| undefined` | **Default: `DATABASE_PRIVATE_URL` or `DATABASE_URL` env var**. Postgres database connection string. |
-| **schema**           | `string \| undefined` | **Default: `"public"`**. Postgres schema to use for indexed data. |
+| field                |           type            |                                                                                                                  |
+| :------------------- | :-----------------------: | :--------------------------------------------------------------------------------------------------------------- |
+| **kind**             |       `"postgres"`        |                                                                                                                  |
+| **connectionString** |   `string \| undefined`   | **Default: `DATABASE_PRIVATE_URL` or `DATABASE_URL` env var**. Postgres database connection string.              |
+| **schema**           |   `string \| undefined`   | **Default: `"public"`**. Postgres schema to use for indexed data.                                                |
+| **poolConfig**       | [`PoolConfig`](https://node-postgres.com/apis/pool) \| `undefined` | **Default: `{ max: 30 }`**. Pool configuration passed to `node-postgres`. |
 
 <details>
   <summary><p>Example `ponder.config.ts` using Postgres</p></summary>
   <p />
 
-  ```ts filename="ponder.config.ts" {4-8}
-  import { createConfig } from "@ponder/core"
+```ts filename="ponder.config.ts" {4-10}
+import { createConfig } from "@ponder/core";
 
-  export default createConfig({
-    database: {
-      kind: "postgres",
-      connectionString: "postgresql://user:password@localhost:5432/dbname",
-      schema: "ponder_dev",
+export default createConfig({
+  database: {
+    kind: "postgres",
+    connectionString: "postgresql://user:password@localhost:5432/dbname",
+    schema: "ponder_dev",
+    poolConfig: {
+      max: 100,
     },
-    // ... more config
-  });
-  ```
+  },
+  // ... more config
+});
+```
+
 </details>
 
 ## Options
diff --git a/packages/core/src/bin/commands/serve.ts b/packages/core/src/bin/commands/serve.ts
index c41914b2..18f8c91f 100644
--- a/packages/core/src/bin/commands/serve.ts
+++ b/packages/core/src/bin/commands/serve.ts
@@ -6,7 +6,7 @@ import { buildOptions } from "@/common/options.js";
 import { buildPayload, createTelemetry } from "@/common/telemetry.js";
 import { PostgresDatabaseService } from "@/database/postgres/service.js";
 import type { NamespaceInfo } from "@/database/service.js";
-import { getReadIndexingStore } from "@/indexing-store/readStore.js";
+import { getReadonlyStore } from "@/indexing-store/readonly.js";
 import { createServer } from "@/server/service.js";
 import type { CliOptions } from "../ponder.js";
 import { setupShutdown } from "../utils/shutdown.js";
@@ -87,9 +87,12 @@ export async function serve({ cliOptions }: { cliOptions: CliOptions }) {
     common,
     poolConfig,
     userNamespace,
+    // Ensures that the `readonly` connection pool gets
+    // allocated the maximum number of connections.
+    isReadonly: true,
   });
 
-  const indexingStore = getReadIndexingStore({
+  const readonlyStore = getReadonlyStore({
     kind: "postgres",
     schema,
     // Note: `ponder serve` serves data from the `publishSchema`. Also, it does
@@ -98,10 +101,10 @@ export async function serve({ cliOptions }: { cliOptions: CliOptions }) {
     namespaceInfo: {
       userNamespace: databaseConfig.publishSchema,
     } as unknown as NamespaceInfo,
-    db: database.indexingDb,
+    db: database.readonlyDb,
   });
 
-  const server = await createServer({ graphqlSchema, indexingStore, common });
+  const server = await createServer({ graphqlSchema, common, readonlyStore });
   server.setHealthy();
 
   cleanupReloadable = async () => {
diff --git a/packages/core/src/bin/utils/run.ts b/packages/core/src/bin/utils/run.ts
index 10131859..b72c5e69 100644
--- a/packages/core/src/bin/utils/run.ts
+++ b/packages/core/src/bin/utils/run.ts
@@ -4,9 +4,9 @@ import type { Common } from "@/common/common.js";
 import { PostgresDatabaseService } from "@/database/postgres/service.js";
 import type { DatabaseService, NamespaceInfo } from "@/database/service.js";
 import { SqliteDatabaseService } from "@/database/sqlite/service.js";
-import { getHistoricalIndexingStore } from "@/indexing-store/historicalStore.js";
-import { getReadIndexingStore } from "@/indexing-store/readStore.js";
-import { getRealtimeIndexingStore } from "@/indexing-store/realtimeStore.js";
+import { getHistoricalStore } from "@/indexing-store/historical.js";
+import { getReadonlyStore } from "@/indexing-store/readonly.js";
+import { getRealtimeStore } from "@/indexing-store/realtime.js";
 import type { IndexingStore } from "@/indexing-store/store.js";
 import { createIndexingService } from "@/indexing/index.js";
 import { createServer } from "@/server/service.js";
@@ -81,22 +81,14 @@ export async function run({
     syncStore = new PostgresSyncStore({ db: database.syncDb });
   }
 
-  let indexingStore: IndexingStore = {
-    ...getReadIndexingStore({
-      kind: database.kind,
-      schema,
-      namespaceInfo,
-      db: database.indexingDb,
-    }),
-    ...getHistoricalIndexingStore({
-      kind: database.kind,
-      schema,
-      namespaceInfo,
-      db: database.indexingDb,
-    }),
-  };
+  const readonlyStore = getReadonlyStore({
+    kind: database.kind,
+    schema,
+    namespaceInfo,
+    db: database.readonlyDb,
+  });
 
-  const server = await createServer({ common, graphqlSchema, indexingStore });
+  const server = await createServer({ common, graphqlSchema, readonlyStore });
 
   // This can be a long-running operation, so it's best to do it after
   // starting the server so the app can become responsive more quickly.
@@ -160,6 +152,21 @@ export async function run({
     },
   });
 
+  let indexingStore: IndexingStore = {
+    ...getReadonlyStore({
+      kind: database.kind,
+      schema,
+      namespaceInfo,
+      db: database.indexingDb,
+    }),
+    ...getHistoricalStore({
+      kind: database.kind,
+      schema,
+      namespaceInfo,
+      db: database.indexingDb,
+    }),
+  };
+
   let indexingService = createIndexingService({
     indexingFunctions,
     common,
@@ -217,13 +224,13 @@ export async function run({
     });
 
     indexingStore = {
-      ...getReadIndexingStore({
+      ...getReadonlyStore({
         kind: database.kind,
         schema,
         namespaceInfo,
         db: database.indexingDb,
       }),
-      ...getRealtimeIndexingStore({
+      ...getRealtimeStore({
         kind: database.kind,
         schema,
         namespaceInfo,
diff --git a/packages/core/src/build/configAndIndexingFunctions.ts b/packages/core/src/build/configAndIndexingFunctions.ts
index 01ccaead..a33d613a 100644
--- a/packages/core/src/build/configAndIndexingFunctions.ts
+++ b/packages/core/src/build/configAndIndexingFunctions.ts
@@ -124,9 +124,14 @@ export async function buildConfigAndIndexingFunctions({
         );
       }
 
+      const poolConfig = {
+        max: config.database.poolConfig?.max ?? 30,
+        connectionString,
+      };
+
       databaseConfig = {
         kind: "postgres",
-        poolConfig: { connectionString },
+        poolConfig,
         schema,
         publishSchema,
       };
@@ -202,9 +207,11 @@ export async function buildConfigAndIndexingFunctions({
         );
       }
 
+      const poolConfig = { max: 30, connectionString };
+
       databaseConfig = {
         kind: "postgres",
-        poolConfig: { connectionString },
+        poolConfig,
         schema,
         publishSchema,
       };
diff --git a/packages/core/src/config/config.ts b/packages/core/src/config/config.ts
index 0471f33e..af385bf0 100644
--- a/packages/core/src/config/config.ts
+++ b/packages/core/src/config/config.ts
@@ -28,6 +28,11 @@ type DatabaseConfig =
       schema?: string;
       /** Postgres schema to use for views returning indexed data. Default: undefined, or `RAILWAY_SERVICE_NAME` environment variable if provided. */
       publishSchema?: string;
+      /** Postgres pool configuration passed to `node-postgres`. */
+      poolConfig?: {
+        /** Maximum number of clients in the pool. Default: `30`. */
+        max?: number;
+      };
     };
 
 export type OptionConfig = {
diff --git a/packages/core/src/config/database.ts b/packages/core/src/config/database.ts
index 38bab845..54f1c815 100644
--- a/packages/core/src/config/database.ts
+++ b/packages/core/src/config/database.ts
@@ -1,4 +1,7 @@
-import type { PoolConfig } from "pg";
+import type { Prettify } from "@/types/utils.js";
+import type { PoolConfig as RawPoolConfig } from "pg";
+
+export type PoolConfig = Prettify<RawPoolConfig & { max: number }>;
 
 export type DatabaseConfig =
   | { kind: "sqlite"; directory: string }
diff --git a/packages/core/src/database/postgres/service.ts b/packages/core/src/database/postgres/service.ts
index 5b1d5698..4c9ddf25 100644
--- a/packages/core/src/database/postgres/service.ts
+++ b/packages/core/src/database/postgres/service.ts
@@ -1,5 +1,6 @@
 import type { Common } from "@/common/common.js";
 import { NonRetryableError } from "@/common/errors.js";
+import type { PoolConfig } from "@/config/database.js";
 import type { Schema } from "@/schema/types.js";
 import { isEnumColumn, isManyColumn, isOneColumn } from "@/schema/utils.js";
 import type { SyncStoreTables } from "@/sync-store/postgres/encoding.js";
@@ -24,7 +25,7 @@ import {
   WithSchemaPlugin,
   sql,
 } from "kysely";
-import type { Pool, PoolConfig } from "pg";
+import type { Pool } from "pg";
 import prometheus from "prom-client";
 import { HeadlessKysely } from "../kysely.js";
 import { revertIndexingTables } from "../revert.js";
@@ -40,58 +41,92 @@ export class PostgresDatabaseService implements BaseDatabaseService {
   private internalNamespace = "ponder";
 
   private common: Common;
-  private poolConfig: PoolConfig;
   private userNamespace: string;
   private publishSchema?: string | undefined;
 
   db: HeadlessKysely<InternalTables>;
-  indexingDb: HeadlessKysely<any>;
   syncDb: HeadlessKysely<SyncStoreTables>;
+  indexingDb: HeadlessKysely<any>;
+  readonlyDb: HeadlessKysely<any>;
 
   private schema: Schema = null!;
   private buildId: string = null!;
   private heartbeatInterval?: NodeJS.Timeout;
 
   // Only need these for metrics.
-  private adminPool: Pool;
-  private indexingPool: Pool;
+  private internalPool: Pool;
   private syncPool: Pool;
+  private indexingPool: Pool;
+  private readonlyPool: Pool;
 
   constructor({
     common,
     poolConfig,
     userNamespace,
     publishSchema,
+    isReadonly = false,
   }: {
     common: Common;
     poolConfig: PoolConfig;
     userNamespace: string;
     publishSchema?: string | undefined;
+    isReadonly?: boolean;
   }) {
     this.common = common;
-    this.poolConfig = poolConfig;
     this.userNamespace = userNamespace;
     this.publishSchema = publishSchema;
 
-    this.adminPool = createPool({
+    const internalMax = 2;
+    const equalMax = Math.floor((poolConfig.max - internalMax) / 3);
+    const [readonlyMax, indexingMax, syncMax] = isReadonly
+      ? [poolConfig.max - internalMax, 0, 0]
+      : [equalMax, equalMax, equalMax];
+
+    this.internalPool = createPool({
+      ...poolConfig,
+      application_name: `${userNamespace}_internal`,
+      max: internalMax,
+      statement_timeout: 10 * 60 * 1000, // 10 minutes to accommodate slow sync store migrations.
+    });
+    this.syncPool = createPool({
+      ...poolConfig,
+      application_name: `${userNamespace}_sync`,
+      max: readonlyMax,
+    });
+    this.indexingPool = createPool({
       ...poolConfig,
-      // 10 minutes to accommodate slow sync store migrations.
-      statement_timeout: 10 * 60 * 1000,
+      application_name: `${userNamespace}_indexing`,
+      max: indexingMax,
+    });
+    this.readonlyPool = createPool({
+      ...poolConfig,
+      application_name: `${userNamespace}_readonly`,
+      max: syncMax,
     });
-    this.indexingPool = createPool(this.poolConfig);
-    this.syncPool = createPool(this.poolConfig);
 
     this.db = new HeadlessKysely<InternalTables>({
-      name: "admin",
+      name: "internal",
       common,
-      dialect: new PostgresDialect({ pool: this.adminPool }),
+      dialect: new PostgresDialect({ pool: this.internalPool }),
       log(event) {
         if (event.level === "query") {
-          common.metrics.ponder_postgres_query_total.inc({ pool: "admin" });
+          common.metrics.ponder_postgres_query_total.inc({ pool: "internal" });
         }
       },
     });
 
+    this.syncDb = new HeadlessKysely<SyncStoreTables>({
+      name: "sync",
+      common,
+      dialect: new PostgresDialect({ pool: this.syncPool }),
+      log(event) {
+        if (event.level === "query") {
+          common.metrics.ponder_postgres_query_total.inc({ pool: "sync" });
+        }
+      },
+      plugins: [new WithSchemaPlugin("ponder_sync")],
+    });
+
     this.indexingDb = new HeadlessKysely<InternalTables>({
       name: "indexing",
       common,
@@ -103,16 +138,15 @@ export class PostgresDatabaseService implements BaseDatabaseService {
       },
     });
 
-    this.syncDb = new HeadlessKysely<SyncStoreTables>({
-      name: "sync",
+    this.readonlyDb = new HeadlessKysely<InternalTables>({
+      name: "readonly",
       common,
-      dialect: new PostgresDialect({ pool: this.syncPool }),
+      dialect: new PostgresDialect({ pool: this.readonlyPool }),
       log(event) {
         if (event.level === "query") {
-          common.metrics.ponder_postgres_query_total.inc({ pool: "sync" });
+          common.metrics.ponder_postgres_query_total.inc({ pool: "readonly" });
         }
       },
-      plugins: [new WithSchemaPlugin("ponder_sync")],
     });
 
     this.registerMetrics();
@@ -446,13 +480,15 @@ export class PostgresDatabaseService implements BaseDatabaseService {
         msg: `Released lock on namespace '${this.userNamespace}'`,
       });
 
+      await this.readonlyDb.destroy();
       await this.indexingDb.destroy();
       await this.syncDb.destroy();
       await this.db.destroy();
 
+      await this.readonlyPool.end();
       await this.indexingPool.end();
       await this.syncPool.end();
-      await this.adminPool.end();
+      await this.internalPool.end();
 
       this.common.logger.debug({
         service: "database",
@@ -584,25 +620,36 @@ export class PostgresDatabaseService implements BaseDatabaseService {
         registers: [this.common.metrics.registry],
         collect() {
           this.set(
-            { pool: "indexing", kind: "idle" },
-            service.indexingPool.idleCount,
+            { pool: "internal", kind: "idle" },
+            service.internalPool.idleCount,
+          );
+          this.set(
+            { pool: "internal", kind: "total" },
+            service.internalPool.totalCount,
           );
+
           this.set({ pool: "sync", kind: "idle" }, service.syncPool.idleCount);
           this.set(
-            { pool: "admin", kind: "idle" },
-            service.adminPool.idleCount,
+            { pool: "sync", kind: "total" },
+            service.syncPool.totalCount,
+          );
+
+          this.set(
+            { pool: "indexing", kind: "idle" },
+            service.indexingPool.idleCount,
           );
           this.set(
             { pool: "indexing", kind: "total" },
             service.indexingPool.totalCount,
           );
+
           this.set(
-            { pool: "sync", kind: "total" },
-            service.syncPool.totalCount,
+            { pool: "readonly", kind: "idle" },
+            service.readonlyPool.idleCount,
           );
           this.set(
-            { pool: "admin", kind: "total" },
-            service.adminPool.totalCount,
+            { pool: "readonly", kind: "total" },
+            service.readonlyPool.totalCount,
           );
         },
       },
@@ -618,9 +665,10 @@ export class PostgresDatabaseService implements BaseDatabaseService {
         labelNames: ["pool"] as const,
         registers: [this.common.metrics.registry],
         collect() {
-          this.set({ pool: "indexing" }, service.indexingPool.waitingCount);
+          this.set({ pool: "internal" }, service.internalPool.waitingCount);
           this.set({ pool: "sync" }, service.syncPool.waitingCount);
-          this.set({ pool: "admin" }, service.adminPool.waitingCount);
+          this.set({ pool: "indexing" }, service.indexingPool.waitingCount);
+          this.set({ pool: "readonly" }, service.readonlyPool.waitingCount);
         },
       },
     );
diff --git a/packages/core/src/database/sqlite/service.ts b/packages/core/src/database/sqlite/service.ts
index 2efe8ece..777bebbb 100644
--- a/packages/core/src/database/sqlite/service.ts
+++ b/packages/core/src/database/sqlite/service.ts
@@ -45,6 +45,7 @@ export class SqliteDatabaseService implements BaseDatabaseService {
   private syncDatabase: SqliteDatabase;
 
   db: HeadlessKysely<InternalTables>;
+  readonlyDb: HeadlessKysely<any>;
   indexingDb: HeadlessKysely<any>;
   syncDb: HeadlessKysely<SyncStoreTables>;
 
@@ -81,7 +82,7 @@ export class SqliteDatabaseService implements BaseDatabaseService {
     );
 
     this.db = new HeadlessKysely<InternalTables>({
-      name: "admin",
+      name: "internal",
       common,
       dialect: new SqliteDialect({ database: this.internalDatabase }),
       log(event) {
@@ -93,6 +94,19 @@ export class SqliteDatabaseService implements BaseDatabaseService {
       },
     });
 
+    const syncDatabaseFile = path.join(directory, "ponder_sync.db");
+    this.syncDatabase = createSqliteDatabase(syncDatabaseFile);
+    this.syncDb = new HeadlessKysely<SyncStoreTables>({
+      name: "sync",
+      common,
+      dialect: new SqliteDialect({ database: this.syncDatabase }),
+      log(event) {
+        if (event.level === "query") {
+          common.metrics.ponder_sqlite_query_total.inc({ database: "sync" });
+        }
+      },
+    });
+
     this.indexingDb = new HeadlessKysely<InternalTables>({
       name: "indexing",
       common,
@@ -106,15 +120,15 @@ export class SqliteDatabaseService implements BaseDatabaseService {
       },
     });
 
-    const syncDatabaseFile = path.join(directory, "ponder_sync.db");
-    this.syncDatabase = createSqliteDatabase(syncDatabaseFile);
-    this.syncDb = new HeadlessKysely<SyncStoreTables>({
-      name: "sync",
+    this.readonlyDb = new HeadlessKysely<InternalTables>({
+      name: "readonly",
       common,
-      dialect: new SqliteDialect({ database: this.syncDatabase }),
+      dialect: new SqliteDialect({ database: this.internalDatabase }),
       log(event) {
         if (event.level === "query") {
-          common.metrics.ponder_sqlite_query_total.inc({ database: "sync" });
+          common.metrics.ponder_sqlite_query_total.inc({
+            database: "readonly",
+          });
         }
       },
     });
@@ -365,6 +379,7 @@ export class SqliteDatabaseService implements BaseDatabaseService {
         msg: `Released lock on namespace '${this.userNamespace}'`,
       });
 
+      await this.readonlyDb.destroy();
       await this.indexingDb.destroy();
       await this.syncDb.destroy();
       await this.db.destroy();
diff --git a/packages/core/src/indexing-store/historicalStore.test.ts b/packages/core/src/indexing-store/historical.test.ts
similarity index 100%
rename from packages/core/src/indexing-store/historicalStore.test.ts
rename to packages/core/src/indexing-store/historical.test.ts
diff --git a/packages/core/src/indexing-store/historicalStore.ts b/packages/core/src/indexing-store/historical.ts
similarity index 98%
rename from packages/core/src/indexing-store/historicalStore.ts
rename to packages/core/src/indexing-store/historical.ts
index 8c617a14..aa65e091 100644
--- a/packages/core/src/indexing-store/historicalStore.ts
+++ b/packages/core/src/indexing-store/historical.ts
@@ -2,14 +2,14 @@ import { HeadlessKysely } from "@/database/kysely.js";
 import type { NamespaceInfo } from "@/database/service.js";
 import type { Schema } from "@/schema/types.js";
 import { sql } from "kysely";
-import type { Row, WhereInput, WriteIndexingStore } from "./store.js";
+import type { Row, WhereInput, WriteStore } from "./store.js";
 import { decodeRow, encodeRow, encodeValue } from "./utils/encoding.js";
 import { parseStoreError } from "./utils/errors.js";
 import { buildWhereConditions } from "./utils/filter.js";
 
 const MAX_BATCH_SIZE = 1_000 as const;
 
-export const getHistoricalIndexingStore = ({
+export const getHistoricalStore = ({
   kind,
   schema,
   namespaceInfo,
@@ -19,7 +19,7 @@ export const getHistoricalIndexingStore = ({
   schema: Schema;
   namespaceInfo: NamespaceInfo;
   db: HeadlessKysely<any>;
-}): WriteIndexingStore<"historical"> => ({
+}): WriteStore<"historical"> => ({
   create: async ({
     tableName,
     id,
diff --git a/packages/core/src/indexing-store/readStore.test.ts b/packages/core/src/indexing-store/readonly.test.ts
similarity index 100%
rename from packages/core/src/indexing-store/readStore.test.ts
rename to packages/core/src/indexing-store/readonly.test.ts
diff --git a/packages/core/src/indexing-store/readStore.ts b/packages/core/src/indexing-store/readonly.ts
similarity index 98%
rename from packages/core/src/indexing-store/readStore.ts
rename to packages/core/src/indexing-store/readonly.ts
index 70650803..731b7c0e 100644
--- a/packages/core/src/indexing-store/readStore.ts
+++ b/packages/core/src/indexing-store/readonly.ts
@@ -3,7 +3,7 @@ import type { HeadlessKysely } from "@/database/kysely.js";
 import type { NamespaceInfo } from "@/database/service.js";
 import type { Schema } from "@/schema/types.js";
 import { sql } from "kysely";
-import type { OrderByInput, ReadIndexingStore, WhereInput } from "./store.js";
+import type { OrderByInput, ReadonlyStore, WhereInput } from "./store.js";
 import {
   buildCursorConditions,
   decodeCursor,
@@ -19,7 +19,7 @@ import {
 const DEFAULT_LIMIT = 50 as const;
 const MAX_LIMIT = 1_000 as const;
 
-export const getReadIndexingStore = ({
+export const getReadonlyStore = ({
   kind,
   schema,
   namespaceInfo,
@@ -29,7 +29,7 @@ export const getReadIndexingStore = ({
   schema: Schema;
   namespaceInfo: NamespaceInfo;
   db: HeadlessKysely<any>;
-}): ReadIndexingStore => ({
+}): ReadonlyStore => ({
   findUnique: async ({
     tableName,
     id,
diff --git a/packages/core/src/indexing-store/realtimeStore.test.ts b/packages/core/src/indexing-store/realtime.test.ts
similarity index 100%
rename from packages/core/src/indexing-store/realtimeStore.test.ts
rename to packages/core/src/indexing-store/realtime.test.ts
diff --git a/packages/core/src/indexing-store/realtimeStore.ts b/packages/core/src/indexing-store/realtime.ts
similarity index 98%
rename from packages/core/src/indexing-store/realtimeStore.ts
rename to packages/core/src/indexing-store/realtime.ts
index 5fc158e1..ad9f3d57 100644
--- a/packages/core/src/indexing-store/realtimeStore.ts
+++ b/packages/core/src/indexing-store/realtime.ts
@@ -1,14 +1,14 @@
 import type { HeadlessKysely } from "@/database/kysely.js";
 import type { NamespaceInfo } from "@/database/service.js";
 import type { Schema } from "@/schema/types.js";
-import type { Row, WhereInput, WriteIndexingStore } from "./store.js";
+import type { Row, WhereInput, WriteStore } from "./store.js";
 import { decodeRow, encodeRow, encodeValue } from "./utils/encoding.js";
 import { parseStoreError } from "./utils/errors.js";
 import { buildWhereConditions } from "./utils/filter.js";
 
 const MAX_BATCH_SIZE = 1_000 as const;
 
-export const getRealtimeIndexingStore = ({
+export const getRealtimeStore = ({
   kind,
   schema,
   namespaceInfo,
@@ -18,7 +18,7 @@ export const getRealtimeIndexingStore = ({
   schema: Schema;
   namespaceInfo: NamespaceInfo;
   db: HeadlessKysely<any>;
-}): WriteIndexingStore<"realtime"> => ({
+}): WriteStore<"realtime"> => ({
   create: ({
     tableName,
     encodedCheckpoint,
diff --git a/packages/core/src/indexing-store/store.ts b/packages/core/src/indexing-store/store.ts
index b68bd0a5..aeafc88d 100644
--- a/packages/core/src/indexing-store/store.ts
+++ b/packages/core/src/indexing-store/store.ts
@@ -1,7 +1,7 @@
 import type { Prettify } from "@/types/utils.js";
 import type { Hex } from "viem";
 
-export type ReadIndexingStore = {
+export type ReadonlyStore = {
   findUnique(options: {
     tableName: string;
     id: string | number | bigint;
@@ -25,16 +25,12 @@ export type ReadIndexingStore = {
   }>;
 };
 
-export type WriteIndexingStore<
+export type WriteStore<
   env extends "historical" | "realtime",
   ///
   checkpointProp = env extends "realtime"
-    ? {
-        encodedCheckpoint: string;
-      }
-    : {
-        encodedCheckpoint?: never;
-      },
+    ? { encodedCheckpoint: string }
+    : { encodedCheckpoint?: never },
 > = {
   create(
     options: {
@@ -92,7 +88,7 @@ export type WriteIndexingStore<
 
 export type IndexingStore<
   env extends "historical" | "realtime" = "historical" | "realtime",
-> = ReadIndexingStore & WriteIndexingStore<env>;
+> = ReadonlyStore & WriteStore<env>;
 
 export type Table = {
   [key: string]:
diff --git a/packages/core/src/server/graphql/buildLoaderCache.ts b/packages/core/src/server/graphql/buildLoaderCache.ts
index f792694b..bfb6fbeb 100644
--- a/packages/core/src/server/graphql/buildLoaderCache.ts
+++ b/packages/core/src/server/graphql/buildLoaderCache.ts
@@ -1,9 +1,9 @@
-import type { ReadIndexingStore } from "@/indexing-store/store.js";
+import type { ReadonlyStore } from "@/indexing-store/store.js";
 import DataLoader from "dataloader";
 
 export type GetLoader = ReturnType<typeof buildLoaderCache>;
 
-export function buildLoaderCache({ store }: { store: ReadIndexingStore }) {
+export function buildLoaderCache({ store }: { store: ReadonlyStore }) {
   const loaderCache: Record<
     string,
     DataLoader<string | number | bigint, any> | undefined
diff --git a/packages/core/src/server/service.ts b/packages/core/src/server/service.ts
index 69bb54ad..70eacf4b 100644
--- a/packages/core/src/server/service.ts
+++ b/packages/core/src/server/service.ts
@@ -1,6 +1,6 @@
 import http from "node:http";
 import type { Common } from "@/common/common.js";
-import type { ReadIndexingStore } from "@/indexing-store/store.js";
+import type { ReadonlyStore } from "@/indexing-store/store.js";
 import { graphiQLHtml } from "@/ui/graphiql.html.js";
 import { graphqlServer } from "@hono/graphql-server";
 import { serve } from "@hono/node-server";
@@ -14,7 +14,7 @@ import {
 } from "./graphql/buildLoaderCache.js";
 
 type Server = {
-  hono: Hono<{ Variables: { store: ReadIndexingStore; getLoader: GetLoader } }>;
+  hono: Hono<{ Variables: { store: ReadonlyStore; getLoader: GetLoader } }>;
   port: number;
   setHealthy: () => void;
   kill: () => Promise<void>;
@@ -22,15 +22,15 @@ type Server = {
 
 export async function createServer({
   graphqlSchema,
-  indexingStore,
+  readonlyStore,
   common,
 }: {
   graphqlSchema: GraphQLSchema;
-  indexingStore: ReadIndexingStore;
+  readonlyStore: ReadonlyStore;
   common: Common;
 }): Promise<Server> {
   const hono = new Hono<{
-    Variables: { store: ReadIndexingStore; getLoader: GetLoader };
+    Variables: { store: ReadonlyStore; getLoader: GetLoader };
   }>();
 
   let port = common.options.port;
@@ -78,9 +78,9 @@ export async function createServer({
       }
 
       if (c.req.method === "POST") {
-        const getLoader = buildLoaderCache({ store: indexingStore });
+        const getLoader = buildLoaderCache({ store: readonlyStore });
 
-        c.set("store", indexingStore);
+        c.set("store", readonlyStore);
         c.set("getLoader", getLoader);
 
         return graphqlServer({
@@ -94,9 +94,9 @@ export async function createServer({
     })
     .use("/", async (c, next) => {
       if (c.req.method === "POST") {
-        const getLoader = buildLoaderCache({ store: indexingStore });
+        const getLoader = buildLoaderCache({ store: readonlyStore });
 
-        c.set("store", indexingStore);
+        c.set("store", readonlyStore);
         c.set("getLoader", getLoader);
 
         return graphqlServer({
diff --git a/packages/core/src/utils/sqlite.ts b/packages/core/src/utils/sqlite.ts
index 050e2e25..2bf7ac55 100644
--- a/packages/core/src/utils/sqlite.ts
+++ b/packages/core/src/utils/sqlite.ts
@@ -68,9 +68,12 @@ function improveSqliteErrors(database: BetterSqlite3.Database) {
 
 export type SqliteDatabase = BetterSqlite3.Database;
 
-export function createSqliteDatabase(file: string): SqliteDatabase {
+export function createSqliteDatabase(
+  file: string,
+  options?: BetterSqlite3.Options,
+): SqliteDatabase {
   ensureDirExists(file);
-  const database = new BetterSqlite3(file);
+  const database = new BetterSqlite3(file, options);
   improveSqliteErrors(database);
   database.pragma("journal_mode = WAL");
   return database;


Test patch:
diff --git a/packages/core/src/_test/setup.ts b/packages/core/src/_test/setup.ts
index 8d516067..0e09eda3 100644
--- a/packages/core/src/_test/setup.ts
+++ b/packages/core/src/_test/setup.ts
@@ -15,10 +15,10 @@ import { PostgresDatabaseService } from "@/database/postgres/service.js";
 import type { DatabaseService, NamespaceInfo } from "@/database/service.js";
 import { SqliteDatabaseService } from "@/database/sqlite/service.js";
 import { createSchema } from "@/index.js";
-import { getHistoricalIndexingStore } from "@/indexing-store/historicalStore.js";
-import { getReadIndexingStore } from "@/indexing-store/readStore.js";
-import { getRealtimeIndexingStore } from "@/indexing-store/realtimeStore.js";
-import type { IndexingStore } from "@/indexing-store/store.js";
+import { getHistoricalStore } from "@/indexing-store/historical.js";
+import { getReadonlyStore } from "@/indexing-store/readonly.js";
+import { getRealtimeStore } from "@/indexing-store/realtime.js";
+import type { IndexingStore, ReadonlyStore } from "@/indexing-store/store.js";
 import { PostgresSyncStore } from "@/sync-store/postgres/store.js";
 import { SqliteSyncStore } from "@/sync-store/sqlite/store.js";
 import type { SyncStore } from "@/sync-store/store.js";
@@ -77,7 +77,7 @@ export async function setupIsolatedDatabase(context: TestContext) {
     const databaseUrl = new URL(process.env.DATABASE_URL);
     databaseUrl.pathname = `/${databaseName}`;
 
-    const poolConfig = { connectionString: databaseUrl.toString() };
+    const poolConfig = { max: 30, connectionString: databaseUrl.toString() };
 
     const client = new pg.Client({
       connectionString: process.env.DATABASE_URL,
@@ -124,6 +124,7 @@ export async function setupDatabaseServices(
   namespaceInfo: NamespaceInfo;
   syncStore: SyncStore;
   indexingStore: IndexingStore;
+  readonlyStore: ReadonlyStore;
   cleanup: () => Promise<void>;
 }> {
   const config = { ...defaultDatabaseServiceSetup, ...overrides };
@@ -138,21 +139,23 @@ export async function setupDatabaseServices(
 
     await database.migrateSyncStore();
 
+    const syncStore = new SqliteSyncStore({ db: database.syncDb });
+
     const indexingStore = {
-      ...getReadIndexingStore({
+      ...getReadonlyStore({
         kind: "sqlite",
         schema: config.schema,
         namespaceInfo: result.namespaceInfo,
         db: database.indexingDb,
       }),
       ...(config.indexing === "historical"
-        ? getHistoricalIndexingStore({
+        ? getHistoricalStore({
             kind: "sqlite",
             schema: config.schema,
             namespaceInfo: result.namespaceInfo,
             db: database.indexingDb,
           })
-        : getRealtimeIndexingStore({
+        : getRealtimeStore({
             kind: "sqlite",
             schema: config.schema,
             namespaceInfo: result.namespaceInfo,
@@ -160,13 +163,19 @@ export async function setupDatabaseServices(
           })),
     };
 
-    const syncStore = new SqliteSyncStore({ db: database.syncDb });
+    const readonlyStore = getReadonlyStore({
+      kind: "sqlite",
+      schema: config.schema,
+      namespaceInfo: result.namespaceInfo,
+      db: database.readonlyDb,
+    });
 
     const cleanup = () => database.kill();
 
     return {
       database,
       namespaceInfo: result.namespaceInfo,
+      readonlyStore,
       indexingStore,
       syncStore,
       cleanup,
@@ -182,21 +191,23 @@ export async function setupDatabaseServices(
 
     await database.migrateSyncStore();
 
+    const syncStore = new PostgresSyncStore({ db: database.syncDb });
+
     const indexingStore = {
-      ...getReadIndexingStore({
+      ...getReadonlyStore({
         kind: "postgres",
         schema: config.schema,
         namespaceInfo: result.namespaceInfo,
         db: database.indexingDb,
       }),
       ...(config.indexing === "historical"
-        ? getHistoricalIndexingStore({
+        ? getHistoricalStore({
             kind: "postgres",
             schema: config.schema,
             namespaceInfo: result.namespaceInfo,
             db: database.indexingDb,
           })
-        : getRealtimeIndexingStore({
+        : getRealtimeStore({
             kind: "postgres",
             schema: config.schema,
             namespaceInfo: result.namespaceInfo,
@@ -204,15 +215,21 @@ export async function setupDatabaseServices(
           })),
     };
 
-    const syncStore = new PostgresSyncStore({ db: database.syncDb });
+    const readonlyStore = getReadonlyStore({
+      kind: "postgres",
+      schema: config.schema,
+      namespaceInfo: result.namespaceInfo,
+      db: database.readonlyDb,
+    });
 
     const cleanup = () => database.kill();
 
     return {
       database,
       namespaceInfo: result.namespaceInfo,
-      indexingStore,
       syncStore,
+      indexingStore,
+      readonlyStore,
       cleanup,
     };
   }
diff --git a/packages/core/src/build/configAndIndexingFunctions.test.ts b/packages/core/src/build/configAndIndexingFunctions.test.ts
index 4e3f2655..d559c8c2 100644
--- a/packages/core/src/build/configAndIndexingFunctions.test.ts
+++ b/packages/core/src/build/configAndIndexingFunctions.test.ts
@@ -634,6 +634,32 @@ test("buildConfigAndIndexingFunctions() throws for postgres database with no con
   process.env.DATABASE_URL = prev;
 });
 
+test("buildConfigAndIndexingFunctions() database with postgres uses pool config", async () => {
+  const config = createConfig({
+    database: {
+      kind: "postgres",
+      connectionString: "postgres://username@localhost:5432/database",
+      poolConfig: { max: 100 },
+    },
+    networks: { mainnet: { chainId: 1, transport: http() } },
+    contracts: { a: { network: "mainnet", abi: [event0] } },
+  });
+
+  const { databaseConfig } = await buildConfigAndIndexingFunctions({
+    config,
+    rawIndexingFunctions: [],
+    options,
+  });
+  expect(databaseConfig).toMatchObject({
+    kind: "postgres",
+    poolConfig: {
+      connectionString: "postgres://username@localhost:5432/database",
+      max: 100,
+    },
+    schema: "public",
+  });
+});
+
 test("buildConfigAndIndexingFunctions() database with postgres uses RAILWAY_DEPLOYMENT_ID if defined", async () => {
   const config = createConfig({
     networks: { mainnet: { chainId: 1, transport: http() } },
diff --git a/packages/core/src/server/service.test.ts b/packages/core/src/server/service.test.ts
index 4ad5c29b..f19f4bdf 100644
--- a/packages/core/src/server/service.test.ts
+++ b/packages/core/src/server/service.test.ts
@@ -3,7 +3,7 @@ import {
   setupDatabaseServices,
   setupIsolatedDatabase,
 } from "@/_test/setup.js";
-import type { IndexingStore } from "@/indexing-store/store.js";
+import type { ReadonlyStore } from "@/indexing-store/store.js";
 import { createSchema } from "@/schema/schema.js";
 import { encodeCheckpoint, zeroCheckpoint } from "@/utils/checkpoint.js";
 import type { GraphQLSchema } from "graphql";
@@ -18,13 +18,13 @@ test("port", async (context) => {
   const server1 = await createServer({
     graphqlSchema: {} as GraphQLSchema,
     common: context.common,
-    indexingStore: {} as IndexingStore,
+    readonlyStore: {} as ReadonlyStore,
   });
 
   const server2 = await createServer({
     graphqlSchema: {} as GraphQLSchema,
     common: context.common,
-    indexingStore: {} as IndexingStore,
+    readonlyStore: {} as ReadonlyStore,
   });
 
   expect(server1.port + 1).toBe(server2.port);
@@ -37,7 +37,7 @@ test("not healthy", async (context) => {
       ...context.common,
       options: { ...context.common.options, maxHealthcheckDuration: 5_000 },
     },
-    indexingStore: {} as IndexingStore,
+    readonlyStore: {} as ReadonlyStore,
   });
 
   const response = await server.hono.request("/health");
@@ -52,7 +52,7 @@ test("healthy", async (context) => {
       ...context.common,
       options: { ...context.common.options, maxHealthcheckDuration: 0 },
     },
-    indexingStore: {} as IndexingStore,
+    readonlyStore: {} as ReadonlyStore,
   });
 
   const response = await server.hono.request("/health");
@@ -67,7 +67,7 @@ test("healthy PUT", async (context) => {
       ...context.common,
       options: { ...context.common.options, maxHealthcheckDuration: 0 },
     },
-    indexingStore: {} as IndexingStore,
+    readonlyStore: {} as ReadonlyStore,
   });
 
   const response = await server.hono.request("/health", { method: "PUT" });
@@ -79,7 +79,7 @@ test("metrics", async (context) => {
   const server = await createServer({
     graphqlSchema: {} as GraphQLSchema,
     common: context.common,
-    indexingStore: {} as IndexingStore,
+    readonlyStore: {} as ReadonlyStore,
   });
 
   const response = await server.hono.request("/metrics");
@@ -91,7 +91,7 @@ test("metrics error", async (context) => {
   const server = await createServer({
     graphqlSchema: {} as GraphQLSchema,
     common: context.common,
-    indexingStore: {} as IndexingStore,
+    readonlyStore: {} as ReadonlyStore,
   });
 
   const metricsSpy = vi.spyOn(context.common.metrics, "getMetrics");
@@ -106,7 +106,7 @@ test("metrics PUT", async (context) => {
   const server = await createServer({
     graphqlSchema: {} as GraphQLSchema,
     common: context.common,
-    indexingStore: {} as IndexingStore,
+    readonlyStore: {} as ReadonlyStore,
   });
 
   const response = await server.hono.request("/metrics", { method: "PUT" });
@@ -127,9 +127,10 @@ test("graphql", async (context) => {
     }),
   }));
 
-  const { indexingStore, cleanup } = await setupDatabaseServices(context, {
-    schema,
-  });
+  const { indexingStore, readonlyStore, cleanup } = await setupDatabaseServices(
+    context,
+    { schema },
+  );
 
   await indexingStore.create({
     tableName: "table",
@@ -150,7 +151,7 @@ test("graphql", async (context) => {
   const server = await createServer({
     graphqlSchema: graphqlSchema,
     common: context.common,
-    indexingStore: indexingStore,
+    readonlyStore: readonlyStore,
   });
   server.setHealthy();
 
@@ -208,7 +209,7 @@ test("graphql extra filter", async (context) => {
     }),
   }));
 
-  const { indexingStore, cleanup } = await setupDatabaseServices(context, {
+  const { readonlyStore, cleanup } = await setupDatabaseServices(context, {
     schema,
   });
 
@@ -217,7 +218,7 @@ test("graphql extra filter", async (context) => {
   const server = await createServer({
     graphqlSchema: graphqlSchema,
     common: context.common,
-    indexingStore: indexingStore,
+    readonlyStore: readonlyStore,
   });
   server.setHealthy();
 
@@ -252,7 +253,7 @@ test("graphql interactive", async (context) => {
   const server = await createServer({
     graphqlSchema: {} as GraphQLSchema,
     common: context.common,
-    indexingStore: {} as IndexingStore,
+    readonlyStore: {} as ReadonlyStore,
   });
   server.setHealthy();
 
@@ -265,7 +266,7 @@ test("missing route", async (context) => {
   const server = await createServer({
     graphqlSchema: {} as GraphQLSchema,
     common: context.common,
-    indexingStore: {} as IndexingStore,
+    readonlyStore: {} as ReadonlyStore,
   });
 
   const response = await server.hono.request("/kevin");
@@ -279,7 +280,7 @@ test.skip("kill", async (context) => {
   const server = await createServer({
     graphqlSchema: {} as GraphQLSchema,
     common: context.common,
-    indexingStore: {} as IndexingStore,
+    readonlyStore: {} as ReadonlyStore,
   });
 
   await server.kill();