Tag: development
Get the best PostgreSQL development tips and tricks from CYBERTEC’s blog posts – our experts develop and test new tools and code constantly, so their blogs will give you insider knowledge you can’t get anywhere else.
PostgreSQL parallel query problems in JDBC and DBeaver
I recently investigated a surprising behavior of the DBeaver database client in connection with PostgreSQL parallel query, which I’d like to share with you. This might be interesting for everybody who accesses PostgreSQL using the JDBC driver. Some basics about PostgreSQL parallel query Parallel query concepts Parallel query was introduced in PostgreSQL 9.6 and […]
Broken foreign keys: how can that happen in PostgreSQL?
One of the fundamental requirements for a relational database is that the transaction system always maintains consistency. That means that database constraints must always be satisfied, even in the face of concurrent data modifications. PostgreSQL certainly strives to live up to this requirement. Still, you can end up with broken foreign key constraints if […]
PostgreSQL hash index performance
Among the many index types in PostgreSQL, the hash index is the most widely ignored. This came home to me when somebody asked me a question about hash index performance recently. High time to explore that little-known corner of PostgreSQL and run some benchmarks! The history of the hash index PostgreSQL has had hash […]
Kubernetes: Running the CYBERTEC PostgreSQL operator
Kubernetes is a powerful and popular container orchestration platform that offers numerous benefits for organisations and developers. Many companies out there these days rely on Kubernetes (or some flavour of it including Rancher, OpenShift, etc.) to automate their environments. PostgreSQL is perfectly suitable to run on Kubernetes. The way to handle a large relational database […]
Using multiple triggers on the same table in PostgreSQL
“Developers, developers, developers, …” – let us not remind ourselves of this painful Microsoft presentation which was given by Mr Ballmer some time ago. But what happens if we deploy triggers, triggers, triggers in PostgreSQL? And what happens if we deploy them all on the very same table? That’s an interesting question, and important to […]
Exclusion constraints in PostgreSQL and a tricky problem
Exclusion constraints are a feature that is not very well known, but can be used to implement highly sophisticated constraints. A few years ago, Hans wrote his blog post about EXCLUDE USING GIST… WITH. Recently we received a note from someone dealing with a very tricky problem concerning exclusion constraints: (many thanks to @necrotikS at […]
Conditional foreign keys and polymorphism in SQL: 4 Methods
This article is about the notoriously difficult problem of “conditional foreign keys”. In object-oriented programming languages, polymorphism is a natural concept. However, it does not translate well to SQL. As a consequence, many people whose data models are driven by an object-oriented application design end up trying to implement conditional foreign keys. In this […]
Indexing “LIKE” in PostgreSQL and Oracle
© Laurenz Albe 2023 Unless you use the binary collation, creating a b-tree index to support a LIKE condition in PostgreSQL is not straightforward. This keeps surprising Oracle users, who claim that a regular b-tree index will of course always support LIKE. I decided to explore the differences between Oracle and PostgreSQL when it comes […]
Subqueries and performance in PostgreSQL
SQL allows you to use subqueries almost anywhere where you could have a table or column name. All you have to do is surround the query with parentheses, like (SELECT …), and you can use it in arbitrary expressions. This makes SQL a powerful language – and one that can be hard to read. […]
Unexpected downsides of UUID keys in PostgreSQL
There are various compelling reasons to use universally unique identifiers (UUID) as primary keys. Two examples are: To be able to generate keys independently of the database To move sets of related records between different databases without having to deal with renumbering everything However, like everything good in life, UUID’s come with their own downsides. […]
What is a schema in PostgreSQL?
One way to organize data in PostgreSQL is to make use of schemas. What is a schema in PostgreSQL? And more importantly: What is the purpose of a schema and how can schemas be used to make your life easier? Let’s dive in and find out. The purpose of a schema Before you figure out […]
PostgreSQL ALTER DEFAULT PRIVILEGES – permissions explained
Many people have heard that ALTER DEFAULT PRIVILEGES makes it easy to allow other users access to tables. But then, many people don’t understand the command in depth, and I hear frequent complaints that ALTER DEFAULT PRIVILEGES does not work as expected. Read on if you want to know better! Default privileges You cannot […]