正文
postgresql列存的简单介绍
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
如何向postgreSQL中添加bytea类型的大对象数据
如果想要在PostgreSQL中存储二进制数据，例如存储Word、Excel文档，图片文件等，可以使用bytea类型的列。bytea类型是PostgreSQL特有的存储二进制数据的字段类型，与SQL标准中的BLOB和BINARY
LARGE OBJECT类型异曲同工。这在PostgreSQL文档的bytea类型介绍中有所说明。
接下来先说说如何向表中插入、更新bytea数据。
PostgreSQL允许在SQL命令中包含bytea类型的数据，以便能够使用INSERT向表中插入含有二进制数据的记录，使用UPDATE和调用与bytea类型相关的函数更新和操作bytea类型数据。二进制数据是一个字节序列，然而SQL命令是文本字符串，怎样在SQL中写入二进制数据呢？答案很简单，把每一个字节转换成对应的三位十进制数字的八进制数字符串表示，以双斜线做为前缀，即0x00表示为\\000、0x2C表示为\\02C、0xFF表示为\\377，并按照bytea类型的要求在字符串前端的单引号外注明E。举例如下：
INSERT INTO table1 (fileid, filename, content) VALUES (1, 'filename.doc', E'\\000\\001\\002');
INSERT INTO table1 (fileid, filename, content) VALUES (2, 'anotherfile.jpg', E'\\000\\377');
UPDATE table1 SET content = E'\\000\\000\\000' WHERE fileid
= 1;
UPDATE table1 SET content = content || E'\\377\\377\\377' WHERE fileid
= 2;
可以在INSERT
INTO中包含整个文件的bytea类型字符串，也可以像上面第四行那样，分块追加。对于短小的二进制数据，在命令控制台中编辑SQL命令也未尝不可。但是如果要存储一个图片文件或者Word文档之类的大型二进制数据的时候，就需要借助数据访问借口，或者自己写一个字节转换程序，直接操作SQL语句。
插入bytea数据后可以使用SELECT语句获取它。如下所示：
SELECT content FROM table1;
在命令控制台中，我们会看到以输入时的字符串格式输出二进制数据，这是PostgreSQL做的转换。在Python中使用psycopg2模块，执行上述SELECT语句后能够获得原始的二进制字节字符串，可以直接写入二进制文件。
顺便说明一下。对于字节的转换，PostgreSQL的文档说的非常详细，按照零字节、单引号、斜线，以及字符的可打印性分别作了讨论。原因是需要逃逸单引号和斜线字符，另外可打印字符可以不作转换，直接出现。
如何评价postgresql
PostgreSQL的主要优点：
1、对事务的支持与MySQL相比，经历了更为彻底的测试。对于一个严肃的商业应用来说，事务的支持是不可或缺的。
2、MySQL对于无事务的MyISAM表。采用表锁定，一个长时间运行的查询很可能会长时间地阻碍对表的更新。而PostgreSQL不存在这样的问题。
3、PostgreSQL支持存储过程，而目前MySQL不支持，对于一个严肃的商业应用来说，作为数据库本身，有众多的商业逻辑的存在，此时使用存储过程可以在较少地增加数据库服务器的负担的前提下，对这样的商业逻辑进行封装，并可以利用数据库服务器本身的内在机制对存储过程的执行进行优化。此外存储过程的存在也避免了在网络上大量的原始的SQL语句的传输，这样的优势是显而易见的。
4、对视图的支持，视图的存在同样可以最大限度地利用数据库服务器内在的优化机制。而且对于视图权限的合理使用，事实上可以提供行级别的权限，这是MySQL的权限系统所无法实现的。
5、对触发器的支持，触发器的存在不可避免的会影响数据库运行的效率，但是与此同时，触发器的存在也有利于对商业逻辑的封装，可以减少应用程序中对同一商业逻辑的重复控制。合理地使用触发器也有利于保证数据的完整性。
6、对约束的支持。约束的作用更多地表现在对数据完整性的保证上，合理地使用约束，也可以减少编程的工作量。
如何扩大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中创建序列数并应用在数据表
序列数可以增加postgresql数据表的检索速度，同时降低数据查询时的资源消耗。那么如何在postgresql中创建序列数并且应用呢？下面我给大家分享一下。
工具/材料
pgAdmin4
创建序列数
01
首先打开pgAdmin4，展开postgresql数据库，找到模式下面的public选项，如下图所示
02
接下来在public下面右键单击序列，然后点击Create下面的sequence选项，如下图所示
03
在弹出的创建Sequence界面中首先给序列数起一个名字，如下图所示，注意都用英文
04
然后切换到Definition页卡，定义一下序列的增加量，如下图所示，其中maximum根据自己的需要进行设置
05
最后回到数据库主界面，你会看到序列下面多出了一个项，这就是我们创建的序列数了，如下图所示
在数据表中应用序列数
01
首先选中一个数据表，点击右侧的编辑按钮，如下图所示
02
在弹出的编辑界面中切换到Columns页卡，点击ID签名的编辑按钮，如下图所示
03
最后在字段的编辑界面中切换到Variables选项卡，然后在Value列中通过nextval函数带入刚才定义的序列数即可，如下图所示
postgreSQL数据类型问题
一个bigint它占用了八个字节的存储空间，可以精确的表示从-2^63到2^63-1(即从-9，223，372，036，854，775，808到 9，223，372，036，854，775，807)之间的整数。所以恩，不是说一个数字，而是从-2^63到2^63-1都可以.
varchar 是按字节来存储，如 varchar(10),会占用11个字节，其中10个是用来定义字符，还有一个字节是用来存储列的长度
postgresql 合并多张表 ，保留所有不同的列，最后生成一张新表
先不考虑数据库设计的是否合理
你是想新建表，之后把数据存进去？还是建立一个临时表把数据查出来？
首先你两个表要有一定的关联关系，两个表的 SIP一样？DIP一样？还是User一样？
根据一样的作为两个表的关联条件
select * from 表1 t1 left join 表2 t2 on t1.SIP =t2.SIP
