Skip to content

Connection Settings

The PostgreSQL® ESF Client allows defining some properties to connect to a PostgreSQL® server.

JDBC URL

The JDBC connector URL of the database instance can be specified via the property JDBC URL from the component configuration page.

Allowed URLs can have the following forms:

jdbc:postgresql:database
jdbc:postgresql:/
jdbc:postgresql://host/database
jdbc:postgresql://host/
jdbc:postgresql://host:port/database
jdbc:postgresql://host:port/

Where:

  • host is the hostname of the server, which defaults to localhost.
  • port is the port number the server is listening on, which defaults to the PostgreSQL® standard port number 5432.
  • database is the database name, which defaults to the username used to connect to the database.

Tip

If the ESF PostgreSQL client is not able to immediately connect to the server, it will retry the connection attempt every 30 seconds. Once connected, failover is handled by the JDBC driver (see Connection failover)

Connection pool max size

This parameter allows setting the maximum pool size. If in doubt on which size to choose, please leave the default value.

If a specific fine-tuning of this parameter is needed, please read the About Pool Sizing tutorial provided by the Hikari CP project used by this plugin as JDBC Connection Pool.

Username and Password

The username to use for connecting to the database instance is specified via the property Username, which is required. The Password field is optional but will work only if the PostgreSQL server is put in trust mode (anyone who can connect to the server is authorized to access the database with whatever database user name). A default server is not configured as trusted, so a password field is usually required.

Warning

If no SSL method is selected, the password will be sent in plain text.

Connection failover

This client provides a connection failover when a connected server becomes unavailable.

When multiple hosts are specified in JDBC URL as a comma-separated list like jdbc:postgresql://host1:port1,host2:port2/ database, then the driver will attempt to make connections to each host until it succeeds. The host list will be processed in order if the property loadBalanceHosts enabled is set to false, otherwise, the hosts are chosen randomly from the set of candidates.

This feature is useful when running against a high-availability postgres installation that has identical data on each node.

The hostRecheckSeconds property controls how long in seconds the knowledge about a host state is cached in JVM-wide global cache. The default value is 10 seconds.

The targetServerType property controls the type (state) of the server that the driver will connect to. Possible values are:

  • any
  • primary/master: server that allows writes.
  • secondary/slave: server that is read-only.
  • preferPrimary: try to connect to primary if any is available, otherwise, fall back to secondary.
  • preferSecondary: try to connect to secondary if any is available, otherwise fall back to primary.

Autosave feature

Autosave is a feature of PostgreSQL® database that allows the creation of a savepoint before each query to use for rollback when the query fails. At first glance, it seems similar to the transaction mechanism but it is not. The semantics of a transaction is all or nothing, and a transaction can contain many queries. This option comes in place when that semantic is not desirable.

The possible values for the Autosave mode property are:

  • never (default)
  • always
  • conservative

When Autosave Mode is set to always the JDBC driver sets a savepoint before each query, and rolls back to that savepoint in case of failure. If conservative mode is selected, the driver determines first if reparsing of the query will work, and only then it will be reparsed and retried. This mode allows rollbacking when errors like cached statement cannot change return type appear since the driver will flush whatever details it is caching at the rollback.

Autosave Mode does come with a performance penalty, prefer transactions if not strictly needed.

The Autosave savepoints cleanup determines if the created savepoint in autosave mode is released before the statement. This is done to avoid running out of shared buffers on the server in the case where thousands of queries are performed. The default is false.

Example transaction vs. autosave

If Autosave mode is set to always, when the query INSERT INTO sensor2_data ... fails then the query INSERT INTO sensor1_data ... is still valid. If those two queries were put inside a transaction, the failure of the second would have invalidated also the first.

INSERT INTO sensor1_data ...
INSERT INTO sensor2_data ...

Performance settings

The property reWriteBatchedInserts allows the driver to rewrite batch inserts by merging multiple insert statements into one providing a 2-3x performance improvement. For example, the queries:

INSERT INTO foo (col1, col2, col3) VALUES (1, 2, 3)
INSERT INTO foo (col1, col2, col3) VALUES (4, 5, 6)

Will be rewritten in:

INSERT INTO foo (col1, col2, col3) VALUES (1, 2, 3), (4, 5, 6)

It is possible to control the number of rows fetched in ResultSets with the property defaultRowFetchSize. Limiting the number of rows that are fetched avoids unnecessary memory consumption and as a consequence OutOfMemoryErrors. The default is zero, meaning that ResultSet will fetch all rows at once.

Query modes

Using the property preferQueryMode it is possible to specify which mode is used to execute queries to the database:

  • extended (default): creates a server PreparedStatement, uses parse, bind and execute. Protects against SQL injections and makes the statement reusable.
  • simple: simple query protocol - no parse, no bind, text mode only. Fewer round trips to db. Required for replication connection.
  • extendedForPrepared: does not use extended for statements, only prepared statements. Potentially faster execution of statements.
  • extendedCacheEverything: uses extended and caches even simple statements such as SELECT x FROM table; which are normally not cached.

Other settings

By default, the driver sanitizes the columns in the ResultSet to lowercase. To disable this behavior, set the property disableColumnSanitiser to false.

By default, the current schema is public meaning that if you want to refer to a table in a different schema it would have to be specified by schema.table. Using the currentSchema property it is possible to specify the schema (or several schemas separated by commas) to be set in the search path. This schema will be used to resolve unqualified object names used in statements over this connection. As an example, if the currentSchema is set to example, then the following queries are equal:

SELECT * FROM example.mytable;
SELECT * FROM mytable;

More settings that are not covered by the previous configurations (see PostgreSQL JDBC Driver Documentation) can be passed to the component using the Additional settings property, which allows a comma-separated list of key-value pairs. Any value will be casted in one of the Java types that are allowed by the driver: String, int, boolean. Example:

additionalSetting1=false, additionalSetting2=xyz, additionalSetting3=49