9

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;

(from this helpful question).

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.

gregoltsov
  • 2,269
  • 1
  • 22
  • 37

2 Answers2

5

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

Testing environment:

  • PostgreSQL 9.5
  • 4 cores, 16GB RAM
  • 2KB/row of JSON content
  • 5.2 million rows
Boris Schegolev
  • 3,601
  • 5
  • 21
  • 34
3

Some back-of-the napkin numbers for a test instance migration:

  • PostrgeSQL 9.6
  • 2 vcpu cores, 8GB RAM, EBS max bandwith 450 MBit/s
  • 930K rows
  • 20KB/row of json

Took 66:40min - 4000 seconds.
During the migration the write IOPS were around 250, read at 50, cpu stayed at 60% throughout the run.

Todor Minakov
  • 19,097
  • 3
  • 55
  • 60