Neues vom PostgreSQL Planet
SQL and especially PostgreSQL provide a nice set of general purpose data types you can use to model your data. However, what if you want to store fewer generic data? What if you want to have more advanced server side check constraints? The way to do that in SQL and in PostgreSQL in particular is to use CREATE DOMAIN.
This blog will show you how to create useful new data types which are commonly needed in many applications.
Image from Flickr user fsse8info
Recently the topic of generating random-looking coupon codes and other strings came up on internal chat. My go-to for something like that is always this solution based on Feistel networks, which I didn’t think was terribly obscure. But I was surprised when nobody else seemed to recognize it, so maybe it is. In any case here’s a little illustration of the thing in action.
Postgres indexes can only be defined on single tables. Why would you want to have indexes that reference multiple tables, i.e., global indexes?
Recently we had some clients who had the desire to store timeseries in PostgreSQL. One of the questions, which seems to interest people in this area, is related to calculating the difference between values in timeseries data. How can one calculate the difference between the current and the previous row?
To answer this question I have decided to share some simple queries outlining what can be done. Note that this is not a complete tutorial about analytics and windowing functions but just a short introduction to what can be done in general.
When working with large tables, even simple actions can have high costs to complete. What queries are acceptable for smaller tables can often be less than ideal when applied to large tables, so your specific choice of approach to a given problem becomes more important.
There is a long history of hardware acceleration, i.e., hardware modules helping the CPU. There was the 80287 math coprocessor, sound cards, and video cards. The computer industry is constantly moving things from the CPU to the motherboard and external cards, and back again. Movement is mostly determined by whether the CPU is able to efficiently perform the task, the transfer bandwidth needed to perform the task, and the flexibility of replaceable external cards.
Computer tasks are one of the most precise activities we do on a daily basis. Driving, cooking, walking, and reading are fairly imprecise compared to computer interaction.
Computers represent symbols like "a" and "A" precisely and require external facilities to define relationships between them. This email thread makes a convincing argument that you usually want case-preserving, but less-precise case-insensitive behavior.
Today’s blog post is going to be a nice little adventure of learning how to use composite primary keys in a PostgreSQL many-to-many relationship table while building a Django application. Along the way we will talk about some basics of Django and some workarounds you need to use. Let’s dig in and get started.
Semab Tariq: How to use Neural Network Machine Learning model with 2UDA – PostgreSQL and Orange (Part 7)
It has been a while since I have written about the new features in a major pgpool II release. Well pgpool II 4.2 is in the works and the plan is to release it towards the end of this year. As usual every major release of pgpool II is compatible with the parser of latest PG release so pgpool II 4.2 will be compatible with PG-13 parser. There are many nice features that will be part of next major release however the ones that particularly catches my attention is supporting LDAP authentication with pgpool II.
© Laurenz Albe 2020
There are three join strategies in PostgreSQL that work quite differently. If PostgreSQL chooses the wrong strategy, query performance can suffer a lot. This article explains the join strategies, how you can support them with indexes, what can go wrong with them and how you can tune your joins for better performance.
An important part of performing a migration from Oracle to PostgreSQL is selecting the right tool for helping with the conversion between systems. When people ask me how they can get the migration process started, I like to recommend a powerful open source utility called "ora2pg".