Wednesday, October 24

Tag: PostgreSQL

Adding new table columns with default values in PostgreSQL 11

2ndQuadrant, Andrew's PlanetPostgreSQL
In PostgreSQL version 10 or less, if you add a new column to a table without specifying a default value then no change is made to the actual values stored. Any existing row will just fill in a NULL for that column. But if you specify a default value, the entire table gets rewritten with the default value filled in on every row. That rewriting behavior changes in PostgreSQL 11. In a new feature I worked on and committed, the default value is just stored in the catalog and used where needed in rows existing at the time the change was made. New rows, and new versions of existing rows, are written with the default value in place, as happens now. Any row that doesn't have that column must have existed before the table change was made, and uses this value stored in the catalog when the row is (more…)

PGDay Down Under 2018

2ndQuadrant, Andrea's Planet PostgreSQL
After the success of last year's event, the second PGDay held in Australia, we're back this year with PGDay Down Under. The name “Down Under” refers to Australia and New Zealand, due to the fact these countries are located in the lower latitudes of the southern hemisphere. The conference is a one-day community event organized by the newborn PostgreSQL Down Under Incorporated (also known as PGDU), a not-for-profit association established to support the growth and learning of PostgreSQL, the world’s most advanced open source database, in Australia and New Zealand. PGDay Down Under aims to satisfy a large audience of PostgreSQL users and enthusiasts by selecting a wide range of talks and presentations that are of interest to: Database administrators that are already using (more…)

PG Phriday: Studying Stored Procedures in Postgres 11

2ndQuadrant, Shaun's PlanetPostgreSQL
Studying Stored Procs in Postgres 11 With Postgres 11 looming on the near horizon, it's only appropriate to check out a recent beta and kick the tires a few times. Whether it's improvements in parallelism, partitions, stored procedures, JIT functionality, or any number of elements in the release page, there's a lot to investigate. It just so happens that I ran across a fortuitous event on Twitter when deciding on an appropriate topic. Behold! Wait! No! That's not what stored procedures are for! I felt so good like anything was possible When confronted with such a blatant corruption of such a cool new feature, it's only natural to question the wisdom of doing so. It is, after all, not a great idea to programatically consume transaction IDs. I said as much and moved on with life (more…)

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…)

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

Webinar : Database Security in PostgreSQL [Follow Up]

Liaqat's PlanetPostgreSQL, Webinars
Database security is an increasingly critical topic for any business handling personal data. Data breach can have serious ramifications for an organization, especially if the proper security protocols are not in place. There are many ways to harden your database. As an example PostgreSQL addresses security using firewalls, encryption and authentication levels among other ways. 2ndQuadrant hosted a webinar on Database Security in PostgreSQL to highlight security concepts, features and architecture. The webinar was presented by Kirk Roybal, Principal Consultant at 2ndQuadrant - the recording is now available here. Some of the questions that Kirk responded to are listed below: Q1: What are your thoughts on performance of row-level security vs. doing that filtering via WHERE at (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…)

PostgreSQL installer by 2ndQuadrant – now supports OmniDB!

2ndQuadrant, Marriya's PlanetPostgreSQL, OmniDB, PGInstaller
PostgreSQL installer or PGInstaller - is a user-friendly, graphical installation and configuration tool for PostgreSQL. With just a few clicks you can install PostgreSQL - version 9.5, 9.6, 10 and 11(beta) - on Windows, Linux and macOS. The latest PGInstaller release includes support for OmniDB - an interactive and user-friendly database management tool to manage multiple databases in a unified workspace. Using utilities bundled with PostgreSQL, the only means to connect to the database is via psql. Psql works via the command line, which can be fairly tricky for new users especially if they are migrating from another database and are not used to the interface. PGInstaller makes the connection process easier with OmniDB. PGInstaller comes bundled with OmniDB, as an optional (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…)