Introduction – Implicit Conversion

Oracle to PostgreSQL migration is a playground that uncovers and touches upon many database concepts, which are always intriguing and fun to explore. Implicit Conversion, i.e., imposing automatic conversion on data types to make them comparable by database optimizers, is also a concept frequently encountered in database migrations. Implicit conversion allows for the automatic conversion of data types for an expression or condition when necessary for SQL execution, thus preventing failures.

Implicit conversion can be a performance issue in many cases, but it’s still common to encounter it in databases due to various real-world constraints. In some instances, we might utilize function-based indexes to mitigate this issue as well. In this blog, we will explore the differences in implicit conversion between Oracle and PostgreSQL and discuss methods to mitigate it. Our focus is not on comparing which database engine makes ideal choices regarding implicit conversion, but rather on achieving similar functionality in PostgreSQL when needed, such as comparing two different data types with implicit conversions.

Oracle – Implicit Conversion Observations.

Let’s begin by creating some mock tables in Oracle to understand implicit conversion characteristics. In our examples, we will focus on equality comparison between VARCHAR or TEXT datatypes and NUMBER family data types.

create table tstimplicitconv (col1 number, col2 number(6), col3 float , 
col4 varchar2(100));
insert into tstimplicitconv values (1,1,1.1,'1');
commit;

explain plan for select 1 from tstimplicitconv where col1 = col4;
select * from table(dbms_xplan.display);

explain plan for select 1 from tstimplicitconv where col4 = col2;
select * from table(dbms_xplan.display);

Tabular representation of implicit conversion behavior in Oracle for equality operator.

1. Overview

In this blog post, we will guide you through the process of integrating IvorySQL, an open-source database built on PostgreSQL, into Neon Autoscaling Platform. Throughout this guide, we’ll walk you through each step, providing clear instructions and demonstrations.

2. What is IvorySQL

“IvorySQL is advanced, fully featured, open source Oracle compatible PostgreSQL with a firm commitment to always remain 100% compatible and a Drop-in replacement of the latest PostgreSQL. IvorySQL adds a “compatible_db” toggle switch to switch between Oracle and PostgreSQL compatibility modes. One of the highlights of IvorySQL is PL/iSQL procedural language that supports oracle’s PL/SQL syntax and Oracle style Packages.” [1]

The general architecture of IvorySQL is shown below. For more details, you can refer to here.

IvorySQL Key features:

• Powered by PostgreSQL
  Leveraging the robust foundation of PostgreSQL, IvorySQL inherits its comprehensive SQL capabilities, rock-solid reliability, and access to a vast ecosystem of tools and extensions.
  
  
• Oracle Compatibility
  IvorySQL introduces a unique “compatible_db” toggle switch, facilitating effortless transitions between Oracle and PostgreSQL compatibility modes. It supports Oracle’s PL/SQL syntax and includes Oracle-style Packages, making migration from Oracle databases a smooth process.
  
  
• Customization
  Users have the freedom to customize IvorySQL to suit their specific requirements. Simply download the source code and tailor it to your preferences.
  
  
• Open Source
  As an Apache 2 licensed project, IvorySQL is freely available for download and use, fostering a collaborative development environment.
  
  
• Open Community
  A creative and inclusive community that encourages collaboration and innovation.
  
  
• Backed by HighGo
  Supported by HighGo, a leading provider of PostgreSQL databases, IvorySQL benefits from expert guidance and support to ensure its con

Hacking on PostgreSQL is really hard. I think a lot of people would agree with this statement, not all for the same reasons. Some might point to the character of discourse on the mailing list, others to the shortage of patch reviewers, and others still to the difficulty of getting the attention of a committer, or of feeling like a hostage to some committer's whimsy. All of these are problems, but today I want to focus on the purely technical aspect of the problem: the extreme difficulty of writing reasonably correct patches.

Explore foundational parameters for maximizing the utility of PostgreSQL logs.

The post Logging Basics for PostgreSQL appeared first on Stormatics.

I wanted to write a “year-in-review” covering all the performance pgvector has made (with significant credit to Andrew Kane), highlighting specific areas where pgvector has improved (including one 150x improvement!) and areas where we can continue to do better.

A few weeks ago, I started outlining this post and began my data collection the data. While I was working on this over a two week period, no fewer than three competitive benchmarks against pgvector published. To me, this is a testament both how well pgvector is at handling vector workloads (and by extension, PostgreSQL too) that people are using it as the baseline to compare it to their vector search systems.

Some of these benchmarks did contain info that identified areas we can continue to improve both PostgreSQL and pgvector, but I was generally disappointed in the methodology used to make these comparisons. Of course I’d like to see pgvector perform well in benchmarks, but it’s important to position technologies fairly and be vocally self-critical on where your system can improve to build trust in what you’re building.

I have a separate blog post planned for how to best present benchmark studies between different systems for vector similarity search (it’s a topic I’m interested in). Today though, I want to compare pgvector against itself, and highlight areas it’s improved over the past year, and where the project can continue to go and grow.

How I ran these tests

An important aspect of any benchmark is transparency. First, I’ll discuss the test methodology I used, describe the test environment setup (instances, storage, database configuration), and then discuss the results. If you’re not interested in this part, you can skip ahead to “The 150x pgvector speedup”, but this information can help you with your own testing!

First, what are testing for? We’ll be looking at these specific attributes in these tests:

• Recall: A measurement of the relevancy of our results - what percentage of the expected results are returned dur

The PG Day Chicago 2024 is over. For the last two days, I have been reading my LinkedIn and X feeds, which are full of mentions of PG Day Chicago. Everyone is happy and thankful.

Although I am busy “liking” all of these posts—not just “liking” but truly liking them—I didn’t blog anything myself. Two days later, I finally organized my thoughts to talk about that event.

I want to start by saying a big thank you to everyone involved: organizers, speakers, sponsors, volunteers, and attendees! You were all essential to the conference’s success. And although there are definitely some things that I did wrong, I wanted to focus on things that I did for the first time his year and which, I hope, were done right.

• The best decision I made was inviting Karen Jex to be a CfP committee chair. Her support was incredible, and I am forever thankful to her! (Of course, she’ll have to be the CfP char again :)).

• We had fourteen student volunteers in addition to experienced staff, and everybody mentioned their excellent work
• Three participants received Travel scholarships from Pg.Us Diversity and Inclusion committee
• We distributed 6 vouchers from Postgres Women
• We were able to redistribute six sponsor’s vouchers which they were not going to use and pass them to students.
• … and all of the students were very thankful for the opportunity!

And that’s what makes me especially happy: the number of participants for whom it was the first Postgres conference ever and who recognized it as a unique opportunity to get to know the Postgres community, to be exposed to the incredible amount of knowledge, and to get inspired.

The recent discussion about AIX support in PostgreSQL (as of now removed in PostgreSQL 17) led me to look through the project’s history, to learn what platforms we have supported when.

In this context, “platform” really means operating system. One useful proxy is looking at the files in src/template/, because every supported platform needs to be listed there. There are other dimensions, such as what CPU architectures are supported. Historically, this was tied to the operating system (e.g., HP-UX/HPPA, Alpha/Tru64, AIX/PPC, Irix/MIPS), but nowadays, the combinations are more plentiful. CPU support in PostgreSQL might be the subject of a future article.

As usual, at some point in the distant past, this was all very murky and hard to decipher from the source code history, so let’s start with …

• PostgreSQL 6.5 (1999) was the first mention in the source code of support for building on Windows, using MinGW (essentially GCC). I wonder what you could do with this then? It’s listed in the release notes! But official full Windows support didn’t come until much later, so this must have been quite limited.

• PostgreSQL 7.1 (2001) added QNX, BeOS, and macOS (a.k.a. Darwin). With hindsight, one of these is not like the other!

At this point, PostgreSQL supported a formidable 22 platforms.

Also, the PostgreSQL build farm started in 2004. Over time, we have developed a quasi-policy that a platform needs to be represented in the build farm to be considered officially supported.

• PostgreSQL 8.0 (2005) added native Windows server. This completed the work started around PostgreSQL 6.5.

• PostgreSQL 8.2 (2006) removed QNX and BeOS. It added support for building for Windows using MSVC (Microsoft’s Visual C compiler suite).

• PostgreSQL 9.2 (2012) began the great purges. This release removed DG/UX, NeXTSTEP, SunOS 4 (pre-Solaris), SVR 4, Ultrix, Univel, and BSDi. For some of these, I don’t even remember what they were. Al

On April 23rd - after a very long break - the third PostgreSQL Meetup took place in Hamburg.

Sascha had temporarily taken over the Meetup group after the previous admins were no longer available. Last year at PGConf.DE 2023 I asked if there is anyone interested who would like to revive the group. Tobias had agreed to do so, and Lenz also joined the effort at the PGConf.EU 2023. After a few organizational details, the first (or rather third) meetup was scheduled.

Learn how to ensure PCI DSS compliance in your PostgreSQL database with these 7 crucial considerations.

The post 7 considerations for PCI DSS compliance in PostgreSQL appeared first on Stormatics.

There's a thread on hackers about recovering memory consumed by paths. A reference count is maintained in each path. Once paths are created for all the upper level relations that a given relation participates in, any unused paths, for which reference count is 0, are freed. This adds extra code and CPU cycles to traverse the paths, maintain reference counts and free the paths. Yet, the patch did not show any performance degradation. I was curious to know why. I ran a small experiment.

Experiment

I wrote an extension palloc_test which adds two SQL-callable functions palloc_pfree() and mem_context_free() written in C. Function definitions can be found here. The first function palloc's some memory and then pfree's it immediately. Other function just palloc's but never pfrees, assuming that the memory will be freed when the per-tuple memory context is freed. Both functions take the number of iterations and size of memory allocated in each iteration respectively as inputs. These functions return amount of time taken to execute the loop allocating memory. It appears that the first function spends CPU cycles to free memory and the second one doesn't. So the first one should be slower than the second one.

Results

The table below shows the amount of time reported by the respective functions to execute the loop as many times as the value in the first column, each iteration allocating 100 bytes. The figure shows the same as a plot. The time taken to finish the loop increases linearly for both the function indicating that the palloc logic is O(n) in terms of number of allocations. But the lines cross each other around 300K allocations.

In an era where data breaches are both costly and damaging to reputations, implementing best practices and robust security measures is crucial. PostgreSQL has emerged as one of the most successful and highly adopted open-source databases, competing with the enterprise standards of Oracle and SQL Server. Its increasing popularity is evident from DBEngine rankings, Stackoverflow […]

The post Announcing PGDSAT to satisfy CIS benchmarks for PostgreSQL appeared first on HexaCluster.

For those who have studied ERD-lore, it's not new that a relational database is very much like a graph. But it has taken SQL, more than 30 years since it became a standard and almost half a century since its inception to incorporate construct that will allow a DataBase to be treated as a Graph, DBaaG. This is surprising given that SQL was developed as language for relational databases which are modeled using ER diagrams. Better late than never. SQL/PGQ has arrived as 16th part of SQL:2023.

Entity Relationship Diagram, ERD in short, is a tool to model and visualize a database as entity types (which classify the things of interest) and relationships that can exist between them. Entity types and the relationships both map to relations in a Relational DataBase Management System (RDBMS in short). The rows in the relations represent entities (instances of entity types) and relationship between entities respectively. Fig. 1 below shows an ERD for a hypothetical shop.

This diagram very much looks like a graph with entity types represented as nodes and relationships represented by edges. That's exactly what SQL/PGQ is about. It adds language constructs to SQL to present underlying database as a "Property Graph". For example, property graph definition corresponding to the above ERD would look like

If you’re fascinated by the internals of PL/pgSQL processing and want to handle it efficiently, especially if you’re migrating procedural logic from another database, be sure to check out the previous posts in the series on PL/pgSQL Conversion Gotchas – Part 1 and Part 2

In Oracle, we used to build multiple functionalities within procedural blocks, either in functions or procedures. In these blogs, we will explore some of the gotchas that we need to take care of.

Migrating Functions with Out Parameters and Return Types

Oracle allows functions to have arguments defined as out parameters and also return additional status arguments or execution statuses as part of function execution. Though it is not best practice, when migrating legacy code bases, we encounter all types of scenarios.

Below is one such sample function with out params and return type.

create or replace function func_test1
(var1 in integer,var2 out integer )
return integer
as
begin
var2 := var1 + 1;
return var2*2;
end;

If we run it in Oracle, it will produce two values: one as an out parameter and another as a return value

set serveroutput on;
declare
  l_out_var2 integer; 
  l_out_ret integer;  
begin
  l_out_ret := func_test1(1,l_out_var2 );
  dbms_output.put_line('Out Value - ' || l_out_var2);
  dbms_output.put_line('Return Value - ' || l_out_ret);
end;
/
/

Let’s attempt to convert the sample function to PostgreSQL using the PL/pgSQL language. As a reference, we’ll aim to build similar code logic as in the Oracle sample, creating a function in PostgreSQL with PL/pgSQL that includes both an out parameter and a return type.

create or replace function func_test1
(var1 in integer,var2 out integer )
returns integer
language plpgsql
as
$$
begin
var2 := var1 + 1;
return var2*2;
end;
$$;

Let’s proceed with deploying it within the PostgreSQL database

In PostgreSQL, when there’s a requirement to return multiple values, we typically transform them into record types with attributes corresponding t

The material and the videos are now online!

PgTraining Free Online Event: Material Available

The past Friday, on April 19th, we did our fourth edition of the webinar dedicated entirely to PostgreSQL, provided by PgTraining.

As in the previous editions, we had three talks and an open discussion at the end. The talks (all in italian) were:

• Introduzione al linguaggio PL/Java (“An introduction to the PL/Java language”), from yours truly;
• PgVector - in R768 nessuno può sentirti urlare (“PgVector - in R768 nobody can hear you screaming”), by Chris Mair;
• Repliche logiche e migrazione di versione a caldo da PostgreSQL 12 a PostgreSQL 16 (“Logical replication and hot upgrade from PostgreSQL 12 to PostgreSQL 16”), by Enrico Pirozzi

The material is available on our Gitlab repository and such repository contains also links and material from the previous editions!

Some material is still under upgrading, so if not already there, it will appear any moment soon.

As promised in the CFP for POSETTE: An Event for Postgres 2024, all of the talk selection decisions were emailed out on April 17^th. Our talk selection work has now concluded, with the possible exception of accepting proposals from the Reserve list.

So what’s next? First I want to thank all of you Postgres people who submitted such amazing talk proposals into the CFP for POSETTE, now in its 3rd year. I was so impressed by the submissions and wish we could have accepted more of them.

And I also want to thank Alicja Kucharczyk, Daniel Gustafsson, and Melanie Plageman from POSETTE’s Talk Selection Team for contributing their time and expertise to collaborate with me to select the talks for this year’s virtual POSETTE event. It’s not easy to carefully read through and review 184 talk proposals—in just 8 days—to come up with the program for an event like #PosetteConf.

That’s right, 184 talk proposals—from 120 unique speakers. (The CFP had a maximum of 4 submissions per speaker.) With just 38 talks to accept this year, that means POSETTE 2024 has a ~20% talk acceptance rate. Bottom line, we had some difficult decisions to make.

So many great talk proposals we had to lengthen the POSETTE schedule to make space

The original POSETTE plan for 2024 was to have 4 livestreams with 9 talks each. The math looked like this:

• Each livestream would have:
  • 1 invited keynote—not selected through the CFP talk selection process, but rather an invited keynote speaker
  • 8 unique talks selected via the CFP process
• Hence, 36 talks total:
  • 32 talks selected via the CFP process + 4 unique keynotes

However, the best laid plans of mice and men and all that, we had to throw that math out the window. There were too many good talk proposals.

Luckily the talk production team led by Teresa Giacomini was able to rejigger their recording schedules to make room for 6 more talks.

So the final POSETTE 2024 schedule will have:

• 42 talks total: 38

PGDay UK 2024 will take place in London on September 11th at the Cavendish Conference Centre, London, UK.

Our Call for Papers and Call for Sponsors are now open.

We are accepting proposals for talks in English. Each session will last 50 minutes, and may be on any topic related to PostgreSQL. The submission deadline is 9th June 2024 23:59:59 BST. Selected speakers will be notified on or before July 1st, 2024. Please submit your proposals here:

https://2024.pgday.uk/call-for-papers/

The proposals will be considered by a committee who will produce a schedule to be published nearer the conference date. The members of the committee are listed on the website linked above. All selected speakers will get free entry to the conference.

Our Call for Sponsors is also open. Take your chance to present your services or products to the PostgreSQL community - or see it as a give-back opportunity. The benefactor sponsorship level also includes a free entrance ticket. Please head to:

https://2024.pgday.uk/become-sponsor/

for more details.

As usual, if you have any questions, don't hesitate to contact us at contact@pgday.uk.

We look forward to seeing you in London on September the 11th!

PGDay UK 2024 is a PostgreSQL Europe event run according to the PostgreSQL Community Conference Recognition programme. Local logistics and other services are provided by Slonik Enterprises Ltd. on a not-for-profit basis.

The first two articles in this series demonstrated how PostgreSQL is a capable tool for ELT – taking raw input and transforming it into usable data for querying and analyzing. We used sample data from the Advent of Code 2023 to demonstrate some of the ELT techniques in PostgreSQL.

In the first article, we discussed functions and features of SQL in PostgreSQL that can be used together to transform and analyze data. In the second article, we introduced Common Table Expressions (CTE) as a method to build a transformation query one step at a time to improve readability and debugging.

In this final article, I’ll tackle one last feature of SQL that allows us to process data in a loop, referencing previous iterations to perform complex calculations: Recursive CTE’s.

SQL is Set-based

SQL is primarily a set-based, declarative language. Using standard ANSII SQL and platform-specific functions, a SQL developer declares the desired outcome of a query, not the process by which the database should retrieve and process the data. The query planner typically uses statistics about the distribution of data to determine the best plan to get the desired result and return the full set of rows. While CTE’s and LATERAL joins make it feel like we can use the output of one query to impact another, those are always a one-shot opportunity. As a set-based language, it’s impossible to do algorithmic calculations, the ability to use the output of a query as input and control to another in a loop.

Stated another way, early versions of the SQL standard did not have procedural capabilities. To do that, most database platforms use their own superset of SQL that provides procedural capabilities. By default, this is T-SQL in SQL Server and pl/pgsql in PostgreSQL.

That changed with the SQL:1999 standard. With this new feature, implemented by all major databases, SQL became a Turing-complete language that can solve complex calculations in a single query.

Recursive Common Table Expressions (aka. Hierarchical Queries)

Sometimes it happens: I write something in a rush, and present it in a not-optimal way. And then I get advices!

Using PL/Java: need for clarifications

On January, I wrote an article about installing PL/Java on Rocky Linux, and about some of the difficulties I had in achieving a fully operational installation, even if I did not dig enough into the problems that I encountered.

Chapman Flack, the most active developer in the project at the moment, take the time to write to me a very detailed email with a lot of suggestions for improvements and providing corrections to some of the misconceptions I present in such an article.

I’m really glad to have received all those insights, and in order to spread the word, I’m writing here another article that, hopefully, fixes my mistakes. I’m not following the same order that Chapman presented them to me, since in my opinion some issues are much more important than others, so I present from the most important to the least one, according to me.

Editing the java.policy file

In my previous article, I advised readers to edit java.policy in the case there was a problem with Java permissions when executing PL/Java code. Despite the fact that I clearly stated that relaxing the permissions to all permissions was not a good idea, Chapman emphasized two main problems in my example: 1) I was editing the main policy file, therefore changing the policy rules for all the Java code, not only for PL/Java one; 2) adding java.security.AllPermission made no distinction between trusted and untrusted languages.

Chapman pointed out that PL/Java uses a customized policy file, that can be found in the PostgreSQL configuration directory, hence in $(pg_config --sysconfdir). This customizable configuration is available since PL/Java version 1.6, and is documented here in the section “Permissions available in sandboxed/unsandboxed PL/Java”. This file defines two main principals:

grant principal org.postgresql.pljava.PLPrincipal$Sandboxed * {
};


grant princ