Configuration parameters#
This reference guide describes the configuration parameters provided by the Postgres Analytics Accelerator (PGAA) extension, allowing you to tune the execution engine, manage remote connectivity, and optimize query performance across your data lake.
Executor engine#
pgaa.executor_engine#
Determines which compute backend is responsible for executing analytical operations.
Supported values are seafowl , and spark_connect .
Default is seafowl .
pgaa.spark_connect_url#
Defines the connection endpoint for the Spark Connect service. Specifies
the URL of the remote Spark Connect service. This setting is mandatory
when pgaa.executor_engine is set to spark_connect .
The value must follow the specific Spark Connect URI format:
sc://<host>:<port> .
Default is NULL .
pgaa.spark_connect_extra_config#
When using pgaa.executor_engine= 'spark_connect' , provides
fine-grained tuning parameters and runtime settings directly to the
Spark session as a JSON object. See Spark Connect configuration for a list of options.
Example
SET pgaa.spark_connect_extra_config = {
"spark.sql.execution.arrow.maxRecordsPerBatch": "65536"
};
Default is NULL .
Query behavior#
pgaa.enable_direct_scan#
Controls the query execution strategy for analytical tables. It
determines whether PGAA attempts to offload the entire query to the
optimized Seafowl engine or processes it using standard Postgres
compatibility layers.
Default is on , which means PGAA will attempt a DirectScan first
before failing over to CompatScan.
pgaa.direct_scan_fail_behavior#
Defines the system’s reaction when a Direct Scan is attempted but cannot be completed due to unsupported SQL features or syntax.
Supported values are:
ignore: The system silently falls back to CompatScan.warn(default): The query executes using the compatibility fallback, but Postgres issues a warning notice indicating that DirectScan failed.error: The query execution is aborted entirely if it cannot use DirectScan.
Behavior if DirectScan was requested but failed, one of ignore ,
warn , error . Default is warn .
pgaa.enable_join_pushdown#
In CompatScan, controls the optimization strategy for join operations
between analytical tables. It determines whether joins are processed
locally within Postgres or offloaded to the remote execution engine
(Seafowl or Spark).
Default is on .
pgaa.enable_groupby_pushdown#
In CompatScan, attempt to offload aggregation operations (like SUM ,
COUNT , AVG ) and (GROUP BY ) to the remote executor engine
(Seafowl or Spark).
Default is on .
pgaa.enable_orderby_pushdown#
In CompatScan, attempt to offload ORDER BY and any constant
LIMIT /OFFSET as a bounded top-N operation to the remote
executor engine (Seafowl or Spark).
Default is on .
pgaa.enable_distinct_pushdown#
In CompatScan, attempt to offload SELECT DISTINCT to the remote
executor engine (Seafowl or Spark).
Default is on .
!!!Note pgaa.enable_distinct_pushdown doesn’t apply on WarehousePG,
where the MPP planner deduplicates DISTINCT with a Motion and
HashAggregate above the scan before PGAA’s pushdown stage runs.
pgaa.enable_window_pushdown#
In CompatScan, attempt to offload window functions to the remote
executor engine (Seafowl or Spark).
Default is on .
pgaa.ctas_intermediate_flush_size#
Controls the batching behavior during large INSERT or
CREATE TABLE AS (CTAS) operations into PGAA-managed tables.
Default is 8192.
pgaa.max_in_memory_mb#
Sets the maximum amount of memory, in megabytes, that the writer can utilize to buffer data before a mandatory flush to storage is triggered.
Default is 3072 (MB).
pgaa.scan_aggregation_cost_factor#
A multiplier used by the Postgres query planner to weight the cost of
performing aggregations on the remote backend (Spark or Seafowl )
compared to local Postgres execution.
Default is 0.005.
pgaa.scan_per_tuple_cost#
A multiplier used by the Postgres query planner to weight the cost of
scanning a single row (tuple) on the remote backend (Spark or
Seafowl ) compared to local Postgres execution.
Default is 0.02.
pgaa.scan_sort_cost_factor#
A multiplier used by the Postgres query planner to weight the cost of a
pushed-down sort made by the remote backend (Spark or Seafowl )
relative to Postgres’s own tuplesort estimate.
Default is 0.5.
pgaa.scan_startup_cost#
A multiplier used by the Postgres query planner to weight the fixed
“startup” cost of initiating a scan on the remote backend (Spark or
Seafowl ) compared to local Postgres execution.
Default is 25.
pgaa.use_seafowl_cost_estimates#
Determines whether the Postgres query planner should request real-time
cost estimates directly from the Seafowl engine instead of relying
on the statistics collected internally by Postgres.
Default is on .
pgaa.enable_metadata_stats#
For tables lacking real ANALYZE statistics, synthesizes planner
statistics from Iceberg, Delta Lake, or Parquet metadata, including
row-count estimates, null fractions, average column widths,
distinct-value counts, and min/max histograms. The query planner uses
these synthesized statistics for CompatScan cardinality estimates and
query plans.
Default is on .
Seafowl#
!!!Note The pgaa.autostart_seafowl parameters are not available on
PGAA for WarehousePG. On WarehousePG, Seafowl is managed as a
systemd service on each host. See the WarehousePG installation section for
details.
pgaa.autostart_seafowl#
Controls automatic start of the Seafowl process managed by the
background worker.
Default is on .
pgaa.autostart_seafowl_max_memory_mb#
Sets the upper limit in megabytes on the amount of RAM the embedded
Seafowl engine is permitted to consume, ensuring that the analytical
engine does not starve the main Postgres process of resources.
Default is 2147483647.
pgaa.autostart_seafowl_memory_pool_type#
Sets the DataFusion memory pool type used by the embedded Seafowl
engine. Accepted values are greedy and fair_spill . greedy
allocates memory as fast as possible for best query performance.
fair_spill distributes memory fairly across concurrent consumers and
spills intermediate data to disk when memory is exhausted, which reduces
out-of-memory failures under high concurrency at some performance cost.
Default is greedy .
pgaa.autostart_seafowl_track_memory_consumers#
Wraps the Seafowl memory pool with a tracking layer that includes
the top memory consumers in out-of-memory error messages, making it
easier to diagnose which query or operation exhausted memory.
Default is off .
pgaa.autostart_seafowl_enable_metrics#
Enables Prometheus metrics on the Seafowl process by opening a
dedicated HTTP endpoint that serves real-time performance metrics to
monitor the health, query throughput, and resource utilization of the
analytical engine using standard observability stacks.
Default value is on .
pgaa.autostart_seafowl_metrics_host#
Metrics endpoint bind host.
When pgaa.autostart_seafowl_enable_metrics= on , specifies the
hostname or IP address where the Prometheus metrics service will be
reachable.
Default is 127.0.0.1 , which points to the local loopback for
security, ensuring metrics aren’t exposed publicly by default.
pgaa.autostart_seafowl_metrics_port#
Defines the specific network port used for the Prometheus metrics
service when pgaa.autostart_seafowl_enable_metrics= on .
Supported values range from 1024 to 65535. Default is 9090 .
pgaa.autostart_seafowl_enable_object_store_cache#
Determines whether to enable the Seafowl object store cache for
external storage locations on S3, Azure, or GCP.
Default is on .
pgaa.autostart_seafowl_enable_metadata_cache#
Determines whether Seafowl caches Iceberg table metadata in its REST
catalog client. Cached entries are revalidated with conditional (ETag)
requests, reducing query planning latency.
Default is on .
pgaa.autostart_seafowl_metadata_cache_capacity_mb#
Sets the maximum total size, in megabytes, of Iceberg table metadata
retained in the Seafowl metadata cache.
Default is 256.
pgaa.autostart_seafowl_metadata_cache_ttl_s#
Sets the time, in seconds, after which a cached Iceberg table metadata entry is refreshed. A value of 0 disables expiry and relies solely on ETag revalidation.
Default is 0.
pgaa.autostart_seafowl_concurrent_manifest_ops#
Sets the maximum number of concurrent Iceberg manifest object-store
operations in the Seafowl process, bounding both in-flight requests
and peak manifest-decode memory during scan planning and maintenance.
Default is 64.
pgaa.autostart_seafowl_log_level#
Specifies the logging verbosity for the background Seafowl process.
Supported values include error , warn , info , debug ,
and trace .
Default is info .
pgaa.autostart_seafowl_path#
Specifies the absolute file path to the Seafowl executable. This
tells the PGAA supervisor exactly which binary to launch when starting
the background analytical engine. Modification of this parameter is only
required for custom configurations where you are running the Seafowl
service on a separate machine or container.
Default is an empty string.
pgaa.autostart_seafowl_port#
Specifies the TCP port that the background Seafowl process listens
on. The PGAA extension uses this port to communicate with the local
execution engine.
Default is 47470.
pgaa.autostart_seafowl_tmp_dir#
Specifies the directory where the background Seafowl process stores
temporary files during query execution.
Default is an empty string.
pgaa.enable_maintenance_worker#
Controls whether the PGAA background supervisor launches and manages a dedicated maintenance worker process.
Default is off .
pgaa.maintenance_worker_sleep_interval#
Defines the duration, in seconds, that the maintenance worker process waits before checking for new tasks when there is currently no pending work to perform.
Default is 30 seconds.
pgaa.seafowl_client_retry_base_ms#
Specifies the initial delay, in milliseconds, that the Postgres client
waits before retrying a failed request to the Seafowl process. For
every subsequent failure, the wait time doubles.
Default is 100.
pgaa.seafowl_url#
Specifies the endpoint URL used by the PGAA extension to communicate
with the Seafowl service. By default, PGAA manages the Seafowl
process automatically on the same host as the database. Modification of
this parameter is only required for custom configurations where you are
running the Seafowl service on a separate machine or container.
Default is http://localhost:47470 .
Catalog synchronization#
pgaa.metastore_sync_poll_rate_s#
Sets the interval, in seconds, for polling the state of the current attached catalog. Setting this to 0 completely disables the automatic background synchronization.
Default is 60 seconds.
pgaa.metastore_sync_stale_duration_s#
Defines the threshold at which the local metadata for an attached catalog is considered stale. If the metadata in the local cache is older than this duration, the system prioritizes a refresh to ensure consistency with the data lake.
Default is 60 seconds.
pgaa.enable_metastore_sync_worker#
Determines whether PGAA spawns a dedicated background worker to continuously synchronize metadata from a remote provider, such as an Iceberg REST catalog.
Default is off .
Caching#
pgaa.lakehouse_table_stats_cache_ttl_s#
Controls the duration (in seconds) that physical table sizes are cached
to support the pgaa.lakehouse_table_stats() function. This prevents
frequent requests to the object store for metadata. Setting this to 0
disables statistics caching and forces the function to retrieve data
directly from the storage provider.
Default is 300 seconds.
pgaa.metadata_stats_ttl_s#
Controls the duration, in seconds, that PGAA caches the planner
statistics synthesized by pgaa.enable_metadata_stats before refetching them from
Iceberg, Delta Lake, or Parquet metadata. Setting this to 0 always
refetches the statistics.
Default is 300 seconds.
pgaa.autostart_seafowl_object_store_cache_capacity_mb#
Specifies the maximum amount of local disk space, in megabytes, that the
Seafowl process can use for caching data from storage locations.
Default is 1024.
pgaa.autostart_seafowl_object_store_cache_min_fetch_size_kb#
Defines the minimum size of an outbound request sent to the object store
when pgaa.autostart_seafowl_enable_object_store_cache is enabled.
Default is 1024.
pgaa.autostart_seafowl_object_store_cache_ttl_s#
Specifies the Time To Live (TTL) for objects stored in the Seafowl
object store cache. This parameter determines how long a cached data
chunk remains valid before it is eligible for eviction.
Default is 3600.
Postgres Distributed (PGD) integration#
pgaa.max_replication_lag_s#
Maximum allowed lag (seconds) before a flush is forced.
Default is 5.
DataFusion-inherited configurations#
Since PGAA uses Apache DataFusion as its underlying execution engine for
the Seafowl query engine, PGAA provides several
pgaa.datafusion.* configuration parameters that allow you to tune
the query execution engine.
These settings control low-level behaviors like memory management,
execution parallelism, optimizer rules, and query explain output. The
exposed namespaces are execution , explain , and optimizer .
You can check the full list of DataFusion configuration parameters that PGAA incorporates by running:
SELECT name, setting, short_desc, context FROM pg_settings WHERE name LIKE %pgaa.data%;
Refer to the DataFusion documentation for a comprehensive list of keys, default
values, and performance impacts. When referring to the DataFusion
documentation, you can translate their documentation keys to the PGAA
configuration parameters by prefixing them with pgaa. .