Wednesday, December 12

Tag: PostgreSQL

Webinar : Introduction to OmniDB [Follow Up]

2ndQuadrant, Liaqat's PlanetPostgreSQL, OmniDB
A database management tool that simplifies what is complex and drives performance. OmniDB is one such tool with which you can connect to several different databases - including PostgreSQL, Oracle, MySQL and others. 2ndQuadrant recently hosted a webinar on this very topic: Introduction to OmniDB. The webinar was presented by OmniDB co-founders and PostgreSQL consultants at 2ndQuadrant, Rafael Castro & William Ivanski. The recording of the webinar is now available here. Questions that Rafael and William couldn’t respond to during the live webinar have been answered below. Q1: There are other open source GUI tools around to manage PostgreSQL. Why are you investing efforts on a new tool? A1: When OmniDB was created we wanted a web tool, and not all available tools (more…)

Sequential UUID Generators

2ndQuadrant, Tomas' PlanetPostgreSQL
UUIDs are a popular identifier data type - they are unpredictable, and/or globally unique (or at least very unlikely to collide) and quite easy to generate. Traditional primary keys based on sequences won't give you any of that, which makes them unsuitable for public identifiers, and UUIDs solve that pretty naturally. But there are disadvantages too - they may make the access patterns much more random compared to traditional sequential identifiers, cause WAL write amplification etc. So let's look at an extension generating "sequential" UUIDs, and how it can reduce the negative consequences of using UUIDs. (more…)

OmniDB debugger for PostgreSQL 11

OmniDB, William's PlanetPostgreSQL
PostgreSQL 11 was released recently, with exciting new features. One of them is the ability to write SQL procedures that can perform full transaction management, enabling developers to create more advanced server-side applications. SQL procedures can be created using the CREATE PROCEDURE command and executed using the CALL command. Since OmniDB 2.3.0 it is possible to debug PostgreSQL PL/pgSQL functions. Support to PostgreSQL 11 functions and procedures was added in OmniDB 2.11.0. Last week we released OmniDB 2.12.0 with nice new features and a new revamped visual, so I'm going to show you how OmniDB 2.12.0 can debug PostgreSQL 11 procedures. First of all, if you have not done that already, download and install a binary PostgreSQL library called omnidb_plugin and enable it in (more…)

Webinar: PostgreSQL is NOT your traditional SQL database [Follow Up]

2ndQuadrant, Liaqat's PlanetPostgreSQL
PostgreSQL is referred to as "The world’s most advanced open source database" - but what does PostgreSQL have that other open source relational databases don't?   2ndQuadrant recently hosted a webinar on this very topic: PostgreSQL is NOT your traditional SQL database, presented by Gülçin Yıldırım Jelínek, Cloud Services Manager at 2ndQuadrant. The recording of the webinar is now available here. Questions that Gülçin couldn’t respond to during the live webinar have been answered below. Q1: What exactly is the role of postgresql for a marketplace like ebay or rakuten? A1: This question is not very clear. If the question is about whether Postgres can be used in an e-commerce website, the answer is yes.   Q2: I'm in process of switching from MS SQL (more…)

PostgreSQL 11: Partitioning Evolution from Postgres 9.6 to 11

2ndQuadrant, David's PlanetPostgreSQL
During the PostgreSQL 11 development cycle an impressive amount of work was done to improve table partitioning.  Table partitioning is a feature that has existed in PostgreSQL for quite a long time, but it really wasn't until version 10 that it started to become a highly useful feature. We'd previously claimed that table inheritance was our implementation of partitioning, which was true.  It just left you to do much of the work yourself manually. For example, during INSERTs, if you wanted the tuples to make it to your partitions then you had to set up triggers to do that for you. Inheritance partitioning was also slow and hard to develop additional features on top of. In PostgreSQL 10 we saw the birth of "Declarative Partitioning", a feature which is designed to solve many of the (more…)

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