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
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
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
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.
Amit Langote, Robert Haas
Dilip Kumar, Jesper Pedersen, Rajkumar Raghuwanshi
Amul Sul, David Rowley, Rafia Sabih, Simon Riggs, Thomas Munro
Antonin Houska, Ashutosh Bapat, Kyotaro Horiguchi
Á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
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
When working with database capacity planning, there are a lot of variables to consider, and Postgres is no different in this regard. One of the elements which requires management is storage. However, there's an aspect of storage that escapes inspection almost without exception, and it's hiding in the shadows between the columns themselves.
In most low-level computer languages like C, in which the venerable Postgres is written, data types are addressed by their maximum size, regardless of how large they actually are. Thus a standard 32-bit integer which can store a value of just over 2-billion, must be read as a whole unit. This means even the value of 0 requires 4 bytes of storage.
Further, Postgres is designed such that its own internal natural alignment is 8 bytes,
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
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
We do understand that IoT revolution is resulting in enormous amounts of data. With brisk data growth where data is mostly time series append-only, relational databases and DBAs have a rather tough task to store, maintain, archive and in some cases get rid of the old data in an efficient manner. In my previous posts, I talked about various strategies and techniques around better scalability for data storage using PostgreSQL and Postgres-BDR extension. Data retention is becoming ever so important. So let’s see what PostgreSQL 10 and above have to offer to efficiently manage your data retention needs.
PostgreSQL has supported time based partitioning in some form for quite some time. However, it wasn’t part of the core PostgreSQL. PostgreSQL 10 made a major improvement in this area by
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: