4 ms·

Unlogged tables have existed since 9.1. The only thing that was added now was a quick way to toggle between modes. Before, you could achieve the he same by crea

by pilif 11y ago

Unlogged tables have existed since 9.1. The only thing that was added now was a quick way to toggle between modes. Before, you could achieve the he same by creating a second unlogged table (using 'like x including indexes'), followed by an 'insert into ... select ...' an the subsequent dropping and renaming (remember: DDL is transactional in Postgres, so you can do all of this in a transaction). Yes, the new toggle is much more convenient, but you do not have to wait for 9.5 if you really want it. In our case, we converted some tables we use as kind of materialized views the moment 9.1 came out. edit: fixed autocorrect wittyness (unclogged => unlogged)

rgbrenner 11y ago
and when you use alter table unlogged.. that's actually what it does anyway -- it rewrites the entire table (holding an exclusive lock the entire time). So this is really just a convenient syntax.
orf 11y ago
Thanks for that tip, I will try and integrate it with our tests later and see if there is much of a speedup (before I was trying to alter Django's CREATE TABLE statements which is harder than I thought). I was also suggesting something further than unlogged tables, maybe something completely in-memory? Good tests are run in isolation and are really just care about the constraints rather than any form of persistence, combined with the fact that all data inserted is erased at the end of the test anyway it just seems a bit wasteful.
- rgbrenner 11y ago
  if it's just for testing, you can use a ram disk to store the data directory. If you don't want the entire db on the ramdisk, you can create a tablespace for the ramdisk, and then create tables on it. http://www.postgresql.org/docs/9.5/static/manage-ag-tablespaces.html http://www.postgresql.org/docs/9.5/static/manage-ag-tablespa...
  - pjungwir 11y ago
    I'm very tempted to try that, but this warning in the docs you linked sounds pretty scary: > Placing a tablespace on a temporary file system like a ramdisk risks the reliability of the entire cluster Are you sure putting your test db on a ramdisk won't destroy the development db too?
    - rgbrenner 11y ago
      The idea is, since it's a development db, after you destroy it, you can recreate it easily. But there is a way to do it without destroying the db. When you're finished, stop postgres, copy the files off of the ramdisk, and then you can destroy the ramdisk. When you want to start it back up, create the ramdisk, copy the files back, and then start postgres. Obviously something like MySQL's memory tables would be a better solution.. but postgres doesn't have anything similar. So this is as close as you can get.
      - pjungwir 11y ago
        Thanks for writing back! I don't love either of those options. Maybe I should just make a separate cluster for my test database?!: `initdb -D /pgramdisk` I wonder if anyone has tried this, and what the drawbacks are.

by pilif 11y ago

rgbrenner 11y ago

and when you use alter table unlogged.. that's actually what it does anyway -- it rewrites the entire table (holding an exclusive lock the entire time). So this is really just a convenient syntax.

orf 11y ago

Thanks for that tip, I will try and integrate it with our tests later and see if there is much of a speedup (before I was trying to alter Django's CREATE TABLE statements which is harder than I thought). I was also suggesting something further than unlogged tables, maybe something completely in-memory? Good tests are run in isolation and are really just care about the constraints rather than any form of persistence, combined with the fact that all data inserted is erased at the end of the test anyway it just seems a bit wasteful.

rgbrenner 11y ago
if it's just for testing, you can use a ram disk to store the data directory. If you don't want the entire db on the ramdisk, you can create a tablespace for the ramdisk, and then create tables on it. http://www.postgresql.org/docs/9.5/static/manage-ag-tablespaces.html http://www.postgresql.org/docs/9.5/static/manage-ag-tablespa...
- pjungwir 11y ago
  I'm very tempted to try that, but this warning in the docs you linked sounds pretty scary: > Placing a tablespace on a temporary file system like a ramdisk risks the reliability of the entire cluster Are you sure putting your test db on a ramdisk won't destroy the development db too?
  - rgbrenner 11y ago
    The idea is, since it's a development db, after you destroy it, you can recreate it easily. But there is a way to do it without destroying the db. When you're finished, stop postgres, copy the files off of the ramdisk, and then you can destroy the ramdisk. When you want to start it back up, create the ramdisk, copy the files back, and then start postgres. Obviously something like MySQL's memory tables would be a better solution.. but postgres doesn't have anything similar. So this is as close as you can get.
    - pjungwir 11y ago
      Thanks for writing back! I don't love either of those options. Maybe I should just make a separate cluster for my test database?!: `initdb -D /pgramdisk` I wonder if anyone has tried this, and what the drawbacks are.