JSON大约有多快-> Postgres 9.4中的JSONB列转换 [英] Roughly how fast is JSON -> JSONB column conversion in Postgres 9.4

问题描述

我希望从Postgres 9.3迁移到9.4，并且在JSON列中有很多数据.很好，但我想看看迁移到更高效的列存储(JSONB似乎是一项非常令人兴奋的技术！).

I'm looking to migrate from Postgres 9.3 to 9.4, and have a lot of data in JSON columns. While it's fine, I wanted to have a look at migrating to the more efficient column storage (which JSONB seems to be — a really exciting piece of tech!).

要真正进行迁移，我想了解类似的迁移特性

To actually migrate, I want to know migration characteristics for something like

ALTER TABLE table_with_json
    ALTER COLUMN my_json
    SET DATA TYPE jsonb
    USING my_json::jsonb;

(来自这个有用的问题).

理想情况下，最好知道迁移100万和1000万条目需要多长时间，以及如何扩展.

Ideally, it would be good to know how long it takes to migrate 1mil and 10mil entries, and how it scales.

虽然我可以自己获得这些数字，但我认为这个问题可能对其他人有帮助.如果我自己尝试一下，一定会为其他人提供答案，但不确定何时会发生.

While I can get these numbers myself, I thought this question might be helpful for others. If I end up trying this out myself, I'll be sure to provide an answer for others, but not sure when that'll happen.

推荐答案

从JSON迁移到JSONB需要282秒.它基本上与使用SQL INSERT插入数据所需的时间相匹配.

Migrating from JSON to JSONB took 282 seconds. It basically matches the time required to insert data with an SQL INSERT.

测试环境:

• PostgreSQL 9.5
• 4核，16GB RAM
• 每行2KB JSON内容
• 520万行

这篇关于JSON大约有多快-> Postgres 9.4中的JSONB列转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！