PostgreSQL

Detailed information on the PostgreSQL state store component

Note

This is the v2 of the PostgreSQL state store component, which contains some improvements to performance and reliability. New applications are encouraged to use v2.

The PostgreSQL v2 state store component is not compatible with the v1 component, and data cannot be migrated between the two components. The v2 component does not offer support for state store query APIs.

There are no plans to deprecate the v1 component.

This component allows using PostgreSQL (Postgres) as state store for Dapr, using the “v2” component. See this guide on how to create and apply a state store configuration.

apiVersion: dapr.io/v1alpha1
kind: Component
metadata:
  name: <NAME>
spec:
  type: state.postgresql
  # Note: setting "version" to "v2" is required to use the v2 of the component
  version: v2
  metadata:
    # Connection string
    - name: connectionString
      value: "<CONNECTION STRING>"
    # Timeout for database operations, as a Go duration or number of seconds (optional)
    #- name: timeout
    #  value: 20
    # Prefix for the table where the data is stored (optional)
    #- name: tablePrefix
    #  value: ""
    # Name of the table where to store metadata used by Dapr (optional)
    #- name: metadataTableName
    #  value: "dapr_metadata"
    # Cleanup interval in seconds, to remove expired rows (optional)
    #- name: cleanupInterval
    #  value: "1h"
    # Maximum number of connections pooled by this component (optional)
    #- name: maxConns
    #  value: 0
    # Max idle time for connections before they're closed (optional)
    #- name: connectionMaxIdleTime
    #  value: 0
    # Controls the default mode for executing queries. (optional)
    #- name: queryExecMode
    #  value: ""
    # Uncomment this if you wish to use PostgreSQL as a state store for actors (optional)
    #- name: actorStateStore
    #  value: "true"

Warning

The above example uses secrets as plain strings. It is recommended to use a secret store for the secrets as described here.

Spec metadata fields

Authenticate using a connection string

The following metadata options are required to authenticate using a PostgreSQL connection string.

Authenticate using Microsoft Entra ID

Authenticating with Microsoft Entra ID is supported with Azure Database for PostgreSQL. All authentication methods supported by Dapr can be used, including client credentials (“service principal”) and Managed Identity.

Other metadata options

Setup PostgreSQL

• Self-Hosted

1. Run an instance of PostgreSQL. You can run a local instance of PostgreSQL in Docker with the following command:

   docker run -p 5432:5432 -e POSTGRES_PASSWORD=example postgres

   This example does not describe a production configuration because it sets the password in plain text and the user name is left as the PostgreSQL default of “postgres”.

2. Create a database for state data.
   Either the default “postgres” database can be used, or create a new database for storing state data.

   To create a new database in PostgreSQL, run the following SQL command:

   CREATE DATABASE my_dapr;

Advanced

Differences between v1 and v2

The PostgreSQL state store v2 was introduced in Dapr 1.13. The pre-existing v1 remains available and is not deprecated.

In the v2 component, the table schema has been changed significantly, with the goal of increasing performance and reliability. Most notably, the value stored by Dapr is now of type BYTEA, which allows faster queries and, in some cases, is more space-efficient than the previously-used JSONB column.
However, due to this change, the v2 component does not support the Dapr state store query APIs.

Also, in the v2 component, ETags are now random UUIDs, which ensures better compatibility with other PostgreSQL-compatible databases, such as CockroachDB.

Because of these changes, v1 and v2 components are not able to read or write data from the same table. At this stage, it’s also impossible to migrate data between the two versions of the component.

Displaying the data in human-readable format

The PostgreSQL v2 component stores the state’s value in the value column, which is of type BYTEA. Most PostgreSQL tools, including pgAdmin, consider the value as binary and do not display it in human-readable form by default.

If you want to inspect the value in the state store, and you know it’s not binary (for example, JSON data), you can have the value displayed in human-readable form using a query like the following:

-- Replace "state" with the name of the state table in your environment
SELECT *, convert_from(value, 'utf-8') FROM state;

TTLs and cleanups

This state store supports Time-To-Live (TTL) for records stored with Dapr. When storing data using Dapr, you can set the ttlInSeconds metadata property to indicate after how many seconds the data should be considered “expired”.

Because PostgreSQL doesn’t have built-in support for TTLs, this is implemented in Dapr by adding a column in the state table indicating when the data is to be considered “expired”. Records that are “expired” are not returned to the caller, even if they’re still physically stored in the database. A background “garbage collector” periodically scans the state table for expired rows and deletes them.

You can set the deletion interval of expired records with the cleanupInterval metadata property, which defaults to 3600 seconds (that is, 1 hour).

• Longer intervals require less frequent scans for expired rows, but can require storing expired records for longer, potentially requiring more storage space. If you plan to store many records in your state table, with short TTLs, consider setting cleanupInterval to a smaller value; for example, 5m (5 minutes).
• If you do not plan to use TTLs with Dapr and the PostgreSQL state store, you should consider setting cleanupInterval to a value <= 0 (for example, 0 or -1) to disable the periodic cleanup and reduce the load on the database.

Related links

• Basic schema for a Dapr component
• Read this guide for instructions on configuring state store components
• State management building block

Last modified March 21, 2024: Merge pull request #4082 from newbe36524/v1.13 (f4b0938)