Your Postgres constraints may be capping ingest throughput. Learn how FOREIGN KEY and UNIQUE checks slow high-rate inserts, plus four fixes that protect integrity.
#postgresql
48 posts
24 Jul
29 Jun
Stale statistics on small Postgres metadata tables can silently throttle your largest queries. Learn to spot it with EXPLAIN and fix it—no schema migration.
24 Jun
At scale, Postgres ORDER BY on time-series data spills to disk and slows queries. See how time_bucket and continuous aggregates cut a 4s query to 9ms.
19 Jun
A skewed partition key can route 80% of your writes to one partition. Learn how to diagnose PostgreSQL partition hot spots and fix them at the schema level.
12 Jun
When PostgreSQL Isn't the Right Fit: Recognizing Workloads That Need Different Architecture
TimescalePostgres handles 90% of workloads well. Here's how to tell if yours is in the 10% — and what the diagnostic query that confirms it looks like.
9 Jun
Creating checkpoints by gaslighting a Postgres database
Stack OverflowRyan 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. …
11 Mar
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.
3 Dec 2025
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.
24 Nov 2025
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…
23 Sept 2025
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…
2 Jul 2024
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…
4 Jun 2024
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,…
22 May 2024
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…
15 May 2024
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…
18 Apr 2024
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…
10 Apr 2024
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…
3 Apr 2024
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,…
26 Mar 2024
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.…
11 Mar 2024
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…
14 Feb 2024
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…
11 Jan 2024
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…
19 Dec 2023
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…
1 Dec 2023
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…
22 Nov 2023
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…
9 Nov 2023
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…
1 Nov 2023
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…
11 Oct 2023
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…
3 Oct 2023
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…
31 Aug 2023
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…
17 May 2023
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…
13 Apr 2023
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…
1 Sept 2022
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…
5 Nov 2021
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…
27 Aug 2021
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…
20 Sept 2018
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…
5 Jul 2018
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…
14 May 2018
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…
5 Feb 2018
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…
1 Jun 2017
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…
31 May 2017
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…
29 Mar 2017
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…
17 Mar 2017
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…
17 Jun 2015
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…
30 Dec 2014
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…
18 Dec 2014
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…
22 Jul 2014
So you might’ve seen Destroy all Software’s talk about JavaScript: Here’s a similar talk (less funny more scary) about MySQL:
7 Jul 2014
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…
15 Apr 2014
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…