State Data Model

Relevant source files

The following files were used as context for generating this wiki page:

• src/meltano_state_backend_postgresql/backend.py
• tests/test_state.py
• README.md
• pyproject.toml
• tests/test_integration.py
• tests/test_conninfo.py

The State Data Model for the meltano-state-backend-postgresql extension defines how Meltano job states are persisted, structured, and managed within a PostgreSQL database. Its primary purpose is to provide a reliable, ACID-compliant storage layer for job progress, allowing Meltano to resume incremental data integration tasks.

The model revolves around a centralized table structure that maps Meltano's internal MeltanoState objects to relational rows. It supports flexible schema positioning, custom table naming, and robust concurrency control through PostgreSQL advisory locks.

Sources: README.md:1-20, src/meltano_state_backend_postgresql/backend.py:1-40

Database Schema Structure

The core of the data model is a single table, typically named state. This table stores the identification, payload, and temporal metadata for each Meltano job state.

Table Definition

The table is created automatically by the PostgreSQLStateStoreManager using the following schema:

Column	Type	Constraints	Description
state_id	VARCHAR(900)	PRIMARY KEY	Unique identifier for the Meltano job/state.
partial_state	TEXT		JSON-encoded string representing incomplete/incremental state.
completed_state	TEXT		JSON-encoded string representing fully successfully flushed state.
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	The timestamp of the last modification.

Sources: src/meltano_state_backend_postgresql/backend.py:204-213, tests/test_integration.py:86-98

The following entity relationship diagram illustrates the logical structure of the state storage:

The diagram shows the schema of the state storage table where state_id serves as the primary unique identifier.
Sources: src/meltano_state_backend_postgresql/backend.py:205-212

Data Mapping and Serialization

The backend translates between Python MeltanoState objects and the PostgreSQL storage format. JSON serialization is used to bridge the gap between Python dictionaries and the database TEXT columns.

• Serialization: When saving (set), partial_state and completed_state dictionaries are converted to JSON strings using json.dumps. If a state value is None, it is stored as a SQL NULL.
• Deserialization: When retrieving (get), JSON strings are parsed back into dictionaries using json.loads. The implementation ensures that NULL database values are converted back to empty Python dictionaries {} to maintain compatibility with Meltano's core expectations.

Sources: src/meltano_state_backend_postgresql/backend.py:223-233, src/meltano_state_backend_postgresql/backend.py:255-265, tests/test_state.py:65-80

Connection and Namespace Logic

The data model supports flexible namespacing through PostgreSQL schemas and custom table names. This is managed via Identifier objects to ensure safe SQL generation.

This flowchart describes how the backend determines the target table location based on user configuration.
Sources: src/meltano_state_backend_postgresql/backend.py:173-182, tests/test_conninfo.py:46-60

Namespace Configuration Options

Setting	Setting Name	Default	Description
schema	state_backend.postgresql.schema	None (server search_path)	The Postgres schema where the table resides.
table	state_backend.postgresql.table	state	The specific table name for storage.

Sources: src/meltano_state_backend_postgresql/backend.py:82-95, pyproject.toml:39-47

Concurrency Control Model

To prevent race conditions when multiple Meltano processes attempt to update the same state simultaneously, the model implements a distributed locking mechanism using PostgreSQL Advisory Locks.

Lock Key Derivation

Because PostgreSQL advisory locks require 64-bit integer keys, the state_id (a string) is transformed using a deterministic hashing process:

1. The state_id string is encoded to UTF-8.
2. An MD5 hash is generated from the string.
3. The first 8 bytes of the hash are converted into a signed 64-bit integer.

Sources: src/meltano_state_backend_postgresql/backend.py:356-372

Locking Flow

The system uses a "try-and-retry" logic to acquire the lock before performing state operations:

The sequence diagram illustrates the lifecycle of a state lock during a job execution.
Sources: src/meltano_state_backend_postgresql/backend.py:388-410, tests/test_state.py:155-190

Lifecycle and State Operations

The PostgreSQLStateStoreManager provides the implementation for the standard state lifecycle operations:

1. Initialize: Connects to the database and executes CREATE TABLE IF NOT EXISTS to ensure the data model is present.
2. Set: Uses an UPSERT pattern (INSERT ... ON CONFLICT (state_id) DO UPDATE) to either create a new state or update an existing one atomically.
3. Get: Retrieves state by state_id.
4. List: Retrieves state_id values, supporting glob-to-SQL-LIKE pattern conversion (e.g., job_* becomes job_%).
5. Clear: Performs a TRUNCATE TABLE to remove all state records.

Sources: src/meltano_state_backend_postgresql/backend.py:204-213, src/meltano_state_backend_postgresql/backend.py:230-244, src/meltano_state_backend_postgresql/backend.py:307-320, tests/test_integration.py:158-168