A unique constraint where NULL conflicts with everything
A cute solution for an unusual request for a unique constraint in PostgreSQL. This blog showcases range data types & exclusion constraints.
Pagination and the problem of the total result count
This article compares pagination methods for PostgreSQL and their performance. Also, avoid displaying the total result count!
JSON logs in PostgreSQL 15
Find out how JSON logs can be configured in PostgreSQL 15 and get all the advantages of this brand new feature
hex, oct, bin integers in PostgreSQL 16
NEW in PostgreSQL 16 - support for non-decimal notation of integer constants. Find out how the changes will affect you-- adds possibilities!
UNION ALL, data types and performance
Find out the impact of PostgreSQL column data types on the performance of queries that involve a UNION ALL.
Faceting large result sets in PostgreSQL
Learn how to implement faceting in your PostgreSQL-based applications to narrow search results - very attractive for users.
Partition PostgreSQL: which partition did I INSERT my data into?
When partitioning in PostgreSQL, it's not obvious how to find the location of data afterwards. Here's some great partition tips and tricks.
EXPLAIN that parameterized statement in PostgreSQL!
EXPLAIN (ANALYZE, BUFFERS) output is hard to get for a parameterized statement in PostgreSQL. With a few tricks we can get EXPLAIN output.
Rewrite OR to UNION in PostgreSQL queries
Improve queries in PostgreSQL - it is not always possible to rewrite OR to UNION in a query. Find out when it is safe to do manually.
btree vs. BRIN: 2 options for indexing for PostgreSQL data warehouses
How do you index in a data warehouse? See the advantages and disadvantages of btree (AKA B-tree) and BRIN indexes in PostgreSQL.
PostGIS: Upgrade GEOS with Ubuntu in 3 Steps
How to upgrade current library versions such as GEOS or GDAL in PostGIS - use the latest stunning spatial features. Upgrade GEOS with Ubuntu.
Why are my PostgreSQL updates getting slower?
Updates getting slower is a frequent complaint of PostgreSQL users. What is behind it and how can I avoid slow updates?
pgbouncer: Types of PostgreSQL connection pooling
Connections are not free. Conserve your resources - find out about connection pooling in PostgreSQL with pgbouncer.
VACUUM does not shrink my PostgreSQL table
Did you ever wonder why VACUUM does not shrink PostgreSQL data files? Learn all the most useful secrets of VACUUM.
LISTEN / NOTIFY: Automatic client notification in PostgreSQL
If 1000's of users constantly poll the database, a lot of load is caused for no benefit. LISTEN / NOTIFY is a better way, find out how.
PostgreSQL: Sequences vs. Invoice numbers
What's the problem with using PostgreSQL database side sequences to create unique invoice numbers to send to clients? Let's find out.
What is an inner join in SQL? And what is an outer join?
Joins: Learn how to use an inner join and an outer join in PostgreSQL today by reading our tutorial. Also covers semi and full joins.
Aliases for sub-SELECTS in FROM clause
Aliases are mandatory for sub-SELECTS by SQL standard. Some vendors (Oracle) don't force that rule. Will PostgreSQL follow it in the future?
Better SQL functions in PostgreSQL v14
New syntax for SQL functions was introduced in PostgreSQL v14, this article gives you vital tips for this important change & its advantages.
gexec in psql: PostgreSQL poweruser practice
gexec PostgreSQL poweruser practice: Learn how to use the gexec command and the `||` operator to get the best results out of your psql.
Column order in PostgreSQL does matter
Column order can determine how quickly your data is processed in PostgreSQL. Find out how to troubleshoot performance problems in wide tables
Handling Bonus Programs in SQL
Learn how to address the issue of bonus programs in SQL, improve your understanding of PostgreSQL, and calculate bonus points efficiently
Transaction anomalies with SELECT FOR UPDATE
This article shows how surprising transaction anomalies can happen with SELECT FOR UPDATE and what you can to to avoid them.
Case-insensitive pattern matching in PostgreSQL
We discuss the options for case-insensitive comparison and pattern matching in PostgreSQL, comparing the performance of different approaches.
View permissions and row-level security in PostgreSQL
How view permissions are checked, and how that changes if you use the security_invoker view option from PostgreSQL v15. Use row-level security effectively.
How to cancel a hanging PostgreSQL query
This article describes how to cancel queries in PostgreSQL and shows a trick that you can use if canceling a query doesn't work.
Time zone management in PostgreSQL
Time zone management and timestamp data types in PostgreSQL - learn how to use 'timestamp with time zone' correctly and optimize performance.
PostgreSQL 15: Using MERGE in SQL
Merge - UPDATED 2023 - a technical preview of how to merge lists with ease. Find out about a wonderful command - add power to your PostgreSQL.
Analyzing historical flight data with MobilityDB
Use MobilityDB to analyze historical air traffic data. A quick-start guide to dealing with spatio-temporal data together with PostGIS
How to DROP ROLE or DROP USER in PostgreSQL
Learn how to use DROP ROLE and DROP USER in PostgreSQL. This article explains how to remove users and gives troubleshooting tips.
Query parameter data types and performance
How do query parameter data types affect performance? Find out how to avoid bad performance by choosing the correct parameter types.
Pipeline mode for better PostgreSQL performance on slow networks
This article shows how pipeline mode, new with PostgreSQL v14, can improve query performance over slow network connections.
Google Cloud PostgreSQL under pgwatch2 monitoring
Find out how to monitor PostgreSQL databases on Google Cloud with the pgwatch2 monitoring solution to get extended metrics for added clarity!
Practical Examples of Data Normalization in PostgreSQL
Data normalization - Learn how to apply the theory from part 1 of Michał Małecki's blog to real life with practical examples.
Entity-attribute-value (EAV) design in PostgreSQL - don't do it!
This article explains what the entity-attribute-value (EAV) model is and why you shouldn't use it in a relational database.
WITH HOLD cursors and transactions in PostgreSQL
This article describes how cursors and transactions interact, how WITH HOLD can overcome the limitations, plus some caveats and tricks.
OpenStreetMap service by CYBERTEC
Importing OpenStreetMap data into PostGIS is time-consuming. Download OpenStreetMap offers two dataset types periodically served as sql dumps.
Gaps in sequences in PostgreSQL
How you can end up with gaps in sequences & how they can even skip backwards. How to build a gapless sequence in PostgreSQL.
JSON in PostgreSQL: how to use it right
This article describes what can go wrong when using the much-loved JSON capabilities of PostgreSQL and gives guidelines how to do it right.
Building an Oracle to PostgreSQL migrator: Lessons learned
Oracle to PostgreSQL migration - Lessons learned in building a migration tool. How to avoid errors, null bytes and broken content.
Index bloat reduced in PostgreSQL v14
This article describes how PostgreSQL v14 reduces B-tree index bloat with "bottom-up index tuple deletion". A test case shows the difference.
PostgreSQL vs Redis vs Memcached performance
PostgreSQL vs Redis vs Memcached: How would it look on the performance side, if one just skips the cache & hits the database directly?
PostgreSQL zheap: Current status
zheap has been designed as a new storage engine to handle UPDATE in PostgreSQL more efficiently. Read about the current status and more.
PostgreSQL: LIMIT vs FETCH FIRST ROWS ... WITH TIES
The LIMIT/OFFSET clause is off standard and thus not portable. Read about the proper way to handle it by using “SELECT … FETCH FIRST ROWS”.
Simulating temporal tables with dblink and replication delay
How to simulate unsupported out of the box temporal tables in PostgreSQL using delayed streaming replication and dblink extension
Constraints over multiple rows in PostgreSQL
Constraints over multiple rows: How to cope with complex PostgreSQL constraints. Advanced techniques - enforce integrity; manage concurrency.
Creating and refreshing materialized views in PostgreSQL
A “materialized view” is an integral feature of every advanced database system. How can you make use of materialized views in PostgreSQL?
Data warehousing: Making use of synchronized seq scans
Did you know PostgreSQL has the ability to synchronize sequential scans? If you are running data warehouses, you might have already used it without knowing.
How to interpret PostgreSQL EXPLAIN ANALYZE output
UPDATED July 2023: How to use EXPLAIN ANALYZE in PostgreSQL, learn tools to visualize the output. See query execution time and row count.
PostgreSQL: Bulk loading huge amounts of data
This article is about bulk loading, which is the quickest way to import large amounts of data into a PostgreSQL database.
PostgreSQL: Detecting slow queries quickly
UPDATED Mar 2023: How to use pg_stat_statements to detect slow queries - including examples and code - improve PostgreSQL performance
Setting up PostgreSQL streaming replication
This tutorial will show you how to setup up and configure streaming replication for PostgreSQL 13 & how to set up database servers quickly.
Publishing maps with Geoserver and PostGIS
Analysing data within PostGIS is just one side of the coin. This blogpost is about publishing datasets as maps using Geoserver and PostGIS.
TLS: Demystifying communication encryption in PostgreSQL
TLS is a technology widely used in combination with PostgreSQL to encrypt client / server connections. This blogpost explains how TLS works.
PostgreSQL: Understanding deadlocks
"ERROR: deadlock detected" - message - what should I do? This article explains what this really means and how deadlocks can be prevented.
pg_read_all_data - a system level "read all data" role for PostgreSQL!
A new feature has arrived: "pg_read_all_data" - a predefined role that can run an unfettered pg_dump without that role being a superuser.
Cleaning up a large number of BLOBs in PostgreSQL
Basically, there are two ways to delete BLOBs (binary large objects):Change the PostgreSQL configuration or delete the BLOBs in chunks.
Getting started with QGIS, PostgreSQL and PostGIS
Visualizing spatial data is an important task. This tutorial helps you with getting started with visualizing OSM data with PostGIS and QGIS.
PostgreSQL: Get member roles and permissions
PostgreSQL allows you to define users (=roles), groups, etc. This post explains how to retrieve information on roles & role membership.
When to use tablespaces in PostgreSQL
This article explains what PostgreSQL tablespaces are, when to use them and how they are different from Oracle tablespaces.