Creating sample multi-TB databases with Postgresql



  • For reasons I won't go into I need to do some tests with sample terabyte-sized databases in Postgres. Those tests are not your typical SQL-level benchmark tests, more like tests of how quickly I can back the database up, restore it, etc, so while test DBs should have some structure, it's not really important what those structures are as long as there's minimum complexity to them like a few tables.

    The requirement is that tested databases have at least 10TB size, preferably 20TB, size being understood as diskspace occupied by database files (yes it should be size after vacuuming etc).

    So I'm trying to use pgbench tool to generate such sample databases. I started pgbench with intention of creating 15TB DB like this:

    pgbench -i -s 1600000 pgbench15t

    So this run has generated tuples in like 3 days, but now pgbench is hanging on vacuuming for like 2 days by now. What's worse, there does not seem to be much activity with either postmaster process or disk (I tested that with iotop).

    Note: PG version is 11 (I'm not at liberty to choose version) and I've used default settings. Can that be a problem (i.e. default settings)?

    OK so I have two main questions:

    1. Is there some other quick way of generating such sample TB-sized databases that would not involve me just writing DB generation script / program?
    2. Can pgbench be used in some specific way that would result in successful generation of say 15TB DB?


  • It will take a long time to create a 15 TB database (I found https://www.cybertec-postgresql.com/en/a-formula-to-calculate-pgbench-scaling-factor-for-target-db-size/ useful for estimating the database size).

    If you left the database at default settings, I am not surprised if it takes a long time. To speed up VACUUM, set max_parallel_mainenance_workers and maintenance_work_mem high (but it will still take a long time).


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2