Re: [GENERAL] Speed of lo_unlink vs. DELETE on BYTEA

[Reuven M. Lerner]

Hi, everyone.  Daniel Verite <dan...@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