{"id":2886,"date":"2025-06-24T05:55:12","date_gmt":"2025-06-24T05:55:12","guid":{"rendered":"https:\/\/bynatree.com\/?p=2886"},"modified":"2025-06-24T05:55:12","modified_gmt":"2025-06-24T05:55:12","slug":"whats-new-in-postgresql-18-beta-performance-insight-and-control","status":"publish","type":"post","link":"https:\/\/divaind.com\/ie1\/2025\/06\/24\/whats-new-in-postgresql-18-beta-performance-insight-and-control\/","title":{"rendered":"What\u2019s New in PostgreSQL 18 Beta: Performance, Insight and Control"},"content":{"rendered":"

PostgreSQL 18 , which is presently in Beta, offers a robust feature set that enhances security, replication, performance, and observability(PostgreSQL 18 Beta). The main improvements are already obvious\u2014and exciting\u2014even though the final GA (General Availability) release may fine-tune several elements.<\/span><\/p><\/blockquote>\n

Here\u2019s a concise overview of what\u2019s coming:<\/span><\/p>\n

\u26a1 Performance Enhancements<\/b><\/h2>\n
    \n
  • Asynchronous I\/O<\/b>: A major milestone. PostgreSQL now supports asynchronous I\/O with the <\/span>io_method<\/span> setting. This allows background reads using <\/span>io_uring<\/span> or worker threads, unlocking faster read-heavy workloads. Stats are exposed via the new <\/span>pg_aios<\/span> view.<\/span>\n

    <\/span><\/li>\n

  • B\u2011tree Index Skip Scans<\/b>: Improves query speed by skipping unnecessary parts of multi-column indexes.<\/span>\n

    <\/span><\/li>\n

  • Parallel GIN Index Creation<\/b>: Index creation for GIN types can now leverage multiple CPUs.<\/span>\n

    <\/span><\/li>\n

  • Faster GiST and B\u2011tree Builds<\/b>: New sort support boosts performance for range-type index creation.<\/span>
    \n<\/span>
    \n<\/span><\/li>\n<\/ul>\n

    \ud83d\udcca Monitoring & Observability<\/b><\/h2>\n
      \n
    • Detailed I\/O Statistics<\/b>: New <\/span>pg_stat_get_backend_io()<\/span> and <\/span>pg_stat_io<\/span> columns (<\/span>read_bytes<\/span>, <\/span>write_bytes<\/span>, <\/span>extend_bytes<\/span>) provide per-backend I\/O insights.<\/span>\n

      <\/span><\/li>\n

    • WAL & Checkpointer Stats<\/b>: <\/span>pg_stat_get_backend_wal()<\/span> and enhanced <\/span>pg_stat_checkpointer<\/span> report more detailed write-ahead log activity.<\/span>\n

      <\/span><\/li>\n

    • Improved Memory Reporting<\/b>: Functions like <\/span>pg_get_process_memory_contexts()<\/span> and new columns in <\/span>pg_backend_memory_contexts<\/span> help debug memory use.<\/span>\n

      <\/span><\/li>\n

    • Vacuum & Analyze Timing<\/b>: <\/span>pg_stat_all_tables<\/span> now logs time spent in both manual and automatic vacuum\/analyze operations.<\/span>\n

      <\/span><\/li>\n

    • EXPLAIN Enhancements<\/b>: <\/span>EXPLAIN ANALYZE VERBOSE<\/span> now includes WAL usage, CPU time, memory usage, and disk activity\u2014especially for Materialize, CTEs, and Window nodes.<\/span>\n

      <\/span><\/li>\n<\/ul>\n

      \ud83d\udd10 Security Upgrades<\/b><\/h2>\n
        \n
      • OAuth Authentication<\/b>: A new <\/span>oauth<\/span> method has been added to <\/span>pg_hba.conf<\/span>, allowing integration with external token validators via <\/span>oauth_validator_libraries<\/span>.<\/span>\n

        <\/span><\/li>\n

      • Minimum Password Length<\/b>: Configurable through the new <\/span>min_password_length<\/span> variable for better password policies.<\/span>\n

        <\/span><\/li>\n

      • Access Control Auditing<\/b>: The new <\/span>pg_get_acl()<\/span> function simplifies ACL inspection.<\/span>\n

        <\/span><\/li>\n

      • MD5 Authentication Deprecated<\/b>: PostgreSQL continues to tighten security\u2014MD5 will be removed in future versions.<\/span>\n

        <\/span><\/li>\n<\/ul>\n

        \ud83d\udd01 Replication<\/b><\/h2>\n
          \n
        • Generated Columns Support<\/b>: Logical replication now supports generated columns\u2014great for syncing with systems that can\u2019t recompute them.<\/span>\n

          <\/span><\/li>\n

        • Replication Origin Limits<\/b>: Use <\/span>max_active_replication_origins<\/span> to control how many logical origins can be tracked simultaneously.<\/span>\n

          <\/span><\/li>\n

        • Conflict Logging<\/b>: Apply conflicts are now logged and available in <\/span>pg_stat_subscription_stats<\/span>, improving visibility.<\/span><\/li>\n
        • Cleaner Wal files Handling<\/b>: Idle replication slots can now auto-expire using <\/span>idle_replication_slot_timeout<\/span>.<\/span><\/li>\n<\/ul>\n

          \ud83d\udd27 Vacuum & Maintenance<\/b><\/h2>\n
            \n
          • The highest limit for autovacuum workers is defined via a new server option called <\/span>autovacuum_worker_slots<\/b>. This makes it easier to scale vacuum operations according to load because you can now modify autovacuum_max_workers at runtime without having to restart.<\/span><\/li>\n
          • Now, you may use the <\/span>autovacuum_vacuum_max_threshold <\/b>argument to establish a specific threshold for dead tuples that will cause the autovacuum to start. By adding to the current percentage-based triggering method, this gives high-churn tables greater control over vacuum behavior.<\/span><\/li>\n
          • Recently, the <\/span>vacuum_truncate <\/b>You can choose whether PostgreSQL attempts to truncate empty pages at the end of a table file while vacuuming it. Turning off truncation, which requires an exclusive lock, gives you more control over vacuum behavior and helps avoid unnecessary locking on frequently visited databases.<\/span>
            \n<\/span>
            \n<\/span><\/li>\n<\/ul>\n

            \ud83d\udd04 <\/b>pg_upgrade<\/b> Improvements<\/b><\/h2>\n

            PostgreSQL 18 significantly improves the <\/span>pg_upgrade<\/span> experience\u2014reducing downtime and giving DBAs more control:<\/span><\/p>\n

              \n
            • Preserve Optimizer Statistics<\/b>
              \n<\/b> pg_upgrade<\/span> can now retain existing planner statistics (excluding extended statistics). This helps avoid slow query performance right after an upgrade. Use <\/span>–no-statistics<\/span> if you prefer to skip this step.<\/span><\/p>\n

              <\/span><\/li>\n

            • Parallel Database Checks<\/b>
              \n<\/b> Pre-upgrade compatibility checks across databases can now be parallelized using the existing <\/span>–jobs<\/span> option, speeding up validation in large clusters.<\/span><\/p>\n

              <\/span><\/li>\n

            • Fast Directory Swapping<\/b>
              \n<\/b> A new <\/span>–swap<\/span> option enables <\/span>pg_upgrade<\/span> to switch data directories directly instead of copying or linking files. This is potentially the fastest upgrade method\u2014ideal for large databases where speed matters.<\/span><\/p>\n

              <\/span><\/li>\n<\/ul>\n

              \ud83d\udce6 Backup Tool Improvements<\/b><\/h2>\n

              \ud83d\uddc2\ufe0f <\/b>pg_dump<\/b> Enhancements<\/b><\/h3>\n
                \n
              • Fine-Grained Control<\/b>: New options let you selectively dump:<\/span>\n

                <\/span><\/p>\n

                  \n
                • –with-data<\/span>: Dump only table data<\/span>\n

                  <\/span><\/li>\n

                • –with-schema<\/span>: Dump only schema definitions<\/span>\n

                  <\/span><\/li>\n

                • –with-statistics<\/span>: Include planner statistics<\/span>\n

                  <\/span><\/li>\n<\/ul>\n<\/li>\n

                • Exclusion Options<\/b>:<\/span>\n

                  <\/span><\/p>\n

                    \n
                  • –no-data<\/span>: Exclude data<\/span>\n

                    <\/span><\/li>\n

                  • –no-schema<\/span>: Exclude schema<\/span>\n

                    <\/span><\/li>\n

                  • –statistics-only<\/span>: Dump only statistics<\/span>\n

                    <\/span><\/li>\n

                  • –no-statistics<\/span>: Exclude statistics (useful for leaner dumps)<\/span>\n

                    <\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n

                    These allow precise control over what parts of a database you want to export.<\/span><\/p>\n

                    \ud83d\uddc3\ufe0f <\/b>pg_dumpall<\/b> Enhancements<\/b><\/h3>\n
                      \n
                    • Output Format Flexibility<\/b>:<\/span>
                      \n<\/span> pg_dumpall<\/span> can now export in the same output formats as <\/span>pg_dump<\/span>, such as directory or custom formats\u2014previously, it only supported plain text.<\/span><\/p>\n

                      <\/span><\/li>\n

                    • Compatibility with <\/b>pg_restore<\/b>:<\/span>
                      \n<\/span> Dumps created using the new formats can now be restored using <\/span>pg_restore<\/span>, streamlining the backup\/restore pipeline for entire clusters.<\/span><\/li>\n<\/ul>\n

                      \ud83e\udde0 Other Noteworthy Additions<\/b><\/h2>\n
                        \n
                      • Virtual Generated Columns<\/b>: Default behavior now supports virtual (read-computed) columns; <\/span>STORED<\/span> still supported when needed.<\/span>\n

                        <\/span><\/li>\n

                      • Extension Paths<\/b>: <\/span>extension_control_path<\/span> allows control files in custom locations.<\/span>\n

                        <\/span><\/li>\n

                      • File Copy Control<\/b>: <\/span>file_copy_method<\/span> defines how files are handled during CREATE DATABASE or tablespace changes.<\/span>
                        \n<\/span><\/li>\n<\/ul>\n

                        \u2705 Final Thoughts<\/b><\/h2>\n

                        It appears that PostgreSQL 18 will be a game-changer, particularly for teams that prioritize deep system observability, safe access, and performance optimization. Many of these features are developed enough to start testing against your staging environments, even if it’s still in beta.<\/span><\/p>\n

                        We discussed a high-level summary of the most intriguing additions in this piece. We’ll go into more detail about each innovation in the upcoming weeks, including asynchronous I\/O, improved logical replication, autovacuum optimization, and improved EXPLAIN analytics, along with use examples and performance advice.<\/span><\/p>\n

                        Stay tuned for more insights as we explore PostgreSQL 18 in depth.<\/strong><\/p>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"

                        PostgreSQL 18 , which is presently in Beta, offers a robust feature set that enhances security, replication, performance, and observability(PostgreSQL 18 Beta). The main improvements are already obvious\u2014and exciting\u2014even though the final GA (General Availability) release may fine-tune several elements. Here\u2019s a concise overview of what\u2019s coming: \u26a1 Performance Enhancements Asynchronous I\/O: A major milestone.…<\/p>\n","protected":false},"author":1,"featured_media":2887,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[115,116,120,227,228,231,268,272,275,305],"class_list":["post-2886","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-database-optimization","tag-database-performance","tag-database-security","tag-new-features","tag-observability","tag-open-source-database","tag-postgresql-18","tag-postgresql-beta","tag-postgresql-ga","tag-replication","category-28","description-off"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2886","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/comments?post=2886"}],"version-history":[{"count":0,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2886\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media\/2887"}],"wp:attachment":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media?parent=2886"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/categories?post=2886"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/tags?post=2886"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}