Tune postgres 9.1 to speedup deletes

Question

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?

Answer 1

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.