PGWire Client Overview
QuestDB implements the PostgreSQL wire protocol (PGWire) to allow clients to connect to QuestDB using PostgreSQL client libraries. This is a great way to get started with QuestDB, as it allows you to use existing PostgreSQL clients and libraries.
Python
Python is a programming language that lets you work quickly and integrate systems more effectively.
PHP
PHP is a popular general-purpose scripting language that is especially suited to web development.
R
R is a programming language and free software environment for statistical computing and graphics supported by the R Foundation for Statistical Computing.
When using PGWire with QuestDB, there are a few important things to know and the rest of this document will cover them in more detail.
Querying vs. Ingestion
The PGWire interface is primarily recommended for querying data from QuestDB. For data ingestion, especially for high-throughput scenarios, QuestDB recommends using its clients that support the InfluxDB Line Protocol (ILP). These are optimized for fast data insertion.
Timestamp Handling
QuestDB stores all timestamps internally in
UTC. However, when
transmitting timestamps over the PGWire protocol, QuestDB represents them as
TIMESTAMP WITHOUT TIMEZONE. This can lead to client libraries interpreting
these timestamps in their local timezone by default, potentially causing
confusion or incorrect data representation. Our language-specific guides provide
detailed examples on how to configure your client to correctly interpret these
timestamps as UTC.
We realize the current behavior is not ideal and we are actively working on improving it. In the meantime, we recommend that you set the timezone in your client library to UTC to ensure consistent handling of timestamps.
PGWire vs. SQL Semantics
While QuestDB supports the PGWire protocol for communication, its SQL dialect and feature set are not identical to PostgreSQL. QuestDB is a specialized time-series database and does not support all SQL features, functions, or data types that a standard PostgreSQL server does. Always refer to the QuestDB SQL documentation for supported operations.
Forward-only Cursors
QuestDB's cursors are forward-only, differing from PostgreSQL's support for scrollable cursors (which allow bidirectional navigation and arbitrary row access). With QuestDB, you can iterate through query results sequentially from start to finish, but you cannot move backward or jump to specific rows. Explicit DECLARE CURSOR statements for scrollable types, or operations like fetching in reverse (e.g., Workspace BACKWARD), are not supported.
This limitation can impact client libraries that rely on scrollable cursor features. For example, Python's psycopg2 driver might encounter issues if attempting such operations. For optimal compatibility, choose drivers or configure existing ones to use forward-only cursors, such as Python's asyncpg driver.
Protocol Flavors and Encoding
The PostgreSQL wire protocol has different implementations and options. When your client library allows, prefer the Extended Query Protocol over the Simple Query Protocol. Additionally, for optimal performance and type fidelity, choose clients that support BINARY encoding for data transfer over TEXT encoding whenever possible. The specifics of how to configure this will vary by client library.
Decimal
To insert decimal values via PGWire, you must either use the m suffix to
indicate that the value is a decimal literal or cast the value to decimal. For
example:
INSERT INTO my_table (decimal_column) VALUES (123.45m); -- Using 'm' suffix
INSERT INTO my_table (decimal_column) VALUES (CAST($1 AS DECIMAL(18, 3))); -- Using CAST over bind parameter
In the text format, PostgreSQL clients send decimal values as strings.
Currently, QuestDB parses these strings as double values and doesn't
implicitly convert them to decimal to avoid unintended precision loss. So you
must explicitly cast double values to decimal in your SQL queries when
inserting into decimal columns.
Highly-Available Reads with QuestDB Enterprise
QuestDB Enterprise supports running multiple replicas to serve queries. Many client libraries allow specifying multiple hosts in the connection string. This ensures that initial connections succeed even if a node is unavilable. If the connected node fails later, the application should catch the error, reconnect to another host, and retry the read.
For background and code samples in multiple languages, see:
- Blog: Highly-available reads with QuestDB
- Examples: questdb/questdb-ha-reads