我正在测试Postgres插入性能。我有一个表,其中有一列,其数据类型为数字。还有一个索引。我用这个查询填充了数据库:

insert into aNumber (id) values (564),(43536),(34560) ...

我非常快地插入了400万行,使用上面的查询一次插入10,000行。在数据库达到600万行之后,性能急剧下降到每15分钟100万行。有什么技巧可以提高插入性能吗?我需要在这个项目上的最佳插入性能。

在内存为5gb的机器上使用Windows 7 Pro。


当前回答

除了Craig Ringer的文章和depesz的博客文章外,如果您想通过在事务中使用预处理语句插入来加快通过ODBC (psqlodbc)接口的插入速度,还需要做一些额外的事情来使其快速工作:

Set the level-of-rollback-on-errors to "Transaction" by specifying Protocol=-1 in the connection string. By default psqlodbc uses "Statement" level, which creates a SAVEPOINT for each statement rather than an entire transaction, making inserts slower. Use server-side prepared statements by specifying UseServerSidePrepare=1 in the connection string. Without this option the client sends the entire insert statement along with each row being inserted. Disable auto-commit on each statement using SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, reinterpret_cast<SQLPOINTER>(SQL_AUTOCOMMIT_OFF), 0); Once all rows have been inserted, commit the transaction using SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT);. There is no need to explicitly open a transaction.

不幸的是,psqlodbc通过发出一系列未准备好的插入语句来“实现”SQLBulkOperations,因此为了实现最快的插入,需要手动编写上述步骤。

其他回答

为了获得最佳的插入性能,如果可以选择禁用索引。除此之外,更好的硬件(磁盘、内存)也很有帮助

除了Craig Ringer的文章和depesz的博客文章外,如果您想通过在事务中使用预处理语句插入来加快通过ODBC (psqlodbc)接口的插入速度,还需要做一些额外的事情来使其快速工作:

Set the level-of-rollback-on-errors to "Transaction" by specifying Protocol=-1 in the connection string. By default psqlodbc uses "Statement" level, which creates a SAVEPOINT for each statement rather than an entire transaction, making inserts slower. Use server-side prepared statements by specifying UseServerSidePrepare=1 in the connection string. Without this option the client sends the entire insert statement along with each row being inserted. Disable auto-commit on each statement using SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, reinterpret_cast<SQLPOINTER>(SQL_AUTOCOMMIT_OFF), 0); Once all rows have been inserted, commit the transaction using SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT);. There is no need to explicitly open a transaction.

不幸的是,psqlodbc通过发出一系列未准备好的插入语句来“实现”SQLBulkOperations,因此为了实现最快的插入,需要手动编写上述步骤。

我今天花了大约6个小时在同一个问题上。插入以“常规”速度(每100K小于3秒)进行,直到5MI(总共30MI)行,然后性能急剧下降(一直下降到每100K 1分钟)。

我不会列出所有不起作用的事情,直接切入正题。

我在目标表上放置了一个主键(这是一个GUID),我的30MI或行愉快地以每100K不到3秒的恒定速度流到目的地。

使用COPY表…用二进制,根据文档“比文本和CSV格式快一些。”只有当您有数百万行要插入,并且您对二进制数据感到满意时才这样做。

下面是一个使用psycopg2和二进制输入的Python食谱示例。

我也遇到了这个插入性能问题。我的解决方案是衍生一些go例程来完成插入工作。与此同时,SetMaxOpenConns应该被赋予一个适当的数字,否则会有太多的打开连接错误被警告。

db, _ := sql.open() 
db.SetMaxOpenConns(SOME CONFIG INTEGER NUMBER) 
var wg sync.WaitGroup
for _, query := range queries {
    wg.Add(1)
    go func(msg string) {
        defer wg.Done()
        _, err := db.Exec(msg)
        if err != nil {
            fmt.Println(err)
        }
    }(query)
}
wg.Wait()

对于我的项目,加载速度要快得多。这段代码片段只是给出了它的工作原理。读者应该能够轻松地修改它。