Highlights from PASS Data Community Summit 2023

In November 2023, we had another great in-person event with the chance to catch up with people we met in last year's show and make lots of new acquaintances. We got an opportunity to see all of the new presentations, as well as demonstrate our latest release of StandbyMP with newly added support for PostgreSQL and, here is a summary of my key learnings for DBAs.  

While there was no new release of SQL Server this year, there were certainly lots of valuable sessions, particularly in the area of DevOps, as well as an entirely new stream focused on PostgreSQL - owing to its increased popularity within many organizations. Lots of great content to absorb.

The keynote session from Redgate touched on the trends and shifts they are seeing in the market. Redgate’s message was not surprisingly in line with what we have been focused on for several years now:

• Multiple database platforms within a single organization.

• Hybrid deployments - cloud and on-premises.

• Database DevOps - automating and monitoring end-to-end deployments.

Below are my top highlights and takeaways from the sessions I attended and conversations had during the few days in Seattle.

Overcoming PostgreSQL Knowledge Gaps

This year's summit added a new stream focused on PostgreSQL. This was an excellent addition to the summit, given the growth of companies using PostgreSQL as a business-critical database. 

For the SQL Server DBAs attending these sessions, there was a very high percentage that were expecting to use PostgreSQL within their environment in the near future.  While this trend makes sense and is not a surprise, the challenge it provides to DBAs and IT Managers is one of how to efficiently expand their oversight and skills to manage a completely new database engine - one that is quite different from SQL Server. In a lot of cases, new resources will not be onboarded to support PostgreSQL, but rather the existing SQL Server DBAs will need to expand their skills.

Christophe Pettus of PGX gave a great overview of the key improvements in PostgreSQL 16. Here are some of the notable improvements. You can view Christophe’s full presentation at the San Fran Bay Area PostgreSQL users group on Youtube. 

More SQL/JSON Syntax and Usability Improve Development

• More Syntax from SQL/JSON standards such as JSON_ARRAY(), IS JSON, etc has been implemented making it easier to work with JSON.
• Underscores for thousands of separators and non-decimal integer literals including hex, octal and bits can be used. 

Performance Enhancement 

• Under the hood changes - Lots of invisible things especially faster memory management, and SIMD CPU acceleration for x86 and ARM architectures. 
• More Parallelism - This has been a common theme for the last few releases but in query planner can parallelize FULL and RIGHT joins, utilize incremental sorts for SELECT DISTINCT QUERIES, and optimize window functions. 
• Libpq load balancing - The standard C library and what clients link to get an upgrade by enabling a new option to connect to hosts in a random order rather than for simple but effective load balancing. 

Benefits for System Administrators

• pg_hba.conf / pg_ident.conf changes improve access control and security. 
  • Enables regex to match user and database names such as giving users who start with dev and dev databases. “Host /^dev/^dev 10.0.0.0/24 scram-sha-256
  • The client authentication config file pg_hba.conf now enables the use of standard Linux Include / include_if_exists / include_dir operations so you can include diravates for external configuration files.

Improved Monitoring and System Statistics

• New views that keep track of more detailed I/O statistics have been introduced with pg_stat_io to help the tuning of database workloads.
• PG 16 also now keeps track of each index scan and sequential scan of each relation in  pg_stat_all_tables.

However, as a DBA it's also important not to get too focused on the shiny new capabilities. I'd like to point out that a critical area that should be addressed early on is how your database recovery plan is correctly implemented to accommodate PostgreSQL. This is where Dbvisit StandbyMP has uniquely positioned itself - helping companies manage disaster recovery across multiple different database platforms (Oracle SE, SQL Server and PostgreSQL), through a single interface that provides consistency in operations regardless of database and also can shield the complexity from the DBA. This allows SQL Server DBAs to easily incorporate PostgreSQL into their DR strategy without needing to be an extremely experienced PostgreSQL DBA.  

If you are interested in reading more about how Dbvisit Standby MP can help manage DR across multiple database platforms in our PostgreSQL launch blog. 

DevOps - Level up Observability / Monitoring

Another stream that is getting increased attention these days is database DevOps. There were several good sessions talking about automating the end-to-end aspects of database deployments - code testing, deployments, schema management, test/QA environments, etc.

The interesting aspect this year was around the observability and monitoring component.  As developers and DBAs look to incorporate more DevOps aspects into their application and database landscape, ensuring that better observability and monitoring capabilities are also included is key to support.  The days of only having cryptic debug or error messages, or application stack traces are (hopefully) coming to an end. Trying to [a] read these cryptic log messages and [b] figure out what was happening at the time has always been a challenging task that tends to prolong the resolution times. 

The DevOps observability thread was focused on providing logging and monitoring capabilities that will allow support staff (and ultimately developers) to see what is going on at any point in time, as well as where things are happening. e.g. is my UI response time slow because of the database query, application logic or network latency/bandwidth.  

I agree with the importance of this, and it has been a huge focus of our development to take Disaster Recovery maintenance and actions from a black art to simple business decisions conveyed in common-sense language.  StandbyMP does this by providing detailed real-time information and notifications about what is happening with the standby databases, such as the current lag time between primary and standby, the state of both database platforms, etc.  This ensures that if there are any issues in the environment, such as network outages, you can see and resolve them proactively. The last thing you want to find out when you need to failover to a standby database is that it is not up-to-date due to network issues, or disk space challenges. 

[Image of StandbyMP providing a real-time overview of database status]

Many Dbvisit customers are already utilizing our CLI to centralize deployment and monitoring in their internal systems.  But excitingly in a few months, we will be launching a new common CLI across SQL Server, PostgreSQL and Oracle to enable customers to get the full value of a multi-platform architecture and more easily integrate StandbyMP into their internal deployment and monitoring systems. 

With organizations now incorporating multiple database platforms, it increases the operations and management complexity. Having a product, like Dbvisit StandbyMP, to help automate aspects such as disaster recovery will reduce the burden (and stress) on DBA resources. To learn more, check out our website and technical data sheet. 

Alternatively, if you would like to organize a POC or have any questions you'd like answered over a quick call, please contact us. 

[The Dbvisit crew at the PASS Data Summit]