PGSQL study1

有三个主要方面可以提升PostgreSQL的潜能。

• 查询方式的变化，这主要涉及修改查询方式以获取更好的性能:

• • 创建索引，包括表达式和部分索引；
  • 使用COPY语句代替多个Insert语句；
  • 将多个SQL语句组成一个事务以减少提交事务的开销；
  • 从一个索引中提取多条记录时使用CLUSTER；
  • 从一个查询结果中取出部分记录时使用LIMIT；
  • 使用预编译式查询（Prepared Query)；
  • 使用ANALYZE以保持精确的优化统计；
  • 定期使用 VACUUM 或 pg_autovacuum
  • 进行大量数据更改时先删除索引（然后重建索引）

• 服务器的配置
  • 配置文件postgres.conf中的很多设置都会影响性能，所有参数的列表可见：管理员指南/数据库服务器运行环境/数据库服务器运行配置， 有关参数的解释可见： http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html 和 http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html。

• 硬件的选择
  • 计算机硬件对性能的影响可浏览 http://candle.pha.pa.us/main/writings/pgsql/hw_performance/index.html 和 http://www.powerpostgresql.com/PerfList/。

PostgreSQL里可以获得什么样的调试特性？

PostgreSQL 有很多类似 log_* 的服务器配置变量可用于查询的打印和进程统计，而这些工作对调试和性能测试很有帮助。

为什么在试图连接时收到“Sorry, too many clients（已有太多用户连接）”消息？

这表示你已达到缺省100个并发后台进程数的限制，你需要通过修改postgresql.conf文件中的max_connections值来 增加postmaster的后台并发处理数，修改后需重新启动postmaster。

单条记录，单个表，单个数据库的最大限制是多少？

下面是一些限制：

   单个数据库最大尺寸？      无限制（已存在有 32TB 的数据库）
   单个表的最大尺寸？    32 TB
   一行记录的最大尺寸？   400 GB
   一个字段的最大尺寸?   1 GB
   一个表里最大行数？    无限制
   一个表里最大列数？    250-1600 （与列类型有关）
   一个表里的最大索引数量？ 无限制

当然，实际上没有真正的无限制，还是要受系统可用磁盘空间、可用内存/交换区的制约。 事实上，当上述这些数值变得异常地大时，系统性能也会受很大影响。

单表的最大大小 32 TB 不需要操作系统对单个文件也需这么大的支持。大表用多个 1 GB 的文件存储，因此文件系统大小的限制是不重要的。

如果缺省的块大小增长到 32K ，最大的单表大小和最大列数还可以增加到四倍。

有一个限制就是不能对大小多于2000字节的列创建索引。幸运地是这样的索引很少用到。通过对多字节列的内容进行MD5哈稀运算结果进行函数索引可对列的唯一性得到保证， 并且全文检索允许对列中的单词进行搜索。

存储一个典型的文本文件里的数据需要多少磁盘空间？

一个 Postgres 数据库（存储一个文本文件）所占用的空间最多可能需要相当于这个文本文件自身大小5倍的磁盘空间。

例如，假设有一个 100,000 行的文件，每行有一个整数和一个文本描述。 假设文本串的平均长度为20字节。文本文件占用 2.8 MB。存放这些数据的PostgreSQL数据库文件大约是 6.4 MB:

    24 字节: 每行的头（大约值）
     24 字节: 一个整数型字段和一个文本型字段
  +  4 字节: 页面内指向元组的指针
  ----------------------------------------
    52 字节每行

  PostgreSQL 数据页的大小是 8192 字节 (8 KB)，则：

  8192 字节每页
  -------------------   =  158 行/数据页（向下取整）
    52 字节每行

  100000 数据行
  --------------------  =  633 数据页（向上取整）
     158 行/数据页

  633 数据页 * 8192 字节/页  = 5,185,536 字节（5.2 MB）

索引不需要这么多的额外消耗，但也确实包括被索引的数据，因此它们也可能很大。

空值NULL存放在位图中，因此占用很少的空间。

各种字符类型之间有什么不同？

   类型 内部名称    说明
   VARCHAR(n)   varchar 指定了最大长度，变长字符串，不足定义长度的部分不补齐
   CHAR(n)      bpchar  定长字符串，实际数据不足定义长度时，以空格补齐
   TEXT text    没有特别的上限限制（仅受行的最大长度限制）
   BYTEA        bytea   变长字节序列（使用NULL字符也是允许的）
   "char"       char    单个字符

在系统表和在一些错误信息里你将看到内部名称。

上面所列的前四种类型是"varlena"（变长）类型（也就是说，开头的四个字节是长度，后面才是数据）。 于是实际占用的空间比声明的大小要多一些。 然而这些类型如定义很长时都可以被压缩存储，因此磁盘空间也可能比预想的要少。

VARCHAR(n) 在存储限制了最大长度的变长字符串是最好的。 TEXT 适用于存储最大可达 1G左右但未定义限制长度的字符串。

CHAR(n) 最适合于存储长度相同的字符串。 CHAR(n)会根据所给定的字段长度以空格补足（不足的字段内容）， 而 VARCHAR(n) 只存储所给定的数据内容。 BYTEA 用于存储二进制数据，尤其是包含 NULL 字节的值。这些类型具有差不多的性能。

我怎样创建一个序列号或是自动递增的字段？

PostgreSQL 支持 SERIAL 数据类型。（字段定义为SERIAL后）将自动创建一个序列生成器，例如：

   CREATE TABLE person ( 
      id   SERIAL, 
      name TEXT 
   );

会自动转换为以下SQL语句：

   CREATE SEQUENCE person_id_seq;
   CREATE TABLE person ( 
      id   INT4 NOT NULL DEFAULT nextval('person_id_seq'),
      name TEXT
   );

参考 create_sequence 手册页获取关于序列生成器的更多信息。

为什么我收到错误信息“ERROR: Memory exhausted in AllocSetAlloc()”？

这很可能是系统的虚拟内存用光了，或者内核对某些资源有较低的限制值。在启动 postmaster 之前试试下面的命令：

   ulimit -d 262144
   limit datasize 256m

取决于你用的 shell，上面命令只有一条能成功，但是它将把你的进程数据段限制设得比较高， 因而也许能让查询完成。这条命令应用于当前进程，以及所有在这条命令运行后创建的子进程。 如果你是在运行SQL客户端时因为后台返回了太多的数据而出现问题，请在运行客户端之前执行上述命令。

我如何创建一个缺省值是当前时间的字段？

使用 CURRENT_TIMESTAMP：

CREATE TABLE test (x int, modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP );