Neues vom PostgreSQL Planet

Neues vom PostgreSQL Planet Feed abonnieren
Planet PostgreSQL
Aktualisiert: vor 29 Minuten 46 Sekunden

Christophe Pettus: OK, sometimes you can lock tables.

16. Januar 2023 - 8:00

Previously, I wrote that you should never lock tables. And you usually shouldn’t! But sometimes, there’s a good reason to. Here’s one.

When you are doing a schema-modifying operation, like adding a column to a table, PostgreSQL needs to take an ACCESS EXCLUSIVE lock on the table while it is modifying the system catalogs. Unless it needs to rewrite the table, this lock isn’t held for very long.

Christophe Pettus: How slow is DECIMAL, anyway?

15. Januar 2023 - 8:00

In PostgreSQL, NUMERIC is a variable length type of fixed precision. You can have as many digits as you want (and you want to pay the storage for). DOUBLE PRECISION is a floating point type, with variable precision.

Sometimes, the question comes up: How much slower is NUMERIC than DOUBLE PRECISION, anyway?

Here’s a quick, highly unscientific benchmark:

Andrew Dunstan: Announcing Release 16 of the PostgreSQL Buildfarm client

14. Januar 2023 - 2:21

 

Hot on the heels of Release 15 comes Release 16.

This release deals with some issues that have been discovered with the check for update feature of Release 15 and the force_every and trigger_exclude features, so that it now works correctly with those features.

It also features these items:

Greg Sabino Mullane: How to Solve Advent of Code 2022 Using Postgres - Day 12

13. Januar 2023 - 19:30
Spoiler Alert!

This article will contain spoilers both on how I solved 2022 Day 12's challenge "Hill Climbing Algorithm" using SQL, as well as general ideas on how to approach the problem. I recommend trying to solve it yourself first, using your favorite language. This article is delayed from the actual puzzle's release. Also note that my solutions are seldom going to be the "best" solutions - they were solved as quickly as possible, and these articles will show my first solutions, with some minor reformatting and cleaning up.

Akhil Reddy Banappagari: Avoiding Constraint violations while migrating Oracle to PostgreSQL – DATE data type

13. Januar 2023 - 8:46

Choosing a correct datatype mapping while migrating from Oracle to PostgreSQL is very important to avoid migration failures. Especially when we have date and time involved, it is very important to understand the behavior in both Oracle and PostgreSQL. We have documented about DATE datatype in Oracle vs SQL Server vs PostgreSQL in our previous article.

Bruce Momjian: Beyond Joins and Indexes

12. Januar 2023 - 21:00

My presentation Explaining the Postgres Query Optimizer covers the details of query optimization, optimizer statistics, joins, and indexes. I have delivered it 20 times since 2011.

Tobias Petry: RETURNING Modified Rows

12. Januar 2023 - 16:43
Many maintenance operations are based on finding particular rows, processing them (e.g. sending an email) and finally applying changes to the database. Typically this is implemented as one query to find all rows and many subsequent queries to modify them. This workflow can sometimes be simplified by using the RETURNING feature to manipulate data and get the resulting rows in one step.

Jacob Coblentz: Fun with Letters in PostGIS 3.3!

12. Januar 2023 - 16:00

Working at Crunchy Data on the spatial team, I'm always looking for new features and fun things to show on live demos. I recently started playing around with ST_Letters and wanted to jot down some quick code samples for playing around with this feature, introduced in PostGIS 3.3. These examples are super easy to use, they don't need any data!

The screenshots shown below came from pgAdmin's geometry viewer and will also work with other query GUI tools like QGIS or DBeaver.

Henrietta Dombrovskaya: PGSQL Phriday #004 Recap

12. Januar 2023 - 4:26

Thank you to everybody who contributed to the January PGSQL Phriday topic! I suggested “Postgres and Software Development,” while being very well aware that this topic is not popular in the PostgreSQL community. To be completely honest, I thought that it was possible that only Ryan Booz would contribute! 

That being said – a big thank you to everyone who participated! Please find a summary of contributions below, and please let me know if I missed your post!

Luca Ferrari: From Numbers to Words using Perl (and Lingua::)!

12. Januar 2023 - 1:00

How to convert a digit into a sentence with the power of Perl.

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 16 – Invent random_normal() to provide normally-distributed random numbers.

11. Januar 2023 - 17:19
On 9th of January 2023, Tom Lane committed patch: Invent random_normal() to provide normally-distributed random numbers.   There is already a version of this in contrib/tablefunc, but it seems sufficiently widely useful to justify having it in core.   Paul Ramsey   Discussion: https://postgr.es/m/CACowWR0DqHAvOKUCNxTrASFkWsDLqKMd6WiXvVvaWg4pV1BMnQ@mail.gmail.com Description tells is all, but let's see how that works.

Chris Travers: A PostgreSQL Developer's Perspective: Six Interesting Patches From November's Commitfest

11. Januar 2023 - 15:30
🐘 The PostgreSQL community organizes patch reviews into “commitfests” which last for a month at a time, every other month. In this series, our very own PostgreSQL developer advocate and expert, Chris Travers, will discuss a few patches that may be of interest to PostgreSQL users after each commitfest.

Ibrar Ahmed: Upgrading PostgreSQL Extensions

11. Januar 2023 - 14:12

PostgreSQL is a powerful and flexible open-source database management system that allows users to install and use extensions to add additional functionality to their databases. In this tutorial, we will cover the process of installing and upgrading PostgreSQL extensions using the example of the pg_stat_monitor extension.

Peter Eisentraut: PostgreSQL largest commits

11. Januar 2023 - 6:00

I like to poke around the PostgreSQL Git repository to find interesting statistics and trends that affect PostgreSQL development. The question I had lately is, what are the largest patches that have been committed? That might indicate some kind of upper bound on the size of features you can get committed in one piece. If something is larger, you might need to split it up.

Akhil Reddy Banappagari: Row level and Column level Security – Oracle vs PostgreSQL

10. Januar 2023 - 20:14

We often get interesting questions from Customers before starting migrations from Oracle to PostgreSQL or SQL Server to PostgreSQL. One of such questions is regarding the Oracle or SQL Server alternatives for security beyond the object level.

Francesco Tisiot: How to JSON in PostgreSQL®

10. Januar 2023 - 16:07

This series covers how to solve common problems on JSON datasets with PostgreSQL® and it includes (links will appear once the target pages are up):

Laurenz Albe: Pagination and the problem of the total result count

10. Januar 2023 - 11:00


© Laurenz Albe 2022

When processing a big result set in an interactive application, you want to paginate the result set, that is, show it page by page. Everybody is familiar with that from the first web search on. You also get a button to scroll to the next page, and you get a total result count. This article shows the various options for pagination of a result set and their performance. It also discusses the problem of the total result count.

Grant Fritchey: Index Types in PostgreSQL: Learning PostgreSQL with Grant

10. Januar 2023 - 0:59

As with any other relational data management system (RDBMS), PostgreSQL uses indexes as a mechanism to improve data access. PostgreSQL has a number of different index types, supporting different behaviors and different types of data. In addition, again, similar to other RDBMS, there are properties and behaviors associated with these indexes.

Alexander Korotkov: Rethinking buffer mapping for modern hardware architectures

9. Januar 2023 - 23:00
Original Design Implications

Traditional database engines were designed in the ’80s and early ’90s. At that time CPUs were much slower than they are today. Even worse was storage, hard drive head positioning time was enormous [1]. And CPU (or, at most, a few single-core CPUs) was assumed to be infinitely fast in comparison to IOPS. Therefore, systems were designed to save IOPS as much as possible, while CPU overhead was considered a secondary optimization target.

Greg Sabino Mullane: How to Solve Advent of Code 2022 Using Postgres - Day 11

9. Januar 2023 - 21:00
Spoiler Alert!

This article will contain spoilers both on how I solved 2022 Day 11's challenge "Monkey in the Middle" using SQL, as well as general ideas on how to approach the problem. I recommend trying to solve it yourself first, using your favorite language. This article is delayed from the actual puzzle's release. Also note that my solutions are seldom going to be the "best" solutions - they were solved as quickly as possible, and these articles will show my first solutions, with some minor reformatting and cleaning up.

Seiten