退出 \q 查看当前库 \l 切换库 \c 2dtable 查看插件 \dx 安装插件 CREATE EXTENSION pg_jieba; 删除插件 DROP EXTENSION pg_jieba; 进入banniu_global set search_path to banniu_global; 列出schema下面的表 \d #查看更详细表的信息 \d+ 查看表结构 \d content_v2_262120370634.table_9064 查看schema的权限FF \dn 查看用户权限 \du+ banniu; 查看过程及函数 \df 按列展示 \x
pg帮助命令
\h create user abc passward 'abc'
\h create schema
执行计划 explain analyze 也可以 explain 查看当前库下面的schema SELECT * FROM information_schema.schemata limit 5; 得到当前db中所有表的信息（这里pg_tables是系统视图） select * from pg_tables 加索引 btree concurrently create index sys_user_role_rel_role_id_index on banniu_global.sys_user_role_rel (role_id); 加索引 jsonb create index table_167_column_177_gin on content_v2_262236167542.table_167 using gin (column_177); 不锁表加索引 create index concurrently task_creator_status_created_index on common_262274865852.task_snapshot_data(creator,status,created); create index on points using gist(p); create index concurrently task_creator_status_created_index on common_262274865852.task_snapshot_data(creator,status,created); create index idx_time_status on common_v2_262066523976.app_job_list (create_time ,status);
jsonb 索引使用方法 explain analyze SELECT column_0 FROM content_v2_262274865852.table_6040 WHERE is_deleted = false and column_10661 @> '[{"10655":"9B025FDEFD5C34EA8BB6A07D67769261"}]'; 年龄参数 默认是2亿（超过2亿自动清理）。。欧莱雅是12亿 autovacuum_freeze_max_age #调整自动回收动作中 analyze频率 越大频率越低 autovacuum_analyze_scale_factor 默认0.05 可以调整为0.1 通过schema和表a 找 年龄 select t1.relname,age(t1.relfrozenxid) aa,t.nspname from pg_namespace t,pg_class t1 where t1.relkind IN ('r', 'm') and t1.relnamespace=t.oid and t1.relname='t_report' and t.nspname='common_262268960599'; SELECT MAX(AGE(datfrozenxid)) AS db_age FROM pg_database ORDER BY db_age DESC LIMIT 10 查看库及年龄 SELECT datname, age(datfrozenxid) FROM pg_database; 重命名 修改库名 alter database dtable2 rename to dtable2_bak; 重命名 修改模式名 ALTER SCHEMA schema_name RENAME TO new_name; #修改表名 ，必须要进入schema 才能修改表名 alter table t_sys_usage_increment rename to t_sys_usage_increment_bak; 查询当前会话 select * from dba.sessions; 查询某个schema下面所有表的大小。。从大到小排序 SELECT table_name AS table_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables where table_schema='banniu_global' order by pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC ; #查询某个表的大小 SELECT pg_size_pretty(pg_total_relation_size('t_workflow_message_idempotent_record')) AS table_size; #表的大小total_size及碎片大小fragmentation 2dtable=> SELECT pg_size_pretty(pg_total_relation_size('t_workflow_message_idempotent_record')) AS total_size,pg_size_pretty(pg_total_relation_size('t_workflow_message_idempotent_record') - pg_relation_size('t_workflow_message_idempotent_record')) AS fragmentation; total_size | fragmentation ------------+--------------- 9075 MB | 1459 MB 2dtable=> SELECT pg_size_pretty(pg_total_relation_size('t_platform_erp_request_log')) AS total_size,pg_size_pretty(pg_total_relation_size('t_platform_erp_request_log') - pg_relation_size('t_platform_erp_request_log')) AS fragmentation;
备份某个库 用户 banniu 数据库 dtable2_bak 会提示输入密码 pg_dump -h pgm-k2jy11avf20939v812400.pgsql.zhangbei.rds.aliyuncs.com -p 3433 -U banniu dtable2_bak > dtable2_bak.bak 恢复数据 用户 banniu 数据库 dtable2_bak 会提示输入密码 psql -h pgm-k2j66o19z7jp793785720.pgsql.zhangbei.rds.aliyuncs.com -p 1921 -U banniu dtable2_bak < dtable2_bak.bak 导出某个schema的下面的结构 不导出数据，加上 -s PGPASSWORD=pwd pg_dump -h pgr-k2jx06mz0604v7v5.pgsql.zhangbei.rds.aliyuncs.com -p3433 -Ubanniu 2dtable -n banniu_global -s > /home/deploy/cj_test/pg_split/test4/0709/banniu_global.sql PGPASSWORD=pwd pg_dump -h pgm-k2j8byh7302em14p57320.pgsql.zhangbei.rds.aliyuncs.com -p5432 -Ubanniu 2dtable -n common_v2_251604984266 -n common_251604984266 | PGPASSWORD=pwd psql -U banniu -d 2dtable -h pgm-k2j62dc0dc0g14x0.pgsql.zhangbei.rds.aliyuncs.com -p 5432 #按条件备份 pg_dump -U backup_user -d your_database_name --column-inserts --clean --table-data your_table_name --where="age > 20" -f backup.sql 复制槽延迟，清理复制槽 select * from pg_replication_slots; canalsubxs74e6n9_increment select pg_drop_replication_slot('canal452esy7b239_increment'); 使用pgdump操作的时候会发生的动作 LOCK TABLE definition_262092297550."column" IN ACCESS SHARE MODE COPY definition_global_tag.invoked_log (id, method_parameter, method_result, method_type, create_time, extra_info, company_id, client_ip) TO stdout; #删除schema的时候 如果里面有表依赖使用下面的命令 drop schema banniu_global2 CASCADE; #创建序列 CREATE SEQUENCE "banniu_global".wf_email_approval_task_seq INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 999999999999 CACHE 1; #查找下一个序列值 select nextval('banniu_global.column_rule_id_seq') #设置序列 SELECT setval('banniu_global.column_rule_id_seq',1000000); #序列当前值 last_value序列当前值 SELECT sequencename seq ,last_value ,'select setval('''||'banniu_global.'||sequencename||''','||last_value+100000||');' FROM pg_sequences where schemaname='banniu_global' and last_value is not null; 外表处 序列统一产生偶数 注意需要查询当前表的最大值 CREATE SEQUENCE content_v2_125.table_100_column_0_seq INCREMENT 2 START 20002 MINVALUE 1 MAXVALUE 999999999999 CACHE 1; 源头 序列统一产生奇数 ALTER TABLE content_v2_125.table_100 ALTER COLUMN column_0 SET DEFAULT null; drop SEQUENCE content_v2_125.table_100 注意需要查询当前表的最大值 CREATE SEQUENCE content_v2_125.table_100_column_0_seq INCREMENT 2 START 20001 MINVALUE 1 MAXVALUE 999999999999 CACHE 1; #创建schema create schema banniu_global; #-T 忽略表，-t 指定表，-n 指定schema 指定多个schema 可以使用 -n sch1 -n sch2 pg_dump -h pgm-k2j5w212y3j6c8n757310.pgsql.zhangbei.rds.aliyuncs.com -p 5432 -U banniu 2dtable -n banniu_global -T banniu_global.t_sys_usage_increment -T banniu_global.t_sys_usage_increment_his 进入schema set search_path to banniu_global; #修改表名 ，必须要进入schema 才能修改表名 alter table t_sys_usage_increment rename to t_sys_usage_increment_bak;
#pg年龄 前5 SELECT c.oid::regclass as vacuum_cmd ,greatest(age(c.relfrozenxid),age(t.relfrozenxid)) aa FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind IN ('r', 'm') order by greatest(age(c.relfrozenxid),age(t.relfrozenxid)) desc limit 5; #pg年龄 大于200000000 数量 select count(1) from( SELECT c.oid::regclass as vacuum_cmd ,greatest(age(c.relfrozenxid),age(t.relfrozenxid)) aa FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind IN ('r', 'm') order by greatest(age(c.relfrozenxid),age(t.relfrozenxid)) desc )t where t.aa>200000000; #pg复制表 create table tmp_x as select * from tmp where name='苹果'; #排查是否有某个schema遗漏建表 select distinct table_schema FROM INFORMATION_SCHEMA .tables t1 where (t1.table_schema) not in (SELECT table_schema FROM information_schema.tables where table_name ='t_message_buyer_notify_record' and table_schema like 'common_v2_%') and table_schema like 'common_v2_%'; 修改时间默认值 ALTER TABLE "banniu_global"."t_partner_app" ALTER COLUMN "gmt_modified" SET DEFAULT TIMEZONE('UTC-8'::TEXT, NOW()::TIMESTAMP WITHOUT TIME ZONE); 索引信息表 select * from pg_stat_user_indexes; pg授予用户banniu创建schema的权限 GRANT CREATE ON DATABASE banniudb TO banniu; pg授权用户：pg创建只读账户。必须指定具体的schema，无法指定具体的库，也没有关键字授权 #创建用户 CREATE ROLE "oly_ro" NOCREATEDB NOCREATEROLE LOGIN ENCRYPTED PASSWORD '0123456' VALID UNTIL '2022-08-31 00:00'; #授予用户只读权限 alter user oly_ro set default_transaction_read_only=on; #授予用户具体的schema只读 GRANT usage ON SCHEMA common_125 TO oly_ro; #授予表只读（前提必须是有usage 权限） GRANT SELECT ON ALL tables IN SCHEMA common_125 TO oly_ro; #未来访问schema下面所有新建的表 ALTER DEFAULT privileges IN SCHEMA common_262066523976 GRANT SELECT ON tables TO oly_ro; #设置单个用户的超时设置 3000ms 也就是3秒 alter user oly_ro set statement_timeout = 300000; #添加默认路径schema ALTER ROLE oly_ro SET search_path = 'matebase'; #批量添加只读权限 CREATE ROLE bn_ro NOCREATEDB NOCREATEROLE LOGIN ENCRYPTED PASSWORD '0123456' ; alter user bn_ro set default_transaction_read_only=on; #拼接之后 执行 select 'grant usage on schema '|| schema_name || ' to bn_ro ;' from information_schema.schemata where catalog_name='2dtable' and schema_owner='banniu'; select 'GRANT SELECT ON ALL TABLES IN SCHEMA '|| schema_name || ' TO bn_ro ;' from information_schema.schemata where catalog_name='2dtable' and schema_owner='banniu'; select 'ALTER DEFAULT PRIVILEGES IN SCHEMA '|| schema_name || ' GRANT SELECT ON TABLES TO bn_ro ;' from information_schema.schemata where catalog_name='2dtable' and schema_owner='banniu'; #修改到期时间 ALTER ROLE "oly_ro" NOCREATEDB NOCREATEROLE LOGIN VALID UNTIL '2022-12-08 00:00'; #授权dml及ddl权限 GRANT usage ON SCHEMA knowledge_audit TO dev; GRANT all privileges ON ALL tables IN SCHEMA knowledge_audit TO dev; GRANT all privileges ON ALL SEQUENCES IN SCHEMA knowledge_audit TO dev; ALTER DEFAULT privileges IN SCHEMA knowledge_audit GRANT all privileges ON tables TO dev; ALTER DEFAULT privileges IN SCHEMA knowledge_audit GRANT all privileges ON SEQUENCES TO dev; #使用户具有ddl权限 grant create on schema knowledge_audit to dev #接触只读锁定 alter user oly_ro set default_transaction_read_only=off; #查询活跃会话，状态为activty的为活跃会话，其他的状态表示上一个sql select client_addr,query from pg_stat_activity where query!='' and state='active' and usename='banniu' limit 100; #查询活跃会话 中 做自动回收动作的sql select * from pg_stat_activity where state='active' and backend_type like 'autovacuum%' #查询正在人工做自动回收任务的会话 select * from pg_stat_activity where state='active' and query like 'vacuum%' limit 10; #按查询时间排序 越久越前 select * from pg_stat_activity where state='active' order by query_start asc; #查看并发操作 例如vacuum (freeze ,parallel 3); select * from pg_stat_activity where backend_type='parallel worker'; # pg_blocking_pids 显示有阻塞的pid select pid, pg_blocking_pids(pid),query,query_start from pg_stat_activity where state<>'idle' order by query_start asc
#并发执行 3个进程 vacuum (freeze ,parallel 3); vacuum (freeze ,parallel 2); #为任何表触发分析所需的插入、更新或删除的行数。默认50 调整成100 autovacuum_analyze_threshold=100， #将触发任意一张表的 vacuum 操作的插入行数。 默认1000 调整成2000 autovacuum_vacuum_insert_threshold=2000 查看表的状态信息，表的数量 删除的数量（碎片） 最近vacuum 时间 select * from pg_stat_user_tables where relname='table_1058'; pg_stat_statements.max=10000 可以改成5000 pg_stat_statements.track 可以改成top 或者 none （不跟踪sql） 当发现好的sql 都变成慢sql的时候 有可能就是pg_stat_statements更新统计信息。。此时io，cpu都不会有明显变化。。会有锁 #单个用户连接数最大限制 ALTER USER kms WITH CONNECTION LIMIT 20; #判断哪些字段没有索引 select t1.table_schema,t1.table_name,t1.column_name,'create index '||t1.table_name||'_'||t1.column_name||'_'||'btree'||' on '||t1.table_schema||'.'||t1.table_name||'('||t1.column_name||');' from INFORMATION_SCHEMA.columns t1 where (table_schema,table_name,column_name ) not in ( select t.table_schema,t.table_name,t.column_name from (select table_schema,table_name,column_name,table_name||'_'||column_name||'_' as tcol from INFORMATION_SCHEMA.columns where table_schema like 'content_v2%' and table_name like 'table%' and column_name not in ('column_0','is_deleted','column_1','column_2','column_3','column_4','column_5','column_6','column_7','column_8','column_9'))t, (select schemaname,relname,indexrelname,replace(replace(replace(indexrelname,'btree',''),'gin',''),'gist','') idx from pg_stat_user_indexes where schemaname like 'content_v2%' and relname like 'table%' and indexrelname not like '%pkey')t1 where t.table_schema=t1.schemaname and t.table_name=t1.relname and t1.idx =t.tcol) and table_schema like 'content_v2%' and table_name like 'table%' and column_name not in ('column_0','is_deleted','column_1','column_2','column_3','column_4','column_5','column_6','column_7','column_8','column_9') ; #授予用户具体的schema只读 GRANT usage ON SCHEMA company_262066523976 TO oly_ro; #授予表只读（前提必须是有usage 权限） GRANT SELECT ON ALL tables IN SCHEMA company_262066523976 TO oly_ro; #未来访问schema下面所有新建的表 ALTER DEFAULT privileges IN SCHEMA company_262066523976 GRANT SELECT ON tables TO oly_ro; #查询数据量超过500万的表 select * from ( select t1.nspname,relname , ''||reltuples as cnt from pg_class t,(select oid,nspname from pg_namespace ) t1 where t.relkind = 'r' and t.relnamespace = t1.oid )m where to_number(m.cnt,'99999999999999999')>=5000000 and nspname not like 'remove%' order by nspname desc; 初始化序列 及序列查询 alter sequence common_v2_251604984266.app_log_id_seq restart with 17856542 select nextval('common_v2_251604984266.app_log_id_seq') 把序列关联到表上 alter table "content_v2_262066523976"."table_121235" alter column column_0 set default nextval('"content_v2_262066523976".table_121235_column_0_seq'::regclass); 删除索引 drop index common_v2_262314175403.app_table_type_index; 删除有唯一约束的索引 ALTER TABLE table_name DROP CONSTRAINT index_name; 删除物化视图 drop MATERIALIZED VIEW common_v2_262066523976.oly_exp_task; 创建物化视图 create materialized view common_v2_262066523976.oly_exp_task as SELECT t1.id as leaf_in_id,t1.biz_key,t1.instance_id,bb.title , tf.status ,t1.biz_type ,t1.create_time,t1.update_time, date_part('hour',now()-t1.update_time) as timeout_hour FROM common_v2_262066523976.app_job_list t1 left join common_v2_262066523976.app_job_list t2 on t1.id =t2.prev_job_id left join common_v2_262066523976.workflow_template_node AS bb ON t1.node_id = bb.ID left join common_v2_262066523976.ticket_info AS tf ON tf.id = t1.ticket_id where t2.id IS NULL and t1.process_code = '3b892e9axw4000' AND t1.create_time > '2022-09-26 00:00:00' and (t1.biz_type in ( '100002003','100002001') and t1.status =301 and t1.result_value='0')=false and t1.biz_type in('100002003','100002001','100004001','100009001','100008001','100007001','100003003','100001001') and tf.status =1 and t1.update_time < (now()-interval '1 hour') order by t1.instance_id ; 刷新物化视图 更新里面的数据 REFRESH MATERIALIZED VIEW global.t_sys_usage_increment_mat_view_1 #查询会话中 未提交的事务 select * from pg_stat_activity where (state = 'idle in transaction') and xact_start is not null; select * from pg_stat_activity where (state = 'active') and xact_start is not null; #查询长事务及锁 with t_wait as ( select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted, a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath, b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted ), t_run as ( select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted, a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath, b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted ), t_overlap as ( select r.* from t_wait w join t_run r on ( r.locktype is not distinct from w.locktype and r.database is not distinct from w.database and r.relation is not distinct from w.relation and r.page is not distinct from w.page and r.tuple is not distinct from w.tuple and r.virtualxid is not distinct from w.virtualxid and r.transactionid is not distinct from w.transactionid and r.classid is not distinct from w.classid and r.objid is not distinct from w.objid and r.objsubid is not distinct from w.objsubid and r.pid <> w.pid ) ), t_unionall as ( select r.* from t_overlap r union all select w.* from t_wait w ) select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid, string_agg( 'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)|| 'Lock_Granted: '||case when granted is null then 'NULL' else granted::text end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , FastPath: '||case when fastpath is null then 'NULL' else fastpath::text end||' , VirtualTransaction: '||case when virtualtransaction is null then 'NULL' else virtualtransaction::text end||' , Session_State: '||case when state is null then 'NULL' else state::text end||chr(10)|| 'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)|| 'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)|| 'SQL (Current SQL in Transaction): '||chr(10)|| case when query is null then 'NULL' else query::text end, chr(10)||'--------'||chr(10) order by ( case mode when 'INVALID' then 0 when 'AccessShareLock' then 1 when 'RowShareLock' then 2 when 'RowExclusiveLock' then 3 when 'ShareUpdateExclusiveLock' then 4 when 'ShareLock' then 5 when 'ShareRowExclusiveLock' then 6 when 'ExclusiveLock' then 7 when 'AccessExclusiveLock' then 8 else 0 end ) desc, (case when granted then 0 else 1 end) ) as lock_conflict from t_unionall group by locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ; %
修改数组类型字段 update dp_global.ads_consumer_dim_test set citys_nearly_year=array['湖南省_湘潭市123','dddd'] where id=20983; 修改表的所有者 需要高权限账户操作 ALTER TABLE "lg_global"."intercept_order_manual_batch" OWNER TO "banniu_exp"; function_bind_extend ALTER TABLE "lg_global"."function_bind_extend" OWNER TO "banniu_exp"; alter schema lg_refund owner to banniu_exp; 修改某个账户为高权限账户。。需要高权限账户操作 alter user banniu superuser createrole createdb replication; alter user kongque superuser createrole createdb replication; #pg拼接 SELECT id, array_to_string(ARRAY(SELECT unnest(array_agg(name)) ),',') FROM t_kenyon GROUP BY id; #查询主键 select * from information_schema.columns where COLUMN_KEY='PRI' ; 5. join 嵌套循环（Nested Loops）或循环联接 ,时间复杂度O(m*n)（补充：索引可以提高nested loop 的效率,O(M**log(N))） （1）A表提取一条记录，取B表查找相应的匹配记录，如果有，就把该条记录的信息推到等待返回的结果集中 （2）再去从A表中提取第二条记录，取B表中找匹配记录，如果有，就把记录信息返回结果集 （3）以此类推，直到A表中的数据全部被处理完成，将结果集返回，完成嵌套循环连接的操作 合并连接（merge join） （1）A表先进行排序，形成临时表C （2）B表排序，形成临时表D （3）C与D进行合并操作，返回结果集 Hash连接（hash join） （1）B表在内存建立一个散列表C （2）从A表读取第一条记录，到C中查询匹配记录，若有，推到结果集中 （3）以此类推，直到A中没有记录，返回结果集 将表迁移到某个schema ALTER TABLE "remove_content_v2_262403662646_remove"."table_101136" SET SCHEMA "content_v2_262403662646"; ALTER sequence "remove_content_v2_262403662646_remove"."table_100658_column_0_seq" SET SCHEMA "content_v2_262403662646"; #修改jsonb里面某个键值 update definition_v2_262066523976.column set extra_info =(extra_info::jsonb || '{"cooRelationTableColumnTag":0}') where table_id =134940 and id IN (6); #清理表碎片 banniu_global.t_me_message #查询失效索引（invalid） select relnamespace::regnamespace, indrelid::regclass, indexrelid::regclass from pg_index t1,pg_class t2 where t1.indexrelid =t2.oid and not t1.indisvalid;
#模糊查询索引详细信息 select * from pg_indexes where tablename='app_log' and indexdef like '%(app_id, table_id, type)' limit 10; #查询冗余索引 （模糊查询的条件里面需要写索引字段） select schemaname,indexname,'drop index '||schemaname||'.'||indexname||';' from pg_indexes where schemaname like 'common_v2%' and tablename='app_log' and indexdef like '%(app_id, table_id, type)' and schemaname in (select distinct schemaname from pg_indexes where tablename='app_log' and indexdef like '%(app_id, table_id, type, sub_type)') 查看索引碎片。leaf_fragmentation字段是碎片率 2dtable=> SELECT * FROM pgstatindex('table_1337_column_10119_btree'); version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation ---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-------------------- 3 | 2 | 563322880 | 283 | 319 | 68402 | 0 | 43 | 30.61 | 81.23 REINDEX索引，回收不必要的空间 REINDEX INDEX CONCURRENTLY your_index_name; 收集统计信息 可以回收碎片。提升索引性能 analyze schema.table ANALYZE dp_global.dws_daily_consumer_platform_target ANALYZE common_262329176842.task_snapshot_data 设置会话级别超时时间 SET statement_timeout = 900000; 监控参数 select * FROM pg_stat_database; tup_returned 执行出结果 遍历过的行数 tup_fetched 返回给客户端的行数 #查看schema大小，并按schema冲大到小排序 SELECT schema_name, pg_size_pretty(sum(table_size)::bigint) as "disk space", round((sum(table_size) / pg_database_size(current_database())) * 100,2) as "percent(%)" FROM ( SELECT pg_catalog.pg_namespace.nspname as schema_name, pg_total_relation_size(pg_catalog.pg_class.oid) as table_size FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid ) t GROUP BY schema_name ORDER BY "percent(%)" desc; #查看表的大小，并降序 SELECT table_catalog AS database_name,table_schema AS schema_name,table_name, pg_size_pretty(relation_size) AS table_size FROM (SELECTtable_catalog,table_schema,table_name, pg_total_relation_size(('"' || table_schema || '"."' || table_name || '"')) AS relation_size FROM information_schema.tables WHERE table_schema not in ('pg_catalog', 'public', 'public_rb', 'topology', 'tiger', 'tiger_data', 'information_schema') ORDER BY relation_size DESC) AS all_tables WHERE relation_size >= 1073741824; limit删除 with tmp as (select id from app_log_bak where id<1000 order by id asc limit 50 ) delete from app_log_bak where id= any (array(select id from tmp)); with tmp as (select id from app_log_bak where id<1000 order by id asc limit 50 ) delete from app_log_bak where id= any (array(select id from tmp)); 重建索引 回收索引碎片 争对整个表所有索引 reindex table concurrently banniu_global.ticket_node_flow_log ; vacuum是回收数据表碎片及收集统计信息 不回收索引碎片 针对具体得一个索引 reindex index concurrently table_4999_column_10654_gin; #重建索引 及 整理数据碎片 reindex table concurrently orm.im_scheme_result_log; vacuum orm.im_scheme_result_log; #完全释放空间需要 但是会锁表 vacuum full orm.im_scheme_result_log; #查看表的膨胀率（碎片 死元组） 1.安装插件 create extension pgstattuple; 1需要修改登录sre SELECT * FROM banniu_global.pgstattuple('banniu_global.job_error'); 2.查询膨胀率及碎片 SELECT * FROM banniu_global.pgstattuple('common_v2_262120370634.app_job_list'); 未删除数据 2dtable=> SELECT * FROM banniu_global.pgstattuple('common_v2_262120370634.app_job_list'); table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent -------------+-------------+-------------+---------------+------------------+----------------+--------------------+------------+-------------- 99638706176 | 157271472 | 93763078426 | 94.1 | 327891 | 235074635 | 0.24 | 3870816924 | 3.88
删除数据后 delete from common_v2_262120370634.app_job_list where create_time<'2024-04-15'; common_v2_262120370634.app_job_list（175G 188059844608）删除的空间没有释放 2dtable=> SELECT * FROM banniu_global.pgstattuple('common_v2_262120370634.app_job_list'); table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent -------------+-------------+-------------+---------------+------------------+----------------+--------------------+-------------+-------------- 99686752256 | 90308498 | 54261840389 | 54.43 | 9373084 | 5296005500 | 5.31 | 38528827572 | 38.65 执行 vacuum freeze common_v2_262120370634.app_job_list; 发现 dead_tuple_count，dead_tuple_len 大幅度变小，free_space无法释放 2dtable=> SELECT * FROM banniu_global.pgstattuple('common_v2_262120370634.app_job_list'); table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent -------------+-------------+-------------+---------------+------------------+----------------+--------------------+-------------+-------------- 99686752256 | 91651130 | 55151786086 | 55.33 | 4792 | 3153308 | 0 | 42954190916 | 43.09 vacuum 可以影响或者回收dead_tuple_len ， dead_tuple_percent字段 vacuum full 可以影响或回收 free_space ， free_percent 真正的把空间释放给os（操作系统） 3.索引的碎片 SELECT * FROM pgstatindex('idx1'); #动态复制表结构 包括索引 默认值（序列） 备注 create table tab_new (like tab_old INCLUDING indexes INCLUDING defaults INCLUDING comments) #查询表的大小 SET statement_timeout = 1800000; SELECT nspname,relnamespace, SUM(pg_table_size(pg_class.oid)) sum_table , pg_size_pretty(SUM(pg_table_size(pg_class.oid))) AS table_size, SUM(pg_indexes_size(pg_class.oid)) sum_idx , pg_size_pretty(SUM(pg_indexes_size(pg_class.oid))) AS indexes_size, SUM(pg_total_relation_size(pg_class.oid)) sum_tot , pg_size_pretty(SUM(pg_total_relation_size(pg_class.oid))) AS total_size FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE nspname != 'pg_catalog' and nspname != 'information_schema' and nspname not like 'definition%' AND pg_class.relkind = 'r' GROUP BY nspname,relnamespace order by nspname; 重建索引的方法： 1. reindex：reindex不支持并行重建【CONCURRENTLY】;索引会锁表；会进行阻塞。 2. vacuum full; 对表进行重构；索引也会重建；同样也会锁表。 3. 创建一个新索引(索引名不同)；再删除旧索引。 --通过pg_stat_user_indexes.idx_scan可检查利用索引进行扫描的次数；这样可以确认那些索引可以清理掉。 select idx_scan from pg_stat_user_indexes where indexrelname = 'ind_t_id';
