6. Database and table operations

By the end of this chapter: You register the tables for GritDB to manage, receive data, view status, and run queries in an analytics session. You know the operations not to perform on registered tables.

• Order of this chapter: register → receive data → view status → analytics sessions → operations not to perform. Loading data (6.8) and managing analytic queries (6.9) follow.

6.1 Create a database

A new database is a copy of template1 (PostgreSQL default, Template databases). GritDB puts the gritsurfer extension in template1, so you can register tables in a new database right away.

CREATE DATABASE <db>;

• To check, connect with ~/gritdb1/bin/psql -p 5432 -d <db> and run \dx gritsurfer. The output has a gritsurfer line.
• Run SHOW search_path; and check that the value contains gritdb. If it does not, call the registration and status functions with the gritdb. prefix (example: gritdb.register_gritsurfer_table).

Databases created with TEMPLATE template0 or another template do not have the extension. Connect to that database as the service account (superuser) and create the extension.

CREATE EXTENSION gritsurfer;

• To check, run \dx gritsurfer. The output has a gritsurfer line. The command is described in CREATE EXTENSION.
• Do not drop the gritsurfer extension (DROP EXTENSION). Dropping it also drops the GritDB functions in that database (register, unregister, and status). If you create it again with CREATE EXTENSION gritsurfer, the privileges to run the functions return to the installation default (all accounts). In that case, repeat the revoke steps in 11.5 in that database and check with row 4 of 11.10.

⚠ Do not drop or rename the postgres database

• Reason: GritDB connects to the postgres database when it starts. Without it, GritDB does not start.
• Instead: Keep business data in a new database (CREATE DATABASE <db>;).

6.2 Registration requirements

Choose the tables to register.

• Register all tables that you query together. If even one is missing, PostgreSQL runs that query (6.6).
• For a table with very many writes, every write waits for an acknowledgment from the GritDB engine after registration (1.1).
• Do not register tables that have row-level security (RLS) policies or tables that must show different data by privilege (11.5, 11.6). A table that must show different data by privilege is one where each account can read different rows or columns.

The registration requirements are as follows.

• REPLICA IDENTITY FULL is required. Without it, register_gritsurfer_table rejects the table.
• If REPLICA IDENTITY FULL is already set, do not run it again. This ALTER TABLE locks the table and stops a running autovacuum.
  • To check, run SELECT relreplident FROM pg_class WHERE oid = '<s>.<t>'::regclass;. It returns f.
• Registration targets are ordinary tables and partitioned tables (6.3). Views and foreign tables are rejected first with Table <s>.<t> must have REPLICA IDENTITY FULL. Current setting: NOTHING. …. These objects cannot have a REPLICA IDENTITY, so do not follow the ALTER TABLE at the end of the message. Materialized views and other objects are rejected with GritSurfer: cannot register relation "<t>".
• The supported column types are the 15 types below. If even one column has another type, registration is rejected (GritSurfer: Table '<t>' has unsupported column types - cannot proceed).

• Examples of rejected types: uuid, bytea, json, jsonb, arrays, enum, domains, and timetz

• The type check (before registration) finds columns with unsupported types. Run it on either the operational database or GritDB.

  SELECT a.attname, a.atttypid::regtype
  FROM pg_attribute a
  WHERE a.attrelid = '<s>.<t>'::regclass AND a.attnum > 0 AND NOT a.attisdropped
    AND a.atttypid NOT IN ('boolean'::regtype, 'smallint'::regtype, 'integer'::regtype, 'bigint'::regtype,
      'real'::regtype, 'double precision'::regtype, 'numeric'::regtype, 'date'::regtype, 'time'::regtype,
      'timestamp'::regtype, 'timestamptz'::regtype, 'interval'::regtype, 'bpchar'::regtype,
      'varchar'::regtype, 'text'::regtype);
  

  It is normal if the query returns 0 rows. char(n) is bpchar.

• The quoted 1-byte type "char" is not supported. It is a different type from char(n).

• For numeric, analytics session values are exact only for columns whose precision is set to 38 or less (numeric(p, s), p ≤ 38). See the precision check below (10.3).

• Register a table while it is empty. Rows that existed before registration are missing from analytics session results (6.3).

• Without a primary key or a NOT NULL unique constraint, registration still succeeds. However, it shows a WARNING (… has no PRIMARY KEY or UNIQUE(NOT NULL) constraint. Registering in degraded mode …), and applying updates and deletes is slow.

• In an analytics setup (6.4), always give the table the same primary key as the operational database. Without it, applying updates and deletes stops with an error (6.4.1 step 6).

• Create tables with a schema name (CREATE TABLE <s>.<t> …). If you create a table as the service account without a schema, it is created in the gritdb schema (11.5).

• Write schema and table names exactly, with the correct case. Names created without quotes are lowercase (CREATE TABLE Sales → 'sales', CREATE TABLE "Sales" → 'Sales'). If a name is wrong, registration is rejected with Relation <s>.<t> not found.

• A table that ever had a column dropped (DROP COLUMN) is rejected at registration with cache lookup failed for type 0, even if it passes the type check. In that case, create it again with “Rebuild the table” in 6.7 (skip step 4), then register it. To check, run SELECT count(*) FROM pg_attribute WHERE attrelid = '<s>.<t>'::regclass AND attisdropped;. It returns 0.

The precision check (before registration) finds numeric columns with no precision or a precision over 38. Run it on either the operational database or GritDB.

SELECT table_schema, table_name, column_name, numeric_precision, numeric_scale
FROM information_schema.columns
WHERE data_type = 'numeric'
  AND (numeric_precision IS NULL OR numeric_precision > 38)
  AND table_schema NOT IN ('pg_catalog', 'information_schema', 'gritdb')
ORDER BY 1, 2, 3;

• It is normal if the query returns 0 rows. The columns in the result have approximate values in analytics sessions. For amounts and quantities, receive the data into a table created with numeric(p, s) (p ≤ 38).
• To check only the tables you will receive, add AND table_name IN ('<t1>', '<t2>').
• If the operational database is not PostgreSQL, check whether the CDC or ETL tool creates numeric source columns without precision as numeric without precision.

6.3 Register and unregister

• Register a table while it is empty. Rows that existed before registration are not moved to the GritDB engine (10.4).
• ⚠ After you register or unregister a table, reopen the connections that write to it. Each connection checks whether the table is registered when it first writes to it, and keeps that result until the connection ends. So changes from a connection that wrote to the table before registration do not reach the GritDB engine.
  • For applications, restart the connection pool.
  • For setups that receive data with a CDC or ETL tool, restart the tool’s apply process after you register or unregister.
  • In an analytics setup (a setup that receives data through built-in replication), register before you create the subscription. For an existing subscription, run ALTER SUBSCRIPTION <sub> DISABLE; and confirm that the worker stopped. Then restart the replication worker with ALTER SUBSCRIPTION <sub> ENABLE; (6.4.2 step 6).
• Do not register or unregister inside a transaction block (BEGIN … COMMIT), a DO block, or a function. A ROLLBACK does not undo a registration or an unregistration.
• Before you unregister, stop writes to the table. You do not need to wait until changes are applied. When you unregister, the GritDB engine discards the rows of that table that are not applied. The unify checkpoint does not stop, so applying changes to the other registered tables does not fall behind. The unify checkpoint is the WAL position up to which the GritDB engine has applied changes durably. It is not the PostgreSQL checkpoint.

⚠ You cannot undo an unregistration

• Reason: Unregistering deletes the table’s cold files (files the GritDB engine stores by column for analytic queries). If you re-register the table, it starts from an empty state.
• Instead: If you only want to pause analytics, do not unregister. To rebuild the table, follow the “Rebuild the table” procedure in 6.7.

• GritDB deletes the cold files of an unregistered table separately, after the unregistration. If you re-register the same table right away, registration waits until that cleanup finishes. While it waits, the register_gritsurfer_table: waiting for tombstone cleanup … NOTICE appears every 30 seconds.
  • If the cleanup does not finish within 300 seconds, registration stops with an ERROR. In that case, register a newly created table (6.7 Rebuild the table).
• The table’s cold file directory is created when the first row is applied, not at registration. So it may not exist right after registration.
• To drop a registered table, unregister it first, then run DROP TABLE. If you run DROP TABLE without unregistering, the registration is removed when that transaction commits. To check, confirm that the table is not in the registration list (6.5). After ROLLBACK or ROLLBACK TO SAVEPOINT, the registration stays. DROP DATABASE removes all registrations in that database. To check, find GritSurfer: dropped database <oid>: unregistered <n> relations (failed 0, …) in postgresql.log (9.1).
  • If removing the registration at commit fails, you see a GritSurfer: WARNING that ends with (registration left in place), (registrations left in place), or (tombstone written, row left). The DROP completes, but the registration stays. In that case, contact support (chapter 0). Do not use the function shown in the HINT.
  • This WARNING is also written to postgresql.log (9.1), so look for it there even if the application ignores it. To check, run grep -c -e 'left in place' -e 'row left' ~/gritdb1/log/postgresql.log. It returns 0 (7.3).
  • A transaction that ran DROP on a registered table or a database cannot run PREPARE TRANSACTION (cannot PREPARE a transaction that dropped a GritDB registered relation or database).
• Every table DROP asks the GritDB catalog whether the table is registered. This also applies to unregistered tables, temporary tables, and tables dropped by DROP SCHEMA … CASCADE. If the catalog does not answer within 5 seconds, that DROP is rejected with GritSurfer: cannot tell whether relation <oid> is registered (catalog did not answer within 5000 ms). In that case, check the status with 4.5, then try again.

For a partitioned table, register the parent and every partition (including intermediate levels and the DEFAULT partition) one by one. A REPLICA IDENTITY set on the parent does not carry over to the partitions.

1. Find all partitions.

   SELECT relid, parentrelid, level, isleaf
   FROM pg_partition_tree('<s>.<t>')
   ORDER BY level;
   

   • Function: pg_partition_tree

2. Run ALTER TABLE <table> REPLICA IDENTITY FULL; for each table from step 1.

3. Run register_gritsurfer_table for each table in the order from step 1 (parent → partitions, smallest level first).

4. To check, run SELECT schema_name, table_name, is_partition FROM get_gritsurfer_tables();. The output has all tables from step 1.

• Unregister in reverse order (partitions → parent), then run DROP TABLE.
• After registration, do not attach or detach partitions (6.7).

To register several tables, run one statement per table (a commit per statement). Example that registers tables one after another from a list of names in psql:

SELECT format('SELECT register_gritsurfer_table(%L, %L);', schemaname, tablename)
FROM pg_tables
WHERE schemaname = '<s>' AND tablename IN ('<t1>', '<t2>')
ORDER BY tablename
\gexec

• To check, run SELECT table_name FROM get_gritsurfer_tables() WHERE schema_name = '<s>';. The output has all the tables you listed. In psql, \gexec runs each cell of the result as a statement.
• For partitioned tables, do not use this method. Follow the procedure above.

The register and unregister output is as follows. One registration prints several INFO and NOTICE lines. On success, the output ends with the function name and one empty row (QuickStart 4.4).

6.4 Receive into an analytics setup

An analytics setup is a GritDB setup that receives the changes of the operational database and handles analytics (1.1). This section covers the case where the operational database is PostgreSQL and GritDB receives the data through built-in replication (PostgreSQL logical replication, publication → subscription).

• Operational database: PostgreSQL 17. For other major versions and managed services, contact support (chapter 0) before you attach. For details, see 6.4.5.
• The operational database administrator runs the operational database commands on the operational database. The service account runs the GritDB commands on the GritDB server (~/gritdb1/bin/psql -p 5432 -d <db>).
• If the operational database is not PostgreSQL, receive the data with a CDC or ETL tool that writes to PostgreSQL as its target. The GritDB-side steps (6.4.1 steps 6 to 7) are the same. After you register or unregister, restart the tool’s apply process (6.3). Keep the tool’s commit size, including the initial load, below half the single-transaction limit (6.8). For tool settings, follow the tool’s documentation.
• The size check (before you attach) compares two kinds of row counts with the single-transaction limit (6.8). The first is the row count of each table you will receive. The second is the number of rows that operational database batch jobs change in one transaction. If any of them exceeds half the limit, contact support (chapter 0) before you attach.
• Do not use pg_createsubscriber, which turns a physical standby server into a logical replication subscriber. GritDB does not receive data through physical replication (1.1). For large tables, use the size check above and contact support.
• Notation: <pub> publication, <sub> subscription, <db> database (if the names differ on the operational database side and the GritDB side, use each one), <s>.<t> schema and table, <replication account>, <password>, <operational database address>, <port> (operational database port), and <GritDB server address>

6.4.1 Attach

1. Check the operational database settings (operational database)

SHOW wal_level;
SHOW max_replication_slots;
SHOW max_wal_senders;
SHOW max_slot_wal_keep_size;

• It is normal if wal_level is logical. If not, run ALTER SYSTEM SET wal_level = logical; and restart the operational database once.
• Set max_replication_slots and max_wal_senders to at least the number in use now plus (1 + max_sync_workers_per_subscription of GritDB, default 2) for each subscription. If they are too low, change them with ALTER SYSTEM SET …, then restart the operational database. Both settings take effect only after a restart, so when you change wal_level, change them at the same time and restart only once.
• All settings on the operational database side: Logical replication configuration (publisher)
• The operational database administrator chooses max_slot_wal_keep_size. With -1 (default), WAL accumulates while the analytics setup is stopped, until the operational database disk is full. A limit protects the disk, but if WAL goes over it, you take the tables again from the start (8.6).

2. Find tables without a replica identity (operational database)

SELECT c.oid::regclass AS table_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = '<s>'
  AND c.relkind IN ('r', 'p')
  AND (c.relreplident = 'n'
       OR (c.relreplident = 'd'
           AND NOT EXISTS (SELECT 1 FROM pg_index i
                           WHERE i.indrelid = c.oid AND i.indisprimary)));

• It is normal if none of the tables you will receive is in the list.
• For tables in the list, run ALTER TABLE <s>.<t> REPLICA IDENTITY FULL; before step 3.
• With REPLICA IDENTITY FULL, UPDATE and DELETE on the table write the entire old row to WAL, so WAL grows. Also, ALTER TABLE takes a lock, so do it at a quiet time.
• If you use REPLICA IDENTITY USING INDEX <index> with a unique index, create the primary key of the GritDB table on the columns of that index (step 6).

⚠ Do not add tables without a replica identity to a publication

• Reason: UPDATE and DELETE on that table fail with an error on the operational database (cannot update table "<t>" because it does not have a replica identity and publishes updates).
• Instead: Set the replica identity first, in step 2.

3. Create the publication (operational database)

CREATE PUBLICATION <pub> FOR TABLE <s>.<t1>, <s>.<t2>;
SELECT schemaname, tablename FROM pg_publication_tables WHERE pubname = '<pub>';

• It is normal if only the tables you added appear. The command and view are described in CREATE PUBLICATION and pg_publication_tables.
• Add only the tables to analyze, by name. Do not use FOR ALL TABLES or FOR TABLES IN SCHEMA. With them, tables created later would come in without the step 2 check and without preparation on GritDB.
• Leave the publish option at its default of insert, update, delete, and truncate (6.4.2).
• Do not add tables that operational database batch jobs empty with TRUNCATE and fill again. If you add them, you must take that table again (6.4.2, including the initial copy) after every TRUNCATE. If you do not, deleted rows remain in analytics sessions. So before you attach, ask the operational database administrator which batch jobs use TRUNCATE.
• Put only one table with many rows in the first publication. Add the other tables one at a time after step 9 shows r (6.4.2 “Add a table to an existing analytics setup”, 6.8).
• If the operational database has a gritdb schema, do not add the tables in that schema. GritDB rejects them.

4. Create the replication account and allow connections (operational database)

CREATE ROLE <replication account> WITH LOGIN REPLICATION;
\password <replication account>
GRANT USAGE ON SCHEMA <s> TO <replication account>;
GRANT SELECT ON <s>.<t1>, <s>.<t2> TO <replication account>;

• With \password, psql encrypts the password before it sends it, so the password does not remain in the operational database log or in ~/.psql_history (11.3). With a tool other than psql, use that tool’s password change dialog.
• If CONNECT was revoked from PUBLIC on the operational database, also run GRANT CONNECT ON DATABASE <db> TO <replication account>;.

Add one line to pg_hba.conf, then run SELECT pg_reload_conf();.

host  <db>  <replication account>  <GritDB server address>/32  scram-sha-256

• In the firewall, allow only connections from the GritDB server to the operational database port.

• To check, run ~/gritdb1/bin/psql "host=<operational database address> port=<port> dbname=<db> user=<replication account> replication=database" -c "IDENTIFY_SYSTEM;" on the GritDB server. After you enter the password, it returns one line with systemid, timeline, xlogpos, and dbname. This checks the login, pg_hba.conf, and the replication privilege together (Replication connections).

• GritDB 1.1 has no connection encryption (TLS), so attach only within the same private network (11.8). If the operational database accepts only encrypted connections through hostssl or the SSL enforcement setting of a managed service, you cannot attach.

• Check whether operational database tables have row-level security (RLS) policies. If the replication account does not have BYPASSRLS, the initial copy reads only the rows that the policies allow, and rows go missing in GritDB without an error.

  SELECT c.oid::regclass
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
  JOIN pg_publication_tables p ON p.schemaname = n.nspname AND p.tablename = c.relname
  WHERE p.pubname = '<pub>' AND c.relrowsecurity;
  

  It is normal if the query returns 0 rows. If every analytics account on GritDB may read all rows of a table in the result (11.5 security requirements for registered tables), give the replication account BYPASSRLS. If not, remove the table from the publication, because analytics sessions do not apply RLS (11.6). The connection string of step 8 has options=-crow_security=off. With it, the copy stops with an error when it meets such a table, instead of leaving rows out (Logical replication security).

5. Set the number of workers (GritDB)

SHOW max_worker_processes;
SHOW max_logical_replication_workers;
SHOW max_sync_workers_per_subscription;
SHOW max_parallel_workers;
SHOW max_replication_slots;

• Required values
  • max_logical_replication_workers ≥ number of subscriptions × (1 + max_sync_workers_per_subscription)
  • max_worker_processes ≥ 6 (GritDB workers: 5 that always run + 1 during start) + 1 (logical replication launcher) + max_logical_replication_workers + max_parallel_workers
  • max_replication_slots (GritDB) ≥ number of subscriptions × (1 + max_sync_workers_per_subscription). Progress records on the subscriber side also count against this value.
• Installation defaults (PostgreSQL defaults): max_worker_processes 8, max_logical_replication_workers 4, max_sync_workers_per_subscription 2, max_parallel_workers 8, max_replication_slots 10. With these defaults, max_worker_processes must be 19 or more even with one subscription. So raise it when you attach for the first time (example: ALTER SYSTEM SET max_worker_processes = 24;). Keep the value at 256 or less (⚠ below).
• If they are too low, run ALTER SYSTEM SET <setting> = <value>; and follow the restart procedure in 5.4. After the restart, check for the shm_limit_mb warning in 5.6 (5.4).
• Before step 8, reduce the number of tables copied at the same time to one (6.8). This keeps two initial copies from sharing the single-transaction limit. Run ALTER SYSTEM SET max_sync_workers_per_subscription = 1; and then SELECT pg_reload_conf(); (no restart). When step 9 shows r for all tables, run ALTER SYSTEM RESET max_sync_workers_per_subscription; and then SELECT pg_reload_conf();.
• Settings: max_logical_replication_workers, max_sync_workers_per_subscription. All subscriber-side settings: Logical replication configuration (subscriber)
• ⚠ If there are not enough worker slots, GritDB may fail to start, so set them before you create or add subscriptions.
• ⚠ Do not set max_worker_processes higher than 256 (GritDB does not start).

6. Create the same tables (GritDB)

• First create the database that receives the data (6.1). Do not use the postgres database. Do all GritDB-side work in steps 6 to 9 while connected to that database (~/gritdb1/bin/psql -p 5432 -d <db>). A subscription applies only to tables in the database where it is created (CREATE SUBSCRIPTION).
• Use the same schema, table name, column names, column types, and primary key as the operational database. The column order can differ.
• Example: Extract the definition on the operational database with pg_dump --schema-only --no-owner --no-privileges --table=<s>.<t> <db> and run it on GritDB. If the schema does not exist, first run CREATE SCHEMA <s>;. The two options leave out the owner and privilege lines of the operational database. Adjust the trigger lines for your environment.
• Delete the ENABLE ROW LEVEL SECURITY and CREATE POLICY lines from the pg_dump output. Do not register tables with RLS policies (11.6). To check, run the check query in 11.6 after step 7. It returns 0 rows.
• Use only the 15 column types (6.2).
• Create numeric columns as numeric(p, s) with p ≤ 38 (6.2 precision check). If the operational database column is numeric without precision, first measure the largest number of digits in the operational database values and set a precision with room to spare. If a larger value arrives, apply stops (numeric field overflow), and digits beyond the scale are rounded.
• Registration requirement: ALTER TABLE <s>.<t> REPLICA IDENTITY FULL;
• To check, run \d+ <s>.<t>. The output has a primary key and Replica Identity: FULL. \d does not show this line.

⚠ Do not create tables without a primary key

• Reason: If the operational database replicates by primary key or unique index, applying updates and deletes on GritDB stops with an error (logical replication target relation "<s>.<t>" has neither REPLICA IDENTITY index nor PRIMARY KEY and published relation does not have REPLICA IDENTITY FULL). While apply is stopped, WAL accumulates on the operational database.
• Instead: Give the table the same primary key as the operational database (if the operational database uses REPLICA IDENTITY USING INDEX, on the columns of that index). If the operational database table is REPLICA IDENTITY FULL, apply does not stop, but applying updates and deletes is slow.

7. Register (GritDB, before the subscription)

SELECT register_gritsurfer_table('<s>', '<t>');
SELECT * FROM get_gritsurfer_tables();

• It is normal if all the tables you will receive are in the list. The registration output is in 6.3.
• If you register after you create the subscription, changes to the table may not reach the analytics data (6.3, 10.4).

8. Create the subscription (GritDB)

CREATE SUBSCRIPTION <sub>
  CONNECTION 'host=<operational database address> port=<port> dbname=<db> user=<replication account> password=<password> options=-crow_security=off'
  PUBLICATION <pub>
  WITH (streaming = off);

Expected output:

NOTICE:  created replication slot "<sub>" on publisher
CREATE SUBSCRIPTION

SELECT oid, subname, subenabled, substream FROM pg_subscription;

• It is normal if subenabled is t and substream is f.
• Write down the oid value. When you clean up, it identifies the slots on the operational database (6.4.4). The command is described in CREATE SUBSCRIPTION.
• options=-crow_security=off makes sure that you do not receive data with rows left out by row-level security policies on the operational database (step 4).
• streaming = off is the default, but write it anyway. Do not use on or parallel.
• To keep the password out of the connection string, put one line <operational database address>:<port>:<db>:<replication account>:<password> in the service account’s ~/.pgpass (permissions 600). Then remove password=<password> from the connection string. A subscription that the service account creates connects with the password in this file, so the password does not remain in pg_subscription, the logs, or ~/.psql_history. When the password changes, edit only this file (.pgpass).
• If you put the password in the connection string, it remains in pg_subscription on GritDB. Only superusers can read it (11.5).
• If log_statement is ddl or higher, the password in this statement remains in postgresql.log. If the statement fails (example: could not connect to the publisher), the whole statement remains as a STATEMENT: line regardless of the log_statement value. This is because the default of log_min_error_statement is error. So in the session that creates the subscription, first run SET log_statement = 'none'; and SET log_min_error_statement = 'panic';. Also check ~/.psql_history (11.7, 11.9).
• If a statement with the password is already in the logs, change the replication account password on the operational database (\password <replication account>). If the subscription already exists, edit the line in the service account’s ~/.pgpass or run ALTER SUBSCRIPTION <sub> CONNECTION '<new connection info>'; to give it the new password (6.4.5). Run this statement after the two SET statements above, too. Then check with step 9. Before you send logs outside, delete the log lines that contain the password (9.7, QuickStart 7.4).
• The initial copy of each table arrives as one transaction (warning below, 6.8).

⚠ Do not use a subscription to receive a table with more rows than half the single-transaction limit

• Reason: The initial copy loads one table as one transaction. If it exceeds the limit (6.8), GritDB stops; when you start GritDB again, it repeats the same copy from the beginning and stops again.
• Instead: Before step 3, measure with SELECT count(*) FROM <s>.<t>; on the operational database. If the count exceeds half the limit, contact support before you attach (chapter 0).

9. Check the initial copy (GritDB → operational database)

SELECT srrelid::regclass AS table_name, srsubstate FROM pg_subscription_rel;

• It is normal if srsubstate is r for all tables. i, d, f, and s mean that the copy is in progress (pg_subscription_rel).
• Compare counts in the order below.
  1. Stop writes to the table on the operational database, or choose a time without writes. Then write down the value of SELECT pg_current_wal_lsn();.
  2. On the operational database, run SELECT confirmed_flush_lsn >= '<value from step 1>'::pg_lsn FROM pg_replication_slots WHERE slot_name = '<sub>';. It returns t (pg_replication_slots).
  3. Confirm that changes are applied on GritDB (6.6.1).
  4. Measure on the operational database, in a GritDB regular session, and in a GritDB analytics session.

Operational database and GritDB regular session:

SELECT count(*), sum(<numeric column>) FROM <s>.<t>;

GritDB analytics session (6.6):

/*+ engine=burst, fallback=false */ SELECT count(*), sum(<numeric column>) FROM <s>.<t>;

• It is normal if the three values are the same.
• For <numeric column>, choose an integer column or a numeric(p, s) column. The sum of a real column and avg can differ between the two sessions (6.6).

6.4.2 When you run TRUNCATE or change columns on the operational database

During “Take that table again”, the other tables keep receiving data. That table runs the initial copy again. So if its row count exceeds half the single-transaction limit (6.8), contact support before you start (warning in 6.4.1 step 8). If you receive data with a CDC or ETL tool, see the end of this section. Before step 1, get the new definition (columns to add and types to change) from the operational database administrator. Check it against the 15 types in 6.2 and numeric(p, s) (p ≤ 38). If a column with an unsupported type appears, registration in step 5 is rejected. In that case, contact support (chapter 0) before you do step 1.

• PostgreSQL rules for what is replicated and what is not (DDL, sequences, and TRUNCATE): Logical replication restrictions. Commands: ALTER PUBLICATION, ALTER SUBSCRIPTION. Workers: pg_stat_subscription
• The wait in step 1 makes sure that step 3 also receives changes from open transactions. These transactions wrote to the table before DROP TABLE and have not committed yet. If the operational database uses two-phase commit (SHOW max_prepared_transactions; is greater than 0), after the value above becomes 0, also wait until SELECT count(*) FROM pg_prepared_xacts; is 0. This is because prepared transactions are not in pg_stat_activity (pg_prepared_xacts).
• Step 3 checks that GritDB has received all changes written before step 1. If you skip it and do step 5, those changes arrive for the dropped table and the whole subscription stops (logical replication target relation "<s>.<t>" does not exist).
• In step 5, register a newly created table. If you empty the same table and re-register it right away, registration can wait for the unregister cleanup and fail with an ERROR after 300 seconds.
• In step 5, check the privileges with \dp <s>.<t> on GritDB. Find the dependent objects with the query in step 5 of “Rebuild the table” in 6.7 (pg_depend). Record views and materialized views with pg_dump --schema-only --table=<view>, then drop them. For foreign keys in other tables, record the definitions and drop them (same step 5). If dependent objects exist, DROP TABLE is rejected (cannot drop table … because other objects depend on it). Do not use CASCADE. Re-create views at the end of step 5. Re-create materialized views and foreign keys after the three values match in step 9. For materialized views, also run REFRESH MATERIALIZED VIEW <view>;. If you set ALTER DEFAULT PRIVILEGES on the schema (11.5), the analytics account gets SELECT automatically.
• Step 6 makes the replication worker read the new registration (6.3). If the worker is busy, it may not go down between DISABLE and an ENABLE right after it. So confirm that it stopped (pid is empty), then enable the subscription.
• If the operation already ran on the operational database and the subscription did not stop (TRUNCATE, or dropping a column that is not NOT NULL), do all steps except step 4. If apply already stopped because of adding a column, changing a type, renaming, or dropping a NOT NULL column (is missing replicated column, does not exist, or violates not-null constraint), this procedure alone does not fix it. In that case, contact support (chapter 0).

In “Add a table to an existing analytics setup”, first check the row count, the replica identity (step 2 in 6.4.1), and row-level security on the operational database. If the row count exceeds half the limit, contact support (warning in 6.4.1 step 8). To check row-level security, run SELECT relrowsecurity FROM pg_class WHERE oid = '<s>.<t>'::regclass;. It returns f (step 4). Give the replication account GRANT SELECT (step 4). For another schema, also give it GRANT USAGE ON SCHEMA <s>. Then create and register the table on GritDB (steps 6 and 7). Next, do steps 6 to 9 of the table above. Add one table at a time (6.8).

When you receive data with a CDC or ETL tool:

1. Remove the table from the tool (tool documentation).
2. Unregister the table, then run DROP TABLE.
3. Run CREATE TABLE with the new definition (same primary key and REPLICA IDENTITY FULL), then register the table.
4. Restart the tool’s apply process.
5. Load the table from the beginning with the tool. Keep the commit size below half the single-transaction limit (6.8). Then compare as in 6.4.1 step 9.

⚠ Do not stop the subscription (DISABLE) and run TRUNCATE on the operational database in the meantime

• Reason: Changes to the table made while the subscription was stopped, and changes not yet received before it stopped, arrive after you enable it again. Deleted rows remain in analytics sessions.
• Instead: Follow “Take that table again” above.

6.4.3 What happens on the operational database

• Normally, the WAL sender process (walsender) on the operational database decodes the changes and sends them to GritDB.
• Because of streaming = off, the operational database collects the changes of a large transaction until the transaction ends, then sends them. When the changes exceed the memory limit logical_decoding_work_mem, it collects them on disk. Meanwhile, this uses disk space on the operational database (pg_replslot). GritDB also applies them as one transaction.
  • Also split operational database batch jobs and bulk UPDATE and DELETE, and commit them below half the GritDB single-transaction limit (6.8). Agree on this with the operational database administrator.
  • If a transaction exceeds the limit, GritDB stops. When you start GritDB again, it receives the same transaction again and stops again. In that case, contact support (chapter 0).
• During the initial copy, a long read transaction runs on the operational database. Meanwhile, VACUUM on the operational database cannot remove dead rows created after it started. So attach large tables at a quiet time.
• For tables whose replica identity is set to REPLICA IDENTITY FULL, every UPDATE and DELETE writes the entire old row to WAL (6.4.1 step 2).
• If the subscription stops (GritDB is stopped, or an apply error occurs), the operational database slot holds WAL, and the operational database disk fills up.
  • The limit is max_slot_wal_keep_size on the operational database (6.4.1 step 1). If WAL exceeds it, the operational database slot becomes invalid. In that case, take the analytics setup again from the beginning (8.6).
  • ⚠ Do not set max_slot_wal_keep_size on the GritDB server (10.5).
• For monitoring, see 7.5.

6.4.4 Clean up (when you end a subscription, including at the end of a PoC)

• If step 1 cannot reach the operational database, follow this order: ALTER SUBSCRIPTION <sub> DISABLE; → ALTER SUBSCRIPTION <sub> SET (slot_name = NONE); → DROP SUBSCRIPTION <sub>; → steps 2 and 3 (Replication slot management).
• Command and function: DROP SUBSCRIPTION, pg_drop_replication_slot
• If GritDB is lost and you cannot do step 1, do step 2 with the oid that you wrote down in 6.4.1 step 8.
• You can leave wal_level as it is. Changing it back takes one operational database restart.

⚠ Do not leave slots on the operational database

• Reason: If you remove GritDB first, or finish without dropping the subscription, the operational database slot keeps holding WAL and fills the disk.
• Instead: Clean up in the order 1 → 2, and confirm with step 2.

⚠ Do not drop slots that step 2 does not show

• Reason: If the slot belongs to a standby server, another subscription, or a CDC tool, that replication breaks and cannot be restored.
• Instead: Drop only the slots that match the condition in step 2.

6.4.5 When the operational database changes · managed services

• PostgreSQL 17 failover slots (failover = true) are not tested in 1.1. To use them, first contact support (chapter 0).
• To check after a restart or an address change, run SELECT pid FROM pg_stat_subscription WHERE subname = '<sub>'; on GritDB. It returns a value. Then compare counts as in 6.4.1 step 9.

If the operational database address changes, change the connection string on GritDB.

ALTER SUBSCRIPTION <sub>
  CONNECTION 'host=<new operational database address> port=<port> dbname=<db> user=<replication account> password=<password> options=-crow_security=off';

• This statement also contains the password, so keep it out of the logs and ~/.psql_history as in 6.4.1 step 8. If you use .pgpass, the connection string has no password. In that case, also fix the address in that file’s line (6.4.1 step 8).
• If the GritDB server address changes, update the pg_hba.conf line and the firewall of the operational database to the new address (6.4.1 step 4). For allowing connections on the GritDB side, see 11.4.

For managed services (PostgreSQL from a cloud provider), contact support (chapter 0) before you attach. Differences from standard PostgreSQL:

• Turn on logical replication with the service’s parameter settings instead of wal_level (restart required).
• Grant the replication account the replication role that the service defines.
• If SSL is enforced by default, GritDB 1.1 cannot attach (6.4.1 step 4).

6.5 View status

• get_* functions return a table for processing with SQL. show_* functions return text for people to read.
• The status functions show only the objects of the database you are connected to. Only get_gritsurfer_unify_checkpoints() is common to all databases.
• If there are several lines for registrations with the same name, an old registration was not cleaned up and remains (the left in place or row left WARNING in 6.3). The line with the largest register_ts is the current registration. In that case, contact support (chapter 0).
• gritdb.grit_surfer_heartbeat in the postgres database is an internal GritDB registered table (written every 10 seconds). Do not unregister or drop it (10.5).
• The analytic query history is recorded shortly after a query ends (usually within 1 second).
• The arguments of the history functions can differ between installation package versions, so check them with \df get_gritsurfer_query_history before you use them (3.5).
• get_gritsurfer_constraints() and get_gritsurfer_indexes() show the definitions read at registration. Constraints and indexes changed after registration do not appear. For the current definitions, run \d <s>.<t>.

Status of cold files (status in get_gritsurfer_coldfiles()):

View the number and size of files by table and by status.

SELECT schema_name, table_name, status, count(*) AS files,
       pg_size_pretty(sum(file_size_bytes)) AS size
FROM get_gritsurfer_coldfiles()
GROUP BY schema_name, table_name, status
ORDER BY schema_name, table_name, status;

• If STAGED keeps growing, the unify checkpoint has stopped. In that case, see chapter 7. The thresholds are in 7.2.

To see whether the unify checkpoint (explained in 6.3) has stopped, look at the time of the newest row.

SELECT max(created_at) AS last_checkpoint,
       now() - max(created_at) AS age
FROM get_gritsurfer_unify_checkpoints();

• It is normal if age does not keep growing when you run the query several times. The checkpoint advances even without writes (6.6.1).
• If age keeps growing, see chapter 7.

6.6 Analytics sessions

Set the session mode (gritsurfer_session_mode) when you connect. You cannot change it after you connect (SET is rejected).

Connect as an analytics session.

PGOPTIONS='-c gritsurfer_session_mode=surfer' ~/gritdb1/bin/psql -p 5432 -d <db>

Expected output (first three lines):

NOTICE:  GritSurfer session started: default_transaction_read_only is on
DETAIL:  Cold-file synchronization is asynchronous, so a query inside a transaction that mixes writes and reads is not guaranteed to see the written data.
HINT:  Read-only usage is recommended. For write workloads, connect with gritsurfer_session_mode=pg.

For applications, put -c gritsurfer_session_mode=surfer in the connection options. Only the place to put it differs by driver.

• To check, run SHOW gritsurfer_session_mode; in that connection. It returns surfer.

• You cannot turn it on with SQL that runs after the connection is made (such as the initial SQL of a BI tool). SET is rejected. Tools that have no field for it connect as a regular session.

• Analytics sessions are turned on with a connection option. GritDB 1.1 has no setting that limits which accounts can turn them on. So for the security requirements for registered tables, see 11.5.

• ⚠ You cannot turn it on with account or database defaults (ALTER ROLE, ALTER DATABASE … SET). Those statements are rejected (parameter "gritsurfer_session_mode" cannot be set after connection start).

• ⚠ Do not set it globally with postgresql.conf or ALTER SYSTEM. Even the GritDB internal work connections become analytics sessions.

• Use analytics sessions as read-only.

  • If gritsurfer_write_visibility_guard is on (default), the session starts with default_transaction_read_only = on and shows an informational NOTICE. In this state, a write fails with an ERROR (cannot execute INSERT in a read-only transaction). If the session turns default_transaction_read_only off, the first write shows a WARNING.
  • Reason: Data written in an analytics session is not applied to cold files right away. Queries in the analytics session may not see that data.
  • Do writes (INSERT, UPDATE, DELETE, and DDL) in a regular session (pg).
  • Only a superuser can change this setting.

Statement hints are read only in analytics sessions. Only the first /*+ ... */ comment in a statement is read. Keys and values are not case-sensitive. A hint with a wrong format stops the statement with an ERROR.

• To use both keys together, write /*+ engine=burst, fallback=false */.
• The default of fallback is gritsurfer_session_fallback. When this value is on, a statement is re-run on PostgreSQL if engine execution fails.

The GritDB engine runs statements that meet all of the conditions below. If even one is not met, PostgreSQL runs the statement without a message.

In an analytics session, check which engine runs a statement with EXPLAIN.

EXPLAIN SELECT count(*) FROM <s>.<t>;

• If the first line is engine: GritBurst, the GritDB engine runs the statement (for EXPLAIN ANALYZE, engine: GritBurst (analyze)). Without that line, PostgreSQL runs it.
• The analytic query history (6.5) records only statements sent to the GritDB engine. A statement that was sent and then re-run on PostgreSQL is recorded as FAILED.

Re-run (fallback):

• If fallback is on, only statements that fail before the GritDB engine sends results are re-run on PostgreSQL. In that case, you see the WARNING GritSurfer: falling back to standard PostgreSQL plan: <reason>.
• A failure after the engine starts to send results is not re-run. The statement stops with an ERROR (SQLSTATE 38000).
• PREPARE and the extended protocol (bind execution in drivers) run on the engine chosen at prepare time and are not re-run. Changing the hint at execution time does not change the engine (WARNING … (prepare-time routing is sticky)).

Concurrency:

• The GritDB engine runs up to vCPUs × 4 analytic queries at the same time. Beyond that, queries wait until an execution slot is free.
• If a query waits longer than grit_burst.session.connect_timeout_ms (default 300 seconds, 5.6), it runs on PostgreSQL.
• However, bind execution in drivers (such as BI tools that use JDBC), PREPARE, and fallback=false do not run on PostgreSQL when the time limit passes. They stop with an ERROR (GritSurfer: failed to send query to burst engine, SQLSTATE 38000).
• While a query waits for an execution slot, cancel (Ctrl-C) and statement_timeout do not work until the wait ends. To stop it right away, use pg_terminate_backend (6.9).
• To check the limit, see the cap= value in the line below.

grep -h 'channel capacity: service Session' ~/gritdb1/log/grit_admin_main.log* | tail -1

Apply lag:

• Analytics sessions see committed changes after a short delay. Changes usually become visible in about 12 seconds, and 99 times out of 100 within about 22 seconds (in-house tests).
• In an analytics setup, the replication time from the operational database is added.
• Run queries that must have the latest values in a regular session (in an analytics setup, on the operational database).
• To check whether a change you just committed is visible, see 6.6.1.

Cases where results can differ from PostgreSQL:

• Precision of avg, digits of numeric, sum of real (float4) columns, octet_length of char(n), and concat
• When the GritDB engine shows timestamptz values, it does not use the session time zone (TimeZone). So convert them with AT TIME ZONE '<time zone>' in the query (1.8). To check, run SHOW timezone; in analytics connections, including BI tools. It returns the same value as the server time zone (5.3). If it differs, adjust the time zone setting of the tool. For JDBC tools, set the JVM time zone (example: -Duser.timezone=Asia/Seoul). If the time zones do not match, date and time values differ between statements that the GritDB engine runs and statements that PostgreSQL runs, even within one session.
• If you need exact values, query in a regular session or add /*+ engine=pg */.

Connection pooler:

• If you need a connection pooler, use Pgpool-II. Pgpool-II passes the connection options (options) to GritDB as they are, so analytics sessions open even through the pooler.
• PgBouncer does not pass connection options. Connections through PgBouncer cannot open analytics sessions. So connect analytics connections directly to GritDB, not through PgBouncer.
• Through a pooler, use different accounts (or databases) for analytics connections and regular connections. A pooler reuses connections that have the same account and database. So if only the connection options differ, analytics session connections and regular session connections can get mixed (6.9, 11.5).
• To check, run SHOW gritsurfer_session_mode; in an analytics connection through the pooler. It returns surfer. In a regular connection, it returns pg.

6.6.1 Confirm that changes are applied · measure analytics session values

To confirm that changes are applied, check whether a change you just committed is visible in analytics sessions. In a regular session, write down the WAL position (pg_current_wal_lsn()), then check whether the unify checkpoint has passed that position.

SELECT pg_current_wal_lsn();

SELECT max(ckpt_lsn) >= '<value above>'::pg_lsn AS caught_up
FROM get_gritsurfer_unify_checkpoints();

• It is normal if it returns t. If it returns f, run it again after a few seconds. If it returns t, changes committed before then are visible in analytics sessions. Even without writes, the checkpoint passes that position, because GritDB commits an internal record every 10 seconds.
• When you measure analytics session values, put /*+ engine=burst, fallback=false */ in front of the statement. Without it, if the GritDB engine cannot run the statement and PostgreSQL runs it instead, you still get the same value. If an ERROR occurs, check the registration status (6.5) first.

6.7 Operations not to perform on registered tables

GritDB 1.1 does not correctly apply the operations below to the GritDB engine when you run them on registered tables. Do not run them on registered tables. If you must, follow “What to do instead”.

Rebuild the table (single-database setup):

1. Stop writes to the table (applications and job schedules).
2. Keep a copy of the data with CREATE TABLE <s>.<t>_keep AS TABLE <s>.<t>;.
3. Compare the row count of the kept copy with the original. Run SELECT (SELECT count(*) FROM <s>.<t>) = (SELECT count(*) FROM <s>.<t>_keep); and check that it returns t.
4. Unregister the table (6.3).
5. Run DROP TABLE <s>.<t>;.
   • First, check the dependent objects of the table with SELECT DISTINCT pg_describe_object(classid, objid, 0) FROM pg_depend WHERE refclassid = 'pg_class'::regclass AND refobjid = '<s>.<t>'::regclass AND deptype = 'n';.
   • Record views and materialized views with ~/gritdb1/bin/pg_dump -p 5432 --schema-only --table=<view> <db> > <view>.sql (definition, privileges, and indexes), then drop them. For dependent views of a view, start from the top.
   • For foreign keys in other tables, record the result of SELECT format('ALTER TABLE %s ADD CONSTRAINT %I %s;', conrelid::regclass, conname, pg_get_constraintdef(oid)) FROM pg_constraint WHERE contype = 'f' AND confrelid = '<s>.<t>'::regclass; and drop those constraints. Also stop writes to those tables from step 1.
   • Do not use CASCADE. Re-create views in step 7 with ~/gritdb1/bin/psql -p 5432 -d <db> -f <view>.sql. Re-create materialized views and foreign keys after the count comparison in step 8. Materialized views are created WITH NO DATA, so continue through REFRESH MATERIALIZED VIEW <view>;.
6. Run CREATE TABLE with the new definition (same primary key), then run ALTER TABLE <s>.<t> REPLICA IDENTITY FULL;.
7. Register the table (6.3). Re-create the privileges and dependent views of the table.
8. Reload the data with INSERT INTO <s>.<t> SELECT … FROM <s>.<t>_keep; (6.8). Compare counts, then run DROP TABLE <s>.<t>_keep;.
   • If the row count exceeds the split threshold in 6.8 (half the limit, smaller for wide rows), split by primary key range and insert in several statements. Commit after each statement (example: INSERT INTO <s>.<t> SELECT … FROM <s>.<t>_keep WHERE <key> >= <a> AND <key> < <b>;).
   • If there are serial or identity columns, then for GENERATED ALWAYS AS IDENTITY columns, do step 8 with INSERT INTO <s>.<t> OVERRIDING SYSTEM VALUE SELECT … FROM <s>.<t>_keep;. Leave generated columns (GENERATED ALWAYS AS (…) STORED) out of the column list in step 8. Their values are computed again. Before step 9, set the sequence of each serial or identity column with SELECT setval(pg_get_serial_sequence('<s>.<t>', '<column>'), max(<column>)) FROM <s>.<t>; (Sequence functions, pg_get_serial_sequence).
9. Reopen the connections that write to the table (6.3).

⚠ Do not run DROP TABLE before you check the row count of the kept copy

• Reason: You cannot restore deleted rows.
• Instead: Go to step 5 only when the result of step 3 is t.

• If you empty the same table and re-register it right away, registration can wait for the unregister cleanup and fail with an ERROR after 300 seconds. So register a newly created table.
• Register empty tables. If you register a table after you load data, those rows are missing from analytics session results (6.3, 10.4).
• The operations that you can run on registered tables are creating and dropping indexes, VACUUM, ANALYZE, and COPY (7.8).

6.8 Load data (bulk load)

1. Create an empty table (6.2 requirements and REPLICA IDENTITY FULL).

2. Register it (6.3).

3. Load the data (COPY or INSERT … SELECT). One statement (one COPY, one INSERT … SELECT, or one table in a dump file) is one transaction. So if the row count exceeds the split threshold (half the single-transaction limit below, smaller for wide rows), load in batches through a staging table.

   CREATE UNLOGGED TABLE <s>.<t>_load (LIKE <s>.<t>);
   \copy <s>.<t>_load FROM '<file>' (FORMAT csv)
   SELECT format('INSERT INTO <s>.<t> SELECT * FROM <s>.<t>_load WHERE <key> BETWEEN %L AND %L', min(<key>), max(<key>))
   FROM (SELECT <key>, ntile(<batch count>) OVER (ORDER BY <key>) AS n FROM <s>.<t>_load) x
   GROUP BY n ORDER BY n \gexec
   

   • Run the commands above inside ~/gritdb1/bin/psql -p 5432 -d <db> -v ON_ERROR_STOP=1 (it stops at the first error).
   • Do not register the staging table (_load). It is UNLOGGED, so it writes little WAL. It needs as much extra space as the source data.
   • After an abnormal termination or a quick stop, the staging table remains, but its contents are emptied. In that case, start again from the \copy line. Skip the batches already loaded. If <s>.<t> has rows, add WHERE <key> > (SELECT max(<key>) FROM <s>.<t>) after FROM <s>.<t>_load in the inner SELECT that contains ntile. Batches are committed one by one in key order.
   • If the file has a header line, run \copy with (FORMAT csv, HEADER). It also reads line breaks inside CSV fields as they are, so do not split the file with split.
   • <key> is one primary key column. <batch count> is the staging table row count ÷ the split threshold (rounded up). \gexec runs the generated INSERT statements one by one (a commit per statement).
   • Do not group several statements into one transaction. Do not load inside BEGIN, with psql -1, or with pg_restore --single-transaction. If you load from an application or a driver, turn on autocommit or commit after each batch (example: Python psycopg has autocommit off by default).
   • If there are generated columns or GENERATED ALWAYS identity columns, load as in step 8 of “Rebuild the table” in 6.7.
   • Commands: \gexec, ON_ERROR_STOP, and ntile

4. Collect statistics with ANALYZE <s>.<t>;.

5. Confirm that changes are applied (6.6.1).

6. Compare counts and sums between a regular session and an analytics session (the two queries in 6.4.1 step 9). If you used a staging table, they must also match its values (SELECT count(*), sum(<numeric column>) FROM <s>.<t>_load;). If they match, run DROP TABLE <s>.<t>_load;.

• Check that all the values in step 6 are the same.
• Do not make registered tables UNLOGGED, either when you create them or later. Changes may not reach the GritDB engine (6.7).
• Each write to a registered table waits for an acknowledgment from the GritDB engine. So the same load is slower than on a table that is not registered (1.1).

Each server has a limit on the number of rows that one transaction writes to registered tables (INSERT, UPDATE, and DELETE combined). This is the single-transaction limit. GritDB 1.1 has no setting that changes this limit.

Check the limit (GritDB server, service account).

ls -tr ~/gritdb1/log/grit_unify_main.log* | xargs grep -h 'Tier-3 (RowVersion' | tail -1

Expected output (example):

[…][INFO][…]   Tier-3 (RowVersion,  80%): max=18686611, initial=9343305, sizeof(PoolChunk)=512 + tuple=2229 = 2741

• The limit is the max= value (in the example above, 18,686,611 rows on a server with 186 GiB of memory). It is proportional to server memory.
• The GritDB engine writes this line when it starts. If there is no such line and the logs were deleted, check again after the next start.
• For one transaction, split it and commit below half the limit. Other transactions and changes that are not applied yet share the same limit. So do not run several large loads at the same time.
• If rows are wide (one row exceeds 2 KB), split into smaller batches by that ratio. For example, for 4 KB rows, split below one quarter of the limit. Measure the row size in the staging table (step 3; if there is none, the table that holds the data to load) as the decompressed size. Use SELECT avg(octet_length(t::text))::int FROM <s>.<t>_load t; (for a large table, FROM <s>.<t>_load t TABLESAMPLE SYSTEM (1)). Long text and bytea columns come out at the decompressed size or more (this errs toward smaller batches).
• In an analytics setup, the initial copy and operational database batch jobs use the same limit (warning in 6.4.1 step 8, 6.4.3). In the initial copy, one table is one transaction.
• For signs that a transaction is approaching the limit, see 7.1.

⚠ Do not write a transaction that exceeds the limit to registered tables

• Reason: GritDB stops (PostgreSQL included). That transaction is not applied.
• Instead: Split it and commit below half the limit. If you cannot split it, contact support (chapter 0).

During a load, watch the growth trend. For metrics, see 7.1. For thresholds, see 7.2.

SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots
WHERE slot_name = 'gritsurfer_slot';

For the initial copy in an analytics setup, attach large tables one at a time.

• Put only some of the tables in the first publication. Add the remaining tables one at a time after the initial copy of the previous table finishes (srsubstate is r, 6.4.1 step 9). To add them, follow “Add a table to an existing analytics setup” in 6.4.2 (ALTER PUBLICATION … ADD TABLE → REFRESH PUBLICATION).
• The number of tables that one subscription copies at the same time is the max_sync_workers_per_subscription value (default 2).
• To reduce the number of tables copied at the same time to one, run ALTER SYSTEM SET max_sync_workers_per_subscription = 1; and then SELECT pg_reload_conf(); on GritDB (no restart, 5.4). When all initial copies show r, run ALTER SYSTEM RESET max_sync_workers_per_subscription; and then SELECT pg_reload_conf();.
• Attach when the operational database is quiet (6.4.3).

6.9 Manage analytic queries

View running analytic queries. The functions are in A.3.

SELECT pid, status, query_start_time,
       now() - query_start_time AS running, query_text
FROM get_gritsurfer_active_queries()
ORDER BY query_start_time;

• Long-running queries are the lines with a large running value.

Stop a query. <pid> is the pid in the result above.

• To check, run SELECT pid, state FROM pg_stat_activity WHERE pid = <pid>;. If it returns 0 rows, the connection was terminated. If it returns idle, only the query was canceled.
• Functions and view: pg_cancel_backend and pg_terminate_backend, pg_stat_activity
• Do not terminate the gritdb_catalog_* internal connections or GritDB workers (7.1).

Check wait time and run time with the stage times in the analytic query history. The query below shows 100 rows from the last hour.

SELECT query_text, status,
       dispatched_time - query_start_time AS wait_admit,
       worker_taken_time - dispatched_time AS wait_engine,
       pg_end_time - query_start_time AS total
FROM get_gritsurfer_query_history(100, now() - interval '1 hour');

• wait_admit is the time spent waiting for a GritDB engine execution slot. wait_engine is the time until the engine assigned a worker. total is the total time.
• Queries that waited past the time limit and ran on PostgreSQL have no row in the history (6.6 Concurrency).
• Concurrency is up to vCPUs × 4. Beyond that, queries wait and, after the time limit (5.6), run on PostgreSQL. Bind execution, PREPARE, and fallback=false stop with an error (6.6).
• Keep separate accounts and connection pools for analytics sessions and for regular sessions. A pooler reuses connections that have the same account and database. For connection poolers, see 6.6. For privileges, see 11.5.
• Long analytic queries delay cold file cleanup, so do not let unfinished queries run for a long time (7.8).
• Resource limits for analytic queries (workload groups) are not available in 1.1 (10.6).

If a problem occurs

• If registration is rejected, see GDB-RUNTIME-001 in QuickStart, chapter 7, and the RUNTIME entries in TroubleShooting.
• If analytics session values differ from the regular session, first confirm that changes are applied (6.6.1). Then check whether an operation not to perform was run (6.7). In an analytics setup, see 6.4.2.
• If subscription apply stopped, see 6.4.2 and 7.5.
• If GritDB stopped during a large load or an initial copy, collect diagnostic data with QuickStart 7.4 before you start it again. Then contact support (6.8).

Learn more

• For monitoring, see chapter 7. To take the analytics setup again, see 8.6. For accounts and privileges, see 11.5. For limitations, see 10.4.
• PostgreSQL 17 documentation: Logical replication, Logical replication restrictions, Logical replication security, CREATE SUBSCRIPTION, and EXPLAIN