Architecture
GritDB runs on two engines: the transaction engine (PostgreSQL 17.11) and the analytics engine (GritDB engine). The analytics engine has three parts, PLUG, UNIFY and BURST, and PLUG connects to the transaction engine. The two engines run together on one server and appear to applications as one database. There are two ways to adopt GritDB: add it next to your operational database as an analytics system, or use it as one database.
GritDB
PLUG Connects to the transaction engine
UNIFY Hybrid storage · reads
BURST Optimization · parallel execution
grit_admin starts the two engines and the catalog process together and watches them.
Two engines
Transaction engine
PostgreSQL 17.11
It is the SQL connection point. It handles writes and transactions, tables that are not registered, and queries the analytics engine cannot run. It stores data in rows.
Analytics engine
GritDB engine · GritSurfer
It has three parts: PLUG, UNIFY and BURST. PLUG receives changes and queries in analytics sessions from the transaction engine, UNIFY stores the changes, and BURST runs the queries.
Show query pathsHide query paths
- Regular session (default)
- The transaction engine runs all queries.
- Analytics session
- The analytics engine runs SELECT statements that read only registered tables, including the tables they read inside subqueries, CTEs and views. This holds even for single-row lookups. PLUG receives these queries and passes them to BURST. The analytics engine cannot run a query that uses functions or operators that are not built in, returns columns outside the 15 types that 1.1 accepts, or is read through a cursor. The transaction engine runs such a query without a message.
You turn on an analytics session with a connection option when the connection starts (for example, the libpq connection string options='-c gritsurfer_session_mode=surfer'). You cannot change it after you connect, and you cannot turn it on with role or database defaults. In an analytics session, you can choose the engine with statement hints (/*+ engine=pg */ or /*+ engine=burst */). To see which engine runs a query, use EXPLAIN in an analytics session. When the analytics engine runs the query, the first line of the EXPLAIN output shows engine: GritBurst.
Show the process layoutHide the process layout
grit_admin
Supervisor process
It starts the two engines and the catalog process in order and watches them. Use the gritdb-start command to start and the gritdb-stop command to stop.
postgres
Transaction engine
This process runs PostgreSQL 17.11 with the GritSurfer extension (gritsurfer). The extension holds the transaction-engine side of PLUG's connection and the table registration functions.
grit_catalog_process
Catalog process
It handles the GritDB catalog. It reads and writes information about registered tables and columnar files. It starts after the transaction engine is ready.
grit_surfer_process
Analytics engine
This is the GritDB engine. The analytics-engine side of PLUG's connection, UNIFY and BURST run inside this process.
Ways to adopt
Add as an analytics system
Recommended for 1.1
Operational database Built-in replication · CDC · ETL GritDB Reporting · BI tools
Your operational database stays as it is and sends its changes to GritDB. If it is PostgreSQL, it sends them with PostgreSQL built-in replication. If it is another database, a CDC/ETL tool that writes to PostgreSQL sends them. GritDB's transaction engine receives those writes, and the analytics engine's PLUG takes them over. Reporting and BI tools connect to GritDB and turn on an analytics session with a connection option. Writes and queries that need the latest values run on the operational database. When GritDB applies replicated changes, it also waits for the analytics engine to confirm each write. However, transactions on your operational database do not wait for that confirmation.
Use as one database
Transactions and analytics together
Applications GritDB
Applications write to GritDB and query it directly. Installing with the QuickStart gives you this setup. The transaction engine takes writes, and the analytics engine takes queries in analytics sessions. In 1.1, each write to a registered table waits for the analytics engine to confirm receipt, so for operational work we recommend adding GritDB as an analytics system.
You can watch how an analytics system is added in the demo video on the product home page.
What happens on your operational database
This section covers what changes on your operational database when you add the analytics system, and what to check when the analytics system stops.
- During setup (PostgreSQL)
- You change wal_level to logical and create a publication. Changing wal_level needs one restart of the operational database. You need a replication account with the REPLICATION privilege and read access to the published tables, and you must allow connections from the GritDB server in pg_hba.conf and the firewall. When you create a subscription in GritDB, one replication slot is created on the operational database, and an initial copy reads each table in full once at the start. During the initial copy, a long read transaction stays open on the operational database, so cleanup (VACUUM) there is delayed. Add large tables at quiet times. During the initial copy, extra slots and replication connections for copying tables appear for a while and are dropped automatically, so leave room in max_replication_slots and max_wal_senders on the operational database. These also need a restart to change, so set them when you change wal_level. Before you add a table without a primary key to the publication, set its REPLICA IDENTITY. Otherwise, updates and deletes on that table fail with an error on the operational database.
- Normal operation
- The operational database takes committed changes out of the WAL and sends them to GritDB (logical decoding). Heavy report queries run on GritDB, not on the operational database.
- If the analytics system stops
- Because of the replication slot, the operational database does not remove WAL, so disk usage grows. A limit set with max_slot_wal_keep_size on the operational database can protect the disk. However, once the limit is passed, the replication slot becomes invalid and cannot be used again, and the tables in the analytics system must be copied again from the start.
- When you remove it
- When you drop the subscription in GritDB, its replication slot on the operational database is dropped too. Check pg_replication_slots on the operational database to confirm that the slot is gone. The wal_level, max_replication_slots, max_wal_senders, publication, replication account, access rules and REPLICA IDENTITY that you changed on the operational database stay as they are, so revert them if needed. Reverting the first three settings needs one restart, and changed access rules take effect after a reload.
- What to watch
- On the operational database, check pg_replication_slots for the slot status (wal_status) and the amount of WAL the slot holds. That amount is the current WAL position minus restart_lsn. Also check free disk space.
- Until changes show in analytics results
- In our tests, a change that GritDB received usually showed in query results in an analytics session after 12 seconds, and 99 times out of 100 within 22 seconds. Replication time from the operational database to GritDB adds to this. Run queries that must have the latest values on the operational database.
- Other databases (CDC/ETL tools)
- Settings and load on the operational database side, and what happens when the tool stops, depend on the tool.
PLUG: the engine that connects to the transaction engine
PLUG is the engine inside the analytics engine (GritSurfer) that connects to the transaction engine. In 1.1, PLUG does four things.
- Connections: opens a channel for each connection to the transaction engine. This includes connections from the workers that apply built-in replication.
- Change delivery: receives inserts, updates and deletes on registered tables at write time and passes them to UNIFY. Each write gets a receipt confirmation from the analytics engine.
- Query delivery: passes queries in analytics sessions to BURST and returns the results to the transaction engine. Applications get the results over the same connection.
- Recovery: when the analytics engine restarts, takes over changes from the WAL kept by GritDB's recovery replication slot (gritsurfer_slot).
UNIFY · BURST
Storing changes
Queries
In 1.1, queries read columnar files. Rows collected in memory show in queries after they move to columnar files.
Show UNIFY and BURST in detailHide UNIFY and BURST in detail
UNIFY
Hybrid storage engine
It stores data in rows in memory and in columnar files on disk. It collects the inserts, updates and deletes it receives through PLUG as rows in memory, moves them to columnar files, and applies updates and deletes to the files as well. For queries, it reads only the needed columns from the columnar files and passes them to BURST. It skips row groups that cannot match the conditions by checking the minimum and maximum values recorded for each group. This structure, which takes changes in as rows and reads them as columns for analytics, is the basis for handling transactions and analytics in one database.
BURST
Optimization · parallel execution engine
It rebuilds queries in analytics sessions into execution plans suited to analytics, and processes the data UNIFY reads in parallel on many CPU cores. Unlike the transaction engine, which processes one row at a time, it groups values by the thousands and computes them at once.
BURST speeds up queries in analytics sessions in four ways.
- Batch computation: groups values by the thousands and computes them at once. Instead of moving through execution steps for each row, it runs the same computation across the whole batch.
- Parallel execution: many CPU cores share the work of one query. The data is split into small batches, each core handles everything from reading to joins and aggregation, and the results are combined at the end.
- Execution plans for analytics: applies conditions first to cut the data to process. For queries that need only the top few rows (ORDER BY … LIMIT), it keeps only as many rows as needed instead of sorting everything.
- Subquery flattening: turns subqueries that would run again for each outer row (correlated subqueries) into joins and runs them once.
Timings for 22 analytics queries measured on the same server specification are in Analytics performance based on the TPC-H benchmark.
Notes for 1.1
- The two engines run together on one server. GritDB 1.1 has no redundancy or automatic failover of its own. If you added GritDB as an analytics system and lose its server, you can take the data again from the operational database. First, manually drop the replication slot that the lost server left on the operational database. While that slot remains, the operational database does not remove WAL. Then add a new server and copy again from the start.
- The analytics engine reads data stored in columnar files. In our tests, a change you just committed usually showed in query results in an analytics session after 12 seconds, and 99 times out of 100 within 22 seconds. If you added GritDB as an analytics system, replication time from the operational database adds to this. Run queries that must have the latest values in a regular session, or on the operational database if you added GritDB as an analytics system.
- Analytics sessions start as read-only. Run writes in a regular session.
- In 1.1, analytics sessions do not apply row-level security (RLS) policies or the protection of security barrier views (security_barrier). Do not register tables with RLS, and query data that RLS must separate in a regular session. Also do not register a table that some accounts must not read, or a table where some accounts may read only some columns.
- 1.1 does not encrypt incoming connections or its replication connection to the operational database (no TLS). Connect the operational database, reporting tools, BI tools and GritDB inside the same private network. GritDB cannot connect to an operational database that accepts only encrypted connections.
- Register a table for analytics while it is empty. Rows that existed before registration are not moved to the analytics engine. When you add GritDB as an analytics system, register the empty table first and then create the subscription. Rows already in the operational database arrive through the subscription's initial copy and are stored in the analytics engine as well. To be registered, a table needs REPLICA IDENTITY FULL and supported column types.
- The 15 supported column types are boolean, smallint, integer, bigint, real, double precision, numeric, date, time, timestamp, timestamptz, interval, char, varchar and text. A table with a column of any other type, such as uuid, bytea, json, jsonb or arrays, cannot be registered.
- Create columns that must be exact, such as amounts, as numeric(p, s) with a declared precision, and keep p at 38 or less. The analytics engine computes numeric columns without a declared precision as approximate values.
- After you register or unregister a table, reopen the connections that use it, including connections from connection pools and replication workers.
- In 1.1, there is a limit on the number of rows one transaction can write to registered tables, and the limit depends on server memory. In an analytics system, the initial copy of one table is one transaction, and so is a large batch on the operational database. We check large tables and large batches in the discovery meeting.
- TRUNCATE and ALTER TABLE on registered tables are not reflected in the analytics engine in 1.1. Do not run these two commands in GritDB.
- If you added GritDB as an analytics system, a TRUNCATE on the operational database also arrives through built-in replication, but the analytics engine does not reflect it. When you TRUNCATE a table on the operational database, take that table again on the GritDB side. Technical support guides you through the steps.
- Table structure changes (DDL) on the operational database do not arrive through built-in replication. If you change columns on the operational database, replication stops or values no longer match, so take that table again on the GritDB side.
- When you connect with a CDC/ETL tool, too, create and register the tables in GritDB first, then start the initial load. After you register or unregister a table, restart the tool's apply process. Features that the tool does not support for a PostgreSQL target, such as DDL replication, are not supported with GritDB either.
- GritDB's wal_level is fixed at logical. When the analytics engine restarts, PLUG uses it to take over changes from the WAL.
- If you add a table without a primary key to a publication, updates and deletes on that table fail with an error on the operational database. Set REPLICA IDENTITY before you add it. Give the GritDB table the same primary key as on the operational database, too. Without it, replication of updates and deletes stops or slows down.
- All data is in one set, the datafiles directory. Back up, move and delete it as one set, and keep it at the installed path. If you move it to another path, GritDB loses the columnar files.
Installation and operation steps are in the QuickStart.
See GritDB for yourself
Ask for a discovery meeting or a PoC, and we will get in touch.