一个频繁被问及的问题,在存储数据的世界里面,将"大数据" 存储到POSTGRESQL 中的 binary data 字段的类型, 同时Postgresql 有两个方法来存储binary data , 那种更好呢? 给我来
我决定用benchmark 来对这个事情来进行测试并得到一些让大家感兴趣信息来进行分享.
对于bytea 数据库的存储我们采用集中方式, 来进行, 第一种是我们将文件的路径存储在数据库中,将数据存储到文件系统外面,很明显的特点是这样的存储方式无法保证数据内外部的数据一致性, 因此数据库外存储数据导致数据存储的结构复杂,调用麻烦.
保持一致性的方法也可以耍一个花招,例如数据库中存储的文件的路径,但文件可能不存在,可以做一个所谓的"文件", 在数据查询的时候访问路径时是有文件的. 方式访问数据报错的出现.
那么这样做有什么好处,好处之一是数据库可以尽量保持小的状态,数据量小对于数据库来说是一件好事,对于备份来说是好事, 对于数据库的性能而言, 提取数据是从文件系统中提取的,则提取性能和数据库本身就无关了
这是一种方法,那么另一种方法是使用POSTGRESQL 的 large object ,大对象是PostgreSQL中存储二进制数据的“老方法”。系统将一个oid(一个4字节的无符号整数)分配给大对象，将其分成2kB的块，并将其存储在pg_largeobject编目表中。通过OID 引用大对象的方式表和对象之间并未有关联, 在删除表的信息后,存储在LO 中的信息会无法在关联, 大对象的使用的方式中,仅仅支持API调用和写入,SQL 无法对其进行操作, 所以在操作的复杂性方面,是有问题.
可以肯定的是LO 的两个优点
1 可以存储较大的数据
2 存储大数据API 支持流式数据的读和写
存储数据到POSTGRESQL 的BYTEA 的数据类型中
bytea 是一个存储二进制数据的新的方法,他采用TOAST 的方式来存储较大的数据, bytea 类型的数据是直接存储在数据库的表中的, 当你删除了数据的表行,相关的数据也就被清理了.
bytea 的不利点有那些
1 TOAST存储的数据类型数据的大小限制在1GB (每行)
2 当你去读取和写入数据,所有的数据会先存储在内存中
那么如果你不了解TOAST 下面来了解一下 toast的重要性
如果你选择bytea 的情况下, 你应该明白TOAST 是如何工作的
对于新的表行来说,超过2000 bytes, 对于可变的额类型,数据会进行压缩
如果压缩后的数据仍然超过2000bytes 则POSTGRESQL 分割长度,将信息开始存储在toast 的表中.
这里有几个问题,如果存储的数据本身就是压缩的,那么后期在对数据存储的时候在压缩,其实对CPU是浪费的, 并且如果只需要提取toast中存储数据的一部分,则需要整体的数据提取,并在解压,和从中提取数据. 当然你也可以改表TOAST的存储策略,为external .
对于三种模式我们进行测试,我们先创建一个表
CREATE
TABLE
bins (
id bigint
PRIMARY
KEY,
data bytea NOT
NULL
);
ALTER
TABLE
bins ALTER
COLUMN
data SET
STORAGE EXTERNAL;
我们通过JAVA 来对三种方式进行测试, 其中主要测试读取和写入
import
java.io.EOFException;
import
java.io.IOException;
import
java.sql.SQLException;
public
interface
LOBStreamer {
public
final
static
int
CHUNK_SIZE = 1048576;
public
int
getNextBytes(byte[] buf)
throws
EOFException, IOException, SQLException;
public
void
close() throws
IOException, SQLException;
1 读取FILE SYSTEM 的方式
import
java.io.IOException;
import
java.sql.PreparedStatement;
import
java.sql.ResultSet;
import
java.sql.SQLException;
import
java.io.EOFException;
import
java.io.File;
import
java.io.FileInputStream;
public
class
FileStreamer implements
LOBStreamer {
private
FileInputStream file;
public
FileStreamer(java.sql.Connection conn, long
objectID)
throws
IOException, SQLException {
PreparedStatement stmt = conn.prepareStatement(
"SELECT path FROM lobs WHERE id = ?");
stmt.setLong(1, objectID);
ResultSet rs = stmt.executeQuery();
rs.next();
String path = rs.getString(1);
this.file = new
FileInputStream(new
File(path));
rs.close();
stmt.close();
}
@Override
public
int
getNextBytes(byte[] buf)
throws
EOFException, IOException {
int
result = file.read(buf);
if
(result == -1)
throw
new
EOFException();
return
result;
}
@Override
public
void
close() throws
IOException {
file.close();
}
}
2 读取LO中的数据
import
java.io.EOFException;
import
java.io.IOException;
import
java.sql.Connection;
import
java.sql.SQLException;
import
org.postgresql.PGConnection;
import
org.postgresql.largeobject.LargeObject;
import
org.postgresql.largeobject.LargeObjectManager;
public
class
LargeObjectStreamer implements
LOBStreamer {
private
LargeObject lob;
public
LargeObjectStreamer(Connection conn, long
objectID)
throws
SQLException {
PGConnection pgconn = conn.unwrap(PGConnection.class);
this.lob = pgconn.getLargeObjectAPI().open(
objectID, LargeObjectManager.READ);
}
@Override
public
int
getNextBytes(byte[] buf)
throws
EOFException, SQLException {
int
result = lob.read(buf, 0, buf.length);
if
(result == 0)
throw
new
EOFException();
return
result;
}
@Override
public
void
close() throws
IOException, SQLException {
lob.close();
}
}
3 读取bytea 的数据
import
java.io.EOFException;
import
java.io.IOException;
import
java.io.InputStream;
import
java.sql.Connection;
import
java.sql.PreparedStatement;
import
java.sql.ResultSet;
import
java.sql.SQLException;
public
class
ByteaStreamer implements
LOBStreamer {
private
PreparedStatement stmt;
private
Connection conn;
private
int
position = 1, size;
public
ByteaStreamer(Connection conn, long
objectID)
throws
SQLException {
PreparedStatement len_stmt = conn.prepareStatement(
"SELECT length(data) FROM bins WHERE id = ?");
len_stmt.setLong(1, objectID);
ResultSet rs = len_stmt.executeQuery();
if
(!rs.next())
throw
new
SQLException("no data found", "P0002");
size = rs.getInt(1);
rs.close();
len_stmt.close();
this.conn = conn;
this.stmt = conn.prepareStatement(
"SELECT substr(data, ?, ?) FROM bins WHERE id = ?");
this.stmt.setLong(3, objectID);
}
@Override
public
int
getNextBytes(byte[] buf)
throws
EOFException, IOException, SQLException {
int
result = (position > size + 1
- buf.length) ?
(size - position + 1) : buf.length;
if
(result == 0)
throw
new
EOFException();
this.stmt.setInt(1, position);
this.stmt.setInt(2, result);
ResultSet rs = this.stmt.executeQuery();
rs.next();
InputStream is = rs.getBinaryStream(1);
is.read(buf);
is.close();
rs.close();
position += result;
return
result;
}
@Override
public
void
close() throws
SQLException {
this.stmt.close();
}
}
最终的结果,读取的数为 350 MB 每个方式的数据读取结果
总结
通过benchmark来对二进制数据进行处理,最快速的方式是 file system 的方式，这并不意外，让人意外的是，通过TOAST 技术存储数据的 bytea数据类型采用external方式，的速度会比LO 的方式要快。
下面是各种数据存储方式不同的优缺点
1 file system
优点
1 最快速的数据读取方式
2 数据库容易被备份和恢复
缺点
1 数据的一致性难以保证
2 更复杂的架构
2 LO
优点：
1 数据的一致性有所保障
2 API 流方式提取数据
缺点
1 糟糕的性能问题
2 非标准的API接口
3 需要特殊的维护
4 数据库会变得难以维护和巨大
3 Bytea 模式
优点：
数据的一致性能得到保障
通过标准的SQL 来进行工作
缺点：
较差的性能
输出和写入都要通过内存
数据库会变得较大和不利于维护
那么在POSTGRESQL 中使用 BYTEA 模式来存储数据，仅仅对于小的文件来说是一个好主意，但对于架构设计来说，如果需要高性能，还需要更深层的考虑和利用其他的方法。
原文：
https://www.cybertec-postgresql.com/en/binary-data-performance-in-postgresql/
