PostgreSQL Tables

This section documents all tables related to the PostgreSQL protocol in ProxySQL, including configuration and runtime tables.

List of PostgreSQL Tables

PostgreSQL Tables

pgsql_users

The pgsql_users table defines PostgreSQL users that clients can use to connect to ProxySQL and then subsequently to backend PostgreSQL servers.

CREATE TABLE pgsql_users (
    username VARCHAR NOT NULL,
    password VARCHAR,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
    default_hostgroup INT NOT NULL DEFAULT 0,
    transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1,
    fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
    backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
    frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
    max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
    attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (username, backend),
    UNIQUE (username, frontend)
);

Fields:

• username: The username used for authentication to connect to the PostgreSQL server.
• password: The password associated with the username. The password can be stored in plain text or hashed.
• active: Indicates whether the user is active. If set to 0, the user is tracked in the database but never loaded into the in-memory data structures.
• use_ssl: If set to 1, the user is forced to authenticate using an SSL/TLS certificate.
• default_hostgroup: If no matching query rule is found for the user’s queries, the traffic will be routed to this hostgroup.
• transaction_persistent: If set to 1 for a frontend user, transactions started within a hostgroup will remain in that hostgroup regardless of other rules.
• fast_forward: If set to 1, the query processing layer (rewriting, caching) is bypassed, and the query is passed directly to the backend server.
• backend: If set to 1, this (username, password) pair is used for authenticating to the backend PostgreSQL servers.
• frontend: If set to 1, this (username, password) pair is used for authenticating to the ProxySQL instance itself.
• max_connections: Defines the maximum number of allowable frontend connections for a specific user.
• attributes: A JSON field that can be used to store additional configuration parameters for the user.
• comment: A free-form text field that can be used to store any relevant information about the user.

Important Notes:

• Currently, all PostgreSQL users require both frontend and backend to be set to 1.
• Fast forward bypasses query processing, including caching and rewriting.
• SSL/TLS must be enabled on both the frontend and backend if using compression.
• PostgreSQL users defined in pgsql_users should not be used for admin-admin_credentials or admin-stats_credentials.

pgsql_servers

The pgsql_servers table defines all the backend PostgreSQL servers that ProxySQL manages. Similar to the mysql_servers table for MySQL.

CREATE TABLE pgsql_servers (
    hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0,
    hostname VARCHAR NOT NULL,
    port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 5432,
    status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
    weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1,
    compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0,
    max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,
    max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,
    use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
    max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (hostgroup_id, hostname, port)
);

Fields:

• hostgroup_id: The ID of the hostgroup to which this PostgreSQL server belongs. A single server can be part of multiple hostgroups.
• hostname: The hostname or IP address of the PostgreSQL server.
• port: The TCP port number on which the PostgreSQL server is listening. If port is 0, the value in hostname is interpreted as a Unix Socket file.
• status: The configured status of the PostgreSQL server:
  • ONLINE: The server is fully operational and available for connections.
  • SHUNNED: The server is temporarily taken out of use due to connection errors or exceeding the configured replication lag threshold.
  • OFFLINE_SOFT: No new connections are created to this server, but existing connections are kept until they are returned to the pool or destroyed.
  • OFFLINE_HARD: No new connections are created and existing free connections are immediately dropped. Connections currently in use are dropped as soon as the client tries to use them.
• weight: A relative weight assigned to the server within its hostgroup. A higher weight increases the probability of the server being selected.
• compression: If set to 1, new connections to this server will utilize compression.
• max_connections: The maximum number of connections ProxySQL will open to this PostgreSQL server.
• max_replication_lag: If greater than 0, ProxySQL will monitor the replication lag of the server and temporarily shun it if the lag exceeds this threshold.
• use_ssl: If set to 1, connections to this PostgreSQL server will use SSL/TLS.
• max_latency_ms: The maximum acceptable ping time (in milliseconds) for the server. If a server’s ping time exceeds this value, it is excluded from the connection pool (though the server stays ONLINE).
• comment: A free-form text field that can be used to store any relevant information about the server.

Important Notes:

• For a SHUNNED server to be brought back ONLINE, it must be reachable and there must be activity in the connection pool for the specific hostgroup.
• The max_connections setting should be configured in conjunction with the backend PostgreSQL server’s max_connections setting to avoid exceeding its capacity.
• Multiplexing can significantly improve performance by allowing multiple frontend connections to share a single backend connection.

pgsql_replication_hostgroups

Table pgsql_replication_hostgroups defines replication hostgroups for use with traditional primary/replica replication:

CREATE TABLE pgsql_replication_hostgroups (
  writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
  reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0),
  check_type VARCHAR ... NOT NULL DEFAULT 'read_only',
  comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))

NOTE: Right now the only supported check_type is read_only, so the field is unused.

Each row in pgsql_replication_hostgroups represents a pair of writer_hostgroup and reader_hostgroup. ProxySQL will monitor the configured check_type for all the servers in the specified hostgroups, and based on the result will assign the server to the writer or reader hostgroups.

• writer_hostgroup - the hostgroup where writers are configured. Nodes that have a read only check returning 0 will be assigned to this hostgroup.
• reader_hostgroup - the hostgroup where readers are configured. Read traffic should be sent to this hostgroup, assuming query rules or a separate read only user is defined to route traffic here.
• check_type - type of check to perform. Right now only read_only is supported, so the field is unused.
• comment - text field that can be used for any purpose defined by the user.

pgsql_query_rules

The pgsql_query_rules table defines routing policies and attributes for PostgreSQL queries handled by ProxySQL. It is analogous to the mysql_query_rules table.

CREATE TABLE pgsql_query_rules (
    rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
    username VARCHAR,
    database VARCHAR,
    flagIN INT CHECK (flagIN >= 0) NOT NULL DEFAULT 0,
    client_addr VARCHAR,
    proxy_addr VARCHAR,
    proxy_port INT CHECK (proxy_port >= 0 AND proxy_port <= 65535),
    digest VARCHAR,
    match_digest VARCHAR,
    match_pattern VARCHAR,
    negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
    re_modifiers VARCHAR DEFAULT 'CASELESS',
    flagOUT INT CHECK (flagOUT >= 0),
    replace_pattern VARCHAR CHECK(CASE WHEN replace_pattern IS NULL THEN 1 WHEN replace_pattern IS NOT NULL AND match_pattern IS NOT NULL THEN 1 ELSE 0 END),
    destination_hostgroup INT DEFAULT NULL,
    cache_ttl INT CHECK(cache_ttl > 0),
    cache_empty_result INT CHECK (cache_empty_result IN (0,1)) DEFAULT NULL,
    cache_timeout INT CHECK(cache_timeout >= 0),
    reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
    timeout INT UNSIGNED CHECK (timeout >= 0),
    retries INT CHECK (retries>=0 AND retries <=1000),
    delay INT UNSIGNED CHECK (delay >=0),
    next_query_flagIN INT UNSIGNED,
    mirror_flagOUT INT UNSIGNED,
    mirror_hostgroup INT UNSIGNED,
    error_msg VARCHAR,
    OK_msg VARCHAR,
    sticky_conn INT CHECK (sticky_conn IN (0,1)),
    multiplex INT CHECK (multiplex IN (0,1,2)),
    log INT CHECK (log IN (0,1)),
    apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
    attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',
    comment VARCHAR
);

Fields:

• rule_id: The unique ID of the query rule. Rules are processed in ascending order of rule_id.
• active: Indicates whether the rule is active. If set to 0, the rule is not considered by the query processing module.
• username: Filters queries based on the username of the connected client.
• database: Filters queries based on the database (schema) being accessed.
• flagIN: Used to create chains of rules. The initial flag value is 0. Only rules with flagIN = 0 are considered initially.
• client_addr: Filters queries based on the source IP address of the client.
• proxy_addr: Filters queries based on the local IP address of ProxySQL where the query is received.
• proxy_port: Filters queries based on the local port number of ProxySQL where the query is received.
• digest: Filters queries based on a query digest (a hash of the query).
• match_digest: A regular expression that matches the query digest.
• match_pattern: A regular expression that matches the query text.
• negate_match_pattern: If set to 1, only queries that do not match the match_pattern or match_digest are considered.
• re_modifiers: A comma-separated list of options that modify the behavior of the regular expression engine. CASELESS makes the match case-insensitive.
• flagOUT: The flag value to assign to a query after a rule is matched.
• replace_pattern: The pattern to use to replace a matched pattern. Uses RE2::Replace.
• destination_hostgroup: Routes matched queries to the specified hostgroup.
• cache_ttl: The time-to-live (in milliseconds) for caching the results of a query.
• cache_empty_result: Controls whether to cache empty result sets.
• cache_timeout: Not yet implemented.
• reconnect: Not yet implemented.
• timeout: The maximum time (in milliseconds) allowed for a query to execute.
• retries: The maximum number of times a query can be retried if a failure is detected.
• delay: The number of milliseconds to delay the execution of a query.
• next_query_flagIN: Not yet implemented.
• mirror_flagOUT: Not yet implemented.
• mirror_hostgroup: Not yet implemented.
• error_msg: Blocks a query and returns the specified error message to the client.
• OK_msg: Returns the specified message for a query that matches the rule.
• sticky_conn: Not yet implemented.
• multiplex: Controls whether multiplexing is disabled or not for the current query.
• log: Controls whether to log the query to the events log.
• apply: If set to 1, no further query rules are evaluated after this rule is matched and processed.
• attributes: A JSON field that can be used to specify load balancing between hostgroups based on query rules.
• comment: A free-form text field that can be used to store any relevant information about the rule.

pgsql_query_rules_fast_routing

The pgsql_query_rules_fast_routing table is an extension of pgsql_query_rules and is evaluated after the pgsql_query_rules table for fast routing.

CREATE TABLE pgsql_query_rules_fast_routing (
    username VARCHAR NOT NULL,
    database VARCHAR NOT NULL,
    flagIN INT NOT NULL DEFAULT 0,
    destination_hostgroup INT CHECK (destination_hostgroup >= 0) NOT NULL,
    comment VARCHAR NOT NULL,
    PRIMARY KEY (username, database, flagIN)
);

Fields:

• username: Filters queries based on the username of the connected client.
• database: Filters queries based on the database (schema) being accessed.
• flagIN: Evaluated in the same way as flagIN in pgsql_query_rules and correlates to the flagOUT of a matched rule in pgsql_query_rules.
• destination_hostgroup: Routes matched queries to the specified hostgroup.
• comment: A free-form text field that can be used to store any relevant information about the rule.

How it works:

1. pgsql_query_rules Evaluation: ProxySQL first evaluates the rules in the pgsql_query_rules table.
2. flagOUT and apply: If a rule in pgsql_query_rules has a flagOUT value that differs from 0 and apply is not set, the query will be re-evaluated.
3. pgsql_query_rules_fast_routing Evaluation: ProxySQL then checks if there are any matching rules in the pgsql_query_rules_fast_routing table using the flagIN value.
4. Fast Routing: If a matching rule is found in pgsql_query_rules_fast_routing, the query is routed to the specified destination_hostgroup.

Important Notes:

• pgsql_query_rules_fast_routing rules are only evaluated after pgsql_query_rules.
• The flagIN value in pgsql_query_rules_fast_routing must align with the flagOUT or apply settings in pgsql_query_rules.

pgsql_hostgroup_attributes

The pgsql_hostgroup_attributes table defines hostgroup-specific settings that override global configurations for a particular PostgreSQL hostgroup.

Note: The features in this table are still under development, and some of the settings may not be fully functional yet.

CREATE TABLE pgsql_hostgroup_attributes (
    hostgroup_id INT NOT NULL PRIMARY KEY,
    max_num_online_servers INT CHECK (max_num_online_servers>=0 AND max_num_online_servers <= 1000000) NOT NULL DEFAULT 1000000,
    autocommit INT CHECK (autocommit IN (-1, 0, 1)) NOT NULL DEFAULT -1,
    free_connections_pct INT CHECK (free_connections_pct >= 0 AND free_connections_pct <= 100) NOT NULL DEFAULT 10,
    init_connect VARCHAR NOT NULL DEFAULT '',
    multiplex INT CHECK (multiplex IN (0, 1)) NOT NULL DEFAULT 1,
    connection_warming INT CHECK (connection_warming IN (0, 1)) NOT NULL DEFAULT 0,
    throttle_connections_per_sec INT CHECK (throttle_connections_per_sec >= 1 AND throttle_connections_per_sec <= 1000000) NOT NULL DEFAULT 1000000,
    ignore_session_variables VARCHAR CHECK (JSON_VALID(ignore_session_variables) OR ignore_session_variables = '') NOT NULL DEFAULT '',
    hostgroup_settings VARCHAR CHECK (JSON_VALID(hostgroup_settings) OR hostgroup_settings = '') NOT NULL DEFAULT '',
    servers_defaults VARCHAR CHECK (JSON_VALID(servers_defaults) OR servers_defaults = '') NOT NULL DEFAULT '',
    comment VARCHAR NOT NULL DEFAULT ''
);

Fields:

• hostgroup_id: The ID of the PostgreSQL hostgroup to which these attributes apply.
• max_num_online_servers: When the number of ONLINE servers in the hostgroup exceeds this value, no new connections are accepted into the hostgroup.
• autocommit: Not yet implemented.
• free_connections_pct: The percentage of open idle connections from the total maximum number of connections for a specific server in a hostgroup.
• init_connect: A string containing one or more SQL statements, separated by semicolons, that will be executed by ProxySQL for each backend connection in the specific hostgroup when created or initialized.
• multiplex: Controls whether multiplexing is enabled for the specific hostgroup.
• connection_warming: Controls whether ProxySQL will keep opening new connections in the specific hostgroup until the expected number of warm connections is reached.
• throttle_connections_per_sec: Determines the maximum number of new connections that can be opened per second for the specific hostgroup.
• ignore_session_variables: Not yet implemented.
• hostgroup_settings: Allows you to specify settings for the hostgroup that override global configurations.
• servers_defaults: Allows you to specify default values for servers when discovered and placed in the hostgroup by ProxySQL’s monitoring module. Currently supported values are: weight, max_connections, use_ssl.
• comment: A free-form text field that can be used to store any relevant information about the hostgroup.