- 加列,减列很快,几乎没影响
Version 10
alter table test01 add column col11 varchar(100)
alter table test01 alter COLUMN col12 set default 'abc';
Version 11
alter table test01 add column col11 varchar(100) default 'abc';
alter table test01 add column col12 varchar(100) not null default 'abc';
alter table test01 drop column col12 ;
-
改列的数据类型
- 加新列
- 同时写(trigger)
- copy old column values to new column.
- rename column
- drop old column
-
索引(新建,删除)
create index concurrently on test01(col1);
drop index CONCURRENTLY test01_id_idx ;
索引重建
使用concurrently命令建新并删旧,不要直接使用reindex!
- constraints
先not valid,再valid,时间短。
ALTER TABLE <table> ADD CONSTRAINT <name> CHECK (<column> IS NOT NULL) NOT VALID;
Manually verify that all rows have non-null values in your column.
ALTER TABLE <table> VALIDATE CONSTRAINT <name>;
alter table test02 add foreign key (id) references test01(id) not valid;
alter table test02 validate constraint test02_id_fkey ;
ALTER TABLE ... ADD CONSTRAINT ... CHECK (...) NOT VALID:
ALTER TABLE ... VALIDATE CONSTRAINT <constraint>:
常用脚本
- 查看锁
SELECT
psa.datname as database,
psa.query as current_query,
clock_timestamp() - psa.xact_start AS transaction_age,
array_agg(distinct c.relname) AS tables_with_locks
FROM pg_catalog.pg_stat_activity psa
JOIN pg_catalog.pg_locks l ON (psa.pid = l.pid)
JOIN pg_catalog.pg_class c ON (l.relation = c.oid)
JOIN pg_catalog.pg_namespace ns ON (c.relnamespace = ns.oid)
WHERE psa.pid != pg_backend_pid()
AND ns.nspname != 'pg_catalog'
AND c.relkind = 'r'
AND psa.xact_start < clock_timestamp() - '5 seconds'::interval
GROUP BY psa.datname, psa.query, psa.xact_start;
- 查看表统计信息
SELECT
seq_scan,
seq_tup_read,
idx_scan,
idx_tup_fetch,
n_tup_ins,
n_tup_upd,
n_tup_del
FROM pg_catalog.pg_stat_user_tables
WHERE relname = '<table>';
- 查看对象
SELECT
relname,
relfilenode
FROM pg_catalog.pg_class
WHERE relname in (
'<table>',
'<index>'
)
-- Order by oid for convenience if you're checking multiple relations.
ORDER BY oid;
SELECT
d.objid::regclass AS owning_object,
d.refobjid::regclass AS dependent_object,
a.attname AS dependent_column,
d.deptype -- The meaning of this type is documented at https://www.postgresql.org/docs/current/catalog-pg-depend.html
FROM pg_catalog.pg_depend d
LEFT JOIN pg_catalog.pg_attribute a ON d.refobjid = a.attrelid
AND d.refobjsubid = a.attnum
WHERE refobjid = '<table>'::regclass
AND a.attname = '<column>';