Hi @BandanaPandey,

I looked a bit closer into the postgrex client and the build_bootstrap_query method that generates the query. There are two aspects in the implementation:

1. It uses the (currently) unsupported typsend column
2. It uses correlated subqueries

The missing typesend column is likely an issue we can address short-term (my personal estimation, needs to be validated by a developer/product management).

Correlated subqueries, however, are also not supported in CrateDB. It's a prominent feature (#9320), but work on it hasn't started yet. So even if typsend would be added, the correlated subquery issue would still block the usage of postgrex.

However, it is also possible to change the postgrex implementation to not use correlated subqueries. I took the resulting query that gets sent from the client to CrateDB, and below is a version without correlated subqueries but having the same semantics:

SELECT "oid",
       typname,
       typsend,
       typreceive,
       typoutput,
       typinput,
       typelem AS "coalesce",
       rngsubtype AS "coalesce",
       CASE WHEN atttypid = [NULL] THEN NULL ELSE atttypid END AS atttypid
FROM (
  SELECT "t"."oid",
         "t"."typname",
         "t"."typsend",
         "t"."typreceive",
         "t"."typoutput",
         "t"."typinput",
         COALESCE("d"."typelem", "t"."typelem") AS typelem,
         COALESCE("r"."rngsubtype", 0) AS rngsubtype,
         ARRAY_AGG("a"."atttypid") AS atttypid
  FROM "pg_type" t
  LEFT JOIN "pg_type" d ON "t"."typbasetype" = "d"."oid"
  LEFT JOIN "pg_range" r ON "r"."rngtypid" = "t"."oid" OR ("t"."typbasetype" <> 0 AND "r"."rngtypid" = "t"."typbasetype")
  LEFT JOIN (
    SELECT DISTINCT s.oid
    FROM pg_type s
    WHERE s.typrelid <> 0
  ) s ON s.oid = t.typelem
  LEFT JOIN "pg_attribute" a ON "a"."attrelid" = "t"."typrelid" AND "a"."attnum" > 0 AND NOT "a"."attisdropped"
  WHERE "t"."typrelid" = 0
    AND (t.typelem = 0 OR s."oid" IS NULL)
  GROUP BY 1, 2, 3, 4, 5, 6, 7, 8
) x

I tested it on a PostgreSQL server and the results were identical. But to make use of it, postgrex would need to get forked and adapted accordingly. Which is something we as Crate.io will probably not be able to do from our side, as we concentrate on reaching PostgreSQL compatibility, but aim not to develop CrateDB-specific drivers.

With that in mind, do you think going with postgrex is still a viable option for you, or would you rather make use of the HTTP endpoint (for the time being, until both issues are addressed)?

ohk @hammerhead thanks for the detailed explanation.
Also, if we move forward with HTTP endpoint for both read/write operations, will there be performance fluctuation compared with postgrex option ?
And will it be possible to have some benchmarking documentation using HTTP endpoints ?

Hi @BandanaPandey,

For INSERT statements, the HTTP endpoint is an equivalent alternative to the PostgreSQL endpoint.
In general, benchmark results can vary significantly depending on the specific setup. Including factors like table layout (number of columns/data types), the nodes' hardware (CPU, RAM, disk)/network, client concurrency, and more. We regularly see throughputs in the range of several 100 thousand rows per second on relatively small productive clusters.
In recent internal benchmarks, we measured throughputs between 150k and 200k rows/s on a single-node instance with 16 CPU cores. We plan to publish more details on that specific benchmark setup in the near future. Those numbers got produced using the HTTP endpoint. Performance generally scales linearly when adding additional nodes (although some overhead exists).

For SELECT statements, the performance of the HTTP endpoint is also expected to be in the same range as the PostgreSQL endpoint. One conceptual difference is, however, that the HTTP endpoint is stateless. This could be relevant for significantly large result sets. With the PostgreSQL protocol, a session is maintained that allows to stream result sets using a cursor. When using the HTTP endpoint, the whole result set is returned directly in the response body. HTTP response bodies generally don't have a restricted size, and I'm also not aware of related issues. But it might be a detail to keep in mind.

Ok thank you so much @hammerhead for the explanation.

I had just one query related to HTTP endpoint, the returned duration specified by the curl statement is in the milliseconds right ?
{"cols":["rounded_hour","minute_slot","avg_values"],"rows":[[1645545600000,3,1.5111666520436604],[1645790400000,0,null],[1645790400000,1,null]],"rowcount":173,"**duration**":371.3942}

yes, timestamps are in milliseconds by default