2

Due to historical reasons we used to use one really big table in our Postgresql database to store time-based series of measurements. After a long usage, as the table become 200+ GB in size, we decided we can clean it up a bit, say to keep only 90 days of data to speed things up a bit and also to save some disk space.

The problem is, doing delete from ... where clock_column < ... won't reclaim any space on disk, so we needed both to have VACUUM FULL on it after the delete.

Since we can't afford to stop the DB for such a long time, we decided to go with a partitioning approach and created per-day tables so we'll be able to delete them one by one as time expires. So now we have table (the initial, huge one), and named after date table160101, table160102 etc.

Now I'd like to do:

  1. backup of initial table (only the initial table)

    2.1 make some clearing in it (only in it) or

    2.2 maybe simple truncate it (only it)

But as I did the tests, I see COPY table TO 'file' creates dump of the whole table (both table and all of table160101, table160102 etc). The very same fashion, doing TRUNCATE clears the whole table, not the initial one (tested on test server).

When I work with partitioned tables in SELECT, I can simple state ONLY to choose which table to use. But I can not find a way to do that in COPY or TRUNCATE.

So the question is: how can I archive my goals without sacrificing data in the table?

P.S. What I need to archive is when all 'per-day' data are in the 'per-date' tables I simple don't like to keep initial huge table with very old data. In fact the TRUNCATE only on it would be just fine idea.

migrated from serverfault.com Jan 21 '16 at 12:24

This question came from our site for system and network administrators.

2

Let's quickly check the COPY documentation:

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' ...}
    ...

Your friend will be the query part. It means that you cannot only copy from a table, but also an arbitrary query. And example can be:

COPY (SELECT * FROM ONLY your_table) TO '/path/to/dump.sql';

Notes:

  • you have to specify an absolute path with COPY
  • it is usually handier to use \copy from psql as it copies the dump to the client machine, not the server. In your case this may be irrelevant.
  • in practice, keeping daily tables is usually too fine-grained. If all your queries can use constraint exclusion, then it might be all good, otherwise you might notice some planning overhead.
2

Another answer tells you how to use COPY with a query rather than a bare table to get the data from only the parent table. I'm not sure how that is going to help you, though, if your database has to be always available. The master table will be changing while you copy the data out of it, and so the copy you get will be out of date.

But for TRUNCATE, you don't need to do anything special. The TRUNCATE ONLY command exists in all supported version of PostgreSQL.

But I think your overall strategy is a bit suspect. The main problem was that you didn't have a deletion policy until now. Now that you have a deletion policy, why do you also need partitioning? Partitioning might help you get out of the hole you are currently in. But once you are out of that hole, your new deletion policy, if implemented, will prevent you from falling into it again even without partitioning.

The problem is, doing delete from ... where clock_column < ... won't reclaim any space on disk, so we needed both to have VACUUM FULL on it after the delete.

Are you currently critical on disk space? If you do the deletion and then an ordinary vacuum, it will probably not return unused space to the operating system. But it will free up space to be reused by future data insertions, which means your disk usage would stop growing, even if it doesn't shrink. If your disk space is only worrying and not critical, them making it stop growing is usually good enough. Once the data you inserted just before adopting the deletion policy (and so is probably at the end of the table) gets to be 90 days old and so gets deleted, then you might start actually shrinking your disk space, as free space at the end of a table does get returned to the OS.

  • If I understood the question correctly, they insert new data in the partitions only, therefore the big table does not change anymore. Eventually, when 90 new partitions will be filled, the big one can be truncated as you suggest. On a busy database, we have seen problems with a deletion policy in place from the beginning: the autovacuum could not really keep up. It needed much tuning to handle the workload, including tweaking the wraparound and freezing related parameters. – dezso Jan 21 '16 at 20:37
  • @dezso It is certainly possible to have situations like that, in which case partitioning is the magic bullet. But I don't see any reason to think that he is actually in that situation. His table is only 200GB, and that is even with a huge amount of unneeded data. The steady state size should be much smaller than that. I wouldn't jump to a solution involving 90 partitions until the simpler one has been tried first. – jjanes Jan 21 '16 at 21:05
  • Again, if I understood correctly, they already jumped :) Otherwise, I agree. – dezso Jan 21 '16 at 21:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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