<html style="direction: ltr;">
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
<style>body p { margin-bottom: 10pt; margin-top: 0pt; } </style>
</head>
<body style="direction: ltr;"
bidimailui-detected-decoding-type="UTF-8" bgcolor="#FFFFFF"
text="#000000">
Hi again, everyone. I'm replying to my own posting, to add some
information: I decided to do some of my own benchmarking. And if my
benchmarks are at all accurate, then I'm left wondering why people
use large objects at all, given their clunky API and their extremely
slow speed. I'm posting my benchmarks as a sanity test, because I'm
blown away by the results.<br>
<br>
I basically tried three different scenarios, each with 1,000 and
10,000 records. In each scenario, there was a table named
MasterTable that contained a SERIAL "id" column and a "one_value"
integer column, containing a number from generate_series, and a
second table named SecondaryTable containing its own SERIAL "id"
column, a "one_value" value (from generate_series, identical to the
"id" column, and a "master_value" column that's a foreign key back
to the main table. That is, here's the definition of the tables in
the 10,000-record benchmark:<br>
<br>
CREATE TABLE MasterTable (<br>
id SERIAL NOT NULL,<br>
one_value INTEGER NOT NULL,<br>
<br>
PRIMARY KEY(id)<br>
);<br>
INSERT INTO MasterTable (one_value) values
(generate_series(1,10000));<br>
<br>
CREATE TABLE SecondaryTable (<br>
id SERIAL NOT NULL,<br>
one_value INTEGER NOT NULL,<br>
master_value INTEGER NOT NULL REFERENCES MasterTable ON
DELETE CASCADE<br>
<br>
PRIMARY KEY(id)<br>
);<br>
<br>
INSERT INTO SecondaryTable (master_value, one_value) <br>
(SELECT s.a, s.a FROM generate_series(1,10000) AS s(a));<br>
<br>
I also had two other versions of SecondaryTable: In one scenario,
there is a my_blob column, of type BYTEA, containing 5 million 'x"
characters. A final version had a 5-million 'x' character document
loaded into a large object in SecionaryTable.<br>
<br>
The idea was simple: I wanted to see how much faster or slower it
was to delete (not truncate) all of the records in MasterTable,
given these different data types. Would bytea be significantly
faster than large objects? How would the cascading delete affect
things? And how long does it take to pg_dump with large objects
around?<br>
<br>
Here are the results, which were pretty dramatic. Basically,
pg_dump seems to always be far, far slower than BYTEA columns.
Again, I'm wondering whether I'm doing something wrong here, or if
this explains why in my many years of using PostgreSQL, I've neither
used nor been tempted to use large objects before.<br>
<br>
<p><tt>1.1 1,000 records <br>
==================<br>
<br>
Delete Dump <br>
---------------+---------+--------<br>
Empty content 0.172s 0.057s <br>
bytea 0.488s 0.066s <br>
large object 30.833s 9.275s <br>
<br>
<br>
1.2 10,000 records <br>
===================<br>
<br>
Delete Dump <br>
---------------+-----------+-----------<br>
Empty content 8.162s 0.064s <br>
bytea 1m0.417s 0.157s <br>
large object 4m44.501s 1m38.454s <br>
</tt><br>
</p>
Any ideas? If this is true, should we be warning people away from
large objects in the documentation, and toward bytea?<br>
<br>
Reuven<br>
</body>
</html>