Monday, February 18

PostgreSQL

Webinar: Banking on Postgres – Financial Application Considerations [Follow up]

Liaqat's PlanetPostgreSQL, PostgreSQL
The demand for PostgreSQL within the financial industry has been rapidly increasing in the recent years; mainly due to reduction in licensing costs, better choice of open source tools, and the robust enterprise features that PostgreSQL provides. 2ndQuadrant hosted the "Banking on Postgres" webinar to discuss attributes of financial databases based on Postgres, configuration processes, hardware needs, availability, backups, and more. The webinar was presented by Shaun Thomas, Principal Consultant at 2ndQuadrant. Those who weren’t able to attend the live event can now view the recording here. For any questions or comments regarding Postgres-BDR, please send an email to [email protected] (more…)

PostgreSQL 9.3 EOL – Why is it Important to Upgrade?

2ndQuadrant, Liaqat's PlanetPostgreSQL, PostgreSQL
After the final release of patch 9.3.25 on November 8th 2018, PostgreSQL 9.3 is no longer supported. Therefore it’s time for all users of PG 9.3 to upgrade their databases to a newer supported version. The benefits of having a supported version are many and that’s what Craig Ringer talks about in the Q&A session below: Why is it important to upgrade your PostgreSQL database to the latest version? Craig: Always update to the latest minor version. 2ndQuadrant's 24/7 support services often help customers who could've avoided experiencing a production outage or fault simply by updating every minor version or two. The PostgreSQL community (including 2ndQuadrant) releases minor point releases conservatively, and for good reasons. Keep up to date on those patches. Note: A "minor (more…)

[Video] Ansible and PostgreSQL

PostgreSQL, Tom's PlanetPostgreSQL
I don’t often get to speak on technical topics, but the video of my presentation below covers various concepts around "Ansible and PostgreSQL" - something I am very enthusiastic about. This presentation covers the following topics: Overview of Ansible and PostgreSQL Best strategies for mixed cloud and on-premises deployments How to deploy AlwaysOn PostgreSQL clusters How to perform maintenance updates How to create a variety of cluster types How to backup servers for mixed on-premises and multi-cloud deployments

Managing Freezing in PostgreSQL

Andrew's PlanetPostgreSQL, PostgreSQL
Postgres contains a moving event horizon, which is in effect about 2 billion transactions ahead of or behind the current transaction id. Transactions more than 2 billion ahead of or more than 2 billion behind the current transaction id are considered to be in the future, and will thus be invisible to current transactions. Postgres avoids this catastrophic data loss by specially marking old rows so that no matter where they are in relation to the current transaction id they will be visible. Freezing is this process of marking old live tuples (i.e. database rows) so that they don't get run over by the moving event horizon that would otherwise make them appear to be in the future. This is in contrast to vacuuming, which is the freeing up of space consumed by old dead tuples that are no (more…)

Webinar : New Features in PostgreSQL 11 [Follow Up]

Liaqat's PlanetPostgreSQL, PostgreSQL
PostgreSQL 11, the next major release of the world’s most advanced open source database, is just around the corner. The new release of PostgreSQL will include enhancements in partitioning, parallelism, SQL stored procedures and much more. To give PostgreSQL enthusiasts a deeper look into the upcoming release, 2ndQuadrant hosted a Webinar discussing the new features in PostgreSQL 11. The webinar was presented by Peter Eisentraut, Core Team Member and Major PostgreSQL Contributor. If you weren’t able to make it to the live session, you can now view the recording here. Questions that Peter couldn’t respond to during the live webinar have been answered below. Q: Could you use a custom hash function for partitioning? (or is this in future plans) A: This is currently not (more…)

PostgreSQL 11: Patch Reviewers for Partitioning Patches

Alvaro's PlanetPostgreSQL, PostgreSQL
We seldom credit patch reviewers. I decided to pay a little homage to those silent heroes for a few of them: here's the list of people who were credited as having reviewed the patches mentioned in my previous article for PostgreSQL 11. The number in front is the number of times they were credited as reviewers. 7 Amit Langote, Robert Haas 5 Dilip Kumar, Jesper Pedersen, Rajkumar Raghuwanshi 4 Peter Eisentraut 3 Amul Sul, David Rowley, Rafia Sabih, Simon Riggs, Thomas Munro 2 Antonin Houska, Ashutosh Bapat, Kyotaro Horiguchi 1 Álvaro Herrera, Amit Kapila, Amit Khandekar, Etsuro Fujita, Jaime Casanova, Keith Fiske, Konstantin Knizhnik, Pascal Legrand, Pavan Deolasee, Rajkumar Raghuanshi, Rushabh Lathia, Sven Kunze, Thom Brown, Yugo Nagata (more…)

[Video] Power of Indexing in PostgreSQL

Pavan's PlanetPostgreSQL, PostgreSQL
The video of my presentation below walks you through 'Indexing in PostgreSQL' - a key component to database performance. This presentation covers the following topics: Various ways to access data in PostgreSQL Different types of indexes supported by PostgreSQL Internals of BTree and BRIN indexes Overview of GIN and GiST indexes How to find missing indexes How to find unused indexes

Partitioning Improvements in PostgreSQL 11

Alvaro's PlanetPostgreSQL, PostgreSQL
A partitioning system in PostgreSQL was first added in PostgreSQL 8.1 by 2ndQuadrant founder Simon Riggs. It was based on relation inheritance and used a novel technique to exclude tables from being scanned by a query, called “constraint exclusion”. While it was a huge step forward at the time, it is nowadays seen as cumbersome to use as well as slow, and thus needing replacement. In version 10, it was replaced thanks to heroic efforts by Amit Langote with modern-style “declarative partitioning”. This new tech meant you no longer needed to write code manually to route tuples to their correct partitions, and no longer needed to manually declare correct constraints for each partition: the system did those things automatically for you. Sadly, in PostgreSQL 10 that's pretty much (more…)

Upgrading to PostgreSQL 11 with Logical Replication

2ndQuadrant, Eisentraut's PlanetPostgreSQL, PostgreSQL
It's time. About a year ago, we published PostgreSQL 10 with support for native logical replication. One of the uses of logical replication is to allow low- or no-downtime upgrading between PostgreSQL major versions. Until now, PostgreSQL 10 was the only PostgreSQL release with native logical replication, so there weren't many opportunities for upgrading in this way. (Logical replication can also be used for moving data between instances on different operating systems or CPU architectures or with different low-level configuration settings such as block size or locale -- sidegrading if you will.) Now that PostgreSQL 11 is near, there will be more reasons to make use of this functionality. Let's first compare the three main ways to upgrade a PostgreSQL installation: pg_dump and (more…)

[Video] Data Integration with PostgreSQL

PostgreSQL, Shaun's PlanetPostgreSQL
Just in case you missed the live broadcast, the video of my presentation below covers various topics around integration of PostgreSQL with other data sources and database technologies. This presentation covers the following topics: What is a Foreign Data Wrapper? How to query MySQL, a Flat file, a Python script, a REST interface and a different Postgres Node Perform all of the above simultaneously Take snapshots of this data for fast access Tweak remote systems for better performance Package as an API for distribution Stream to Kafka Write data to... MongoDB!? What does all of this have in common? It's an exciting topic, and I hope more developers and admins begin to see Postgres as the global integration system it really is (more…)