Pull to refresh

PostgreSQL *

Object-relational database management system (ORDBMS) with an emphasis on extensibility and standards compliance

Show first
Rating limit
Level of difficulty

AI-powered semantic search using pgvector and embeddings

Level of difficultyMedium
Reading time9 min

In the age of information, the ability to accurately and quickly retrieve data relevant to a user's query is paramount. Traditional search methodologies, which rely on keyword matching, often fall short when it comes to understanding the context and nuances of user queries. Semantic search, which seeks to improve search accuracy by understanding the searcher's intent and the contextual meaning of terms, has emerged as a solution to these limitations. However, implementing semantic search can be complex, involving advanced algorithms and understanding of natural language processing (NLP).

Existing solutions such as Elasticsearch and Solr have been at the forefront of tackling these challenges, providing platforms that support more nuanced search capabilities. These tools use a combination of inverted indices and text analysis techniques to improve search outcomes. Yet, the advent of machine learning and vector search technologies opens up new avenues for enhancing semantic search, with solutions like OpenAI's Embeddings API and the pgvector extension for PostgreSQL leading the charge.

Read more
Total votes 1: ↑1 and ↓0+1

PostgreSQL 17: Part 3 or Commitfest 2023-11

Level of difficultyMedium
Reading time11 min

The November commitfest is ripe with new interesting features! Without further ado, let's proceed with the review.

If you missed our July and September commitfest reviews, you can check them out here: 2023-07, 2023-09.

ON LOGIN trigger
Event triggers for REINDEX
ALTER OPERATOR: commutator, negator, hashes, merges
pg_dump --filter=dump.txt
psql: displaying default privileges
pg_stat_statements: track statement entry timestamps and reset min/max statistics
pg_stat_checkpointer: checkpointer process statistics
pg_stats: statistics for range type columns
Planner: exclusion of unnecessary table self-joins
Planner: materialized CTE statistics
Planner: accessing a table with multiple clauses
Index range scan optimization
dblink, postgres_fdw: detailed wait events
Logical replication: migration of replication slots during publisher upgrade
Replication slot use log
Unicode: new information functions
New function: xmltext
AT LOCAL support
Infinite intervals
ALTER SYSTEM with unrecognized custom parameters
Building the server from source

Read more →
Total votes 3: ↑3 and ↓0+3

PostgreSQL 17: Part 2 or Commitfest 2023-09

Reading time11 min

We continue to follow the news of the PostgreSQL 17 development. Let's find out what the September commitfest brings to the table.

If you missed our July commitfest review, you can check it out here: 2023-07.

Removed the parameter old_snapshot_threshold
New parameter event_triggers
New functions to_bin and to_oct
New system view pg_wait_events
EXPLAIN: a JIT compilation time counter for tuple deforming
Planner: better estimate of the initial cost of the WindowAgg node
pg_constraint: NOT NULL constraints
Normalization of CALL, DEALLOCATE and two-phase commit control commands
unaccent: the target rule expressions now support values in quotation marks
Audit of connections without authentication
pg_stat_subscription: new column worker_type
The behaviour of pg_promote in case of unsuccessful switchover to a replica
Choosing the disk synchronization method in server utilities
pg_restore: optimization of parallel recovery of a large number of tables
pg_basebackup and pg_receivewal with the parameter dbname
Parameter names for a number of built-in functions
psql: \watch min_rows

Read more →
Total votes 3: ↑3 and ↓0+3

PostgreSQL 17: Part 1 or Commitfest 2023-07

Level of difficultyMedium
Reading time8 min

We continue to follow the news in the world of PostgreSQL. The PostgreSQL 16 Release Candidate 1 was rolled out on August 31. If all is well, PostgreSQL 16 will officially release on September 14.

What has changed in the upcoming release after the April code freeze? What's getting into PostgreSQL 17 after the first commitfest? Read our latest review to find out!

Read more →

PostgreSQL 16: Part 5 or CommitFest 2023-03

Level of difficultyMedium
Reading time28 min

The end of the March Commitfest concludes the acceptance of patches for PostgreSQL 16. Let’s take a look at some exciting new updates it introduced.

I hope that this review together with the previous articles in the series (2022-072022-092022-112023-01) will give you a coherent idea of the new features of PostgreSQL 16.

Read more
Total votes 10: ↑10 and ↓0+10

«Divide and Conquer» for OpenStreetMap world inside PostgreSQL

Level of difficultyMedium
Reading time28 min

I will continue the story "How to put the whole world into a regular laptop: PostgreSQL and OpenStreetMap" with secrets about OpenStreetMap geodata, on which many companies have built their business, but not everyone shares the details... Well, today we will open crucial details.

The OSM database in PosgreSQL after loading from the dump takes up more than 587 GB. This is already a large database by the standards of a DBMS, and one huge table for each type of object will not work. For manageability, such data must be partitioned, it's good that PostgreSQL supports declarative data partitioning. It remains only to figure out how to split geographical data. After searching and comparing, the H3 hierarchical hexagonal geospatial indexing system came to rescue. All this was implemented in my openstreetmap_h3 project for fast processing and loading of the world dump into the PostGIS database.

I considered following options from geopartitioning systems...

Read more

How to put the whole world into a regular laptop: PostgreSQL and OpenStreetMap

Level of difficultyEasy
Reading time12 min

When a person used to say that he controls the whole world, he was usually placed in the next room with Napoleon Bonaparte. I hope that these times are in the past and everyone can analyze the geodata of the entire Earth and get answers to their global questions in minutes and seconds. I published Openstreetmap_h3 - my project, which allows you to perform geoanalytics on data from OpenStreetMap in PostGIS or in any query engine that can work with Apache Arrow / Parquet.

First of all, I say hello to the haters and skeptics. What I developed is really unique and solves the problem of transforming and analyzing geodata using the usual and familiar tools available to every analyst and data science specialist without bigdata, GPGPU, FPGA. What looks easy to use and code now is my personal project where I have invested my vacations, weekends, sleepless nights and a lot of personal time over the past 3 years. Maybe I will share the background of the project and the rake that I went through, but first I will still describe the end result.

Read more
Total votes 8: ↑8 and ↓0+8

Roads and building density in North America. 100GB geodata processing OSM data in PostgreSQL

Reading time15 min

Today I will discover America to you based on OpenStreetMap data in PostgreSQL15/PostGIS and my project openstreetmap_h3. Let's run the query and compare its execution time on the Citus column store in PostgreSQL and on the standard 100GB database partitioned by H3 geoindex.

We will find the top15 buildable locations in North America and the total length of roads, as well as their type and surface. I will not overload the publication with program logs, let's focus on the data! You can easily repeat all requests yourself on your laptop/computer.

Read more

Database selection cheat sheet: SQL or NoSQL?

Reading time9 min

This is a series of articles dedicated to the optimal choice between different systems on a real project or an architectural interview.

This topic seemed relevant to me because such tasks can be encountered both at work and at an interview for System Design Interview and you will have to choose between these two types of DBMS. I plunged into this issue and will tell you what and how. What is better in each case, what are the advantages and disadvantages of these systems and which one to choose, I will show with several examples at the end of the article.


Read more
Total votes 1: ↑1 and ↓0+1

PostgreSQL 16: Part 3 or CommitFest 2022-11

Reading time10 min


We continue to follow the news of the upcoming PostgreSQL 16. The third CommitFest concluded in early December. Let's look at the results.

If you missed the previous CommitFests, check out our reviews: 2022-07, 2022-09.

Here are the patches I want to talk about:

meson: a new source code build system
Documentation: a new chapter on transaction processing
psql: \d+ indicates foreign partitions in a partitioned table
psql: extended query protocol support
Predicate locks on materialized views
Tracking last scan time of indexes and tables
pg_buffercache: a new function pg_buffercache_summary
walsender displays the database name in the process status
Reducing the WAL overhead of freezing tuples
Reduced power consumption when idle
postgres_fdw: batch mode for COPY
Modernizing the GUC infrastructure
Hash index build optimization
MAINTAIN ― a new privilege for table maintenance
SET ROLE: better role change management
Support for file inclusion directives in pg_hba.conf and pg_ident.conf
Regular expressions support in pg_hba.conf

Read more →
Total votes 1: ↑1 and ↓0+1

PostgreSQL 16: Part 2 or CommitFest 2022-09

Reading time13 min

It's official! PostgreSQL 15 is out, and the community is abuzz discussing all the new features of the fresh release.

Meanwhile, the October CommitFest for PostgreSQL 16 had come and gone, with its own notable additions to the code.

If you missed the July CommitFest, our previous article will get you up to speed in no time.

Here are the patches I want to talk about:

SYSTEM_USER function
Frozen pages/tuples information in autovacuum's server log
pg_stat_get_backend_idset returns the actual backend ID
Improved performance of ORDER BY / DISTINCT aggregates
Faster bulk-loading into partitioned tables
Optimized lookups in snapshots
Bidirectional logical replication
pg_auth_members: pg_auth_members: role membership granting management
pg_auth_members: role membership and privilege inheritance
pg_receivewal and pg_recvlogical can now handle SIGTERM

Read more →
Total votes 1: ↑1 and ↓0+1

Queries in PostgreSQL. Nested Loop

Reading time17 min

So far we've discussed query execution stagesstatistics, and the two basic data access methods: Sequential scan and Index scan.

The next item on the list is join methods. This article will remind you what logical join types are out there, and then discuss one of three physical join methods, the Nested loop join. Additionally, we will check out the row memoization feature introduced in PostgreSQL 14.

Read more
Total votes 4: ↑4 and ↓0+4

Queries in PostgreSQL. Sort and merge

Reading time19 min

In the previous articles, we have covered query execution stages, statistics, sequential and index scan, and two of the three join methods: nested loop and hash join.

This last article of the series will cover the merge algorithm and sorting. I will also demonstrate how the three join methods compare against each other.

Read more →
Total votes 3: ↑3 and ↓0+3

Queries in PostgreSQL. Sequential Scan

Reading time15 min

Queries in PostgreSQL. Sequential scan

In previous articles we discussed how the system plans a query execution and how it collects statistics to select the best plan. The following articles, starting with this one, will focus on what a plan actually is, what it consists of, and how it is executed.

In this article, I will demonstrate how the planner calculates execution costs. I will also discuss access methods and how they affect these costs, and use the sequential scan method as an illustration. Lastly, I will talk about parallel execution in PostgreSQL, how it works, and when to use it.

I will use several seemingly complicated math formulas later in the article. You don't have to memorize any of them to get to the bottom of how the planner works; they are merely there to show where I get my numbers from.

Read more →
Total votes 3: ↑3 and ↓0+3

Queries in PostgreSQL. Statistics

Reading time18 min

In the last article we reviewed the stages of query execution. Before we move on to plan node operations (data access and join methods), let's discuss the bread and butter of the cost optimizer: statistics.

Dive in to learn what types of statistics PostgreSQL collects when planning queries, and how they improve query cost assessment and execution times.

Read more
Total votes 3: ↑2 and ↓1+2

Authors' contribution