Neues vom PostgreSQL Planet

While supporting customers at Crunchy Data, I sometimes see users add unlogged tables. In this post, I'm going to review some of the specifics about this.

Unlogged tables in Postgresql are a way of preventing specific tables from generating WAL (Write Ahead Log) information by adding the unlogged keyword to a create table command:

CREATE UNLOGGED TABLE foobar (id int);

You can also change existing tables to unlogged, and switch them back again:

This topic pops up very frequently: “Should we use UUIDs or bigints as primary keys?”

One of the reasons that the question gets so many conflicting answers is that there are really two different questions being asked:

• “Should our keys be random or sequential?”
• “Should our keys be 64 bits, or larger?”

Let’s take them independently.

Should our keys be random or sequential?

There are strong reasons for either one. The case for random keys is:

Creating validated data types in Postgres

I’ll be speaking on Database Antipatterns and How to Find Them at SCaLE 2023, March 9-12, 2023 in Pasadena, CA.

I’m very happy that I’ll be presenting “Extreme PostgreSQL” at PgDay/MED in Malta (yay, Malta!) on 13 April 2023.

Someone at FOSDEM 2023 asked the question: "What happens when PostgreSQL rolls over the version number to 95? Will this cause problems like back then in Windows?"

This is part of an occasional series of blogs about PostgreSQL commitfest patches. Look at the series intro which also lists some ground rules and expectations for what to expect in my thoughts below. 🐘 Patch title and thread: Use the system CA pool for certificate verification Commitfest at time of review: 2023-03 Status at … Continue reading "PostgreSQL Patch of Interest (to me): Using the System CA Pool for Certificate Verification"

Intro

On the 5th of February 2023, I had the privilege of giving a talk at the PostgreSQL devroom during FOSDEM 2023 titled “When it all GOes right.” My talk focused on the process of creating programs in the Go language using a PostgreSQL database. It was an incredible opportunity to share my insights and experiences with fellow open-source enthusiasts from around the world, and to discuss the latest trends and developments in this rapidly evolving field.

If you’re a PostgreSQL user, you’re likely familiar with psql – the interactive terminal that connects you to your Postgresql databases. psql tool is more than just a basic utility. In fact, I consider it to be an essential asset for anyone working with PostgreSQL databases. With an impressive range of features, tips, and tricks, psql is go to tool to talk with PostgreSQL. It never fails to impress and surprise you with its incredible capabilities.

One of the most common questions I get from people running Postgres databases at scale is: How do I optimize the I/O operations of my database? Historically, getting a complete picture of all the I/O produced by a Postgres server has been challenging. To start with, Postgres splits its I/O activity into writing the WAL stream, and reads/writes to the data directory. The real challenge are second order effects around writes: Typically the write to the data directory happens after the transaction…

PostgreSQL provides all kinds of time-related functions. But time is not as easy as it seems. One question many people keep asking: How can I find the current timestamp? What time is it?

While this seems like an easy question the answer isn’t as clear-cut as you might expect. Basically, there are three functions we might use to figure out what time it is:

PostgreSQL is the “Worlds Most Advanced Open Source Relational Database” (the website header says so, and I happen to agree). One of the reasons PostgreSQL popularity continues to increase is because of how new features, bug fixes, and general discussions about improving Postgres are open for anyone to see. Some changes are proposed and merged … Continue reading "PostgreSQL Patch of Interest (to me): An Occasional Blog Series"

Oracle, PostgreSQL and SQL Server support a variety of data types that are very relatable. During the process of migration from Oracle to PostgreSQL and SQL Server to PostgreSQL, we see it is not much of a difficult task in mapping data types between Oracle or SQL Server and PostgreSQL. However, there exists some interesting data types in PostgreSQL that are not otherwise seen in Oracle or SQL Server.

PostgreSQL Person of the Week Interview with Takamichi Osumi: My name is Takamichi Osumi. I’m a Japanese born and raised in Tokyo. Currently, I live in the Shizuoka prefecture where I can enjoy an easier access to the relaxing nature. I have worked in the Fujitsu open-source team for the last couple of years and got involved in some PostgreSQL projects.

Using PostgreSQL On Android

There is an app for Android users, which will give you a terminal: Termux.

With it’s package management you can install your beloved PostgreSQL. That does also include having PostGIS.

There might also be an old Termux app available in the Google Play Store. Do not install this version, it is outdated and will not be updated anymore!