We have a long processing pipeline running on PostgreSQL 9.0.

Is it faster or slower to perform many operations on a single table within one transaction, versus doing auto-commit for each operation?

We're talking in the order of 5 Bulk Inserts and 30 Updates, on one table, querying from other tables. Table rows are in the range of 1-5 million.

We're planning to upgrade to PostgreSQL 9.4 soon. Are there any difference across versions?

  • It is going to be slower because of greater amount of logging operations. Also locks are going to be held for a longer period of time. In case of server shutdown, the recovery must be performed on the next startup and there will be more logged operations to be done on the database. It is recommended to batch big operations in smaller sets (for example 10000 rows in one transaction, commit and a new transaction...) – BuahahaXD Dec 23 '15 at 9:27
  • @BuahahaXD: the amount of "logging operations" when you update 1 million rows is always the same, regardless how often you commit. The database will have to log 1 million updates. – a_horse_with_no_name Dec 23 '15 at 10:35
  • 2
    "Are there any difference across versions" - yes there are. Newer versions are usually faster than older versions. As you are still planning the upgrade, I would suggest to plan for an upgrade to 9.5 which is planned to be released mid of January 2016 (~4 weeks from now). Regarding the question: your first attempt should always use transactions boundaries that are defined by your business requirements, not from some assumed performance impact. If that shows up to be too slow or gives you other problems, then think about doing more frequent commits. – a_horse_with_no_name Dec 23 '15 at 10:39
  • We don't need transaction demarcations from a business perspective in this pipeline. It works on a table that is not used by other sessions, and all-or-nothing is not important. – thomjah Dec 23 '15 at 10:50
  • @a_horse_with_no_name You are right. What I meant was the amount of log entries that would have to be used in case of a rollback or crash recovery. These entries could not be "cleaned" before the commit (I don't know how PostgreSQL handles it) – BuahahaXD Dec 23 '15 at 10:52

Your Answer

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

Browse other questions tagged or ask your own question.