postgresql 统计表数据量并整理表

postgresql 统计表数据量并整理表

1-查看所有表的大小：

select
    t1.relname as 表名,
    pg_size_pretty(pg_relation_size(relid)) as 大小,
    (select cast(reltuples as INTEGER) from pg_class where relkind = 'r' and relname = t1.relname) as 记录数
    from pg_stat_user_tables as t1
    where schemaname='public' order by pg_relation_size(relid) desc

PostgreSQL查看数据库占用空间大小的几种常用方法_PostgreSQL_脚本之家 (jb51.net)

2- 统计所有数据库占用磁盘大小：

SELECT d.datname AS Name,  
       pg_catalog.pg_get_userbyid(d.datdba) AS Owner,  
       CASE 
           WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN 
               pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))  
           ELSE 'No Access' 
       END AS SIZE 
FROM pg_catalog.pg_database d  
ORDER BY CASE 
            WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN 
                pg_catalog.pg_database_size(d.datname)  
        END;

3-查看单个数据表的占用空间大小：

SELECT pg_size_pretty(pg_total_relation_size('table_name'));

SELECT pg_size_pretty(pg_total_relation_size('events'));

4--查看单个数据库的占用磁盘空间大小：

SELECT pg_size_pretty(pg_database_size('database_name'));

-

查看3月份有多少条:

select  count(*) from events where created_at <'2024-03-01' ;

194887 ,19万条

5月份41万

select  count(*) from events where created_at <'2024-05-01' and created_at >'2024-04-01' ;

欢迎来撩 ： 汇总all