PostgreSQL Large Objects

This module utilises PostgreSQLs Large Objects to implement a BinaryStore. This is then only for PostgreSQL, and it also depends on the postgresql jdbc driver.

Using large objects, postgresql stores the data outside its standard table space and the object can be referenced by an id. They also allow to seek into a specific position, which is used to implement loading partial data.

The PgLoBinaryStore also implements JdbcBinaryStore and provides two methods to retrieve a binary. One, the default findBinary, uses one connection per chunk. The other, findBinaryStateful uses a connection for the entire stream.

Table Structure

The table used here is:

CREATE TABLE IF NOT EXISTS "file_lo" (
  "file_id" varchar(254) NOT NULL PRIMARY KEY,
  "data_oid" oid NOT NULL
)

Usage

For the examples to run, a PostgreSQL server is necessary. It is quite easy to start one locally, for example with docker.

import binny._
import binny.util.Logger
import binny.ExampleData._
import binny.jdbc.ConnectionConfig
import binny.pglo._
import fs2.Stream
import cats.effect.IO
import cats.effect.unsafe.implicits._


implicit val logger = Logger.silent[IO]
// logger: Logger[IO] = binny.util.Logger$$anon$2@4a2eb2e

val someData = ExampleData.file2M
// someData: Binary[IO] = Stream(..)
val ds = ConnectionConfig.Postgres.default.dataSource
// ds: javax.sql.DataSource = org.postgresql.ds.PGSimpleDataSource@16ae647d
val store = PgLoBinaryStore.default[IO](logger, ds)
// store: PgLoBinaryStore[IO] = binny.pglo.PgLoBinaryStore@7843efc5
val run =
  for {
    // Create the schema
    _ <- Stream.eval(PgSetup.run[IO](store.config.table, logger, ds))

    // insert some data
    id <- someData.through(store.insert)

    // get the file out
    bin <- Stream.eval(
      store.findBinary(id, ByteRange(0, 100)).getOrElse(sys.error("not found"))
    )
    str <- Stream.eval(bin.readUtf8String)
  } yield str + "..."
// run: Stream[[x]IO[x], String] = Stream(..)

run.compile.lastOrError.unsafeRunSync()
// res0: String = """hello world 1
// hello world 2
// hello world 3
// hello world 4
// hello world 5
// hello world 6
// hello world 7
// he..."""

JdbcBinaryStore

The PgLoBinaryStore also provides a findBinaryStateful variant just like the GenericJdbcStore. The default findBinary method creates a byte stream that loads the file in chunks. After every chunk, the connection is closed again and the next chunk seeks into the large object to start anew. In contrast, the findBinaryStateful method uses a single connection for the entire stream.