Neues vom PostgreSQL Planet
PG13; not to be confused with PG-13, only PostgreSQL Guidance required here!
PostgreSQL Beta 1 was released on May 21, 2020 and Beta 2 on June 25, 2020. A beta might not be bug free, but it almost always includes all core features of the full release. PG betas are no different. Beta 2 includes all the major features we expect to see in the final release. There are some fairly useful and exciting features that are worth mentioning.
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.