does pg18 file_copy_method=clone helps?

[DeoLeung]

hi,

thanks for the great lib, i have a question.
pg18 has file_copy_method=clone which may leverage xfs for instance db copy.

i'm currently running docker pg https://github.com/dbfixtures/pytest-postgresql?tab=readme-ov-file#connecting-to-postgresql-in-docker

on my m4 pro, it's very fast as i map the pg volume to tmpfs
on my server, the memory speed is about 1/3 of my mac, the test speed decreased.

i came across pg18 and it's file_copy_method=clone
i map an xfs volume on my linux server into pg but the tests speed remain the same, does this trick work with the library?

[fizyk]

Hi,
Not at this moment, that's something pg18 introduced, right?

Found some text here: https://boringsql.com/posts/instant-database-clones/
And it being mentioned in changelog here: https://www.postgresql.org/docs/release/18.0/

Need to read a bit about it.

[DeoLeung]

i just modified the fixture and test on my local mac

from pytest_postgresql.janitor import DatabaseJanitor
def _use_file_copy_database_clone() -> bool:
  """Return whether PG18 test DB clones should request FILE_COPY strategy."""
  value = os.environ.get('PYTEST_POSTGRES_FILE_COPY', '1').lower()
  return value not in {'0', 'false', 'no', 'off'}


_DATABASE_JANITOR_INIT: Callable[[DatabaseJanitor], None] = DatabaseJanitor.init


def _database_janitor_init(self: DatabaseJanitor) -> None:
  """Create test databases with explicit PG18 FILE_COPY template clones."""
  if (
    not _use_file_copy_database_clone()
    or not self.template_dbname
    or self.as_template
    or self.version.major < 18
  ):
    _DATABASE_JANITOR_INIT(self)
    return

  with self.cursor() as cur:
    self._terminate_connection(cur, self.template_dbname)
    cur.execute(
      f'CREATE DATABASE "{self.dbname}" '
      f'TEMPLATE "{self.template_dbname}" '
      'STRATEGY FILE_COPY;'
    )

while my docker using tmpfs, it didn't show improvement
but removing the tmpfs, using real local disk, it improves, with xdist 4 workers on my 5k tests setup, origin wall time was 270s, with clone down to 200s. so i think it's worthy to add this.

i could draft a pr, but not sure what's the best interface to implement it