Search code examples
postgresqlcachingperformance-testingdatabase-performance

Generate a 100 GB Postgres relation quickly in Postgres 10

I am trying to generate a 100 GB postgres table quickly. I need this relation for prewarming the buffer cache. I have tried using bytea and text fields. This keeps the actual relation small and a large TOAST table. The tricky part is that the TOAST table cannot be loaded into the cache due to permission issues. I am wondering if anyone has experience creating such a big table quickly?

Solution

  • I don't know if it would be the absolute fastest, but this should be pretty good:

    create unlogged table j (x text);
alter table j alter x set storage plain ;
insert into j select repeat('a',4100) from generate_series(1,13107200);

    Setting the storage clause like this will disable TOAST, both out-of-line storage and in-line compression. Making it unlogged will prevent it from streaming to any replicas or WAL archives. Sizing it to over half a page ensures that only one row is written per page. It will still consume storage on the primary, but I don't think there is any way around that with managed services.

    You might want to populate it with COPY FREEZE to avoid the need to vacuum, but I don't think you would be able to get that to work well on managed services.