0

I currently use MySQL for a very big Database (1TB), daily delete rows to not exceed 1TB. But I faced this issue Howto: Clean a mysql InnoDB storage engine?. I want to switch to PostgreSQL to avoid this issue. (based on others advice)

My questions are:

  1. If I switch to PostgreSQL this problem will solve?
  2. If my idea is bad, then what is the best solution for my situation?
4
  • One question per question please.
    – user1822
    Nov 14, 2022 at 22:48
  • What is "this issue", please? Your link goes to the MySQL architecture diagram? If your issue is with MySQL architecture, I can understand that; in that case moving to any grown-up DBMS will help. If, on the other hand, you're asking how to manage data lifecycle, consider table partitioning (available even in MySQL).
    – mustaccio
    Nov 14, 2022 at 22:48
  • Thanks, @mustaccio. Is this issue exists in Postgresql? If not, I will switch to it. Otherwise, I will handle it in MySQL.
    – Niyaz
    Nov 15, 2022 at 3:14
  • PostgreSQL and MySQL both handle this issue more or less the same. The only difference is that some PostgreSQL users insist on disparaging MySQL, but not vice versa. Nov 15, 2022 at 17:57

3 Answers 3

3

In MySQL (InnoDB), you can delete rows to make room for new rows. However, because the organization of the BTrees, it is not 1:1. Nor even 1000:1000. That is, if you delete a thousand rows at one "end" of the table, that may or may not let you add a thousand rows at the other "end".

Also, you said, "delete from database". If that means deleting from one table in hopes of being able to insert into a different table, you are out of luck.

Please provide some more specifics, including at least the SHOW CREATE TABLE.

It may be that PARTITION BY RANGE would be useful. That is one of the very few uses for PARTITION in MySQL. It allows for very fast jettisoning of chunks of 'old' data, such as one day's worth every month. But there are caveats. Again, more specifics would let me elaborate.

If the DELETE is also causing a performance issue, I can advise further. (Again, more specifics needed.)

10
  • Thanks, @Rick. I have three tables with rows above 100M. I can only keep data for two weeks, meaning I use Event to delete data from two weeks ago every day.
    – Niyaz
    Nov 15, 2022 at 3:08
  • Also, the deletion process did not cause any performance issues. It took around 30 minutes to remove data (three tables) for one day, which is not the problem in my case.
    – Niyaz
    Nov 15, 2022 at 3:23
  • 1
    @Niyaz - PARTITION BY RANGE(TO_DAYS(...)) with about 16 partitions would be excellent for the daily purge. See Partition But that is essentially the only use for partitioning.
    – Rick James
    Nov 15, 2022 at 5:13
  • 1
    @Niyaz - Daily maintenance drops from a 30-minute DELETE to <1 minute DROP PARTITION plus REORGANIZE PARTITION. In either case, the disk space is 14+1 day's worth of data just before the purge. And partitioning has about 100MB of extra overhead.
    – Rick James
    Nov 15, 2022 at 16:06
  • 1
    @Niyaz - Most people cannot afford the lengthy time that OPTIMIZE TABLE takes. It essentially read the entire table, wrote a 300GB copy, rebuilt all the indexes, then freed up the old table. If that is OK with you, fine. The 600GB is the "wasted space" I aluded to. The partition technique would essentially eliminate that. That is, it might stay at about 300-350GB "permanently".
    – Rick James
    Nov 15, 2022 at 19:21
3

In PostgreSQL, a table normally does not shrink if you delete some rows, but the empty space can be reused for new rows. The same applies for index entries, but as Rick James answered, space reuse might not work as nicely.

Still, you should be fundamentally fine, as long as you don't utilize your disk space to the limit.

However, the king's way to solving your problem would be to use partitioning and drop partitions rather than delete rows.

2
  • 1
    So in this respect, PostgreSQL does not solve this problem any better than MySQL's InnoDB storage engine. Nov 15, 2022 at 17:56
  • 1
    @BillKarwin A B-tree index is a B-tree index, no matter what the database engine is. Nov 15, 2022 at 22:23
2

Taking a shot at just answering the question title. but I believe OP is asking how to return tablespace back to the OS in PostgreSQL. There are two methods:

  1. Use VACUUM FULL to compact the table file(s). This copies the table data to a new file and rebuilds all indexes on the table, so it is not fast (depending on how much data there is) and blocks access to the table while in progress.
  2. Use table partitioning & drop partitions when their data is no longer needed. DROP TABLE unlinks the table files, returning their space to the OS immediately.

References:

6
  • Thanks, @dwhitemv. Do you mean, In Postgresql, I've never faced a scenario that after deleting rows disk is still not free? (My focus is on space, not speed)
    – Niyaz
    Nov 15, 2022 at 3:30
  • As with most database engines, once tablespace is allocated to a table in PostgreSQL, it stays allocated. Creating/extending files is relatively slow, and there is a good chance new row versions can use the space. Also, it’s not good practice to run a DB with a full disk, it limits maintenance operations and reduces I/O performance. Disk space is cheap, don’t skimp on it.
    – dwhitemv
    Nov 15, 2022 at 10:06
  • But let us say I have 100GB of space, and the PostgreSQL database takes 99% of this space. To return some space, I delete 50% of the rows from the tables; Is this space return to OS, or PostgreSQL still keep it for future uses? (Like MySQL)
    – Niyaz
    Nov 15, 2022 at 10:55
  • Based on my understanding of the first link you shared, It will not return. But I want to be sure.
    – Niyaz
    Nov 15, 2022 at 11:05
  • 1
    The space will not be returned until you take action.
    – dwhitemv
    Nov 15, 2022 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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