Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 75DAE37BCCE for ; Sat, 11 Oct 2008 18:41:25 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 68508-06 for ; Sat, 11 Oct 2008 18:41:22 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from nathalia.gransy.com (nathalia.gransy.com [82.208.29.194]) by postgresql.org (Postfix) with ESMTP id 1E8FD37BC9D for ; Sat, 11 Oct 2008 18:41:21 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by nathalia.gransy.com (Postfix) with ESMTP id 5752C7AC067; Sat, 11 Oct 2008 23:41:19 +0200 (CEST) Received: from nathalia.gransy.com ([127.0.0.1]) by localhost (nathalia.gransy.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id CKt5U0iAS+Uq; Sat, 11 Oct 2008 23:41:19 +0200 (CEST) Received: from [192.168.1.1] (ip-89-102-139-23.karneval.cz [89.102.139.23]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by nathalia.gransy.com (Postfix) with ESMTP id 1403E7AC0BA; Sat, 11 Oct 2008 23:41:19 +0200 (CEST) Message-ID: <48F11D6C.1000209@fuzzy.cz> Date: Sat, 11 Oct 2008 23:41:00 +0200 From: Tomas Vondra User-Agent: Thunderbird 2.0.0.9 (X11/20080213) MIME-Version: 1.0 To: andrew@catalyst.net.nz CC: pgsql-php@postgresql.org Subject: Re: storing binary files / memory limit References: <48F0D3CD.9020306@fuzzy.cz> <1223756619.26952.7.camel@happy.mcmillan.net.nz> In-Reply-To: <1223756619.26952.7.camel@happy.mcmillan.net.nz> Content-Type: text/plain; charset=ISO-8859-2; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200810/10 X-Sequence-Number: 3907 > On Sat, 2008-10-11 at 18:26 +0200, Tomas Vondra wrote: >> Is there any other way to solve storing of large files in PostgreSQL? > > No, not until there are functions that let you fopen() on the bytea > column. :-( > Also, your "... || more_column" solution will generate large numbers of > dead rows and require frequent vacuuming. Yes, I'm aware of this and it's one more deficiency of the solution. But inserting the data is not so frequent (a few inserts a day), especially when compared to reading (several hundred SELECTs). >> - Optimization is a serious criterion, as is reliability. > > If you're using tables with very large columns, make sure you index on > every other column you're going to access it by. If PostgreSQL has to > resort to full-table scans on this table, and especially with a low > memory constraint, you could easily end up with it doing an on-disk sort > on a copy of the data. Well, the schema is quite well optimized I believe, so this shouldn't be a problem. All the necessary indexes are created etc. so the queries are executed quite fast (we have a comprehensive performance logging of queries, so I'm quite sure about this). But I'm not sure what you mean by 'low memory contraint' - the memory limit I've been talking about is purely PHP feature, so it's related to inserting / reading and escaping / unescaping data. Anyway 99% of queries returning multiple rows do not return BYTEA columns - these columns are references in queries returning single row, so there is no problem with sorting / memory occupied by the postmaster process. > If you *have* to store it in a table column (and it really isn't the > most efficient way of doing it) then create a separate table for it > which is just SERIAL + data. I *want* to store it in a table column, because I'm not able to come up with a better solution. As I understand it, creating a separate table to store the binary data is almost the same as using plain bytea columns. We don't have problems with performance (thanks to creating proper indexes and TOAST architecture), and it does not solve the problem I've described in my original post (hitting the PHP memory limit). OK, it would save a little bit of space when using the 'append' strategy described in my previous post (data = data || new_data), but table usually consists of a small amount of metadata plus large amount of binary data. So the amount of space wasted because of storing metadata in dead rows is negligible compared to space wasted because of dead rows and bytea columns. For example, one of our tables is used to store documents, so it has about this structure Documents ( id SERIAL, created DATE, title VARCHAR(128), author VARCHAR(128), description TEXT, -- short (usually less than 2000 characters) data BYTEA ) The 'data' column may have even several megabytes, so the metadata occupies less than 1% of the row. If you know a better way to store binary data, please describe it here. The only other way I'm aware of is LOB - it solves the problem of inserting data (by streaming), but has other disadvantages (no referential integrity, etc.) But creating a separate table for the binary data looks interesting under one condition - the file will be stored splitted. This basically mimics the LOB storage (pg_largeobject table). It still does not have the support for streaming the data, but it solves the problem with PHP memory limit and does not create large amount of dead rows. regards Tomas