pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedInefficient escape codes.
9+ messages / 5 participants
[nested] [flat]
* Inefficient escape codes.
@ 2005-10-18 18:07 Rodrigo Madera <rodrigo.madera@gmail.com>
2005-10-18 19:03 ` Re: Inefficient escape codes. Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 9+ messages in thread
From: Rodrigo Madera @ 2005-10-18 18:07 UTC (permalink / raw)
To: pgsql-performance
Hello there,
This is my first post in the list. I have a deep low-level background on
computer programming, but I am totally newbie to sql databases. I am using
postgres because of its commercial license.
My problem is with storing large values. I have a database that stores large
ammounts of data (each row consisting of up to 5MB). After carefully reading
the Postgres 8.0 manual (the version I'm using), I was told that the best
option was to create a bytea field.
Large objects are out of the line here since we have lots of tables.
As I understand it, the client needs to put the data into the server using a
textual-based command. This makes the 5MB data grow up-to 5x, making it 25MB
in the worst case. (Example: 0x01 -> \\001).
My question is:
1) Is there any way for me to send the binary field directly without needing
escape codes?
2) Will this mean that the client actually wastes my network bandwidth
converting binary data to text? Or does the client transparently manage
this?
Thanks for any light on the subject,
Rodrigo
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Inefficient escape codes.
2005-10-18 18:07 Inefficient escape codes. Rodrigo Madera <rodrigo.madera@gmail.com>
@ 2005-10-18 19:03 ` Michael Fuhr <mike@fuhr.org>
2005-10-18 20:47 ` Re: Inefficient escape codes. Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 9+ messages in thread
From: Michael Fuhr @ 2005-10-18 19:03 UTC (permalink / raw)
To: Rodrigo Madera <rodrigo.madera@gmail.com>; +Cc: pgsql-performance
On Tue, Oct 18, 2005 at 06:07:12PM +0000, Rodrigo Madera wrote:
> 1) Is there any way for me to send the binary field directly without needing
> escape codes?
In 7.4 and later the client/server protocol supports binary data
transfer. If you're programming with libpq you can use PQexecParams()
to send and/or retrieve values in binary instead of text.
http://www.postgresql.org/docs/8.0/interactive/libpq-exec.html#LIBPQ-EXEC-MAIN
APIs built on top of libpq or that implement the protcol themselves
might provide hooks to this capability; check your documentation.
What language and API are you using?
See also COPY BINARY:
http://www.postgresql.org/docs/8.0/interactive/sql-copy.html
> 2) Will this mean that the client actually wastes my network bandwidth
> converting binary data to text? Or does the client transparently manage
> this?
Binary transfer sends data in binary, not by automatically converting
to and from text.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Inefficient escape codes.
2005-10-18 18:07 Inefficient escape codes. Rodrigo Madera <rodrigo.madera@gmail.com>
2005-10-18 19:03 ` Re: Inefficient escape codes. Michael Fuhr <mike@fuhr.org>
@ 2005-10-18 20:47 ` Michael Fuhr <mike@fuhr.org>
2005-10-19 07:19 ` Re: Inefficient escape codes. Peter Childs <peterachilds@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Michael Fuhr @ 2005-10-18 20:47 UTC (permalink / raw)
To: Rodrigo Madera <rodrigo.madera@gmail.com>; +Cc: pgsql-performance
[Please copy the mailing list on replies so others can participate
in and learn from the discussion.]
On Tue, Oct 18, 2005 at 07:09:08PM +0000, Rodrigo Madera wrote:
> > What language and API are you using?
>
> I'm using libpqxx. A nice STL-style library for C++ (I am 101% C++).
I've only dabbled with libpqxx; I don't know if or how you can make
it send data in binary instead of text. See the documentation or
ask in a mailing list like libpqxx-general or pgsql-interfaces.
> > Binary transfer sends data in binary, not by automatically converting
> > to and from text.
>
> Uh, I'm sorry I didn't get that... If I send: insert into foo
> values('\\001\\002') will libpq send 0x01, 0x02 or "\\\\001\\\\002"??
If you do it that way libpq will send the string as text with escape
sequences; you can use a sniffer like tcpdump or ethereal to see this
for yourself. To send the data in binary you'd call PQexecParams()
with a query like "INSERT INTO foo VALUES ($1)". The $1 is a
placeholder; the other arguments to PQexecParams() provide the data
itself, the data type and length, and specify whether the data is in
text format or binary. See the libpq documentation for details.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Inefficient escape codes.
2005-10-18 18:07 Inefficient escape codes. Rodrigo Madera <rodrigo.madera@gmail.com>
2005-10-18 19:03 ` Re: Inefficient escape codes. Michael Fuhr <mike@fuhr.org>
2005-10-18 20:47 ` Re: Inefficient escape codes. Michael Fuhr <mike@fuhr.org>
@ 2005-10-19 07:19 ` Peter Childs <peterachilds@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Peter Childs @ 2005-10-19 07:19 UTC (permalink / raw)
To: pgsql-performance
On 18/10/05, Michael Fuhr <mike@fuhr.org> wrote:
> [Please copy the mailing list on replies so others can participate
> in and learn from the discussion.]
>
> On Tue, Oct 18, 2005 at 07:09:08PM +0000, Rodrigo Madera wrote:
> > > What language and API are you using?
> >
> > I'm using libpqxx. A nice STL-style library for C++ (I am 101% C++).
>
> I've only dabbled with libpqxx; I don't know if or how you can make
> it send data in binary instead of text. See the documentation or
> ask in a mailing list like libpqxx-general or pgsql-interfaces.
>
> > > Binary transfer sends data in binary, not by automatically converting
> > > to and from text.
> >
> > Uh, I'm sorry I didn't get that... If I send: insert into foo
> > values('\\001\\002') will libpq send 0x01, 0x02 or "\\\\001\\\\002"??
>
> If you do it that way libpq will send the string as text with escape
> sequences; you can use a sniffer like tcpdump or ethereal to see this
> for yourself. To send the data in binary you'd call PQexecParams()
> with a query like "INSERT INTO foo VALUES ($1)". The $1 is a
> placeholder; the other arguments to PQexecParams() provide the data
> itself, the data type and length, and specify whether the data is in
> text format or binary. See the libpq documentation for details.
>
You could base64 encode your data admitiaddly increasing it by 1/3 but
it does at least convert it to text which means that its more
unserstandable. base64 is also pritty standard being whats used in
EMails for mime attachments.
Peter
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Inefficient escape codes.
@ 2005-10-19 14:54 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 9+ messages in thread
From: Merlin Moncure @ 2005-10-19 14:54 UTC (permalink / raw)
To: Rodrigo Madera <rodrigo.madera@gmail.com>; +Cc: pgsql-performance
Rodrigo wrote:
$$
As I understand it, the client needs to put the data into the server
using a textual-based command. This makes the 5MB data grow up-to 5x,
making it 25MB in the worst case. (Example: 0x01 -> \\001).
My question is:
1) Is there any way for me to send the binary field directly without
needing escape codes?
2) Will this mean that the client actually wastes my network bandwidth
converting binary data to text? Or does the client transparently manage
this?
$$ [snip]
I think the fastest, most efficient binary transfer of data to
PostgreSQL via C++ is a STL wrapper to libpq. Previously I would not
have recommended libqpxx for this purpose although this may have changed
with the later releases. As others have commented you most certainly
want to do this with the ExecParams/ExecPrepared interface. If you want
to exclusively use libqxx then you need to find out if it exposes/wraps
this function (IIRC libpqxx build on top of libpq).
You can of course 'roll your own' libpq wrapper via STL pretty easily.
For example, here is how I am making my SQL calls from my COBOL apps:
typedef vector<string> stmt_param_strings;
typedef vector<int> stmt_param_lengths;
typedef vector<const char*> stmt_param_values;
typedef vector<int> stmt_param_formats;
[...]
res = PQexecPrepared( _connection,
stmt.c_str(),
num_params,
¶m_values[0],
¶m_lengths[0],
¶m_formats[0],
result_format);
Executing data this way is a direct data injection to/from the server,
no parsing/unparsing, no plan generating, etc. Also STL vectors play
very nice with the libpq interface because it takes unterminated stings.
Merlin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Inefficient escape codes.
@ 2005-10-20 07:15 Nörder-Tuitje, Marcus <noerder-tuitje@technology.de>
2005-10-21 16:59 ` Re: Inefficient escape codes. Rodrigo Madera <rodrigo.madera@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Nörder-Tuitje, Marcus @ 2005-10-20 07:15 UTC (permalink / raw)
To: pgsql-performance
I guess, You should check, if a blob field and large object access is suitable for you - no escaping etc, just raw binary large objects.
AFAIK, PQExecParams is not the right solution for You. Refer the "Large object" section:
"28.3.5. Writing Data to a Large Object
The function
int lo_write(PGconn *conn, int fd, const char *buf, size_t len);writes len bytes from buf to large object descriptor fd. The fd argument must have been returned by a previous lo_open. The number of bytes actually written is returned. In the event of an error, the return value is negative."
regards,
Narcus
-----Ursprüngliche Nachricht-----
Von: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]Im Auftrag von Michael
Fuhr
Gesendet: Dienstag, 18. Oktober 2005 22:47
An: Rodrigo Madera
Cc: pgsql-performance@postgresql.org
Betreff: Re: [PERFORM] Inefficient escape codes.
[Please copy the mailing list on replies so others can participate
in and learn from the discussion.]
On Tue, Oct 18, 2005 at 07:09:08PM +0000, Rodrigo Madera wrote:
> > What language and API are you using?
>
> I'm using libpqxx. A nice STL-style library for C++ (I am 101% C++).
I've only dabbled with libpqxx; I don't know if or how you can make
it send data in binary instead of text. See the documentation or
ask in a mailing list like libpqxx-general or pgsql-interfaces.
> > Binary transfer sends data in binary, not by automatically converting
> > to and from text.
>
> Uh, I'm sorry I didn't get that... If I send: insert into foo
> values('\\001\\002') will libpq send 0x01, 0x02 or "\\\\001\\\\002"??
If you do it that way libpq will send the string as text with escape
sequences; you can use a sniffer like tcpdump or ethereal to see this
for yourself. To send the data in binary you'd call PQexecParams()
with a query like "INSERT INTO foo VALUES ($1)". The $1 is a
placeholder; the other arguments to PQexecParams() provide the data
itself, the data type and length, and specify whether the data is in
text format or binary. See the libpq documentation for details.
--
Michael Fuhr
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Inefficient escape codes.
2005-10-20 07:15 Re: Inefficient escape codes. Nörder-Tuitje, Marcus <noerder-tuitje@technology.de>
@ 2005-10-21 16:59 ` Rodrigo Madera <rodrigo.madera@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Rodrigo Madera @ 2005-10-21 16:59 UTC (permalink / raw)
To: Nörder-Tuitje, Marcus <noerder-tuitje@technology.de>; +Cc: pgsql-performance
> I guess, You should check, if a blob field and large object access is
> suitable for you - no escaping etc, just raw binary large objects.
>
> AFAIK, PQExecParams is not the right solution for You. Refer the "Large
> object" section:
>
> "28.3.5. Writing Data to a Large Object
> The function
> int lo_write(PGconn *conn, int fd, const char *buf, size_t len);writes len
> bytes from buf to large object descriptor fd. The fd argument must have been
> returned by a previous lo_open. The number of bytes actually written is
> returned. In the event of an error, the return value is negative."
Well, I read that large objects are kept in only ONE table. No matter what,
only the LOID would be kept. I can't affor that since I hav lots of tables
(using the image-album-app analogy, imagine that we have pictures from
several cities, each one corresponding to a city, like Memphis_Photos,
Chicago_Photos, etc.).
This is one major drawback, isn't it?
Rodrigo
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Inefficient escape codes.
@ 2005-10-24 19:11 Rodrigo Madera <rodrigo.madera@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Rodrigo Madera @ 2005-10-24 19:11 UTC (permalink / raw)
To: pgsql-performance
Now this interests me a lot.
Please clarify this:
I have 5000 tables, one for each city:
City1_Photos, City2_Photos, ... City5000_Photos.
Each of these tables are: CREATE TABLE CityN_Photos (location text, lo_id
largeobectypeiforgot)
So, what's the limit for these large objects? I heard I could only have 4
billion records for the whole database (not for each table). Is this true?
If this isn't true, then would postgres manage to create all the large
objects I ask him to?
Also, this would be a performance penalty, wouldn't it?
Much thanks for the knowledge shared,
Rodrigo
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Inefficient escape codes.
@ 2005-10-25 16:03 Rodrigo Madera <rodrigo.madera@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Rodrigo Madera @ 2005-10-25 16:03 UTC (permalink / raw)
To: Nörder-Tuitje, Marcus <noerder-tuitje@technology.de>; +Cc: pgsql-performance
Ok, thanks for the limits info, but I have that in the manual. Thanks.
But what I really want to know is this:
1) All large objects of all tables inside one DATABASE is kept on only one
table. True or false?
Thanks =o)
Rodrigo
On 10/25/05, Nörder-Tuitje, Marcus <noerder-tuitje@technology.de> wrote:
>
> oh, btw, no harm, but :
> having 5000 tables only to gain access via city name is a major design
> flaw.
> you might consider putting all into one table working with a distributed
> index over yer table (city, loc_texdt, blobfield); creating a partitioned
> index over city.
> best regards
>
> -----Ursprüngliche Nachricht-----
> *Von:* pgsql-performance-owner@postgresql.org [mailto:
> pgsql-performance-owner@postgresql.org]*Im Auftrag von *Rodrigo Madera
> *Gesendet:* Montag, 24. Oktober 2005 21:12
> *An:* pgsql-performance@postgresql.org
> *Betreff:* Re: [PERFORM] Inefficient escape codes.
>
> Now this interests me a lot.
>
> Please clarify this:
>
> I have 5000 tables, one for each city:
>
> City1_Photos, City2_Photos, ... City5000_Photos.
>
> Each of these tables are: CREATE TABLE CityN_Photos (location text, lo_id
> largeobectypeiforgot)
>
> So, what's the limit for these large objects? I heard I could only have 4
> billion records for the whole database (not for each table). Is this true?
> If this isn't true, then would postgres manage to create all the large
> objects I ask him to?
>
> Also, this would be a performance penalty, wouldn't it?
>
> Much thanks for the knowledge shared,
> Rodrigo
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2005-10-25 16:03 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-18 18:07 Inefficient escape codes. Rodrigo Madera <rodrigo.madera@gmail.com>
2005-10-18 19:03 ` Michael Fuhr <mike@fuhr.org>
2005-10-18 20:47 ` Michael Fuhr <mike@fuhr.org>
2005-10-19 07:19 ` Peter Childs <peterachilds@gmail.com>
2005-10-19 14:54 Re: Inefficient escape codes. Merlin Moncure <merlin.moncure@rcsonline.com>
2005-10-20 07:15 Re: Inefficient escape codes. Nörder-Tuitje, Marcus <noerder-tuitje@technology.de>
2005-10-21 16:59 ` Rodrigo Madera <rodrigo.madera@gmail.com>
2005-10-24 19:11 Re: Inefficient escape codes. Rodrigo Madera <rodrigo.madera@gmail.com>
2005-10-25 16:03 Re: Inefficient escape codes. Rodrigo Madera <rodrigo.madera@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox