Hologres:Best practices for fast point queries

Prerequisites

Before you begin, make sure you have:

• A running Hologres instance

• The VPC endpoint of your instance (available in the Network Information section on the instance details page in the Hologres console)

• Your AccessKey ID and AccessKey secret from the AccessKey Management page

How it works

A point query looks up a single row by its primary key. Hologres routes each query directly to the shard that owns the row, retrieves it from the row store, and returns it — all without a full table scan.

Three table properties control this path:

• Clustering key — sorts rows within each shard so the target row is found in one seek.

• Distribution key — determines which shard owns a given key, enabling direct routing.

• Row store — keeps all columns of a row physically adjacent, so a single read returns the full row.

When the primary key, clustering key, and distribution key all point to the same column, every point query hits exactly one shard and reads exactly one row.

Design the table

Create the table with orientation set to row, and set both the clustering_key and distribution_key to the key column. By default, the primary key is also the distribution key, so the distribution_key call is redundant when the primary key and distribution key are the same column — but making it explicit avoids ambiguity.

For string columns, use TEXT instead of VARCHAR or CHAR. Variable-length types require extra length checks that add latency at scale.

-- Create a row-oriented table optimized for point queries.
-- The key column is the primary key, clustering key, and distribution key.
-- Queries on key= go directly to the matching shard and return in one seek.
BEGIN;
CREATE TABLE test_kv_table (
  key   TEXT PRIMARY KEY,
  value TEXT
);
CALL set_table_property('test_kv_table', 'orientation',      'row');
CALL set_table_property('test_kv_table', 'clustering_key',   'key');
CALL set_table_property('test_kv_table', 'distribution_key', 'key');
COMMIT;

Query data

-- Single key
SELECT * FROM test_kv_table WHERE key = '1';

-- Multiple keys in one round trip
SELECT * FROM test_kv_table WHERE key IN ('1', '2', '3');

// Query multiple keys using a reused prepared statement.
// The prepared statement is created once and reused for every query in the loop.
private static void testKV(Connection conn) throws Exception {
    String sql = "SELECT * FROM test_kv_table WHERE key = ?";
    try (PreparedStatement stmt = conn.prepareStatement(sql)) {
        for (int i = 0; i < 100; ++i) {
            stmt.setString(1, Integer.toString(i));
            long begin = System.currentTimeMillis();
            try (ResultSet rs = stmt.executeQuery()) {
                long cost = System.currentTimeMillis() - begin;
                while (rs.next()) {
                    System.out.println(
                        "data => " + rs.getObject(1) + " " + rs.getObject(2)
                        + " latency => [" + cost + "]ms"
                    );
                }
            }
        }
    }
}

<dependency>
  <groupId>com.alibaba.hologres</groupId>
  <artifactId>holo-client</artifactId>
  <version>{1.2.16.5}</version>
</dependency>

// The JDBC URL format is jdbc:postgresql://host:port/db.
// Use your VPC endpoint as the host.
HoloConfig config = new HoloConfig();
config.setJdbcUrl(url);
config.setUsername(username);
config.setPassword(password);
config.setReadThreadCount(10); // Maintain up to 10 concurrent JDBC connections

try (HoloClient client = new HoloClient(config)) {
    TableSchema schema0 = client.getTableSchema("t0");

    // Submit two Gets asynchronously. HoloClient batches them into one SQL call.
    Get get  = Get.newBuilder(schema0).setPrimaryKey("id", 0).build(); // WHERE id = 0
    Get get1 = Get.newBuilder(schema0).setPrimaryKey("id", 1).build(); // WHERE id = 1

    client.get(get).thenAcceptAsync(record  -> { /* handle result */ });
    client.get(get1).thenAcceptAsync(record -> { /* handle result */ });
} catch (HoloClientException e) {
    // handle exception
}

Complete Java example

The following end-to-end example creates the table, inserts 10,000 rows, and queries 100 keys using a prepared statement.

package test;

import org.postgresql.jdbc.PgConnection;
import java.sql.*;

public class TestPointQuery {

    // Create a row-oriented table and populate it with test data.
    private static void init(Connection conn) throws Exception {
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("DROP TABLE IF EXISTS test_kv_table;");
            stmt.execute("BEGIN;");
            stmt.execute(
                "CREATE TABLE IF NOT EXISTS test_kv_table(" +
                "  key   TEXT PRIMARY KEY," +
                "  value TEXT" +
                ");"
            );
            stmt.execute("CALL set_table_property('test_kv_table', 'orientation', 'row');");
            stmt.execute("CALL set_table_property('test_kv_table', 'shard_count',  '20');");
            stmt.execute("END;");
            stmt.execute(
                "INSERT INTO test_kv_table SELECT i, i FROM generate_series(1, 10000) i"
            );
        }
    }

    // Query 100 keys using a reused prepared statement.
    private static void testKV(Connection conn) throws Exception {
        String sql = "SELECT * FROM test_kv_table WHERE key = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            for (int i = 0; i < 100; ++i) {
                stmt.setString(1, Integer.toString(i));
                long begin = System.currentTimeMillis();
                try (ResultSet rs = stmt.executeQuery()) {
                    long cost = System.currentTimeMillis() - begin;
                    while (rs.next()) {
                        System.out.println(
                            "data => " + rs.getObject(1) + " " + rs.getObject(2)
                            + " latency => [" + cost + "]ms"
                        );
                    }
                }
            }
        }
    }

    public static void main(String[] args) throws Exception {
        Class.forName("org.postgresql.Driver").newInstance();

        // Replace these placeholders with your actual values.
        String host     = "<vpc-endpoint>";  // VPC endpoint from the Hologres console
        String db       = "<database-name>";
        String user     = "<access-key-id>";      // AccessKey ID
        String password = "<access-key-secret>";  // AccessKey secret
        String url      = "jdbc:postgresql://" + host + "/" + db;

        try (PgConnection conn = (PgConnection) DriverManager.getConnection(url, user, password)) {
            System.out.println("Initializing test_kv_table...");
            init(conn);
            System.out.println("Running point query test...");
            testKV(conn);
        }
    }
}

Replace the following placeholders:

Connection best practices