Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Drop unused tables #608

Closed
zmcNotafraid opened this issue Apr 8, 2024 · 1 comment
Closed

Drop unused tables #608

zmcNotafraid opened this issue Apr 8, 2024 · 1 comment
Assignees
Labels
enhancement New feature or request

Comments

@zmcNotafraid
Copy link
Member

zmcNotafraid commented Apr 8, 2024

sql = "select
  table_name,
  pg_size_pretty(pg_total_relation_size(quote_ident(table_name))),
  pg_total_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'public'
order by 3 desc;"

[{"table_name"=>"pool_transaction_entries", "pg_size_pretty"=>"265 GB", "pg_total_relation_size"=>284052865024},
 {"table_name"=>"cell_outputs", "pg_size_pretty"=>"167 GB", "pg_total_relation_size"=>178790227968},
 {"table_name"=>"old_ckb_transactions", "pg_size_pretty"=>"112 GB", "pg_total_relation_size"=>120526061568},
 {"table_name"=>"witnesses", "pg_size_pretty"=>"102 GB", "pg_total_relation_size"=>109386489856},
 {"table_name"=>"cell_inputs_old", "pg_size_pretty"=>"39 GB", "pg_total_relation_size"=>41864544256},
 {"table_name"=>"cell_inputs", "pg_size_pretty"=>"37 GB", "pg_total_relation_size"=>39659905024},
 {"table_name"=>"cell_dependencies", "pg_size_pretty"=>"33 GB", "pg_total_relation_size"=>34984050688},
 {"table_name"=>"lock_scripts", "pg_size_pretty"=>"29 GB", "pg_total_relation_size"=>31015026688},
 {"table_name"=>"cell_data", "pg_size_pretty"=>"21 GB", "pg_total_relation_size"=>22473228288},
 {"table_name"=>"ckb_transactions_committed", "pg_size_pretty"=>"20 GB", "pg_total_relation_size"=>21605302272},
 {"table_name"=>"account_books", "pg_size_pretty"=>"17 GB", "pg_total_relation_size"=>18486534144},
 {"table_name"=>"addresses", "pg_size_pretty"=>"17 GB", "pg_total_relation_size"=>17884192768},
 ...

From the results we can see that the biggest size table was not used anymore, but it takes a lot of space in the database.So I will remove these tables from database.

@zmcNotafraid zmcNotafraid added the enhancement New feature or request label Apr 8, 2024
@zmcNotafraid zmcNotafraid self-assigned this Apr 8, 2024
@zmcNotafraid zmcNotafraid moved this to 🏗 In Progress in CKB Explorer Apr 8, 2024
@zmcNotafraid zmcNotafraid changed the title Drop no used tables Drop unused tables Apr 8, 2024
@zmcNotafraid
Copy link
Member Author

@zmcNotafraid zmcNotafraid moved this from 🏗 In Progress to ✅ Done in CKB Explorer Apr 9, 2024
@zmcNotafraid zmcNotafraid moved this from ✅ Done to 🚩Pre Release in CKB Explorer Apr 9, 2024
@zmcNotafraid zmcNotafraid moved this from 🚩Pre Release to ✅ Done in CKB Explorer Apr 10, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
Archived in project
Development

No branches or pull requests

1 participant