POSTGRESQL bytea 数据类型到底能不能用 (翻译)

2021-05-11 00:00:00 数据 数据库 读取 方式 提取



一个频繁被问及的问题,在存储数据的世界里面,将"大数据" 存储到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/


相关文章