0
# Large Objects
1

2
This document covers PostgreSQL's Large Object (LOB/BLOB) API for storing and managing large binary data that exceeds the 1GB limit of the bytea type.
3

4
## Capabilities
5

6
### LargeObjectManager
7

8
Manager for creating, opening, and deleting large objects.
9

10
```java { .api }
11
package org.postgresql.largeobject;
12

13
import java.sql.SQLException;
14

15
/**
16
 * Manager for PostgreSQL Large Objects (BLOBs).
17
 * Large objects are stored outside regular tables and can be up to 4TB in size.
18
 * Access via PGConnection.getLargeObjectAPI().
19
 *
20
 * Note: All large object operations must be performed within a transaction.
21
 */
22
public class LargeObjectManager {
23
    /**
24
     * Mode constant: Open large object for reading.
25
     */
26
    public static final int READ = 0x00040000;
27

28
    /**
29
     * Mode constant: Open large object for writing.
30
     */
31
    public static final int WRITE = 0x00020000;
32

33
    /**
34
     * Mode constant: Open large object for reading and writing.
35
     */
36
    public static final int READWRITE = READ | WRITE;
37

38
    /**
39
     * Creates a new large object with default READWRITE access.
40
     * Returns a unique OID that identifies the large object.
41
     *
42
     * @return OID of the created large object
43
     * @throws SQLException if creation fails
44
     */
45
    public long createLO() throws SQLException;
46

47
    /**
48
     * Creates a new large object with specific mode.
49
     *
50
     * @param mode Access mode (READ, WRITE, or READWRITE)
51
     * @return OID of the created large object
52
     * @throws SQLException if creation fails
53
     */
54
    public long createLO(int mode) throws SQLException;
55

56
    /**
57
     * Deprecated: Use createLO() instead. Returns int instead of long.
58
     *
59
     * @return OID of created large object as int
60
     * @throws SQLException if creation fails
61
     * @deprecated Use {@link #createLO()} instead
62
     */
63
    @Deprecated
64
    public int create() throws SQLException;
65

66
    /**
67
     * Deprecated: Use createLO(int) instead. Returns int instead of long.
68
     *
69
     * @param mode Access mode
70
     * @return OID of created large object as int
71
     * @throws SQLException if creation fails
72
     * @deprecated Use {@link #createLO(int)} instead
73
     */
74
    @Deprecated
75
    public int create(int mode) throws SQLException;
76

77
    /**
78
     * Opens an existing large object with READWRITE mode.
79
     *
80
     * @param oid OID of the large object
81
     * @return LargeObject handle for I/O operations
82
     * @throws SQLException if large object doesn't exist or cannot be opened
83
     */
84
    public LargeObject open(long oid) throws SQLException;
85

86
    /**
87
     * Opens an existing large object with READWRITE mode, optionally committing on close.
88
     *
89
     * @param oid OID of the large object
90
     * @param commitOnClose If true, commits transaction when large object is closed
91
     * @return LargeObject handle for I/O operations
92
     * @throws SQLException if large object doesn't exist or cannot be opened
93
     */
94
    public LargeObject open(long oid, boolean commitOnClose) throws SQLException;
95

96
    /**
97
     * Opens an existing large object with specified mode.
98
     *
99
     * @param oid OID of the large object
100
     * @param mode Access mode (READ, WRITE, or READWRITE)
101
     * @return LargeObject handle for I/O operations
102
     * @throws SQLException if large object doesn't exist or cannot be opened
103
     */
104
    public LargeObject open(long oid, int mode) throws SQLException;
105

106
    /**
107
     * Opens an existing large object with specified mode, optionally committing on close.
108
     *
109
     * @param oid OID of the large object
110
     * @param mode Access mode (READ, WRITE, or READWRITE)
111
     * @param commitOnClose If true, commits transaction when large object is closed
112
     * @return LargeObject handle for I/O operations
113
     * @throws SQLException if large object doesn't exist or cannot be opened
114
     */
115
    public LargeObject open(long oid, int mode, boolean commitOnClose) throws SQLException;
116

117
    /**
118
     * Deprecated: Use open(long) instead. Takes int OID.
119
     *
120
     * @param oid OID of large object as int
121
     * @return LargeObject handle
122
     * @throws SQLException if open fails
123
     * @deprecated Use {@link #open(long)} instead
124
     */
125
    @Deprecated
126
    public LargeObject open(int oid) throws SQLException;
127

128
    /**
129
     * Deprecated: Opens large object with int OID, optionally committing on close.
130
     *
131
     * @param oid OID of large object as int
132
     * @param commitOnClose If true, commits transaction when large object is closed
133
     * @return LargeObject handle
134
     * @throws SQLException if open fails
135
     */
136
    public LargeObject open(int oid, boolean commitOnClose) throws SQLException;
137

138
    /**
139
     * Deprecated: Use open(long, int) instead. Takes int OID.
140
     *
141
     * @param oid OID of large object as int
142
     * @param mode Access mode
143
     * @return LargeObject handle
144
     * @throws SQLException if open fails
145
     * @deprecated Use {@link #open(long, int)} instead
146
     */
147
    @Deprecated
148
    public LargeObject open(int oid, int mode) throws SQLException;
149

150
    /**
151
     * Deprecated: Opens large object with int OID and mode, optionally committing on close.
152
     *
153
     * @param oid OID of large object as int
154
     * @param mode Access mode
155
     * @param commitOnClose If true, commits transaction when large object is closed
156
     * @return LargeObject handle
157
     * @throws SQLException if open fails
158
     */
159
    public LargeObject open(int oid, int mode, boolean commitOnClose) throws SQLException;
160

161
    /**
162
     * Deletes a large object.
163
     * The large object must not be open.
164
     *
165
     * @param oid OID of the large object to delete
166
     * @throws SQLException if deletion fails
167
     */
168
    public void delete(long oid) throws SQLException;
169

170
    /**
171
     * Deprecated: Use delete(long) instead. Takes int OID.
172
     *
173
     * @param oid OID of large object to delete as int
174
     * @throws SQLException if deletion fails
175
     * @deprecated Use {@link #delete(long)} instead
176
     */
177
    @Deprecated
178
    public void delete(int oid) throws SQLException;
179

180
    /**
181
     * Deletes a large object (alias for delete).
182
     * The large object must not be open.
183
     *
184
     * @param oid OID of the large object to delete
185
     * @throws SQLException if deletion fails
186
     */
187
    public void unlink(long oid) throws SQLException;
188

189
    /**
190
     * Deprecated: Use unlink(long) instead. Takes int OID.
191
     *
192
     * @param oid OID of large object to delete as int
193
     * @throws SQLException if deletion fails
194
     * @deprecated Use {@link #unlink(long)} instead
195
     */
196
    @Deprecated
197
    public void unlink(int oid) throws SQLException;
198
}
199
```
200

201
**Usage Examples:**
202

203
```java
204
import org.postgresql.PGConnection;
205
import org.postgresql.largeobject.LargeObjectManager;
206
import org.postgresql.largeobject.LargeObject;
207
import java.sql.Connection;
208
import java.sql.SQLException;
209

210
// Example 1: Create and write to large object
211
public class CreateLargeObjectExample {
212
    public static long createAndWrite(Connection conn, byte[] data)
213
            throws SQLException {
214
        // Must be in transaction
215
        conn.setAutoCommit(false);
216

217
        try {
218
            PGConnection pgConn = conn.unwrap(PGConnection.class);
219
            LargeObjectManager lobj = pgConn.getLargeObjectAPI();
220

221
            // Create new large object
222
            long oid = lobj.createLO(LargeObjectManager.READWRITE);
223

224
            // Open for writing
225
            LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);
226

227
            // Write data
228
            obj.write(data);
229

230
            // Close
231
            obj.close();
232

233
            // Commit transaction
234
            conn.commit();
235

236
            return oid;
237

238
        } catch (SQLException e) {
239
            conn.rollback();
240
            throw e;
241
        } finally {
242
            conn.setAutoCommit(true);
243
        }
244
    }
245
}
246

247
// Example 2: Read from large object
248
public class ReadLargeObjectExample {
249
    public static byte[] readAll(Connection conn, long oid) throws SQLException {
250
        conn.setAutoCommit(false);
251

252
        try {
253
            PGConnection pgConn = conn.unwrap(PGConnection.class);
254
            LargeObjectManager lobj = pgConn.getLargeObjectAPI();
255

256
            // Open for reading
257
            LargeObject obj = lobj.open(oid, LargeObjectManager.READ);
258

259
            // Get size and read all data
260
            long size = obj.size64();
261
            byte[] data = new byte[(int) size];
262
            obj.read(data, 0, data.length);
263

264
            // Close
265
            obj.close();
266

267
            conn.commit();
268
            return data;
269

270
        } catch (SQLException e) {
271
            conn.rollback();
272
            throw e;
273
        } finally {
274
            conn.setAutoCommit(true);
275
        }
276
    }
277
}
278

279
// Example 3: Delete large object
280
public class DeleteLargeObjectExample {
281
    public static void deleteLargeObject(Connection conn, long oid)
282
            throws SQLException {
283
        conn.setAutoCommit(false);
284

285
        try {
286
            PGConnection pgConn = conn.unwrap(PGConnection.class);
287
            LargeObjectManager lobj = pgConn.getLargeObjectAPI();
288

289
            // Delete
290
            lobj.delete(oid);
291

292
            conn.commit();
293

294
        } catch (SQLException e) {
295
            conn.rollback();
296
            throw e;
297
        } finally {
298
            conn.setAutoCommit(true);
299
        }
300
    }
301
}
302
```
303

304
### LargeObject
305

306
Handle for reading and writing large object data.
307

308
```java { .api }
309
package org.postgresql.largeobject;
310

311
import java.io.InputStream;
312
import java.io.OutputStream;
313
import java.sql.SQLException;
314

315
/**
316
 * Represents an open large object for I/O operations.
317
 * Must be closed when done.
318
 */
319
public class LargeObject {
320
    /**
321
     * Seek reference: Absolute position from start of large object.
322
     */
323
    public static final int SEEK_SET = 0;
324

325
    /**
326
     * Seek reference: Relative to current position.
327
     */
328
    public static final int SEEK_CUR = 1;
329

330
    /**
331
     * Seek reference: Relative to end of large object.
332
     */
333
    public static final int SEEK_END = 2;
334

335
    /**
336
     * Reads data from the large object into a buffer.
337
     *
338
     * @param buf Buffer to receive data
339
     * @param off Offset in buffer
340
     * @param len Maximum number of bytes to read
341
     * @return Number of bytes actually read, or -1 if at end
342
     * @throws SQLException if read fails
343
     */
344
    public int read(byte[] buf, int off, int len) throws SQLException;
345

346
    /**
347
     * Writes data to the large object.
348
     *
349
     * @param buf Buffer containing data to write
350
     * @throws SQLException if write fails
351
     */
352
    public void write(byte[] buf) throws SQLException;
353

354
    /**
355
     * Writes partial buffer to the large object.
356
     *
357
     * @param buf Buffer containing data
358
     * @param off Offset in buffer
359
     * @param len Number of bytes to write
360
     * @throws SQLException if write fails
361
     */
362
    public void write(byte[] buf, int off, int len) throws SQLException;
363

364
    /**
365
     * Deprecated: Use seek64(long, int) instead.
366
     * Seeks to a position in the large object.
367
     *
368
     * @param pos Position to seek to
369
     * @throws SQLException if seek fails
370
     * @deprecated Limited to 2GB, use {@link #seek64(long, int)} instead
371
     */
372
    @Deprecated
373
    public void seek(int pos) throws SQLException;
374

375
    /**
376
     * Deprecated: Use seek64(long, int) instead.
377
     * Seeks with reference point.
378
     *
379
     * @param pos Position relative to reference
380
     * @param ref Reference point (SEEK_SET=0, SEEK_CUR=1, SEEK_END=2)
381
     * @throws SQLException if seek fails
382
     * @deprecated Limited to 2GB, use {@link #seek64(long, int)} instead
383
     */
384
    @Deprecated
385
    public void seek(int pos, int ref) throws SQLException;
386

387
    /**
388
     * Seeks to a 64-bit position in the large object.
389
     * Supports large objects up to 4TB.
390
     *
391
     * @param pos Position to seek to (or offset if using reference)
392
     * @param ref Reference point:
393
     *            0 (SEEK_SET) = absolute position from start
394
     *            1 (SEEK_CUR) = relative to current position
395
     *            2 (SEEK_END) = relative to end
396
     * @throws SQLException if seek fails
397
     */
398
    public void seek64(long pos, int ref) throws SQLException;
399

400
    /**
401
     * Deprecated: Use tell64() instead.
402
     * Returns the current position in the large object.
403
     *
404
     * @return Current position
405
     * @throws SQLException if position cannot be determined
406
     * @deprecated Limited to 2GB, use {@link #tell64()} instead
407
     */
408
    @Deprecated
409
    public int tell() throws SQLException;
410

411
    /**
412
     * Returns the current 64-bit position in the large object.
413
     *
414
     * @return Current position
415
     * @throws SQLException if position cannot be determined
416
     */
417
    public long tell64() throws SQLException;
418

419
    /**
420
     * Deprecated: Use size64() instead.
421
     * Returns the size of the large object.
422
     *
423
     * @return Size in bytes
424
     * @throws SQLException if size cannot be determined
425
     * @deprecated Limited to 2GB, use {@link #size64()} instead
426
     */
427
    @Deprecated
428
    public int size() throws SQLException;
429

430
    /**
431
     * Returns the 64-bit size of the large object.
432
     *
433
     * @return Size in bytes
434
     * @throws SQLException if size cannot be determined
435
     */
436
    public long size64() throws SQLException;
437

438
    /**
439
     * Deprecated: Use truncate64(long) instead.
440
     * Truncates the large object to specified length.
441
     *
442
     * @param len New length
443
     * @throws SQLException if truncation fails
444
     * @deprecated Limited to 2GB, use {@link #truncate64(long)} instead
445
     */
446
    @Deprecated
447
    public void truncate(int len) throws SQLException;
448

449
    /**
450
     * Truncates the large object to specified 64-bit length.
451
     *
452
     * @param len New length in bytes
453
     * @throws SQLException if truncation fails
454
     */
455
    public void truncate64(long len) throws SQLException;
456

457
    /**
458
     * Returns an InputStream for reading from the large object.
459
     * Allows using standard Java I/O operations.
460
     *
461
     * @return InputStream for reading
462
     * @throws SQLException if stream cannot be created
463
     */
464
    public InputStream getInputStream() throws SQLException;
465

466
    /**
467
     * Returns an InputStream for reading from the large object with a limit.
468
     * The stream will return EOF after the specified number of bytes.
469
     *
470
     * @param limit Maximum number of bytes to read from the stream
471
     * @return InputStream for reading (limited to specified bytes)
472
     * @throws SQLException if stream cannot be created
473
     */
474
    public InputStream getInputStream(long limit) throws SQLException;
475

476
    /**
477
     * Returns an InputStream for reading from the large object with custom buffer size and limit.
478
     * The stream will return EOF after the specified number of bytes.
479
     *
480
     * @param bufferSize Size of the internal buffer for the stream
481
     * @param limit Maximum number of bytes to read from the stream
482
     * @return InputStream for reading (limited to specified bytes)
483
     * @throws SQLException if stream cannot be created
484
     */
485
    public InputStream getInputStream(int bufferSize, long limit) throws SQLException;
486

487
    /**
488
     * Returns an OutputStream for writing to the large object.
489
     * Allows using standard Java I/O operations.
490
     *
491
     * @return OutputStream for writing
492
     * @throws SQLException if stream cannot be created
493
     */
494
    public OutputStream getOutputStream() throws SQLException;
495

496
    /**
497
     * Closes the large object handle.
498
     * Must be called when done with the large object.
499
     *
500
     * @throws SQLException if close fails
501
     */
502
    public void close() throws SQLException;
503

504
    /**
505
     * Creates a copy of this large object handle.
506
     * The copy shares the same file descriptor but has an independent position.
507
     *
508
     * @return A new LargeObject handle pointing to the same large object
509
     * @throws SQLException if copy fails
510
     */
511
    public LargeObject copy() throws SQLException;
512

513
    /**
514
     * Returns the OID of this large object.
515
     *
516
     * @return OID
517
     */
518
    public long getLongOID();
519

520
    /**
521
     * Deprecated: Use getLongOID() instead.
522
     * Returns the OID as int.
523
     *
524
     * @return OID
525
     * @deprecated Use {@link #getLongOID()} instead
526
     */
527
    @Deprecated
528
    public int getOID();
529
}
530
```
531

532
**Usage Examples:**
533

534
```java
535
import org.postgresql.largeobject.LargeObject;
536
import java.io.InputStream;
537
import java.io.OutputStream;
538
import java.io.FileInputStream;
539
import java.io.FileOutputStream;
540
import java.sql.SQLException;
541
import java.io.IOException;
542

543
// Example 1: Streaming read with InputStream
544
public class StreamingReadExample {
545
    public static void readToFile(Connection conn, long oid, String filename)
546
            throws SQLException, IOException {
547
        conn.setAutoCommit(false);
548

549
        try {
550
            PGConnection pgConn = conn.unwrap(PGConnection.class);
551
            LargeObjectManager lobj = pgConn.getLargeObjectAPI();
552

553
            LargeObject obj = lobj.open(oid, LargeObjectManager.READ);
554

555
            // Use InputStream for convenient reading
556
            try (InputStream in = obj.getInputStream();
557
                 FileOutputStream out = new FileOutputStream(filename)) {
558

559
                byte[] buffer = new byte[8192];
560
                int bytesRead;
561
                while ((bytesRead = in.read(buffer)) != -1) {
562
                    out.write(buffer, 0, bytesRead);
563
                }
564
            }
565

566
            obj.close();
567
            conn.commit();
568

569
        } catch (SQLException | IOException e) {
570
            conn.rollback();
571
            throw e;
572
        } finally {
573
            conn.setAutoCommit(true);
574
        }
575
    }
576
}
577

578
// Example 2: Streaming write with OutputStream
579
public class StreamingWriteExample {
580
    public static long writeFromFile(Connection conn, String filename)
581
            throws SQLException, IOException {
582
        conn.setAutoCommit(false);
583

584
        try {
585
            PGConnection pgConn = conn.unwrap(PGConnection.class);
586
            LargeObjectManager lobj = pgConn.getLargeObjectAPI();
587

588
            // Create large object
589
            long oid = lobj.createLO(LargeObjectManager.READWRITE);
590
            LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);
591

592
            // Use OutputStream for convenient writing
593
            try (OutputStream out = obj.getOutputStream();
594
                 FileInputStream in = new FileInputStream(filename)) {
595

596
                byte[] buffer = new byte[8192];
597
                int bytesRead;
598
                while ((bytesRead = in.read(buffer)) != -1) {
599
                    out.write(buffer, 0, bytesRead);
600
                }
601
            }
602

603
            obj.close();
604
            conn.commit();
605

606
            return oid;
607

608
        } catch (SQLException | IOException e) {
609
            conn.rollback();
610
            throw e;
611
        } finally {
612
            conn.setAutoCommit(true);
613
        }
614
    }
615
}
616

617
// Example 3: Random access operations
618
public class RandomAccessExample {
619
    public static void modifyPartial(Connection conn, long oid,
620
                                     long offset, byte[] data)
621
            throws SQLException {
622
        conn.setAutoCommit(false);
623

624
        try {
625
            PGConnection pgConn = conn.unwrap(PGConnection.class);
626
            LargeObjectManager lobj = pgConn.getLargeObjectAPI();
627

628
            LargeObject obj = lobj.open(oid, LargeObjectManager.READWRITE);
629

630
            // Seek to offset
631
            obj.seek64(offset, 0); // 0 = SEEK_SET (absolute position)
632

633
            // Write data at that position
634
            obj.write(data);
635

636
            // Get final position and size
637
            long position = obj.tell64();
638
            long size = obj.size64();
639
            System.out.println("Position: " + position + ", Size: " + size);
640

641
            obj.close();
642
            conn.commit();
643

644
        } catch (SQLException e) {
645
            conn.rollback();
646
            throw e;
647
        } finally {
648
            conn.setAutoCommit(true);
649
        }
650
    }
651

652
    public static byte[] readPartial(Connection conn, long oid,
653
                                     long offset, int length)
654
            throws SQLException {
655
        conn.setAutoCommit(false);
656

657
        try {
658
            PGConnection pgConn = conn.unwrap(PGConnection.class);
659
            LargeObjectManager lobj = pgConn.getLargeObjectAPI();
660

661
            LargeObject obj = lobj.open(oid, LargeObjectManager.READ);
662

663
            // Seek to offset
664
            obj.seek64(offset, 0);
665

666
            // Read specific amount
667
            byte[] data = new byte[length];
668
            int bytesRead = obj.read(data, 0, length);
669

670
            obj.close();
671
            conn.commit();
672

673
            // Return only bytes actually read
674
            if (bytesRead < length) {
675
                byte[] result = new byte[bytesRead];
676
                System.arraycopy(data, 0, result, 0, bytesRead);
677
                return result;
678
            }
679
            return data;
680

681
        } catch (SQLException e) {
682
            conn.rollback();
683
            throw e;
684
        } finally {
685
            conn.setAutoCommit(true);
686
        }
687
    }
688
}
689

690
// Example 4: Truncate large object
691
public class TruncateExample {
692
    public static void truncate(Connection conn, long oid, long newSize)
693
            throws SQLException {
694
        conn.setAutoCommit(false);
695

696
        try {
697
            PGConnection pgConn = conn.unwrap(PGConnection.class);
698
            LargeObjectManager lobj = pgConn.getLargeObjectAPI();
699

700
            LargeObject obj = lobj.open(oid, LargeObjectManager.READWRITE);
701

702
            // Truncate to new size
703
            obj.truncate64(newSize);
704

705
            obj.close();
706
            conn.commit();
707

708
        } catch (SQLException e) {
709
            conn.rollback();
710
            throw e;
711
        } finally {
712
            conn.setAutoCommit(true);
713
        }
714
    }
715
}
716
```
717

718
### Integration with Tables
719

720
Storing large object OIDs in tables.
721

722
**Usage Examples:**
723

724
```java
725
import java.sql.PreparedStatement;
726
import java.sql.ResultSet;
727
import java.sql.SQLException;
728

729
// Example 1: Store OID in table
730
public class StoreLargeObjectExample {
731
    public static void storeDocument(Connection conn, String title,
732
                                     String filename) throws SQLException, IOException {
733
        conn.setAutoCommit(false);
734

735
        try {
736
            // Create large object
737
            PGConnection pgConn = conn.unwrap(PGConnection.class);
738
            LargeObjectManager lobj = pgConn.getLargeObjectAPI();
739

740
            long oid = lobj.createLO(LargeObjectManager.READWRITE);
741
            LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);
742

743
            // Write file to large object
744
            try (OutputStream out = obj.getOutputStream();
745
                 FileInputStream in = new FileInputStream(filename)) {
746
                byte[] buffer = new byte[8192];
747
                int bytesRead;
748
                while ((bytesRead = in.read(buffer)) != -1) {
749
                    out.write(buffer, 0, bytesRead);
750
                }
751
            }
752
            obj.close();
753

754
            // Store OID in table
755
            try (PreparedStatement pstmt = conn.prepareStatement(
756
                    "INSERT INTO documents (title, content_oid) VALUES (?, ?)")) {
757
                pstmt.setString(1, title);
758
                pstmt.setLong(2, oid);
759
                pstmt.executeUpdate();
760
            }
761

762
            conn.commit();
763

764
        } catch (SQLException | IOException e) {
765
            conn.rollback();
766
            throw e;
767
        } finally {
768
            conn.setAutoCommit(true);
769
        }
770
    }
771
}
772

773
// Example 2: Retrieve OID from table and read
774
public class RetrieveLargeObjectExample {
775
    public static byte[] getDocument(Connection conn, int documentId)
776
            throws SQLException {
777
        conn.setAutoCommit(false);
778

779
        try {
780
            // Get OID from table
781
            long oid;
782
            try (PreparedStatement pstmt = conn.prepareStatement(
783
                    "SELECT content_oid FROM documents WHERE id = ?")) {
784
                pstmt.setInt(1, documentId);
785

786
                try (ResultSet rs = pstmt.executeQuery()) {
787
                    if (!rs.next()) {
788
                        throw new SQLException("Document not found");
789
                    }
790
                    oid = rs.getLong("content_oid");
791
                }
792
            }
793

794
            // Read large object
795
            PGConnection pgConn = conn.unwrap(PGConnection.class);
796
            LargeObjectManager lobj = pgConn.getLargeObjectAPI();
797

798
            LargeObject obj = lobj.open(oid, LargeObjectManager.READ);
799
            long size = obj.size64();
800
            byte[] data = new byte[(int) size];
801
            obj.read(data, 0, data.length);
802
            obj.close();
803

804
            conn.commit();
805
            return data;
806

807
        } catch (SQLException e) {
808
            conn.rollback();
809
            throw e;
810
        } finally {
811
            conn.setAutoCommit(true);
812
        }
813
    }
814
}
815

816
// Example 3: Delete document and large object
817
public class DeleteLargeObjectExample {
818
    public static void deleteDocument(Connection conn, int documentId)
819
            throws SQLException {
820
        conn.setAutoCommit(false);
821

822
        try {
823
            // Get OID
824
            long oid;
825
            try (PreparedStatement pstmt = conn.prepareStatement(
826
                    "SELECT content_oid FROM documents WHERE id = ?")) {
827
                pstmt.setInt(1, documentId);
828

829
                try (ResultSet rs = pstmt.executeQuery()) {
830
                    if (!rs.next()) {
831
                        throw new SQLException("Document not found");
832
                    }
833
                    oid = rs.getLong("content_oid");
834
                }
835
            }
836

837
            // Delete from table
838
            try (PreparedStatement pstmt = conn.prepareStatement(
839
                    "DELETE FROM documents WHERE id = ?")) {
840
                pstmt.setInt(1, documentId);
841
                pstmt.executeUpdate();
842
            }
843

844
            // Delete large object
845
            PGConnection pgConn = conn.unwrap(PGConnection.class);
846
            LargeObjectManager lobj = pgConn.getLargeObjectAPI();
847
            lobj.delete(oid);
848

849
            conn.commit();
850

851
        } catch (SQLException e) {
852
            conn.rollback();
853
            throw e;
854
        } finally {
855
            conn.setAutoCommit(true);
856
        }
857
    }
858
}
859
```
860

861
### Large Objects vs. Bytea
862

863
**Comparison:**
864

865
| Feature | Large Objects | Bytea Column |
866
|---------|--------------|-------------|
867
| Maximum size | 4 TB | 1 GB |
868
| Storage | Outside tables (pg_largeobject) | Inside tables |
869
| Random access | Yes (seek/tell) | No (must read entire value) |
870
| Streaming | Yes (InputStream/OutputStream) | No |
871
| Transaction safety | Must be in transaction | Normal ACID |
872
| Vacuum | Requires separate cleanup | Automatic |
873
| Performance | Better for very large data | Better for small/medium data |
874
875
**Best Practices:**
876

877
1. **Use Large Objects for:**
878
   - Files larger than 1 MB
879
   - Data requiring random access
880
   - Streaming uploads/downloads
881

882
2. **Use Bytea for:**
883
   - Small binary data (< 1 MB)
884
   - Data that fits in memory
885
   - Simpler transaction handling
886

887
3. **Always use transactions**
888
   ```java
889
   conn.setAutoCommit(false);
890
   // Large object operations
891
   conn.commit();
892
   ```
893

894
4. **Clean up orphaned large objects**
895
   ```sql
896
   -- Find orphaned large objects
897
   SELECT oid FROM pg_largeobject_metadata
898
   WHERE oid NOT IN (SELECT content_oid FROM documents);
899
   ```
900

901
5. **Consider using triggers for cleanup**
902
   ```sql
903
   CREATE TRIGGER delete_large_object_trigger
904
   AFTER DELETE ON documents
905
   FOR EACH ROW
906
   EXECUTE FUNCTION lo_manage(content_oid);
907
   ```
908