Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to be able to generate random bytea fields of arbitrary length (<1Gb) for populating test data.

What is the best way of doing this?

share|improve this question
add comment

2 Answers

up vote 13 down vote accepted

Enhancing Jack Douglas's answer to avoid the need for PL/PgSQL looping and bytea concatenation, you can use:

CREATE OR REPLACE FUNCTION random_bytea(bytea_length integer)
RETURNS bytea AS $body$
    SELECT decode(string_agg(lpad(to_hex(width_bucket(random(), 0, 1, 256)-1),2,'0') ,''), 'hex')
    FROM generate_series(1, $1);
SET search_path = 'pg_catalog';

It's a simple SQL function that's cheaper to call than PL/PgSQL.

The difference in performance due to the changed aggregation method is immense for larger bytea values. Though the original function is actually up to 3x faster for sizes < 50 bytes, this one scales much better for larger values.

Or use a C extension function:

I've implemented a random bytea generator as a simple C extension function. It's in my scrapcode repository on GitHub. See the README there.

It nukes the performance of the above SQL version:

regress=# \a
regress=# \o /dev/null
regress=# \timing on
regress=# select random_bytea(2000000);
Time: 895.972 ms
regress=# drop function random_bytea(integer);
regress=# create extension random_bytea;
regress=# select random_bytea(2000000);
Time: 24.126 ms
share|improve this answer
Well, I came up with nearly the same solution, but tested only for lower values. There @Jack's solution was a clear winner. +1 for you for not stopping here :) –  dezso Aug 16 '12 at 4:35
Thank you - this is excellent and thought provoking. I think FROM generate_series(0, $1); needs to be FROM generate_series(1, $1);. Have you tried recursion? My limited testing implies that this scales better: –  Jack Douglas Aug 16 '12 at 5:45
I tried symlinking /dev/urandom into /var/lib/pgsql/data and reading it with pg_read_file() for bonus crazy points, but unfortunately pg_read_file() reads text input via an encoding conversion, so it can't read bytea. If you really want max speed, write a C extension function that uses a fast pseudo-random number generator to produce binary data and wrap a bytea datum around the buffer :-) –  Craig Ringer Aug 16 '12 at 6:29
@JackDouglas I couldn't help it. C extension version of random_bytea. github.com/ringerc/scrapcode/tree/master/postgresql/… –  Craig Ringer Aug 16 '12 at 8:57
Another excellent answer! Actually one of the best I've seen so far. I haven't tested the extension, but I trust it works as advertised. –  Erwin Brandstetter Aug 16 '12 at 22:42
show 7 more comments


set role dba;
create role stack;
grant stack to dba;
create schema authorization stack;
set role stack;

function to return random binary strings:

create function random_bytea(p_length in integer)
       returns bytea language plpgsql set search_path to 'stack' as $$
  o bytea := '';
  for i in 1..p_length loop
    o := o||decode(lpad(to_hex(width_bucket(random(), 0, 1, 256)-1),2,'0'), 'hex');
  end loop;
  return o;

output examples:

select random_bytea(2);
select random_bytea(10);

select random_bytea(100);

remove testbed:

share|improve this answer
I'd be very pleased to know a more efficient way of doing this. –  Jack Douglas Aug 15 '12 at 10:08
Nice use of width_bucket. Handy. –  Craig Ringer Aug 16 '12 at 0:22
I've enhanced your approach to avoid the PL/PgSQL and expensive concatenation loop; see new answer. By using string_agg over generate_series instead of a PL/PgSQL concatenation loop on bytea I'm seeing a 150-fold improvement in performance. –  Craig Ringer Aug 16 '12 at 0:38
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.