pgbulk

package
v0.1.95 Latest Latest
Warning

This package is not in the latest version of its module.

Go to latest
Published: Sep 6, 2026 License: MIT Imports: 5 Imported by: 0

Documentation

Overview

Package pgbulk loads many rows at once, the only way that is fast enough for the importers.

The shape is always the same: COPY into an unindexed temp table, then one INSERT ... SELECT into the real one. COPY skips the per-row parse and plan that INSERT pays, and the temp table has no indexes or constraints to maintain while it fills; the single INSERT that follows is what pays for those, once, in bulk. Against batched INSERTs this is five to ten times faster on the row counts the archives produce.

Index

Constants

View Source
const FlushAt = 50_000

FlushAt is how many rows accumulate before a COPY is issued. High enough that the per-COPY overhead disappears, low enough that a day's archive does not have to fit in memory twice.

Variables

This section is empty.

Functions

func MergeSQL

func MergeSQL(table, staging string, columns, pk []string, mode Conflict) string

MergeSQL builds the INSERT ... SELECT that moves staged rows into the real table.

The SELECT is DISTINCT ON the key because a single archive can carry the same key twice, and without the guard DoUpdate aborts the whole statement with "ON CONFLICT DO UPDATE command cannot affect row a second time".

func Staging

func Staging(ctx context.Context, conn Conn, name, like string) (func(), error)

Staging creates a session-scoped temp table for a load that is not inside a transaction, and returns a function that drops it.

The caller must call that function — see StagingTx for what happens otherwise. It uses a context detached from the caller's so that cleanup still runs when the import was cancelled, which is exactly when a leftover temp table is most annoying.

func StagingTx

func StagingTx(ctx context.Context, tx Conn, name, like string) error

StagingTx creates a temp table shaped like a real one, scoped to the transaction.

ON COMMIT DROP is what makes it safe, and the reason is worth stating: a temp table belongs to the *session*, not the connection handle. Creating one with PRESERVE ROWS inside a transaction that then commits leaves it behind on whichever pooled connection ran it, and the next caller to draw that connection fails with "relation already exists". Behind pgbouncer in session pooling that leak outlives the process. Letting the commit drop it removes the whole class of problem — and there is nothing to clean up on the error path either, because the rollback drops it too.

Types

type Conflict

type Conflict int

Conflict says what a merge does when a row already exists.

const (
	// DoNothing keeps what is already stored. Correct when the existing row is
	// at least as good as the incoming one — re-importing an archive, say.
	DoNothing Conflict = iota
	// DoUpdate overwrites every non-key column. Correct when the source is
	// authoritative and the stored row may be stale.
	DoUpdate
)

type Conn

type Conn interface {
	CopyFrom(ctx context.Context, table pgx.Identifier, columns []string, src pgx.CopyFromSource) (int64, error)
	Exec(ctx context.Context, sql string, args ...any) (pgconn.CommandTag, error)
}

Conn is the subset of pgx a bulk load needs. Both *pgx.Conn and pgx.Tx satisfy it, so a load can run inside a caller's transaction or on its own.

type Copier

type Copier struct {
	// contains filtered or unexported fields
}

Copier buffers rows and copies them into a staging table.

func NewCopier

func NewCopier(ctx context.Context, conn Conn, table string, columns []string) *Copier

NewCopier starts a buffered copy into table.

func (*Copier) Add

func (c *Copier) Add(row []any) error

Add queues one row, flushing when the buffer fills.

func (*Copier) Flush

func (c *Copier) Flush() error

Flush writes whatever is buffered. Callers must call it once at the end.

func (*Copier) Written

func (c *Copier) Written() int64

Written reports how many rows have reached the staging table.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL