Month: March 2019
Speeding up GROUP BY in PostgreSQL
In SQL the GROUP BY clause groups records into summary rows and turns large amounts of data into a smaller set. GROUP BY returns one records for each group. While most people know how to use GROUP BY not many actually know how to squeeze the last couple of percentage points out of the query. […]
Foreign data wrapper for PostgreSQL: Performance Tuning
Foreign data wrappers are one of the most widely used feature in PostgreSQL. People simply like foreign data wrappers and we can expect that the community will add even more features as we speak. As far as the postgres_fdw is concerned there are some hidden tuning options which are not widely known by users. So […]
unaccent: Getting rid of umlauts, accents and special characters
Database people dealing with natural languages are all painfully aware of the fact that encodings, special characters, accents and alike are usually hard to deal with. This is especially true if you want to implement search in a user-friendly way. This post describes the PG extension unaccent. Consider the following example in PostgreSQL: My name […]
“LOCK TABLE” can harm your database’s health
© Laurenz Albe 2019 Many people know that explicit table locks with LOCK TABLE are bad style and usually a consequence of bad design. The main reason is that they hamper concurrency and hence performance. Through a recent support case I learned that there are even worse effects of explicit table locks. Table locks […]
Choose plpgsql variable names wisely
Pavel Stehule recently wrote the post “Don’t use SQL keywords as PLpgSQL variable names” describing the situation when internal stored routine variable names match PostgreSQL keywords. But the problem is not only in keywords but also for plpgsql variable names. Consider: Output: OK, at least we have no hidden error like in Pavel’s case. Let’s […]