Symfoware

Symfowareについての考察blog

PostgreSQL 9.3.2 画像データ登録で text vs. bytea

PostgreSQLには、text型、bytea型というデータ型があります。
ふと、画像データとかをbytea型に登録してみたけど、BASE64にしてtext型に登録してみるのも
面白いのでは?と思ったので試してみます。

環境はFreeBSD 10.0 + PostgreSQL 9.3.2です。


bytea型への登録・検索



こんなテーブルを定義しました。


CREATE TABLE image_bytea(
id int,
image bytea,
PRIMARY KEY(id)
)




適当な画像ファイルを10,000回登録してみます。


  1. import java.io.FileInputStream;
  2. import java.nio.ByteBuffer;
  3. import java.nio.channels.FileChannel;
  4. import java.sql.Connection;
  5. import java.sql.DriverManager;
  6. import java.sql.PreparedStatement;
  7. public class InsertBytea {
  8.     public static void main(String... args) throws Exception {
  9.         long start = System.currentTimeMillis();
  10.         String master_url = "jdbc:postgresql://192.168.1.101:5432/sample";
  11.         String sql = "INSERT INTO image_bytea(id, image) values(?, ?)";
  12.         try (Connection con = DriverManager.getConnection(master_url, "pgadmin", "password");
  13.                 PreparedStatement pstmt = con.prepareStatement(sql)) {
  14.             con.setAutoCommit(false);
  15.             for (int i = 1; i <= 10000; i++) {
  16.                 loadImage(i, pstmt);
  17.             }
  18.             con.commit();
  19.         } catch (Exception e) {
  20.             e.printStackTrace();
  21.         }
  22.         long end = System.currentTimeMillis();
  23.         System.out.println(end - start);
  24.     }
  25.     private static void loadImage(int index, PreparedStatement pstmt) throws Exception {
  26.         FileInputStream fis = new FileInputStream("Lenna.jpg");
  27.         FileChannel channel = fis.getChannel();
  28.         ByteBuffer buffer = ByteBuffer.allocate((int) channel.size());
  29.         channel.read(buffer);
  30.         buffer.clear();
  31.         byte[] bytes = new byte[buffer.capacity()];
  32.         buffer.get(bytes);
  33.         channel.close();
  34.         fis.close();
  35.         pstmt.setInt(1, index);
  36.         pstmt.setBytes(2, bytes);
  37.         pstmt.execute();
  38.     }
  39. }




一応、ちゃんと復元できるかのテスト。


  1. import java.io.FileOutputStream;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.ResultSet;
  5. import java.sql.Statement;
  6. public class SearchBytea {
  7.     public static void main(String... args) throws Exception {
  8.         long start = System.currentTimeMillis();
  9.         String master_url = "jdbc:postgresql://192.168.1.101:5432/sample";
  10.         try (Connection con = DriverManager.getConnection(master_url, "pgadmin", "password");
  11.                 Statement stmt = con.createStatement()) {
  12.             
  13.             String sql = "SELECT image FROM image_bytea WHERE id = 1";
  14.             ResultSet rs = stmt.executeQuery(sql);
  15.             while(rs.next()) {
  16.                 
  17.                 byte[] bytes = rs.getBytes("image");
  18.                 
  19.                 FileOutputStream fos = new FileOutputStream("result.jpg");
  20.                 fos.write(bytes);
  21.                 fos.close();
  22.                 
  23.                 
  24.             }
  25.         } catch (Exception e) {
  26.             e.printStackTrace();
  27.         }
  28.         long end = System.currentTimeMillis();
  29.         System.out.println(end - start);
  30.     }
  31. }



問題ありませんでした。





text型への登録・検索



こんなテーブルを定義しました。


CREATE TABLE image_text(
id int,
image text,
PRIMARY KEY(id)
)




Base64変換は、Apache Commons Codecを使用しました。
Apache Commons Codec

登録サンプルはこんな感じ。


  1. import java.io.FileInputStream;
  2. import java.nio.ByteBuffer;
  3. import java.nio.channels.FileChannel;
  4. import java.sql.Connection;
  5. import java.sql.DriverManager;
  6. import java.sql.PreparedStatement;
  7. import org.apache.commons.codec.binary.Base64;
  8. public class InsertText {
  9.     
  10.     public static void main(String... args) throws Exception {
  11.         long start = System.currentTimeMillis();
  12.         String master_url = "jdbc:postgresql://192.168.1.101:5432/sample";
  13.         String sql = "INSERT INTO image_text(id, image) values(?, ?)";
  14.         try (Connection con = DriverManager.getConnection(master_url, "pgadmin", "password");
  15.                 PreparedStatement pstmt = con.prepareStatement(sql)) {
  16.             con.setAutoCommit(false);
  17.             for (int i = 1; i <= 10000; i++) {
  18.                 loadImage(i, pstmt);
  19.             }
  20.             con.commit();
  21.         } catch (Exception e) {
  22.             e.printStackTrace();
  23.         }
  24.         long end = System.currentTimeMillis();
  25.         System.out.println(end - start);
  26.     }
  27.     
  28.     
  29.     private static void loadImage(int index, PreparedStatement pstmt) throws Exception {
  30.         FileInputStream fis = new FileInputStream("Lenna.jpg");
  31.         FileChannel channel = fis.getChannel();
  32.         ByteBuffer buffer = ByteBuffer.allocate((int) channel.size());
  33.         channel.read(buffer);
  34.         buffer.clear();
  35.         byte[] bytes = new byte[buffer.capacity()];
  36.         buffer.get(bytes);
  37.         channel.close();
  38.         fis.close();
  39.         // 文字列をBase64にエンコード
  40.         String base64str = new String(Base64.encodeBase64(bytes));    
  41.         
  42.         pstmt.setInt(1, index);
  43.         pstmt.setString(2, base64str);
  44.         pstmt.execute();
  45.     }
  46. }




復元できるかテスト


  1. import java.io.FileOutputStream;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.ResultSet;
  5. import java.sql.Statement;
  6. import com.sun.org.apache.xml.internal.security.utils.Base64;
  7. public class SearchText {
  8.     public static void main(String... args) throws Exception {
  9.         long start = System.currentTimeMillis();
  10.         String master_url = "jdbc:postgresql://192.168.1.101:5432/sample";
  11.         try (Connection con = DriverManager.getConnection(master_url, "pgadmin", "password");
  12.                 Statement stmt = con.createStatement()) {
  13.             
  14.             String sql = "SELECT image FROM image_text WHERE id = 1";
  15.             ResultSet rs = stmt.executeQuery(sql);
  16.             while(rs.next()) {
  17.                 
  18.                 
  19.                 byte[] bytes = Base64.decode(rs.getString("image"));
  20.                 
  21.                 FileOutputStream fos = new FileOutputStream("result.jpg");
  22.                 fos.write(bytes);
  23.                 fos.close();
  24.                 
  25.                 
  26.             }
  27.         } catch (Exception e) {
  28.             e.printStackTrace();
  29.         }
  30.         long end = System.currentTimeMillis();
  31.         System.out.println(end - start);
  32.     }
  33. }




問題なく復元出来ました。





速度比較



まあ、当然といえば当然なのですが、byteaの方が2倍程度高速でした。
登録するデータ量がそのまま速度に出ているんだと思います。

計測結果は以下のとおり。
単位はmsです。






パターン1回目2回目3回目4回目5回目平均
bytea24,74725,69821,49228,08424,23124,850
text49,85448,64150,99650,12350,73750,070



なんとなくで確証はないのですが、textのほうが
遅いなりに速度が安定している気もします。

また、単件の画像復元程度であれば復元時間に差はみられませんでした。
bytea列を使って遅いとか、速度が安定しない場合はtextに突っ込むのも
ありなのかもしれません。
関連記事

テーマ:プログラミング - ジャンル:コンピュータ

  1. 2014/03/16(日) 18:03:54|
  2. PostgreSQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集
<<FreeBSD 10.0にRedis 2.8.7をインストールする | ホーム | DB2 10.5にDebian + PHP5から接続する>>

コメント

コメントの投稿


管理者にだけ表示を許可する

トラックバック

トラックバック URL
http://symfoware.blog68.fc2.com/tb.php/1345-fcd74328
この記事にトラックバックする(FC2ブログユーザー)