About the unlogged part, I forgot to mention another issue, after a bulk delete, a rebuild may occur, which can hold almost a duplicate of the whole data (other than the deleted rows, which are not duplicated)
> Again I would encourage users to *try* simply archiving old rows more frequently with smaller batches and keep it under control. I agree, it will work for most cases, however the more an airflow deployment scales, the more the deletion will become a problem, for us, not running the cleanup script for a week caused issues where we had to move to our backup database. I understand that it is all due to human error, however allowing the problematic tables to be partitioned decreases the possibility of it happening, as even if for some reason partitioned are not dropped, dropping a couple at once is just like dropping a table, which does not cause a table rebuild (which happens after bulk deletes, and for some time, stores almost a duplicate of the table. >> The problem here is any "reasonable" partitioning you could come up with > > for jobs to speed up your pruning cannot be "static" (and this is what > > makes partitioning really work). According to pgsql documentation: > In the case of a partitioned table, updating a row might cause it to no longer satisfy the partition constraint of the containing partition. In that case, if there is some other partition in the partition tree for which this row satisfies its partition constraint, then the row is moved to that partition. If there is no such partition, an error will occur. Behind the scenes, the row movement is actually a DELETE and INSERT operation So that is not an issue. > If we’re thinking outside the box, it’s conceivable we could make the jobs > table only for active jobs, then immediately archive anything not active to > a job log table. This does seem like a good idea, and having the non active tables have no constraints, and being unlogged to allow for flexibility in managing it's size. About the scale, it depends per airflow cluster, but usually happens for clusters which are growing quickly, faster than we anticipated causing the db to fill up before the storage is increased. We will try a couple of things, starting with partitions as it requires the least amount of code changes, if any other ideas come to mind, I would be more than happy to try them. On Tue, Dec 30, 2025, 19:39 Jarek Potiuk <[email protected]> wrote: > Yeah. I am also interested to see all kinds of experiments with different > approaches and seeing which one works best - at the end we are all > theorising a bit and only experimenting might give some good answers. > > On Tue, Dec 30, 2025 at 6:10 PM Daniel Standish via dev < > [email protected]> wrote: > > > Again I would encourage users to *try* simply archiving old rows more > > frequently with smaller batches and keep it under control. > > > > I would be surprised if this doesn’t work. I’d it doesn’t work I would be > > interested to know what volume of tasks you are pushing through. Or is it > > even used for tasks anymore? > > > > Please try this and report back. > > > > If we’re thinking outside the box, it’s conceivable we could make the > jobs > > table only for active jobs, then immediately archive anything not active > to > > a job log table. > > > > > > > > > > > > On Tue, Dec 30, 2025 at 12:37 AM Jarek Potiuk <[email protected]> wrote: > > > > > *Why not partitions for Job?* > > > > > > I don't think partitioning is a good idea for the Jobs table - and > simply > > > because "job" and "partition" logic is incompatible. > > > > > > The problem here is any "reasonable" partitioning you could come up > with > > > for jobs to speed up your pruning cannot be "static" (and this is what > > > makes partitioning really work). Partitioning really only makes sense > > when > > > your record can be assigned to a partition at the creation time and it > > > never changes. Job row - by its nature will have to change partitions > if > > > you want to make use of partitioning. Something that you cannot prune > > now - > > > because it's "active" now, should - in the future be "completed" and > > ready > > > to be pruned, so it should move to a partition that can be pruned. > > > > > > So, you logically need: > > > > > > * a "partition" or "partitions" of sorts (or some other bucket of jobs) > > > which keeps currently active jobs > > > * other "partitions" (or some other buckets) that are keeping only the > > jobs > > > that are not active any more and can be safely pruned > > > > > > Since each job might take an arbitrarily long time to change from > > "active" > > > to "completed", what you really need is a way for jobs to change from > > > "active" partition to the non-active one. This is not really compliant > > with > > > how partition works, because partitioning is static by definition and > > > moving things between partitions is effectively the same as deleting > rows > > > from one table and creating them in another. Partitions can be thought > as > > > completely separate tables, that do not share indexes, but have the > same > > > structure. Such a "delete"/"insert" pattern for "Job" rows would > > completely > > > destroy performance and would be worse than your WAL issue today. > > > > > > *How to possibly achieve what you want with UNLOGGED? * > > > > > > > and if we set it to unlogged, we lose the replication feature > > > > > > Yeah - that's the tradeoff that I was mentioning before for the JOB > > table. > > > You would definitely lose the "instant" and easy physical block > > replication > > > capabilites, however you could instead implement a way to track > "current" > > > jobs and restore them when needed in case of fail-over (which I > > understand > > > a bit would complicate your replication failover mechanism). I did not > > know > > > how difficult it would be, and I have no "good" experience with > managing > > > Postgres, but with Postgres reputation, I would be surprised if there > was > > > no feature in Postgres that you could use. I looked it up a bit and .. > I > > > think there is one. > > > > > > Postgres has something that can help you with that - i.e. fine-grained, > > > logical replication > > > https://www.postgresql.org/docs/current/logical-replication.html. From > > > what > > > I understand, logical replication can work in parallel with physical > > > replication. And unlike physical block-based replication it can be more > > > fine-grained, and work not only on a "row" fine-grained selection but > > also > > > on "actions" performed. For example - you can tell it to only publish > > jobs > > > in a given state, (WHERE) but also to not publish certain operations > (say > > > "DELETE"). > > > > > > I think your case should be nicely handled for Job by a combination of > > > several things: > > > > > > a) mark the JOB table as UNLOGGED > > > b) enable logical replication for JOB table from primary to replica and > > > only publish INSERT and UPDATE but not publish DELETE and TRUNCATE > > > c) (here I am a bit theorising - because I am not sure how it might > work) > > > but you could subscribe on the replica side to receive those INSERTS > and > > > UPDATES and apply them to the replica's JOBS table. I **guess** it > would > > > work since JOBS table is unlogged (though reading UNLOGGED feature, the > > > table is in a weird Shroedinger state at the replica - it exists but > any > > > access to it in replica will yield an error, so maybe you will have to > > > instead SUBSCRIBE to receive INSERTS and UPDATES to a JOBS_REPLICA > table > > or > > > smth like that). > > > d) You run "airflow db clean jobs" (or equivalent query) periodically > in > > > BOTH -> Primary and Replica instances. That would avoid all the WAL / > > > logical replica overload, because the table is UNLOGGED, and DELETE > > > operations would not be replicated. Effectively you would have > > independent > > > "pruning" of data on both Primary and Replica (and you can scale it > > easily > > > - with as many replicas you want to have) > > > e) if you can subscribe directly to receive the UPDATE and INSERTS in > > > replica JOB table, you are done -> at the moment of failover, all the > > > "current" rows will be in-sync (as much as logical replication lag is > > > concerned). There might be different set of "completed" jobs on both > > > primary and secondary, but that is not an issue. > > > > > > f) if you can only subscribe to receive it in another table, then > > failover > > > will need to be paired with renaming the JOB_REPLICA table to become > JOB > > > table, but this is practically 0 cost operation. > > > > > > All that can be done without changing Airflow code > > > > > > J. > > > > > > > > > On Tue, Dec 30, 2025 at 6:51 AM Natanel <[email protected]> > wrote: > > > > > > > That is a good idea, however the larger and more problematic table is > > the > > > > job table, we are using replication across our dB's for disaster > > > recovery, > > > > and if we set it to unlogged, we lose the replication feature, for > the > > > log > > > > table it is fine, as we do not mind losing audit logs when we switch > > that > > > > active db, however, for the job it will cause problems. > > > > > > > > As the scheduler, dag processor, triggerer and webserver/apiserver, > > along > > > > with all the running task data will be lost, and might cause those > jobs > > > to > > > > crash when they try to update the latest_heartbeat field, or the > state. > > > > > > > > I do not think we can solve this issue without some code changes to > > make > > > > the tables partition friendly. > > > > > > > > Any other suggestions that might avoid a code change will help as it > > will > > > > allow us to fix the issue faster than upgrading a major release, but > I > > do > > > > think that the code change may benefit some of the community. > > > > > > > > On Tue, Dec 30, 2025, 07:02 Rahul Vats <[email protected]> > wrote: > > > > > > > > > +1 to Jarek's suggestion. The UNLOGGED table approach for log and > job > > > > > tables makes sense for high-volume deployments. Pairing with > external > > > log > > > > > storage provides a safety net for recovery scenarios. > > > > > > > > > > Regards, > > > > > Rahul Vats > > > > > > > > > > On Tue, 30 Dec 2025 at 01:50, Jarek Potiuk <[email protected]> > wrote: > > > > > > > > > > > Wait.. Actually ... Stupid me! > > > > > > > > > > > > I just realized setting the "unlogged" for the two tables means > > that > > > > you > > > > > do > > > > > > not have to do any partitioning at all ....... Because you will > not > > > > have > > > > > > all the WAL issues AT ALL. > > > > > > > > > > > > So it's not really about making things "partitioning friendly" > but > > > > simply > > > > > > "mark those two tables as unlogged" and make sure that you handle > > > > > recovery > > > > > > when plug is pulled. No partitioning needed at all. Might be > > > connected > > > > > with > > > > > > the "send audit logs somewhere". > > > > > > > > > > > > So - we might not need **any** change to airflow, just a > > > documentation > > > > > > (maybe even unofficial blog of some sort) - describing how to > mark > > > > those > > > > > > two tables as unlogged and how to handle recovery in case of > > replica > > > > > > failover. > > > > > > > > > > > > > > > > > > > > > > > > On Mon, Dec 29, 2025 at 9:15 PM Jarek Potiuk <[email protected]> > > > wrote: > > > > > > > > > > > > > > But eg lookiups to log table might use ti key attrs . > > > > > > > > > > > > > > Yeah - that would be the price to pay. Trade-offs, trade-offs > > > > > everywhere. > > > > > > > > > > > > > > Such queries over a multi-partition table would have to do full > > > table > > > > > > scan > > > > > > > - they will not be able to use indexes (unless the queries use > > > > > partition > > > > > > > key). So they would **work** but would be **slow** if the table > > > gets > > > > > too > > > > > > > big. > > > > > > > However I would argue that if someone would like to partition > > those > > > > > > > tables, they will frequently and aggressively prune the old > > > > partitions > > > > > > > (this is the reason why they want to have it) - which will > > > > effectively > > > > > > keep > > > > > > > those tables "small" (relatively). And even if full table scan > is > > > > used, > > > > > > if > > > > > > > there is a question about task_id audit log, that would be > fine. > > > > > > > > > > > > > > Actually - I strongly believe - and we had this discussion in > the > > > > past > > > > > - > > > > > > > that the log table is quite a bit of abomination, because it's > > not > > > a > > > > > > "true" > > > > > > > audit log if it is kept in modifiable database, and anyone who > > want > > > > to > > > > > > make > > > > > > > it a "true" audit log will have to effectively send those log > > > entries > > > > > to > > > > > > a > > > > > > > "write-only" storage and query the audit logs there. And that > > > nicely > > > > > fits > > > > > > > into "unlogged" pattern -> you could add a trigger in your DB > to > > > > > > > automatically send audit logs somewhere "write-only" and > > > aggressively > > > > > > prune > > > > > > > the old data (i.e. partitions). > > > > > > > > > > > > > > So - from the side of airflow that would mean that those tables > > are > > > > > > > "partitioning friendly", but not to implement partitioning > > itself. > > > > > > Leaving > > > > > > > "partitioning|" and "handling recovery" and "handling audit > logs" > > > up > > > > to > > > > > > the > > > > > > > Deployment Manager. Which IMHO should happen anyway for "log" > > table > > > > and > > > > > > > would be nice pattern to describe as a way to achieve "true" > > audit > > > > log. > > > > > > > > > > > > > > J,. > > > > > > > > > > > > > > > > > > > > > > > > > > > > On Mon, Dec 29, 2025 at 9:02 PM Daniel Standish via dev < > > > > > > > [email protected]> wrote: > > > > > > > > > > > > > >> The problem with partitioning them is that when querying you > > would > > > > > need > > > > > > to > > > > > > >> know the partition. > > > > > > >> But eg lookiups to log table might use ti key attrs . > > > > > > >> > > > > > > >> I would try just trimming log and job table in small batches > > > > > frequently > > > > > > >> using an appropriate index, before thinking about the > complexity > > > of > > > > > > >> partitioning. > > > > > > >> > > > > > > >> > > > > > > >> > > > > > > >> > > > > > > >> > > > > > > >> On Mon, Dec 29, 2025 at 11:24 AM Jarek Potiuk < > [email protected] > > > > > > > > wrote: > > > > > > >> > > > > > > >> > I have a feeling that making those **two** tables > > > > partition-friendly > > > > > > >> should > > > > > > >> > be easy - and maybe that's all that we need. That would make > > it > > > > > > possible > > > > > > >> > for you to use the partitioning, (where it would not be > > > necessary > > > > > for > > > > > > >> > everyone). > > > > > > >> > There might be a few ways of doing it without losing > > uniqueness. > > > > For > > > > > > >> > example Job ID and audit log ID could follow certain > > > conventions > > > > - > > > > > > and > > > > > > >> > always start with a partition key) thus providing uniqueness > > we > > > > > need. > > > > > > >> > > > > > > > >> > Those two tables are pretty specific and neither job id nor > > log > > > id > > > > > > >> impact > > > > > > >> > anything else in our data model. > > > > > > >> > > > > > > > >> > I think such a change to make those two tables "partition > > > > friendly" > > > > > > >> could > > > > > > >> > be accepted, but we could only say it after seeing a POC. > > > > > > >> > > > > > > > >> > J. > > > > > > >> > > > > > > > >> > > > > > > > >> > On Mon, Dec 29, 2025 at 8:01 PM <[email protected]> > > > wrote: > > > > > > >> > > > > > > > >> > > I do think that the second option is best, it is also what > > we > > > > > wanted > > > > > > >> to > > > > > > >> > > do, the only reason we did not do that is because from our > > > > tests, > > > > > > sql > > > > > > >> > > alchemy sometimes breaks as it expects certain constraints > > > which > > > > > are > > > > > > >> not > > > > > > >> > > there, mainly for update queries, select works well, if I > am > > > not > > > > > > >> > mistaken, > > > > > > >> > > there are 3 or 4 large tables, job being the largest (7 > > times > > > > > larger > > > > > > >> than > > > > > > >> > > the second largest), the question is, will such a pull > > request > > > > be > > > > > > >> > approved? > > > > > > >> > > > > > > > > >> > > As we do lose the unique constraint (as it becomes per > > > > partition), > > > > > > >> though > > > > > > >> > > it is a sequence that most likely won't repeat until the > > > > previous > > > > > > has > > > > > > >> > been > > > > > > >> > > deleted, but if not, we might query unrelated job or log > > data, > > > > and > > > > > > so > > > > > > >> > > changes in the api server are also needed, creating the pr > > is > > > > not > > > > > a > > > > > > >> > > problem, the question is how beneficial will it be, as if > it > > > is > > > > > done > > > > > > >> to > > > > > > >> > > those problematic tables, it means that the preferred way > to > > > > > manage > > > > > > >> > > retention is from the db, and can be an optional alembic > > > script. > > > > > > >> > > > > > > > > >> > > I do not want to just push a fix that will add more > > complexity > > > > > than > > > > > > >> the > > > > > > >> > > benefit it will bring. > > > > > > >> > > > > > > > > >> > > If we do go the pr way, a separate discussion is probably > > > needed > > > > > to > > > > > > >> > decide > > > > > > >> > > how it should be done (most likely an additional airflow > > > command > > > > > to > > > > > > >> turn > > > > > > >> > on > > > > > > >> > > or off the partitions and retention) > > > > > > >> > > > > > > > > >> > > Doing it as a custom solution has caused problems with > > > > sqlalchemy, > > > > > > >> and we > > > > > > >> > > do not want to do so as if later an alembic script relies > on > > > the > > > > > > >> primary > > > > > > >> > > key in some way, we will need to fix it manually and deal > > with > > > > the > > > > > > >> > problems > > > > > > >> > > it may cause when we update. > > > > > > >> > > > > > > > > >> > > > On 29 Dec 2025, at 21:36, Jarek Potiuk < > [email protected]> > > > > > wrote: > > > > > > >> > > > > > > > > > >> > > > Another option is that you ( could make ONLY for those > > two > > > > > > tables - > > > > > > >> > > > partition-friendly. And do not partition anything else. > > I > > > > > think > > > > > > >> that > > > > > > >> > > > **could** be possible - both have datetime fields that > > could > > > > be > > > > > > >> used as > > > > > > >> > > > partition keys - you would have to assess if you can do > it > > > as > > > > > your > > > > > > >> > > "custom" > > > > > > >> > > > solution or whether it would require some changes to > > airflow > > > > > > models. > > > > > > >> > But > > > > > > >> > > I > > > > > > >> > > > can't see foreign key problems if ONLY those two tables > > are > > > > > > >> > partitioned, > > > > > > >> > > so > > > > > > >> > > > likely you could do it yourself in your DB. > > > > > > >> > > > > > > > > > >> > > > In this case - maybe "let's solve those tables that are > > > > > > >> problematic" is > > > > > > >> > > > easier to do than "let's apply partitioning to > > everything". > > > > > > >> > > > > > > > > > >> > > >> On Mon, Dec 29, 2025 at 7:31 PM Jarek Potiuk < > > > > [email protected] > > > > > > > > > > > > >> > wrote: > > > > > > >> > > >> > > > > > > >> > > >> Purely theoretically, you could change the log and job > > > tables > > > > > to > > > > > > be > > > > > > >> > > >> unlogged - and thus avoid WAL for them. > > > > > > >> > > >> > > > > > > >> > > >> The drawback of this: > > > > > > >> > > >> > > > > > > >> > > >> * the data in those tables will be lost if you pull the > > > plug > > > > or > > > > > > >> kill > > > > > > >> > -9 > > > > > > >> > > >> the primary postgres server > > > > > > >> > > >> * the tables are not available (at all) in replicas - > so > > in > > > > > case > > > > > > >> of a > > > > > > >> > > >> fallback, you would have to have a manual data > > > import/export > > > > > for > > > > > > >> those > > > > > > >> > > >> tables on fail-over, rather than rely on replicas being > > > > "ready > > > > > to > > > > > > >> > > fallback > > > > > > >> > > >> immediately". Or accept data loss. > > > > > > >> > > >> * the data from those tables will not be present in > > backups > > > > > > >> > > >> > > > > > > >> > > >> I am not 100% sure, but I believe loss of data in both > > > tables > > > > > is > > > > > > >> not > > > > > > >> > > >> really catastrophic for Airflow, so maybe it's > acceptable > > > > risk > > > > > > (but > > > > > > >> > > likely > > > > > > >> > > >> you should do a disaster-recovery test to see what > > happens > > > > and > > > > > > how > > > > > > >> to > > > > > > >> > > >> recover in case, indeed, someone pulls the plug on your > > > > > postgres > > > > > > >> > server. > > > > > > >> > > >> > > > > > > >> > > >> J, > > > > > > >> > > >> > > > > > > >> > > >> > > > > > > >> > > >> > > > > > > >> > > >> > > > > > > >> > > >>> On Mon, Dec 29, 2025 at 7:16 PM Natanel < > > > > > > [email protected]> > > > > > > >> > > wrote: > > > > > > >> > > >>> > > > > > > >> > > >>> Yes, the problem is the manual deletions, we have > tried > > > it, > > > > it > > > > > > >> > > resulted in > > > > > > >> > > >>> the same exact issue, as the scheduled db procedures > > which > > > > > clean > > > > > > >> up > > > > > > >> > the > > > > > > >> > > >>> rows marked as deleted actually get deleted, and so it > > > takes > > > > > up > > > > > > >> > > storage, > > > > > > >> > > >>> yet it does not solve the WAL problem, the problematic > > > table > > > > > is > > > > > > >> > > actually > > > > > > >> > > >>> not task_instance, it is relatively small, the log and > > job > > > > > > tables > > > > > > >> are > > > > > > >> > > the > > > > > > >> > > >>> biggest tables (the problem with them is the primary > key > > > > > change > > > > > > >> > > required), > > > > > > >> > > >>> by a multiple of 10 (or more, cluster dependant). > > > > > > >> > > >>> > > > > > > >> > > >>> The smaller batches might solve the issue, however, it > > > seems > > > > > to > > > > > > >> just > > > > > > >> > > delay > > > > > > >> > > >>> the problem a little rather than solve it, as deleting > > > data > > > > > > with a > > > > > > >> > > delete > > > > > > >> > > >>> query (especially a lot of data) is not a very light > > > > > operation, > > > > > > >> and > > > > > > >> > so > > > > > > >> > > I > > > > > > >> > > >>> think that this is the main issue. > > > > > > >> > > >>> > > > > > > >> > > >>> It would be nice if we could use partitions instead, > as > > it > > > > is > > > > > a > > > > > > >> > lighter > > > > > > >> > > >>> operation, and does not require us to maintain a query > > and > > > > > > manage > > > > > > >> our > > > > > > >> > > db, > > > > > > >> > > >>> I > > > > > > >> > > >>> have thought about changing the models, most of the > > > changes > > > > > are > > > > > > >> > > relatively > > > > > > >> > > >>> simple, for some it is just removing the foreign key > and > > > > > relying > > > > > > >> on > > > > > > >> > ORM > > > > > > >> > > >>> level constraints, for others, it requires adding a > pre > > > > query > > > > > to > > > > > > >> have > > > > > > >> > > the > > > > > > >> > > >>> same constrains but I do not like that idea, maybe > there > > > is > > > > > > >> another > > > > > > >> > > way to > > > > > > >> > > >>> make airflow "partition-friendly"? > > > > > > >> > > >>> > > > > > > >> > > >>> I can't think of a nice way to do so, maybe it does > not > > > > exist, > > > > > > as > > > > > > >> the > > > > > > >> > > db > > > > > > >> > > >>> clean is as simple as a delete query gets, yet when > > there > > > > is a > > > > > > >> lot of > > > > > > >> > > >>> data, > > > > > > >> > > >>> it is all duplicated in WALs. > > > > > > >> > > >>> > > > > > > >> > > >>> On Mon, Dec 29, 2025, 19:40 Daniel Standish via dev < > > > > > > >> > > >>> [email protected]> > > > > > > >> > > >>> wrote: > > > > > > >> > > >>> > > > > > > >> > > >>>> Have you looked at doing manual deletions? I.e. > > writing > > > > your > > > > > > own > > > > > > >> > sql? > > > > > > >> > > >>>> > > > > > > >> > > >>>> The db clean command is probably not "optimal" for > all > > > > > > scenarios. > > > > > > >> > > >>>> > > > > > > >> > > >>>> So for example, if the main problem table for you is > > > > > > >> task_instance, > > > > > > >> > > you > > > > > > >> > > >>>> could periodically delete TI records in smaller > batches > > > > using > > > > > > >> some > > > > > > >> > > >>>> appropriate index (whether it exists now or you add > > it). > > > > > Then > > > > > > >> maybe > > > > > > >> > > you > > > > > > >> > > >>>> would not stress the db as hard. > > > > > > >> > > >>>> > > > > > > >> > > >>>> Airflow isn't designed to use partitions so, you may > > not > > > > get > > > > > > good > > > > > > >> > > >>> results > > > > > > >> > > >>>> with that approach. > > > > > > >> > > >>>> > > > > > > >> > > >>>> > > > > > > >> > > >>>> > > > > > > >> > > >>>> On Mon, Dec 29, 2025 at 7:32 AM Natanel < > > > > > > [email protected] > > > > > > >> > > > > > > > >> > > >>> wrote: > > > > > > >> > > >>>> > > > > > > >> > > >>>>> Hello everyone, after having issues with the > 'airflow > > db > > > > > > clean' > > > > > > >> > > >>> command, > > > > > > >> > > >>>>> where due to the amount of dags and tasks that are > > > running > > > > > > every > > > > > > >> > day > > > > > > >> > > >>> in > > > > > > >> > > >>>> our > > > > > > >> > > >>>>> deployments, we get a lot of new data every day, > which > > > is > > > > > > >> stored in > > > > > > >> > > >>> the > > > > > > >> > > >>>>> database, and when we delete the data, due to the > way > > > > PGSQL > > > > > > >> works, > > > > > > >> > > the > > > > > > >> > > >>>>> WAL's get replicated to both the archive storage and > > the > > > > > main > > > > > > >> data > > > > > > >> > > >>>> storage > > > > > > >> > > >>>>> of the db instance, which in turn, causes a > > significant > > > > jump > > > > > > in > > > > > > >> cpu > > > > > > >> > > >>>> usage, > > > > > > >> > > >>>>> ram usage and disk usage, whenever we run the > command, > > > > which > > > > > > >> causes > > > > > > >> > > >>> all > > > > > > >> > > >>>>> kinds of issues, we even had it once fill up the db > > > > storage, > > > > > > and > > > > > > >> > > >>> causing > > > > > > >> > > >>>>> the database to be unresponsive, forcing us to move > to > > > our > > > > > > >> backup > > > > > > >> > > >>>> database, > > > > > > >> > > >>>>> after we haven't ran the command for a few months > due > > to > > > > > human > > > > > > >> > error. > > > > > > >> > > >>>>> > > > > > > >> > > >>>>> As of now, I know that this is the accepted and > widely > > > > used > > > > > > way > > > > > > >> of > > > > > > >> > > >>>> managing > > > > > > >> > > >>>>> the airflow database's size, however, we noticed > that > > it > > > > may > > > > > > >> cause > > > > > > >> > > >>> issues > > > > > > >> > > >>>>> in certain cases, just like in our case, where if > the > > db > > > > has > > > > > > not > > > > > > >> > been > > > > > > >> > > >>>>> cleaned up for a while, cleaning it can be > > problematic. > > > > > > >> > > >>>>> > > > > > > >> > > >>>>> We decided to try and partition the table, and use > > > pgsql's > > > > > > >> built in > > > > > > >> > > >>>>> retention of partitions, which does not issue a > DELETE > > > > > query, > > > > > > >> and > > > > > > >> > is > > > > > > >> > > >>>>> lighter and faster, while being simpler to use, > > however, > > > > we > > > > > > have > > > > > > >> > > >>>>> encountered issues due to having Foreign Key > > constraints > > > > in > > > > > > some > > > > > > >> > > >>> tables, > > > > > > >> > > >>>>> having to duplicate such keys and other than forcing > > > code > > > > > > >> changes > > > > > > >> > (as > > > > > > >> > > >>> the > > > > > > >> > > >>>>> foreign key must include the partitioned key, as the > > > > > > partitioned > > > > > > >> > key > > > > > > >> > > >>> must > > > > > > >> > > >>>>> be part of the primary key), while also having the > > issue > > > > of > > > > > > >> > > sqlalchemy > > > > > > >> > > >>>>> breaking once we change the primary key, with the > > > addition > > > > > of > > > > > > >> the > > > > > > >> > > >>>>> constraints on the primary key breaking. > > > > > > >> > > >>>>> > > > > > > >> > > >>>>> And in Mysql, due to the foreign keys, it is not > > > possible > > > > to > > > > > > >> > > partition > > > > > > >> > > >>>>> tables which include them, as it is not supported > yet > > > > > > >> (according to > > > > > > >> > > >>> this > > > > > > >> > > >>>>> < > > > > > > >> > > >>>>> > > > > > > >> > > >>>> > > > > > > >> > > >>> > > > > > > >> > > > > > > > > >> > > > > > > > >> > > > > > > > > > > > > > > > > > > > > > https://dev.mysql.com/doc/refman/8.4/en/partitioning-limitations-storage-engines.html > > > > > > >> > > >>>>>> > > > > > > >> > > >>>>> ). > > > > > > >> > > >>>>> > > > > > > >> > > >>>>> Has anyone else tried to use the databases built in > > > > > partition > > > > > > >> > > >>> retention > > > > > > >> > > >>>>> system instead of the 'airflow db clean' command? > > > > > > >> > > >>>>> > > > > > > >> > > >>>>> Thanks, Natanel. > > > > > > >> > > >>>>> > > > > > > >> > > >>>> > > > > > > >> > > >>> > > > > > > >> > > >> > > > > > > >> > > > > > > > > >> > > > > > > > > > > --------------------------------------------------------------------- > > > > > > >> > > To unsubscribe, e-mail: > [email protected] > > > > > > >> > > For additional commands, e-mail: > > [email protected] > > > > > > >> > > > > > > > > >> > > > > > > > > >> > > > > > > > >> > > > > > > > > > > > > > > > > > > > > > > > > > > > >
