Re: Speed of lo_unlink vs. DELETE on BYTEA - Mailing list pgsql-general

From Reuven M. Lerner
Subject Re: Speed of lo_unlink vs. DELETE on BYTEA
Date
Msg-id 4E7EBF73.4050205@lerner.co.il
Whole thread Raw
In response to Speed of lo_unlink vs. DELETE on BYTEA  ("Reuven M. Lerner" <reuven@lerner.co.il>)
Responses Re: Speed of lo_unlink vs. DELETE on BYTEA  (Eduardo Morras <nec556@retena.com>)
Re: Speed of lo_unlink vs. DELETE on BYTEA  (Eduardo Morras <nec556@retena.com>)
List pgsql-general
body
      p { margin-bottom: 10pt; margin-top: 0pt; }
    body p { margin-bottom: 10pt; margin-top: 0pt; }

  <body style="direction: ltr;"
    bidimailui-detected-decoding-type="UTF-8" bgcolor="#FFFFFF"
    text="#000000">
    Hi, everyone.  Daniel Verite <a class="moz-txt-link-rfc2396E"
        href="mailto:daniel@manitou-mail.org"><daniel@manitou-mail.org>
      wrote:



      How much bytea are you dumping for it to take only 0.066s?
The fact that it takes about the same time than dumping the "empty content"
looks very suspicious.

On my desktop machine, if I create a table with 1000 blobs containing strings
of 5 million 'x', which is what I understood you basically did (perhaps I
misunderstood?), then it takes about 200s to dump it with pg_dump -Fc

    OK, this is an egg-on-my-face moment with my benchmarks: I added the
    pg_dump timing after the "delete" timing, and so I was actually
    dumping the database when it was empty! Not very effective, to say
    the least.

    I've updated my benchmark, and updated the results, as well:

    |                           | Delete    | Dump      | Database
      size | Dump size |
|---------------------------+-----------+-----------+---------------+-----------|
      | Empty content             | 0m0.151s  | 0m38.875s | 88
      kB         | 11K       |
      | bytea                     | 0m0.505s  | 1m59.565s | 57
      MB         | 4.7M      |
      | large object with rule    | 0m31.438s | 2m42.079s | 88
      kB         | 4.7M      |
      | large object with trigger | 0m28.612s | 2m17.156s | 88
      kB         | 4.7M      |



      ** 10,000 records

      |                           | Delete    | Dump       | Database
      size | Dump size |
|---------------------------+-----------+------------+---------------+-----------|
      | Empty content             | 0m7.436s  | 0m0.089s   | 680
      kB        | 66K       |
      | bytea                     | 1m5.771s  | 20m40.842s | 573
      MB        | 47M       |
      | large object with rule    | 5m26.254s | 21m7.695s  | 680
      kB        | 47M       |
      | large object with trigger | 5m13.718s | 20m56.195s | 680
      kB        | 47M       |



    It would thus appear that there's a slight edge for dumping
      bytea, but nothing super-amazing.  Deleting, however, is still
      much faster with bytea than large objects.


      I've put my benchmark code up on GitHub for people to run and play
      with, to see if they can reproduce my results:
    https://github.com/reuven/pg-delete-benchmarks



    Reuven

    --
Reuven M. Lerner -- Web development, consulting, and training
Mobile: +972-54-496-8405 * US phone: 847-230-9795
Skype/AIM: reuvenlerner

pgsql-general by date:

Previous
From: Uwe Schroeder
Date:
Subject: Re: (another ;-)) PostgreSQL-derived project ...
Next
From: Albretch Mueller
Date:
Subject: Re: (another ;-)) PostgreSQL-derived project ...