作者

digoal

日期

2020-11-23

标签

PostgreSQL , golang , driver

背景

用golang选什么PG驱动:

https://github.com/JackC/pgx

pgx - PostgreSQL Driver and Toolkit

pgx is a pure Go driver and toolkit for PostgreSQL.

database/sql
database/sql

The toolkit component is a related set of packages that implement PostgreSQL functionality such as parsing the wire protocol and type mapping between PostgreSQL and Go. These underlying packages can be used to implement alternative drivers, proxies, load balancers, logical replication clients, etc.

pgx v4v3

Example Usage

```go package main

import ( "context" "fmt" "os"

"github.com/jackc/pgx/v4"

)

func main() { conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL")) if err != nil { fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err) os.Exit(1) } defer conn.Close(context.Background())

var name string
var weight int64
err = conn.QueryRow(context.Background(), "select name, weight from widgets where id=$1", 42).Scan(&name, &weight)
if err != nil {
    fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
    os.Exit(1)
}

fmt.Println(name, weight)

} ```

See the getting started guide for more information.

Choosing Between the pgx and database/sql Interfaces

database/sql

The pgx interface is faster and exposes more features.

database/sqlint64float64bool[]bytestringtime.Timenildatabase/sql.Scannerdatabase/sql/driver/driver.Valuer

Features

database/sql
log15adapterlogruszapzerologinetcidrnet.IPNetnet.IPdatabase/sql.Scannerdatabase/sql/driver.Valuer

Performance

database/sql
  1. PostgreSQL specific types - Types such as arrays can be parsed much quicker because pgx uses the binary format.
  2. Automatic statement preparation and caching - pgx will prepare and cache statements by default. This can provide an significant free improvement to code that does not explicitly use prepared statements. Under certain workloads, it can perform nearly 3x the number of queries per second.
  3. Batched queries - Multiple queries can be batched together to minimize network round trips.

Comparison with Alternatives

For prepared queries with small sets of simple data types, all drivers will have have similar performance. However, if prepared statements aren't being explicitly used, pgx can have a significant performance advantage due to automatic statement preparation. pgx also can perform better when using PostgreSQL-specific data types or query batching. See go_db_bench for some database driver benchmarks.

database/sql
database/sqldatabase/sqldatabase/sql

Level of access, ORM

go-pg is a PostgreSQL client and ORM. It includes many features that traditionally sit above the database driver, such as ORM, struct mapping, soft deletes, schema migrations, and sharding support.

pgx is "closer to the metal" and such abstractions are beyond the scope of the pgx project, which first and foremost, aims to be a performant driver and toolkit.

Testing

PGX_TEST_DATABASEPGX_TEST_DATABASEPG*

Example Test Environment

Connect to your PostgreSQL server and run:

create database pgx_test;

Connect to the newly-created database and run:

create domain uint64 as numeric(20,0);

Now, you can run the tests:

PGX_TEST_DATABASE="host=/var/run/postgresql database=pgx_test" go test ./...
PGX_TEST_PGBOUNCER_CONN_STRING

Supported Go and PostgreSQL Versions

pgx supports the same versions of Go and PostgreSQL that are supported by their respective teams. For Go that is the two most recent major releases and for PostgreSQL the major releases in the last 5 years. This means pgx supports Go 1.13 and higher and PostgreSQL 9.5 and higher.

Version Policy

v4

PGX Family Libraries

pgx is the head of a family of PostgreSQL libraries. Many of these can be used independently. Many can also be accessed from pgx for lower-level control.

pgconnlibpq
pgxpool
database/sqldatabase/sql
uuidhstorejsonbyteanumericintervalinetdatabase/sql

pgproto3 provides standalone encoding and decoding of the PostgreSQL v3 wire protocol. This is useful for implementing very low level PostgreSQL tooling.

pglogrepl provides functionality to act as a client for PostgreSQL logical replication.

pgmock offers the ability to create a server that mocks the PostgreSQL wire protocol. This is used internally to test pgx by purposely inducing unusual errors. pgproto3 and pgmock together provide most of the foundational tooling required to implement a PostgreSQL proxy or MitM (such as for a custom connection pooler).

tern is a stand-alone SQL migration system.

pgerrcode contains constants for the PostgreSQL error codes.

3rd Party Libraries with PGX Support

Library for scanning data from a database into Go structs and more.

multi host支持

https://github.com/cockroachdb/vendored/blob/master/github.com/jackc/pgx/conn.go

https://github.com/cockroachdb/vendored/blob/5d84f81dcedd801eeb8251bb0fea7562a27c8424/github.com/jackc/pgx/conn.go

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.