#postgresql

Phoebe Sajor 9 Jun 2026 1 min read

Ryan welcomes Bryan Clark, director of product for Lakebase at Databricks, to discuss what happens when AI agents become the primary creators and users of databases; why agents are “sloppy” about cleaning up infrastructure; and how database branching, scale-to-zero, and centralized access control can help teams keep up with agent-driven development.​​​​‌ ‍ ​‍​‍‌‍ ‌ ​‍‌‍‍‌‌‍‌ ‌‍‍‌‌‍ ‍​‍​‍​ ‍‍​‍​‍‌ ​ ‌‍​‌‌‍…

podcastse-techse-stackoverflowdata-lakehousepostgresql

Jeff Wilson 11 Mar 2026 1 min read

PostgreSQL is an open-source relational database management system (RDBMS). This database management system uses the SQL query language, similar to ... Read More The post How to Install PostgreSQL on Ubuntu 26.04 appeared first on RoseHosting.

ubuntuhow to installpostgresqlubuntu 26.04

Jeff Wilson 3 Dec 2025 1 min read

PostgreSQL is a free, open-source object-relational database that focuses on reliability and performance, offering robust features for users of all ... Read More The post How to install PostgreSQL on Ubuntu 24.04 appeared first on RoseHosting.

databasesubuntuhow to installpostgresqlubuntu 24.04

vladmihalcea 24 Nov 2025 1 min read

Introduction My friend, Denis Magda, wrote a wonderful book called Just Use Postgres!, and I’m glad that I got the chance to read it. Audience This book is useful for any software developer who’s either using PostgreSQL or plans on using it because it covers a lot of features that are very useful for modern applications. No matter your level…

bookbook reviewpostgresql

Rob 23 Sept 2025 1 min read

After pulling a new version of the Docker PostgreSQL container, I started getting this warning: WARNING: database "dev" has a collation version mismatch DETAIL: The database was created using collation version 2.36, but the operating system provides version 2.41. HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE dev REFRESH COLLATION VERSION, or…

postgresql

vladmihalcea 2 Jul 2024 1 min read

Introduction Welcome to a new issue of the High-Performance Java Persistence Newsletter in which we share articles, videos, workshops, and StackOverflow answers that are very relevant to any developer who interacts with a database system using Java. Articles The pick of this week is this article about the drawbacks of using natural keys. Speaking of mapping tables, if you need…

newsletterhibernatejavajpapostgresql

vladmihalcea 4 Jun 2024 1 min read

Introduction Welcome to a new issue of the High-Performance Java Persistence Newsletter in which we share articles, videos, workshops, and StackOverflow answers that are very relevant to any developer who interacts with a database system using Java. Articles The pick of this week is this article from Gergely Orosz about designing and scaling Bluesky. You will learn about favoring flexibility,…

newsletterhibernatejavajpapostgresql

vladmihalcea 22 May 2024 1 min read

Introduction In this article, we are going to see how we can use the PostgreSQL COPY command to export a large result set to an external file. ETL (Extract, Transform, Load) When implementing an ETL (Extract, Transform, Load) process, you might have to extract a large result set from a relational database in order to transform it according to some…

postgresqlsqlcopyetlfile

vladmihalcea 15 May 2024 1 min read

Introduction In this article, we are going to analyze the PostgreSQL plan_cache_mode setting and see when it’s useful to override a given generic plan using the the force_custom_plan strategy. Domain Model Let’s assume we have the following post table: The post_status type is an Enum that was created like this: We have 100,000 post records in our database that have…

postgresqlexecution planforce custom planplan cache modeprepared statement

vladmihalcea 18 Apr 2024 1 min read

Introduction In this article, we are going to investigate how to calculate percentiles with the SQL PERCENTILE_CONT function. Domain Model Let’s consider we have the following quotes table that holds the historical price values of various stocks and indexes: The quotes table is populated with data that looks like this: SQL PERCENTILE_CONT The SQL Standard provides support for a great…

sqloraclepercentilespercentile contpostgresql

vladmihalcea 10 Apr 2024 1 min read

Introduction In this article, we are going to investigate the difference between the PostgreSQL FOR UPDATE and FOR NO KEY UPDATE when locking a parent record and inserting a child row. Domain Model To see the difference between the PostgreSQL FOR UPDATE and FOR NO KEY UPDATE locking clauses, consider the following one-to-many table relationship where the post table is…

databasepostgresqlsqlexplicit lockingfor no key update

vladmihalcea 3 Apr 2024 1 min read

Introduction Welcome to a new issue of the High-Performance Java Persistence Newsletter in which we share articles, videos, workshops, and StackOverflow answers that are very relevant to any developer who interacts with a database system using Java. Articles The pick of this week is this article about Jakarta Persistence 3.2, which brings a lot of improvements to the JPA specifications,…

newsletterhibernatejavajpapostgresql

vladmihalcea 26 Mar 2024 1 min read

Introduction In this article, we are going to analyze how PostgreSQL Heap-Only-Tuple or HOT Update optimization works, and why you should avoid indexing columns that change very frequently. PostgreSQL Tables and Indexes Unlike SQL Server or MySQL, which store table records in a Clustered Index, in Oracle and PostgreSQL, records are stored in Heap Tables that have unique row identifiers.…

databasepostgresqlsqlheap-only-tuplehot

vladmihalcea 11 Mar 2024 1 min read

Introduction In this article, we are going to analyze the PostgreSQL Index Types so that we can understand when to choose one index type over the other. When using a relational database system, indexing is a very important topic because it can help you speed up your SQL queries by reducing the number of pages that have to be scanned…

databasepostgresqlsqlaivenbtree

vladmihalcea 14 Feb 2024 1 min read

Introduction In this article, we are going to explore various PostgreSQL performance tuning settings that you might want to configure since the default values are not suitable for a QA or production environment. As explained in this PostgreSQL wiki page, the default PostgreSQL configuration settings were chosen to make it easier to install the database on a wide range of…

databasepostgresqlaivenconfigurationperformance tuning

vladmihalcea 11 Jan 2024 1 min read

Introduction In this article, we are going to see what is the best way to test the data access layer when using a relational database system. First, we will talk about the pros and cons of unit testing. While unit testing is fine for validating independent logic units, when it comes to testing the data access layer, integration testing is…

databaseaivenintegration testingmysqlpostgresql

vladmihalcea 19 Dec 2023 1 min read

Introduction In this article, we are going to see how the Hibernate StatelessSession Upsert method works. As I explained in this article, the UPSERT operation allows you to INSERT a record if there is no such record matching the filtering criteria or to UPDATE it in case the record exists. While many relational database systems offer the standard MERGE command…

hibernatemysqloraclepostgresqlsql server

vladmihalcea 1 Dec 2023 1 min read

Introduction Welcome to a new issue of the High-Performance Java Persistence Newsletter in which we share articles, videos, workshops, and StackOverflow answers that are very relevant to any developer who interacts with a database system using Java. Articles The pick of this edition is this article about PostgreSQL double buffering. Hibernate 6.3 added support for table partitioning, and in this…

newsletterhibernatejavajpapostgresql

vladmihalcea 22 Nov 2023 1 min read

Introduction In this article, we are going to see how Index Selectivity works in relational database systems and why the database Optimizer might choose to avoid using an index if the number of matching records is large. Index selectivity is inversely proportional to the number of index entries matched by a given value. So, a unique index has the highest…

sqlindexindexingmysqlpostgresql

vladmihalcea 9 Nov 2023 1 min read

Introduction In this article, we are going to see how we can implement a table partitioning solution when using Spring and Hibernate. The goal of table partitioning is to split a large table into multiple smaller partition tables so that the associated table and index records can fit into the in-memory Buffer Pool, therefore allowing a more efficient seek or…

hibernatespringpostgresqltable partitioning

vladmihalcea 1 Nov 2023 1 min read

Introduction Welcome to a new issue of the High-Performance Java Persistence Newsletter in which we share articles, videos, workshops, and StackOverflow answers that are very relevant to any developer who interacts with a database system using Java. Articles The pick of this edition is this article about MySQL 8.2 automatic read and write routing. Until MySQL 8.2 is released, if…

newsletterhibernatejavajpapostgresql

vladmihalcea 11 Oct 2023 1 min read

Introduction In this article, we are going to see what is the best way to use Spring Data JPA Stream query methods. When having to fetch a larger result set, the advantage of using a Java Stream is that the query result set could be fetched progressively instead of getting all the data at once. JPA Stream methods As I…

springjpamysqlpostgresqlspring data

vladmihalcea 3 Oct 2023 1 min read

Introduction Welcome to a new issue of the High-Performance Java Persistence Newsletter in which we share articles, videos, workshops, and StackOverflow answers that are very relevant to any developer who interacts with a database system using Java. Articles The pick of this edition is this article that marks the 10-year anniversary of this blog. For over a decade, I managed…

newsletterhibernatejavajpapostgresql

vladmihalcea 31 Aug 2023 1 min read

Introduction In this article, we’re going to see how the PostgreSQL JDBC Driver implements Statement Caching and what settings we need to configure in order to optimize the performance of our data access layer. Prepared Statements The JDBC API allows you to create a PreparedStatement by calling prepareStatement(java.lang.String) method on a given Connection reference. For this reason, it’s very common…

databasesqlconnection poolingpostgresqlprepared statement

vladmihalcea 17 May 2023 1 min read

Introduction In this article, we are going to see what is the standard SQL operation order. Once you understand the order in which SQL operations are executed, then it will be clear why the Oracle legacy pagination query required a Derived Table in order to make sure that the ROWNUM pseudocolumn is computed after executing the ORDER BY clause. SQL…

sqlmysqloraclepostgresqlsql server

vladmihalcea 13 Apr 2023 1 min read

Introduction In this article, we are going to see how the PostgreSQL Auto Explain feature works and why you should use it to gather the actual execution plan for SQL statements that execute on a production system. SQL Execution Plan As I explained in this article, when you send a SQL statement to PostgreSQL, the statement is executed as illustrated…

postgresqlsqlauto explainexecution plan

lukaseder 1 Sept 2022 1 min read

No need for expensive Tableau subscriptions. Ditch Microsoft Excel. Just use native PostgreSQL to quickly visualise your data! Here’s an idea I had for a while. As you may know, jOOQ can produce fancy charts from your jOOQ results. But that requires you use jOOQ, and you may not be using jOOQ, because you’re not … Continue reading How to…

sqlascii chartschartsplottingpostgresql

lukaseder 5 Nov 2021 1 min read

I’ve recently discovered a pleasant new addition to PostgreSQL 14, the new enable_memoize flag that improves the performance of some nested loop joins where statistics hint at this being appropriate. I mean, who can resist this temptation: Improving query speed by 1000x hints at something very suboptimal having been going on before, and a tool … Continue reading PostgreSQL 14’s…

sql4glenable memoizememoizationpostgresql

lukaseder 27 Aug 2021 1 min read

Database first is at the core of jOOQ’s design. jOOQ has been made primarily for classic systems the database is always there and always has been and will never leave. This is because we think “data have mass” This not only translates to moving logic closer to the data (see our previous posts about the … Continue reading Using Testcontainers…

jooq-in-usecode generationintegration testingjooqpostgresql

7 Apr 2019 12 min read

Hey! In this post we are going to: create a docker image for the Rails chat application that we created in the previous post configure the Docker environment and run the application by: creating a container for the PostgreSQL database creating a container for the Redis server creating a container with the required configuration from the image we built Prerequisites…

rubyrailsdockeractioncablepostgresql

lukaseder 20 Sept 2018 1 min read

SQL is a verbose language, and one of the most verbose features are window functions. In a stack overflow question that I’ve encountered recently, someone asked to calculate the difference between the first and the last value in a time series for any given day: Input volume tstamp --------------------------- 29011 2012-12-28 09:00:00 28701 2012-12-28 10:00:00 … Continue reading How to…

sqlfirst valuelast valuemysqlpostgresql

lukaseder 5 Jul 2018 1 min read

Exciting discovery when playing around with PostgreSQL 11! New SQL standard window function clauses have been supported. If you want to play with this, you can do so very easily using docker: docker pull postgres:11 docker run --name POSTGRES11 -e POSTGRES_PASSWORD=postgres -d postgres:11 docker run -it --rm --link POSTGRES11:postgres postgres psql -h postgres -U postgres … Continue reading PostgreSQL 11’s…

sqlexclude clauseframe clausegroups modepostgresql

lukaseder 14 May 2018 1 min read

Google’s BigQuery has a very interesting SQL language feature, which I’ve missed many times in other databases: select: SELECT [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ] [ REPLACE ( expression [ AS ] column_name [, ...] ) ] | expression [ [ AS ] alias … Continue reading Selecting all…

sqlcross applynested recordspostgresqlprojection

lukaseder 5 Feb 2018 1 min read

A really geeky way to start a Monday morning is to be nerd-sniped by the cool Fermat’s Library twitter account… … reading up on the cool Tupper’s Self-Referential Formula thinking “Can This be Done in SQL?™” As we all know from a previous article, SQL is turing complete, so the answer must be yes. And … Continue reading Calculating Tupper’s…

sqlpostgresqltuppers formulatuppers self-referential formula

lukaseder 1 Jun 2017 1 min read

The following two indexes are redundant in most SQL databases: It is usually safe to drop the first index, because all queries that query the LAST_NAME column only can still profit from the second index I_ACTOR_2. The reason being that LAST_NAME is the first column of the composite index I_ACTOR_2 (it would be a different … Continue reading How to…

sqlindexingoracleperformancepostgresql

lukaseder 31 May 2017 1 min read

I stumbled upon an interesting question on Stack Overflow recently. A user wanted to query a table for a given predicate. If that predicate returns no rows, they wanted to run another query using a different predicate. Preferably in a single query. Challenge accepted! Canonical Idea: Use a Common Table Expression We’re querying the Sakila … Continue reading How to…

sqlcommon table expressionoraclepostgresqlsql performance

lukaseder 29 Mar 2017 1 min read

Tuning SQL isn’t always easy, and it takes a lot of practice to recognise how any given query can be optimised. One of the most important slides of my SQL training is the one summarising “how to be fast”: Some of these bullets were already covered on this blog. For instance avoiding needless, mandatory work, … Continue reading How to…

sqlbenchmarkingoraclepostgresqlsql benchmarking

lukaseder 17 Mar 2017 1 min read

I was nerd-sniped: So tables dee and dum are two theoretical tables in SQL, and they can be characterised as such: [Dee] is the relation that has no attributes and a single tuple. It plays the role of True. [Dum] is the relation that has no attributes and no tuples. It plays the role of … Continue reading Creating Tables…

sqldeedumempty tablespostgresql

lukaseder 17 Jun 2015 1 min read

Before you read on, please note that since jOOQ 3.19, policies are supported out of the box, to implement the same functionality in a much simpler way than what this article suggests! Some time ago, we’ve promised to follow up on our Constraints on Views article with a sequel showing how to implement client-side row-level … Continue reading Implementing Client-Side…

sqljooqoraclepostgresqlrow-level security

lukaseder 30 Dec 2014 1 min read

Sometimes when aggregating data with SQL, we’d love to add some additional filters. For instance, consider the following world bank data: GDP per capita (current US$) 2009 2010 2011 2012 CA 40,764 47,465 51,791 52,409 DE 40,270 40,408 44,355 42,598 FR 40,488 39,448 42,578 39,759 GB 35,455 36,573 38,927 38,649 IT 35,724 34,673 36,988 33,814 … Continue reading The Awesome…

sqlaggregate functionsfilter clausejooqpostgresql

lukaseder 18 Dec 2014 1 min read

We’ve just added support for the EVERY() aggregate function (#1391) to jOOQ, and would like to take the opportunity of letting you know of this true SQL gem that can come in handy EVERY(now and then) (pun intended). Let’s assume we have four books in our table: Now the question is: Is EVERY() ID lower … Continue reading A True…

sqlaggregate functioneverypostgresql

lukaseder 22 Jul 2014 1 min read

So you might’ve seen Destroy all Software’s talk about JavaScript: Here’s a similar talk (less funny more scary) about MySQL:

sqldatabasemysqlpostgresqlrdbms

lukaseder 7 Jul 2014 1 min read

Table-valued functions are an awesome thing. Many databases support them in one way or another and so does PostgreSQL. In PostgreSQL, (almost) everything is a table. For instance, we can write: … and believe it or not, this is a table! We can write: And the above will return: +----+ | v2 | +----+ | … Continue reading PostgreSQL’s Table-Valued…

javajooq-developmentsqljooqpostgresql

lukaseder 15 Apr 2014 1 min read

Haven’t we all been wondering: How can I do this? I have these data in Excel and I want to group / sort / assign / combine … While you could probably pull up a Visual Basic script doing the work or export the data to Java or any other procedural language of choice, why … Continue reading How can…

sqlfanpictorjooqpostgresqlsql standard