postgresql - 非常大的表大小，但行数很少

问题描述

我有这个只有 1M 行的表，但大小是 49GB

我已经在每一列上运行了 pg_column_size，我得到了这个

唯一索引大小：

index_items_on_profile_id_and_code_and_sku：2.64GB
unique_null_sku_code：6.36 MB

我怎么能理解这个尺寸这么大的原因？

PG STAT 用户表

select * from pg_stat_user_tables;

架构数据库

create_table "items", force: :cascade do |t|
    t.bigint "profile_id", null: false
    t.bigint "category_id"
    t.string "title"
    t.string "image"
    t.string "code"
    t.string "sku"
    t.string "parent_code"
    t.datetime "created_at", default: -> { "CURRENT_TIMESTAMP" }, null: false
    t.datetime "updated_at", default: -> { "CURRENT_TIMESTAMP" }, null: false
    t.string "brand"
    t.decimal "price"
    t.string "crawling_status"
    t.decimal "spend", default: "0.0"
    t.decimal "sales", default: "0.0"
    t.string "manufacturer"
    t.string "status", default: "", null: false
    t.string "images", array: true
    t.string "eligibility_status"
    t.json "eligibility_list"
    t.boolean "active", default: true, null: false
    t.index "profile_id, code, ((sku IS NULL))", name: "unique_null_sku_code", unique: true, where: "(sku IS NULL)"
    t.index ["profile_id", "code", "sku"], name: "index_items_on_profile_id_and_code_and_sku", unique: true
  end

PG 列大小查询

select
    pg_size_pretty(pg_relation_size('items')) as pg_relation_size,
    pg_size_pretty(pg_total_relation_size('items')) as pg_total_relation_size,
    pg_size_pretty (pg_indexes_size('items')) as pg_indexes_size,
    pg_size_pretty (pg_tablespace_size('items')) as pg_tablespace_size
FROM (
    select
        
        pg_size_pretty(sum(pg_column_size(profile_id))) as profile_id,
        pg_size_pretty(sum(pg_column_size(category_id))) as category_id,
        pg_size_pretty(sum(pg_column_size(title))) as title,
        pg_size_pretty(sum(pg_column_size(image))) as image,
        pg_size_pretty(sum(pg_column_size(code))) as code,
        pg_size_pretty(sum(pg_column_size(sku))) as sku,
        pg_size_pretty(sum(pg_column_size(parent_code))) as parent_code,
        pg_size_pretty(sum(pg_column_size(created_at))) as created_at,
        pg_size_pretty(sum(pg_column_size(updated_at))) as updated_at,
        pg_size_pretty(sum(pg_column_size(brand))) as brand,
        pg_size_pretty(sum(pg_column_size(price))) as price,
        pg_size_pretty(sum(pg_column_size(crawling_status))) as crawling_status,

        pg_size_pretty(sum(pg_column_size(spend))) as spend,
        pg_size_pretty(sum(pg_column_size(sales))) as sales,
        pg_size_pretty(sum(pg_column_size(manufacturer))) as manufacturer,
        pg_size_pretty(sum(pg_column_size(status))) as status,



        pg_size_pretty(sum(pg_column_size(images))) as images,
        pg_size_pretty(sum(pg_column_size(eligibility_status))) as eligibility_status,
        pg_size_pretty(sum(pg_column_size(eligibility_list))) as eligibility_list,
        pg_size_pretty(sum(pg_column_size(active))) as active

    from items
) i

POSTGRES 配置 https://pastebin.com/9Z2nwBLy

标签： postgresql

postgresql - 非常大的表大小，但行数很少

问题描述

解决方案

推荐阅读