PostgreSQL 9.6.6多Schema场景下元数据提取缓慢优化咨询

嘿，针对你在PostgreSQL 9.6.6下处理40个Schema元数据提取慢的问题，我有几个实用的原生工具和优化思路，应该能帮你大幅提升速度：

• 直接查询PostgreSQL系统目录表
  PostgreSQL的所有元数据都存储在pg_catalog和information_schema的系统表中，相比Java程序通过JDBC逐个Schema调用DatabaseMetaData方法，直接批量查询系统表能减少大量网络往返开销。比如你可以用下面的SQL一次性拉取所有非系统Schema的表、列、约束信息：

  SELECT 
      n.nspname AS schema_name,
      c.relname AS table_name,
      a.attname AS column_name,
      t.typname AS data_type,
      a.attnotnull AS is_nullable,
      con.conname AS constraint_name,
      con.contype AS constraint_type
  FROM 
      pg_namespace n
  JOIN 
      pg_class c ON n.oid = c.relnamespace
  JOIN 
      pg_attribute a ON c.oid = a.attrelid
  JOIN 
      pg_type t ON a.atttypid = t.oid
  LEFT JOIN 
      pg_constraint con ON c.oid = con.conrelid AND a.attnum = ANY(con.conkey)
  WHERE 
      n.nspname NOT IN ('pg_catalog', 'information_schema') -- 排除系统Schema
      AND c.relkind = 'r' -- 仅查询普通表
      AND a.attnum > 0 -- 排除系统内置列
  ORDER BY 
      n.nspname, c.relname, a.attnum;
  

  这个查询会一次性返回所有目标Schema的元数据，比循环遍历每个Schema查询高效得多。

• 使用psql元命令快速导出元数据
  如果你可以先导出元数据再做对比，psql的原生元命令是绝佳选择，它直接和数据库交互，速度远超Java循环查询：

  • 查看所有Schema的详细对象结构：在psql终端执行 \d+ *.*，会列出所有Schema下的表、视图、索引等对象的完整结构。
  • 导出结构化的元数据到文件：用psql的参数控制输出格式，比如导出为CSV：

    psql -d your_database -U your_username -t -F ',' -c "SELECT n.nspname, c.relname, a.attname, t.typname FROM pg_namespace n JOIN pg_class c ON n.oid=c.relnamespace JOIN pg_attribute a ON c.oid=a.attrelid JOIN pg_type t ON a.atttypid=t.oid WHERE n.nspname NOT IN ('pg_catalog','information_schema') AND c.relkind='r' AND a.attnum>0;" > metadata.csv
    

    这里-t表示只返回数据行，-F ','指定字段分隔符为逗号，适合后续解析对比。

• 用pg_dump导出完整Schema结构
  PostgreSQL自带的pg_dump工具不仅用于备份，也能高效导出所有Schema的结构信息。比如导出所有非系统Schema的结构：

  pg_dump -d your_database -U your_username -s -n '!pg_catalog' -n '!information_schema' > schema_structure.sql
  

  参数说明：-s表示仅导出结构（不包含数据），-n '!xxx'表示排除指定的系统Schema。导出的SQL文件包含了所有Schema的完整建表语句、约束等元数据，你可以解析这个文件来做一致性对比，pg_dump处理大量Schema时效率非常高。

• 优化Java程序的JDBC查询逻辑
  如果必须在Java程序中实时处理元数据，可以从这几点优化：

  • 放弃使用DatabaseMetaData的getTables()、getColumns()等方法，改用直接查询系统表的方式，减少JDBC的多次查询开销。
  • 设置JDBC的fetchSize，比如statement.setFetchSize(1000)，让JDBC批量获取结果集，降低网络往返次数。
  • 采用批量查询的方式，一次性拉取所有Schema的元数据，而不是循环遍历每个Schema单独查询。

内容的提问来源于stack exchange，提问作者Edilmar Alves