はてなブックマーク

In PostgreSQL 15, a fundamental change took place which is relevant to every user who happens to work with permissions: The default permissions of the public schema have been modified. This is relevant because it might hurt you during application deployment. You need to be aware of how it may affect you. Creating users Many people work as superusers only. This is not recommended and can lead to se

Understanding Multiranges Range types have been around in PostgreSQL for quite some time and are successfully used by developers to store various kinds of intervals with upper and lower bounds. However, in PostgreSQL 14 a major new feature has been added to the database which makes this feature even more powerful: multiranges. To help you understand multiranges, I have compiled a brief introductio

I've recently seen some really broad tables (hundreds of columns) in a somewhat inefficiently structured database. Our PostgreSQL support customer complained about strange runtime behavior which could not be easily explained. To help other PostgreSQL users in this same situation, I decided to reveal the secrets of a fairly common performance problem many people don’t understand: Column order and c

See the documentation for other functions to manipulate sequences. Generating UUIDs A UUID (universally unique identifier) is a 128-bit number that is generated with an algorithm that effectively guarantees uniqueness. There are several standardized algorithms for that. In PostgreSQL, there are a number of functions that generate UUIDs: The uuid-ossp extension offers functions to generate UUIDs. N

UPDATED July 2023: EXPLAIN ANALYZE is the key to optimizing SQL statements in PostgreSQL. This article does not attempt to explain everything there is to it. Rather, I want to give you a brief introduction, explain what to look for and show you some helpful tools to visualize the output. Also, I want to call your attention to a new patch I wrote which has been included in PostgreSQL 16, EXPLAIN GE

PostgreSQL uses shared_buffers to cache blocks in memory. The idea is to reduce disk I/O and to speed up the database in the most efficient way possible. During normal operations your database cache will be pretty useful and ensure good response times. However, what happens if your database instance is restarted - for whatever reason? Your PostgreSQL database performance will suffer until your I/O

It is a frequent complaint that count(*) is so slow on PostgreSQL. In this article I want to explore the options you have to get your result as fast as possible. Added bonus: I'll show you how to estimate query result counts. Why is count(*) so slow? Most people have no trouble understanding that the following is slow:

By Kaarel Moppel - Compare MySQL and PostgreSQL - First off - I'm not trying to kindle any flame wars here, just trying to broaden my (your) horizons a bit, gather some ideas (maybe I'm missing out on something cool, it's the most used Open Source RDBMS after all) and to somewhat compare the two despite being a difficult thing to do correctly / objectively. Also, I'm leaving aside performance comp

For many years, MySQL and PostgreSQL were competing databases, which addressed slightly different audiences. In my judgement (which is of course a bit biased) PostgreSQL always served my professional users, while MySQL had a strong standing among web developers and so on. But, after Oracle took over MySQL I had the feeling that the star of MySQL was somewhat fading. Since the introduction of Maria