PostGresql 分区表

插入数据的时候，会根据address_id算出它的hashcode值，然后落入到对应的分区表上。查询数据的时候，只要带入这个address_id作为查询条件，就会算出对应的hashcode的值，然后就可以直接去指定的分区表中查询检索数据，避免大表扫描，提升查询效率。

创新互联公司专业为企业提供三台网站建设、三台做网站、三台网站设计、三台网站制作等企业网站建设、网页设计与制作、三台企业网站模板建站服务，十多年三台做网站经验，不只是建网站，更提供有价值的思路和整体网络服务。

举个例子，有2000万的数据，做了hash分区，分出来20张表，在插入数据的时候把2000万的数据均匀分布到这20张表中，平均每张表的数据只有100万，那么查询某个数据的时候，就可以只查询其中的一个分区表，因为数据只有100万，远远比2000万小，所以速度更快

postgresql 怎么自动创建分区

在数据库日渐庞大的今天，为了方便对数据库数据的管理，比如按时间，按地区去统计一些数据时，基数过于庞大，多有不便。很多商业数据库都提供分区的概念，按不同的维度去存放数据，便于后期的管理，PostgreSQL也不例外。

PostgresSQL分区的意思是把逻辑上的一个大表分割成物理上的几块儿。分区不仅能带来访问速度的提升，关键的是，它能带来管理和维护上的方便。

分区的具体好处是：

某些类型的查询性能可以得到极大提升。

更新的性能也可以得到提升，因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里，那么在索引上的读和写都会产生更多的磁盘访问。

批量删除可以用简单的删除某个分区来实现。

可以将很少用的数据移动到便宜的、转速慢的存储介质上。

在PG里表分区是通过表继承来实现的，一般都是建立一个主表，里面是空，然后每个分区都去继承它。无论何时，都应保证主表里面是空的。

小表分区不实际，表在多大情况下才考虑分区呢？PostgresSQL官方给出的建议是：当表本身大小超过了机器物理内存的实际大小时（the size of the table should exceed the physical memory of the database server），可以考虑分区。

PG目前（9.2.2）仅支持范围分区和列表分区，尚未支持散列分区。

二、环境

系统环境：CentOS release 6.3 (Final)

PostgreSQL版本：PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit

三、实现分区

3.1 创建主表

david=# create table tbl_partition (

david(# id integer,

david(# name varchar(20),

david(# gender boolean,

david(# join_date date,

david(# dept char(4));

CREATE TABLE

david=#

3.2 创建分区表

david=# create table tbl_partition_201211 (

check ( join_date = DATE '2012-11-01' AND join_date DATE '2012-12-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201212 (

check ( join_date = DATE '2012-12-01' AND join_date DATE '2013-01-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201301 (

check ( join_date = DATE '2013-01-01' AND join_date DATE '2013-02-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201302 (

check ( join_date = DATE '2013-02-01' AND join_date DATE '2013-03-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201303 (

check ( join_date = DATE '2013-03-01' AND join_date DATE '2013-04-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201304 (

check ( join_date = DATE '2013-04-01' AND join_date DATE '2013-05-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201305 (

check ( join_date = DATE '2013-05-01' AND join_date DATE '2013-06-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=#

3.3 分区键上建索引

david=# create index tbl_partition_201211_joindate on tbl_partition_201211 (join_date);

CREATE INDEX

david=# create index tbl_partition_201212_joindate on tbl_partition_201212 (join_date);

CREATE INDEX

david=# create index tbl_partition_201301_joindate on tbl_partition_201301 (join_date);

CREATE INDEX

david=# create index tbl_partition_201302_joindate on tbl_partition_201302 (join_date);

CREATE INDEX

david=# create index tbl_partition_201303_joindate on tbl_partition_201303 (join_date);

CREATE INDEX

david=# create index tbl_partition_201304_joindate on tbl_partition_201304 (join_date);

CREATE INDEX

david=# create index tbl_partition_201305_joindate on tbl_partition_201305 (join_date);

CREATE INDEX

david=#

对于开发人员来说，希望数据库是透明的，只管 insert into tbl_partition。对于数据插向哪个分区，则希望由DB决定。这点，ORACLE实现了，但是PG不行，需要前期人工处理下。

3.4 创建触发器函数

david=# CREATE OR REPLACE FUNCTION tbl_partition_insert_trigger()

RETURNS TRIGGER AS $$

BEGIN

IF ( NEW.join_date = DATE '2012-11-01' AND

NEW.join_date DATE '2012-12-01' ) THEN

INSERT INTO tbl_partition_201211 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2012-12-01' AND

NEW.join_date DATE '2013-01-01' ) THEN

INSERT INTO tbl_partition_201212 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-01-01' AND

NEW.join_date DATE '2013-02-01' ) THEN

INSERT INTO tbl_partition_201301 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-02-01' AND

NEW.join_date DATE '2013-03-01' ) THEN

INSERT INTO tbl_partition_201302 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-03-01' AND

NEW.join_date DATE '2013-04-01' ) THEN

INSERT INTO tbl_partition_201303 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-04-01' AND

NEW.join_date DATE '2013-05-01' ) THEN

INSERT INTO tbl_partition_201304 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-05-01' AND

NEW.join_date DATE '2013-06-01' ) THEN

INSERT INTO tbl_partition_201305 VALUES (NEW.*);

ELSE

RAISE EXCEPTION 'Date out of range. Fix the tbl_partition_insert_trigger() function!';

END IF;

RETURN NULL;

END;

$$

LANGUAGE plpgsql;

CREATE FUNCTION

david=#

说明：如果不想丢失数据，上面的ELSE 条件可以改成 INSERT INTO tbl_partition_error_join_date VALUES (NEW.*); 同时需要创建一张结构和tbl_partition 一样的表tbl_partition_error_join_date，这样，错误的join_date 数据就可以插入到这张表中而不是报错了。

附上出处链接：

为什么postgrelsql的性能没有mysql好

一、 PostgreSQL 的稳定性极强， Innodb 等引擎在崩溃、断电之类的灾难场景下抗打击能力有了长足进步，然而很多 MySQL 用户都遇到过Server级的数据库丢失的场景——mysql系统库是MyISAM的，相比之下，PG数据库这方面要好一些。

二、任何系统都有它的性能极限，在高并发读写，负载逼近极限下，PG的性能指标仍可以维持双曲线甚至对数曲线，到顶峰之后不再下降，而 MySQL 明显出现一个波峰后下滑（5.5版本之后，在企业级版本中有个插件可以改善很多，不过需要付费）。

三、PG 多年来在 GIS 领域处于优势地位，因为它有丰富的几何类型，实际上不止几何类型，PG有大量字典、数组、bitmap 等数据类型，相比之下mysql就差很多，instagram就是因为PG的空间数据库扩展POSTGIS远远强于MYSQL的my spatial而采用PGSQL的。

四、PG 的“无锁定”特性非常突出，甚至包括 vacuum 这样的整理数据空间的操作，这个和PGSQL的MVCC实现有关系。

五、PG 的可以使用函数和条件索引，这使得PG数据库的调优非常灵活，mysql就没有这个功能，条件索引在web应用中很重要。

六、PG有极其强悍的 SQL 编程能力（9.x 图灵完备，支持递归！），有非常丰富的统计函数和统计语法支持，比如分析函数（ORACLE的叫法，PG里叫window函数），还可以用多种语言来写存储过程，对于R的支持也很好。这一点上MYSQL就差的很远，很多分析功能都不支持，腾讯内部数据存储主要是MYSQL，但是数据分析主要是HADOOP+PGSQL。

七、PG 的有多种集群架构可以选择，plproxy 可以支持语句级的镜像或分片，slony 可以进行字段级的同步设置，standby 可以构建WAL文件级或流式的读写分离集群，同步频率和集群策略调整方便，操作非常简单。

八、一般关系型数据库的字符串有限定长度8k左右，无限长 TEXT 类型的功能受限，只能作为外部大数据访问。而 PG 的 TEXT 类型可以直接访问，SQL语法内置正则表达式，可以索引，还可以全文检索，或使用xml xpath。用PG的话，文档数据库都可以省了。

九，对于WEB应用来说，复制的特性很重要，mysql到现在也是异步复制，pgsql可以做到同步，异步，半同步复制。还有mysql的同步是基于binlog复制，类似oracle golden gate,是基于stream的复制，做到同步很困难，这种方式更加适合异地复制，pgsql的复制基于wal，可以做到同步复制。同时，pgsql还提供stream复制。

十，pgsql对于numa架构的支持比mysql强一些，比MYSQL对于读的性能更好一些，pgsql提交可以完全异步，而mysql的内存表不够实用（因为表锁的原因）

最后说一下我感觉 PG 不如 MySQL 的地方。

第一，MySQL有一些实用的运维支持，如 slow-query.log ，这个pg肯定可以定制出来，但是如果可以配置使用就更好了。

第二是mysql的innodb引擎，可以充分优化利用系统所有内存，超大内存下PG对内存使用的不那么充分，

第三点，MySQL的复制可以用多级从库，但是在9.2之前，PGSQL不能用从库带从库。

第四点，从测试结果上看，mysql 5.5的性能提升很大，单机性能强于pgsql，5.6应该会强更多.

第五点，对于web应用来说,mysql 5.6 的内置MC API功能很好用，PGSQL差一些。

另外一些：

pgsql和mysql都是背后有商业公司，而且都不是一个公司。大部分开发者，都是拿工资的。

说mysql的执行速度比pgsql快很多是不对的，速度接近，而且很多时候取决于你的配置。

对于存储过程，函数，视图之类的功能，现在两个数据库都可以支持了。

另外多线程架构和多进程架构之间没有绝对的好坏，oracle在unix上是多进程架构，在windows上是多线程架构。

很多pg应用也是24/7的应用，比如skype. 最近几个版本VACUUM基本不影响PGSQL 运行，8.0之后的PGSQL不需要cygwin就可以在windows上运行。

至于说对于事务的支持，mysql和pgsql都没有问题。

三分钟！彻底搞懂PostgreSQL 和 MySQL 区别之分

PostgreSQL 和 MySQL 是将数据组织成表的关系数据库。这些表可以根据每个表共有的数据链接或关联。关系数据库使您的企业能够更好地了解可用数据之间的关系，并帮助获得新的见解以做出更好的决策或发现新的机会。

PostgreSQL 和 MySQL 都依赖于 SQL（结构化查询语言），这是与管理系统交互的标准语言。SQL 允许使用具有简单结构的几行源代码连接表，大多数非技术员工可以快速学习。

使用 SQL，分析师不需要知道订单表在磁盘上的位置、如何执行查找以查找特定订单或如何连接订单表和客户表。数据库编译查询并计算出正确的数据点。

MySQL 和 PostgreSQL 都支持 JavaScript Object Notation (JSON) 存储和传输数据，尽管 PostgreSQL 也支持 JSONB，这是 JSON 的二进制版本，它消除了键的重复和无关的空格。

除了传统的支持机制外，这两个数据库都提供强大的社区支持。

PostgreSQL，也称为 Postgres，是一种开源关系数据库，因其可靠性、灵活性和对开放技术标准的支持而享有盛誉。PostgreSQL 支持非关系和关系数据类型。它被称为当今可用的最兼容、最稳定和最成熟的关系数据库之一，并且可以轻松处理复杂的查询。

PostgreSQL 的特性包括：

PostgreSQL 这是一个“一刀切”的解决方案，适用于许多寻求经济高效的方法来改进其数据库管理系统 (DBMS) 的企业。它具有足够的可扩展性和多功能性，可以通过强大的扩展生态系统快速支持各种专业用例，涵盖时间序列数据类型和地理空间分析等工作。作为开源数据库解决方案构建的 PostgreSQL 完全不受许可限制、供应商锁定的可能性或过度部署的风险。PostgreSQL 通过对象关系数据库管理系统 (ORDBMS) 进行管理。

PostgreSQL 负责管理业务活动的在线事务处理 (OLTP)协议的企业数据库管理员提供了理想的解决方案，包括电子商务、客户关系管理系统 (CRM) 和财务分类帐。它也是管理接收、创建和生成的数据分析的理想选择。

这些是 PostgreSQL 的一些主要优点：

MySQL — 一种快速、可靠、可扩展且易于使用的开源关系数据库系统 — 旨在处理关键任务、高负载的生产应用程序。它是一种常见且易于启动的数据库，内存、磁盘和 CPU 利用率较低，有关系数据库管理系统 (RDMS) 管理。MySQL Community Edition 是一个由活跃的在线社区支持的免费下载版本。

MySQL 功能包括所有 SQL 标准命令以及事务和 ACID 合规性（代表原子性、一致性、隔离性和持久性）。

两个最常见的关系数据库是什么 MySQL 和 Oracle。MySQL 不是 SQL Server 的同义词，SQL Server 是 Microsoft 许可产品，与 MAC OS X 缺乏兼容性。

MariaDB 经常与 MySQL 混淆，它是 MySQL 的一个开源分支，速度更快，提供更多存储引擎 (12)，但功能有限。MySQL 和 MariaDB 使用的存储引擎都是 InnoDB。InnoDB 提供标准的 ACID 兼容特性。与 MySQL 不同，MariaDB 不支持数据屏蔽或动态列表。

MySQL 通常用作 Web 数据库来存储各种信息类型，从单个信息数据点到为组织提供的产品或服务的完整列表。它是LAMP（Linux 操作系统、Apache HTTP 服务器、MySQL RDBMS 和 PHP 编程语言）的基础组件，这是一种有助于创建API、Web 应用程序和网站的软件堆栈模型。

MySQL Workbench 是一个单一的、集成的可视化 SQL 平台，用于 MySQL 数据库的创建、开发、设计和管理。

MySQL 为市场提供了许多好处，包括：

PostgreSQL 和 MySQL 之间有很多不同之处。特性、功能和优势方面的一些差异如下：

总之，PostgreSQL 和 MySQL 都有不同的用途，它们之间的选择取决于企业目标和资源。一般来说，PostgreSQL 是一个更强大、更高级的数据库管理系统，非常适合需要在大型环境中快速执行复杂查询的组织。但是，对于预算和空间更受限制的公司来说，MySQL 是一个理想的解决方案。

分享文章：包含postgresql分会的词条
文章转载：http://kswjz.com/article/dscoigs.html