postgresql列式的简单介绍

postgresql的怎么修改序列

Services Management”即“服务”中停止相应的PostgreSQL服务 方式二：通过命令行“NET STOPpostgresql-x64-9.1”停止相应的PostgreSQL服务Step 2:修改注册表对应键值位置在"HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services \postgresql-x64-9。

创新互联专注为客户提供全方位的互联网综合服务，包含不限于网站制作、成都网站设计、印台网络推广、微信小程序定制开发、印台网络营销、印台企业策划、印台品牌公关、搜索引擎seo、人物专访、企业宣传片、企业代运营等，从售前售中售后，我们都将竭诚为您服务，您的肯定，是我们最大的嘉奖；创新互联为所有大学生创业者提供印台建站搭建服务，24小时服务热线：13518219792，官方网址：www.cdcxhl.com

如何在Postgresql中创建序列数并应用在数据表

序列数可以增加postgresql数据表的检索速度，同时降低数据查询时的资源消耗。那么如何在postgresql中创建序列数并且应用呢？下面我给大家分享一下。

工具/材料

pgAdmin4

创建序列数

首先打开pgAdmin4，展开postgresql数据库，找到模式下面的public选项，如下图所示

接下来在public下面右键单击序列，然后点击Create下面的sequence选项，如下图所示

在弹出的创建Sequence界面中首先给序列数起一个名字，如下图所示，注意都用英文

然后切换到Definition页卡，定义一下序列的增加量，如下图所示，其中maximum根据自己的需要进行设置

最后回到数据库主界面，你会看到序列下面多出了一个项，这就是我们创建的序列数了，如下图所示

在数据表中应用序列数

首先选中一个数据表，点击右侧的编辑按钮，如下图所示

在弹出的编辑界面中切换到Columns页卡，点击ID签名的编辑按钮，如下图所示

最后在字段的编辑界面中切换到Variables选项卡，然后在Value列中通过nextval函数带入刚才定义的序列数即可，如下图所示

PostgreSQL全文检索简介

PostgreSQL自带有一个简易的全文检索引擎，可以实现小规模数据量的全文检索功能。本文我们将引导介绍一下这个功能，对于小数据量的搜索这个功能是足够使用的，而无需搭建额外的ES等重量级的全文检索服务器。

PG的全文检索操作符是 @@ ，当一个 tsvector (文档)和 tsquery (条件)匹配时返回 true ，并且前后顺序无影响:

和普通的SQL查询一样，只要在 WHERE 条件中使用这个符号就代表使用全文检索条件筛选文档了。如:

@@ 操作符支持隐式转换，对于 text 类型可以无需强类型转换( ::tsvector 或 to_tsvector(config_name, text) )，所以这个操作符实际支持的参数类型是这样的:

tsquery 查询条件并不是简单的正则，而是一组搜索术语，使用并且使用布尔操作符 （AND）、 | （OR）和 ! （NOT）来组合它们，还有短语搜索操作符 - （FOLLOWED BY）。更详细的语法参见 此文档 。

此外，PostgreSQL还提供了两个相对简化的版本 plainto_tsquery 和 phraseto_tsquery 。

plainto_tsquery ( plainto_tsquery([ config regconfig, ] querytext text) returns tsquery )用户将未格式化的 text 经过分词之后，插入 符号转为 tsquery :

phraseto_tsquery ( phraseto_tsquery([ config regconfig, ] querytext text) returns tsquery )行为和 plainto_tsquery 行为类似，但是分词之后不是插入 而是 - (FOLLOWED BY):

使用索引可以加快全文检索的速度。对于全文检索来说，可选的索引类型是 GIN (通用倒排索引)和 GIST (通用搜索树)，官方文档更推荐使用 GIN索引 。创建一个 GIN 索引的范例:

也可以是一个连接列:

还可以单独创建一个 tsvector 列，为这个列创建索引:

除了普通的 ORDER BY 条件之外，PostgreSQL为全文检索提供了两个可选的排序函数 ts_rank([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) returns float4 和 ts_rank_cd([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) returns float4 ，以便实现基于 权重 的排序。

此外，对于PostgreSQL 9.6以上的版本还可以使用 RUM index 排序。(注意，这个是扩展，默认不包含)。

PostgreSQL默认的分词字典中并不包含中文分词字典，因此我们必须手工引入。目前一个比较好的项目是 zhparser ，同时这个插件也是阿里云的RDS默认包含的。安装和启用没什么好说的。值得一提的是分词配置参数。

在 CREATE EXTENSION 之后，必须配置分词参数才能正确进行分词和查找，否则什么都查不到。官方文档提供的一个配置策略是:

n,v,a,i,e,l 这几个字母分别表示一种token策略，只启用了这几种token mapping，其余则被屏蔽。具体支持的参数和含义可以用 \dFp+ zhparser 显示:

WITH simple 表示词典使用的是内置的simple词典，即仅做小写转换。根据需要可以灵活定义词典和token映射，以实现屏蔽词和同义词归并等功能。

比如我们看下面这个例子:

可以看到 江淮 这个词组在查询的时候被忽略了，我们启用 j (abbreviation,简称)再看看结果:

所以实际使用中要设置合理的token types，过少将导致搜索结果不准确，过多将导致性能下降。此外，还有一些诸如 短词复合: zhparser.multi_short = f 这一类的控制分词结果的选项，根据实际使用酌情开启。

如何压缩Mysql数据库

压缩表从名字上来看，简单理解为压缩后的表，也就是把原始表根据一定的压缩算法按照一定的压缩比率压缩后生成的表。

1.1 压缩能力强的产品

表压缩后从磁盘占用上看要比原始表要小很多。如果你熟悉列式数据库，那对这个概念一定不陌生。比如，基于 PostgreSQL 的列式数据库 Greenplum；早期基于 MySQL 的列式数据库 inforbright；或者 Percona 的产品 tokudb 等，都是有压缩能力非常强的数据库产品。

1.2 为什么要用压缩表？

情景一：磁盘大小为 1T，不算其他的空间占用，只能存放 10 张 100G 大小的表。如果这些表以一定的比率压缩后，比如每张表从 100G 压缩到 10G，那同样的磁盘可以存放 100 张表，表的容量是原来的 10 倍。情景二：默认 MySQL 页大小 16K，而 OS 文件系统一般块大小为 4K，所以在 MySQL 在刷脏页的过程中，有一定的概率出现页没写全而导致数据坏掉的情形。比如 16K 的页写了 12K，剩下 4K 没写成功，导致 MySQL 页数据损坏。这个时候就算通过 Redo Log 也恢复不了，因为几乎有所有的关系数据库采用的 Redo Log 都记录了数据页的偏移量，此时就算通过 Redo Log 恢复后，数据也是错误的。所以 MySQL 在刷脏数据之前，会把这部分数据先写入共享表空间里的 DOUBLE WRITE BUFFER 区域来避免这种异常。此时如果 MySQL 采用压缩表，并且每张表页大小和磁盘块大小一致，比如也是 4K，那 DOUBLE WRITE BUFFER 就可以不需要，这部分开销就可以规避掉了。查看文件系统的块大小：

root@ytt-pc:/home/ytt#  tune2fs -l /dev/mapper/ytt--pc--vg-root  | grep -i 'block size'Block size:               4096

1.3 压缩表的优势

压缩表的优点非常明显，占用磁盘空间小！由于占用空间小，从磁盘置换到内存以及之后经过网络传输都非常节省资源。

简单来讲：节省磁盘 IO，减少网络 IO。

1.4 压缩表的缺陷

当然压缩表也有缺点，压缩表的写入（INSERT,UPDATE,DELETE）比普通表要消耗更多的 CPU 资源。

压缩表的写入涉及到解压数据，更新数据，再压缩数据，比普通表多了解压和再压缩两个步骤，压缩和解压缩需要消耗一定的 CPU 资源。所以需要选择一个比较优化的压缩算法。

1.5 MySQL 支持的压缩算法

这块是 MySQL 所有涉及到压缩的基础，不仅仅用于压缩表，也用于其它地方。比如客户端请求到 MySQL 服务端的数据压缩；主从之间的压缩传输；利用克隆插件来复制数据库操作的压缩传输等等。

从下面结果可以看到 MySQL 支持的压缩算法为 zlib 和 zstd，MySQL 默认压缩算法为 zlib，当然你也可以选择非 zlib 算法，比如 zstd。至于哪种压缩算法最优，暂时没办法简单量化，依赖表中的数据分布或者业务请求。

如何扩大postgresql存储空间

1. 概述

cstore_fdw实现了 PostgreSQL 数据库的列式存储。列存储非常适合用于数据分析的场景，数据分析的场景下数据是批量加载的。

这个扩展使用了Optimized Row Columnar (ORC)数据存储格式，ORC改进了Facebook的RCFile格式，带来如下好处:

压缩:将内存和磁盘中数据大小削减到2到4倍。可以扩展以支持不同压缩算法。

列投影:只提取和查询相关的列数据。提升IO敏感查询的性能。

跳过索引:为行组存储最大最小统计值，并利用它们跳过无关的行。

2. 使用

cstore_fdw的安装和使用都非常简单，可以参考官方资料。

thub.com/citusdata/cstore_fdw

注)注意cstore_fdw只支持PostgreSQL9.3和9.4 。

下面做几个简单的性能对比，看看cstore_fdw究竟能带来多大的性能提升。

2.1 数据加载

2.1.1 普通表

CREATE TABLE tb1

(

id int,

c1 TEXT,

c2 TEXT,

c3 TEXT,

c4 TEXT,

c5 TEXT,

c6 TEXT,

c7 TEXT,

c8 TEXT,

c9 TEXT,

c10 TEXT

);

注:要和普通表的全表扫描作对比，所以不建主键和索引。

[postgres@node2 chenhj]$ time psql -p 40382 -At -F, -c "select id,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text from generate_series(1,10000000) id"|time psql -p 40382 -c "copy tb1 from STDIN with CSV"

COPY 10000000

1.56user 1.00system 6:42.39elapsed 0%CPU (0avgtext+0avgdata 7632maxresident)k

776inputs+0outputs (17major+918minor)pagefaults 0swaps

real 6m42.402s

user 0m15.174s

sys 0m14.904s

postgres=# select pg_total_relation_size('tb1'::regclass);

pg_total_relation_size

------------------------

1161093120

(1 row)

postgres=# \timing

Timing is on.

postgres=# analyze tb1;

ANALYZE

Time: 11985.070 ms

插入1千万条记录，数据占用存储大小1.16G，插入耗时6分42秒，分析耗时12秒。

2.1.2 cstore表

$ mkdir -p /home/chenhj/data94/cstore

CREATE EXTENSION cstore_fdw;

CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;

CREATE FOREIGN TABLE cstb1

(

id int,

c1 TEXT,

c2 TEXT,

c3 TEXT,

c4 TEXT,

c5 TEXT,

c6 TEXT,

c7 TEXT,

c8 TEXT,

c9 TEXT,

c10 TEXT

)

SERVER cstore_server

OPTIONS(filename '/home/chenhj/data94/cstore/cstb1.cstore',

compression 'pglz');

[postgres@node2 chenhj]$ time psql -p 40382 -At -F, -c "select id,id::text,id::text,id::text,id::text, id::text,id::text,id::text,id::text,id::text,id::text from generate_series(1,10000000) id"|time psql -p 40382 -c "copy cstb1 from STDIN with CSV"

COPY 10000000

1.53user 0.78system 7:35.15elapsed 0%CPU (0avgtext+0avgdata 7632maxresident)k

968inputs+0outputs (20major+920minor)pagefaults 0swaps

real 7m35.520s

user 0m14.809s

sys 0m14.170s

[postgres@node2 chenhj]$ ls -l /home/chenhj/data94/cstore/cstb1.cstore

-rw------- 1 postgres postgres 389583021 Jun 23 17:32 /home/chenhj/data94/cstore/cstb1.cstore

postgres=# \timing

Timing is on.

postgres=# analyze cstb1;

ANALYZE

Time: 5946.476 ms

插入1千万条记录，数据占用存储大小390M，插入耗时7分35秒，分析耗时6秒。

使用cstore列存储后，数据占用存储大小降到普通表的3分之1。需要说明的是，由于所有TEXT列填充了随机数据，压缩率不算高，某些实际的应用场景下压缩效果会比这更好。

2.2 Text列的like查询性能对比

2.2.1 普通表

清除文件系统缓存，并重启PostgreSQL

[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart

[root@node2 ~]# free

total used free shared buffers cached

Mem: 2055508 771356 1284152 0 9900 452256

-/+ buffers/cache: 309200 1746308

Swap: 4128760 387624 3741136

[root@node2 ~]# echo 1 /proc/sys/vm/drop_caches

[root@node2 ~]# free

total used free shared buffers cached

Mem: 2055508 326788 1728720 0 228 17636

-/+ buffers/cache: 308924 1746584

Swap: 4128760 381912 3746848

对Text列执行like查询

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.42 0.00 95.40

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 58.55 330.68 212.08 7351441 4714848

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where c1 like '%66'"

count

--------

100000

(1 row)

real 0m7.051s

user 0m0.001s

sys 0m0.004s

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.43 0.00 95.39

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 58.90 381.53 211.90 8489597 4714956

耗时7.1秒，产生IO读1.14G,IO写108K。

不清文件系统缓存，不重启PostgreSQL，再执行一次。消耗时间降到1.6秒，几乎不产生IO。

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.43 0.00 95.39

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 58.81 332.20 213.06 7350301 4714364

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where c1 like '%66'"

count

--------

100000

(1 row)

real 0m1.601s

user 0m0.002s

sys 0m0.001s

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.43 0.00 95.38

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 58.80 332.12 213.01 7350337 4714364

2.2.2 cstore表

清除文件系统缓存，并重启PostgreSQL

[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart

[root@node2 ~]# echo 1 /proc/sys/vm/drop_caches

对Text列执行like查询

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.38 0.00 95.45

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 58.12 376.42 209.04 8492017 4716048

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from cstb1 where c1 like '%66'"

count

--------

100000

(1 row)

real 0m2.786s

user 0m0.002s

sys 0m0.003s

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.38 0.00 95.44

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 58.12 378.75 208.89 8550761 4716048

耗时2.8秒，产生IO读59M,IO写0K。执行时间优化的虽然不是太多，但IO大大减少，可见列投影起到了作用。

不清文件系统缓存，不重启PostgreSQL，再执行一次。消耗时间降到1.4秒，几乎不产生IO。

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.36 0.00 95.47

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 57.75 376.33 207.58 8550809 4716524

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from cstb1 where c1 like '%66'"

count

--------

100000

(1 row)

real 0m1.424s

user 0m0.002s

sys 0m0.001s

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.36 0.00 95.47

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 57.70 375.96 207.38 8550809 4716588

2.3 对Int列执行=查询

2.3.1 普通表

清除文件系统缓存，并重启PostgreSQL后

[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart

[root@node2 ~]# echo 1 /proc/sys/vm/drop_caches

对Int列执行=查询

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.79 0.00 0.37 3.33 0.00 95.50

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 57.25 373.21 205.67 8560897 4717624

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where id =666666"

count

-------

1

(1 row)

real 0m6.844s

user 0m0.002s

sys 0m0.006s

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.79 0.00 0.37 3.34 0.00 95.49

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 57.60 422.57 205.54 9699161 4717708

耗时6.8秒，产生IO读1.14G,IO写84K

不清缓存，再执行一次。消耗时间降到1.1秒，几乎不产生IO。

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.79 0.00 0.37 3.33 0.00 95.50

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 57.44 421.37 204.97 9699177 4718032

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where id =666666"

count

-------

postgresql数据库怎么查询所有的序列名

1、在查询的时候一般使用*查询代表查询所有字段信息，但表太大就不建议使用这种方式。

2、在查询的时候也可以指定字段进行查询，如下图只查询表中的一部分字段信息。

3、不过最经常使用的查询是指定特殊的条件来进行查询，以便查询结果更为精确。

4、在查询的时候有时也需要对查询结果进行适当的排序，这样可以快速定位要查询数据的结果。

5、在查询的时候也可以指定特殊的查询范围，根据指定的查询范围来查询出特定结果。