PostgreSQL 二进制数据存储的性能到底高不高面包真香后续

AustinDatabases

发布于 2020-06-04 15:21:49

2.1K0

发布于 2020-06-04 15:21:49

之前写过一个关于POSTGRESQL TOAST 的存储的文字，这篇算是那篇的后续，起因是这样的，昨天在一个PG 的群里面，有人问是否可以在一个字段中存储1个G 的数据。一个数据库中字段存储数据是无可厚非的，但实际上存储数据的方式和大小决定了一个数据库是否能进行正常的运作，软件的设计中也有相关的限制，数据库本身可以理解为一个软件，既然是软件，既然有相关的数据结构的设计，则什么是适合的什么是不适合的都有相关的定论。

PostgreSQL 本身支持一种二进制的方式来存储数据类型为bytea, 使用这个类型存储数据有什么好处。

1 可以存储任意大的数据

2 数据已块的方式读取，速度快

至于存储的方式还是通过toast的方式来进行数据的存储，至于不清楚什么是toast技术的可以看前面一篇 postgresql 烤面包真香的那一篇。

实际上，存储大容量的数据在数据库中是会对其进行压缩的，而数据的压缩虽然从存储上是有利的，但数据的提取中就会遇到我只需要其中一块的数据，但由于数据是压缩的，所以必须全面解压数据后，才能提取另一部分的数据，这就造成了数据提取的缓慢和资源的浪费消耗等问题。

所以POSTGRESQL 提出了一个方式来存储数据并不进行压缩，将其存储在数据库整体之外的方式。那具体这样做的好处和特点，是什么我们可以测试一下

首先我们创建两个表一个表使用了 storage external 的方式来存储我们的data_save字段，另一个我们采用本身PG的方式来存储我们看看有什么不同

我们可以清晰的看到使用了stroage extenal 的表在存储229MB 容量的数据到一个字段的情况下，使用了这个技术要比不使用快 2 - 5秒，经过多次试验，另外根据插入的数据越大，之间的差距也是越来越大。

另外我们需要看到到底存储这些数据的物理空间有没有不同

我们到目前数据库的存储的物理位置，同时都存储一个229MB的文件到一个字段中的结果

可以看到如果使用独有的列外排的技术，则数据基本上没有太大的压缩，最后实际上存储的数据215MB ，而如果使用默认的方式来存储数据则实际的数据变为63MB，压缩的比率在3.6.

其实这件事还有很多可以挖掘的，但还是由于时间和精力的限制，今天就到此为止。

很烂的PYTHON 语句，请PYTHON高手见谅

#!/usr/bin/python3
# -*- coding: utf-8 -*-
import psycopg2
import sys
import fileinput
from psycopg2 import Error
import datetime
def target():
    f = open("H:/app1.csv", mode='rb', buffering=0)
    read_block = f.read()

    try:  #捕捉连接中的错误
        #连接数据库
        connection = psycopg2.connect(user = "admin",password="1234.com",host="192.168.198.100",port="5432",database = "test")
        #获得连接产生cursor
        cursor = connection.cursor()
        #执行API中的方法
        print (connection.get_dsn_parameters(),"\n")
        #通过游标来执行SQL语句并传到数据库
        cursor.execute("SELECT version();")
        #取第一行记录
        record = cursor.fetchone()
        print("You are connected to - ", record,"\n")
        print(datetime.datetime.now())
        cursor.execute("INSERT INTO bytea_save_l (id,data_save) VALUES (2,%s)", (psycopg2.Binary(read_block),))
        print(datetime.datetime.now())
        print ('insert')
        f.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print("Error while creating PostgreSQL table", error)
    #如果有错误，则捕捉然后打印错误，这里是无法连接时报错
    except (Exception, psycopg2.Error) as error :
        print ("Error while connecting to PostgreSQL", error)
         #在使用完毕后，关闭连接
    finally:
        #closing database connection.
            if(connection):
                cursor.close()
                connection.close()
                print("PostgreSQL connection is closed")


if __name__ == '__main__':
    target()