Neues vom PostgreSQL Planet

Neues vom PostgreSQL Planet Feed abonnieren
Planet PostgreSQL
Aktualisiert: vor 1 Stunde 30 Minuten

Achilleas Mantzios: PostgreSQL Anonymization On Demand

24. Juni 2020 - 20:04

Before, during, and after the GDPR came into town in 2018, there have been many ideas to solve the problem of deleting or hiding user data, using various layers of the software stack but also using various approaches (hard deletion, soft deletion, anonymization). Anonymization has been one of them which is known to be popular among the PostgreSQL-based organizations/companies.

Leigh Halliday: Advanced Active Record: Using Subqueries in Rails

24. Juni 2020 - 14:00
Active Record provides a great balance between the ability to perform simple queries simply, and also the ability to access the raw SQL sometimes required to get our jobs done. In this article, we will see a number of real-life examples of business needs that may arise at our jobs. They will come in the form of a request for data from someone else at the company, where we will first translate the request into SQL, and then into the Rails code necessary to find those records. We will be covering…

Kirk Roybal: Oracle to PostgreSQL: Reasons to Migrate

23. Juni 2020 - 23:09
PostgreSQL has quite a few compelling advantages, some of which are not found in any other database management system. This article will outline the ones that are most interesting to the Oracle developer seeking to do a migration or investigate alternatives for new development. To prevent this article from becoming another list of PostgreSQL features, it […]

Douglas Hunley: tuned, PG, and you

23. Juni 2020 - 20:22

We’ve had a small flurry of customers asking about tuning their OS for the best PostgreSQL performance. While the answer to this question is always ‘that depends on your hardware and workload’ and involves a lot of iteration between changing a setting and benchmarking, I thought I’d take a moment to point out that once you do manage to dial-in the settings, you should be writing a profile and deploying to your systems for tuned to make use of. Please, for the love of $diety, stop editing sysctl.conf and friends!

Amit Khandekar: Leveraging SIMD Vectorization

23. Juni 2020 - 19:23
With the advent of column store databases, there was an urge to make use of SIMD vector processing. It naturally fits into the way table data is arranged. Let's first briefly check what is SIMD. It stands for Single Instruction Multiple Data. Today, CPU instructions support this kind of mechanism where the same instruction can be executed simultaneously on multiple data elements. E.g. Say, you want to double all the column values. Or remove the red component of the RGB values of pixels of an image. For large data, these operations are CPU bottlenecks.

Hans-Juergen Schoenig: PostgreSQL: ltree vs. WITH RECURSIVE

23. Juni 2020 - 10:30

After my last post about ltree and recursive data in PostgreSQL people have asked me privately about performance issues. To share this information, I decided to come up with a follow up post to discuss this topic in a bit more detail. WITH RECURSIVE in PostgreSQL is efficient. However, ltree does have its strengths as well. Let us take a closer look …

Preparing sample data

In preparation for a test I have created a table which contains a balanced tree:

Bruce Momjian: Force One Row

22. Juni 2020 - 16:23

How can you force a table to have at most one row? It is actually very easy by creating a unique expression index on a constant, with no column name references:

Andreas 'ads' Scherbaum: Simon Riggs

22. Juni 2020 - 15:00
PostgreSQL Person of the Week Interview with Simon Riggs: I live in the UK with my wife, and spend a lot of time with my extended family, though “kids” have now grown up and left home. Born a Londoner, so my favourite drink is Tea, but I’m partly Irish with a small hint of Chilean blood.

Asif Rehman: Types of Indexes in PostgreSQL

22. Juni 2020 - 10:40

Finding relevant information quickly speeds up performance. For example, while reading a book in which you have to find a topic that you would like to read, if you know that it is in a certain chapter then you will simply go to that chapter, perhaps look through it and start reading the desired topic.

Shaun M. Thomas: PG Phriday: 10 Things Postgres Could Improve – Part 2

19. Juni 2020 - 22:00
Last week, we examined Postgres XID wraparound complications in greater depth to see exactly how easily they can surprise even prepared enterprises. But we also found the real problem areas and how to mitigate them specifically. In this continuing series to plumb the depths of Postgres constructively, we’re going to focus on some of the […]

Bruce Momjian: Storing Binary Data in the Database

19. Juni 2020 - 17:00

There are some very good responses in an email thread about whether to store binary data in Postgres or externally. The binary storage options discussed were:

cary huang: An Overview of PostgreSQL Backend Architecture

18. Juni 2020 - 23:41

1. Introduction

PostgreSQL backend is a collection of processes forked from the main process called Postmaster. Each forked process has different roles and responsibilities in the backend. This article describes the responsibility of core backend processes that power the PostgreSQL system as we know it today. The overall PostgreSQL backend architecture can be illustrated by the image below:

Hans-Juergen Schoenig: SQL trickery: Hypothetical aggregates

18. Juni 2020 - 9:30

“If we had this data what would it mean?” – these kinds of questions can be answered using plain SQL. The technique you will need in PostgreSQL is a “hypothetical aggregate” which is of course part of the ANSI SQL standard. This post will show what an hypothetical aggregate is good for and how it works.

Luca Ferrari: ORA-2449 and the Constraint Dependencies

18. Juni 2020 - 2:00

What happens if you try to drop a table that is referenced by another table?

ORA-2449 and the Constraint Dependencies

Oracle clients seems somehow a little goofy when you have to deal with dependencies.
Imagine you have two tables, a that references table b; you can generate the tables as follows:

Bruce Momjian: Dinner Q&A

17. Juni 2020 - 17:30

My employer, EnterpriseDB, has been organizing events where potential customers and interested people can ask me questions while enjoying a meal. I thought the idea was strange, but I have done it ten times, and they have gone very well. The Q&A portion usually lasts one hour and forty-five minutes.

Kirk Roybal: Oracle to PostgreSQL: Basic Architecture

17. Juni 2020 - 17:17
This article provides the Oracle database administrator with equivalent PostgreSQL architecture knowledge. The process is a bit loose, but it is sufficient to bootstrap the concepts that are transferable and identify the ones that are not.

Luca Ferrari: PostgreSQL 11 Server Side Programming Errata Corrige

17. Juni 2020 - 2:00

A reader provided us a feedback about a wrong listing.

PostgreSQL 11 Server Side Programming Errata Corrige

I have already written about how my first book on PostgreSQL, named PostgreSQL 11 Server Side Programming Quick Start Guide, gained more attention.

Haroon .: RESTful CRUD API using PostgreSQL and Spring Boot – Part 2

16. Juni 2020 - 16:56
Overview This article is an extended version atop of the previous article which was a kickstart for building an application using Spring Boot and PostgreSQL. There is no internal feature supported by Java which offers mapping between class objects and database tables; therefore, we use Object Relational Model (ORM). JPA is an abstraction above JDBC […]

Marcelo Diaz: Wrapping Db2 with PostgreSQL

16. Juni 2020 - 9:30

Since SQL/MED (Management External Data) was implemented in PostgreSQL, hundreds of projects have emerged that try to connect PostgreSQL with other data sources. Just by doing a simple search on GitHub with the keys “postgres” + “fdw” you can figure that out.