POSTGRESQL bytea 数据类型到底能不能用 (翻译)
一个频繁被问及的问题,在存储数据的世界里面,将"大数据" 存储到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/
相关文章