Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to tune my postgres db to speed up delete queries. I cant't optimize my db structure yet, so I'm looking for options available on server level for postgres 9.1.

I've seen in older posts the parameter max_fsm_pages, however I can't find the parameter in my postgressql.conf, is this parameter deprecated?

Are there other parameters I can tune?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

max_fsm_pages was eliminated in version 8.4. Free space tracking has gotten a lot smarter and more automatic.

Performance of deletes is going to depend on a lot of things.

  • If there are any foreign keys which reference this table, you should check whether there is an index on the referencing column(s). Without that, each DELETE will cause a scan of the referencing table, to check that you're not causing any rows to be orphaned.

  • Like other database modifications, it is generally more efficient to group actions into larger transactions where reasonable, since there is overhead to each database transaction.

  • If you do have many transactions, you might want to consider setting synchronous_commit to off on the connection you're using for that work.

  • If you are deleting a significant fraction of the table, and you can do this during some maintenance window, you might consider dropping indexes not needed to find the rows to be deleted, and create them again after the mass delete.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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