type: docs
title: 'PostgreSQL Data Accelerator'
sidebar_label: 'PostgreSQL Data Accelerator'
description: 'PostgreSQL Data Accelerator Documentation'
sidebar_position: 5
:::info Spice.ai Enterprise
The PostgreSQL Data Accelerator is available only in Spice.ai Enterprise (opens in a new tab).
:::
:::warning[Deprecated]
The postgres acceleration engine is deprecated in v2.4 and will be removed in v2.5. A Spicepod with a dataset or view that uses it logs one warning at startup, and one on each hot reload that adds or changes such a component, naming those datasets and views. A cluster executor does not log it. Runtimes up to and including v2.4.0-rc.3 do not log this warning. Move these accelerations to Spice Cayenne (opens in a new tab) before upgrading to v2.5; see Migrating to Spice Cayenne (opens in a new tab).
:::
To use PostgreSQL as Data Accelerator, specify postgres as the engine for acceleration.
Configuration
The connection to PostgreSQL can be configured by providing the following params:
pg_host: The hostname of the PostgreSQL server.pg_port: The port of the PostgreSQL server.pg_db: The name of the database to connect to.pg_user: The username to connect with.pg_pass: The password to connect with. Use the secret replacement syntax (opens in a new tab) to load the password from a secret store, e.g. ${secrets:my_pg_pass}.pg_sslmode: Optional. Specifies the SSL/TLS behavior for the connection, supported values:
verify-full: (default) This mode requires an SSL connection, a valid root certificate, and the server host name to match the one specified in the certificate.verify-ca: This mode requires a TLS connection and a valid root certificate.require: This mode requires a TLS connection.prefer: This mode will try to establish a secure TLS connection if possible, but will connect insecurely if the server does not support TLS.disable: This mode will not attempt to use a TLS connection, even if the server supports it.
pg_sslrootcert: Optional. Path to a custom PEM certificate file that the connector will trust.
Configuration params are provided in the acceleration section of a dataset.
Specify different secrets for a PostgreSQL source and acceleration:
:::warning[Limitations]
- The Postgres accelerator does not support
Map types.
- The Postgres federated queries may result in unexpected result types due to the difference in DataFusion and Postgres size increase rules. Explicitly specify the expected output type of aggregation functions when writing queries involving Postgres tables in Spice. For example, rewrite
SUM(int_col) into CAST (SUM(int_col) as BIGINT).
:::
Arrow to PostgreSQL Type Mapping
The table below lists the supported Apache Arrow data types (opens in a new tab) and their mappings to PostgreSQL types (opens in a new tab) when stored
| Arrow Type | sea_query ColumnType | PostgreSQL Type |
|---|
Int8 | TinyInteger | smallint |
Int16 | SmallInteger | smallint |
Int32 | Integer | integer |
Int64 | BigInteger | bigint |
UInt8 | TinyUnsigned | smallint |
UInt16 | SmallUnsigned |
Cookbook
