PostgreSQL: Useful new data types
SQL and especially PostgreSQL provide a nice set of general purpose data types you can use to model your data. However, what if you want to store fewer generic data? What if you want to have more advanced server side check constraints? The way to do that in SQL and in PostgreSQL in particular is […]
Calculating differences between rows in SQL
Recently we had some clients who had the desire to store timeseries in PostgreSQL. One of the questions, which seems to interest people in this area, is related to calculating the difference between values in timeseries data. How can one calculate the difference between the current and the previous row? To answer this question I […]
PostgreSQL: ltree vs. WITH RECURSIVE
After my last post about ltree and recursive data in PostgreSQL people have asked me privately about performance issues. To share this information, I decided to come up with a follow up post to discuss this topic in a bit more detail. WITH RECURSIVE in PostgreSQL is efficient. However, ltree does have its strengths as […]
SQL trickery: Hypothetical aggregates
“If we had this data what would it mean?” – these kinds of questions can be answered using plain SQL. The technique you will need in PostgreSQL is a “hypothetical aggregate” which is of course part of the ANSI SQL standard. This post will show what an hypothetical aggregate is good for and how it […]
Composite type performance issues in PostgreSQL
PostgreSQL is a really powerful database and offers many features to make SQL even more powerful. One of these impressive things is the concept of a composite data type. In PostgreSQL a column can be a fairly complex thing. This is especially important if you want to work with server side stored procedures or functions. […]
PostgreSQL: Speeding up recursive queries and hierarchic data
A hierarchical query is an SQL query that handles hierarchical model data such as the structure of organizations, living species, and a lot more. All important database engines including PostgreSQL, Oracle, DB2 and MS SQL offer support for this type of query. However, in some cases hierarchical queries can come with a price tag. This […]
SQL trickery: Configuring windowing functions
Generating simple data sets Before we get started I want to introduce my favorite set-returning functions which can help you to generate sample data: All we do here is simply to generate a list from 1 to 10 and print it on the screen. Let us play around with windowing a bit now: There are […]
How to count hits on a website in PostgreSQL
Recently we have covered “count” quite extensively on this blog. We discussed optimizing count(*) and also talked about “max(id) – min(id)” which is of course a bad idea to count data in any relational database (not just in PostgreSQL). Today I want to focus your attention on a different kind of problem and its solution: […]
PostgreSQL: Network latency does make a BIG difference
Database performance is truly important. However, when looking at performance in general people only consider the speed of SQL statements and forget the big picture. The questions now are: What is this big picture I am talking about? What is it that can make a real difference? What if not the SQL statements? More often […]
PostgreSQL: now() vs. ‘NOW’::timestamp vs. clock_timestamp()
Everybody who has ever written any kind of database application had to use time and date. However, in PostgreSQL there are some subtle issues most people might not be aware of. To make it easier for beginners, as well as advanced people, to understand this vital topic I have decided to compile some examples which […]
Speeding up count(*): Why not use max(id) – min(id)
Our PostgreSQL blog about “Speeding up count(*)” was widely read and discussed by our followers on the internet. We also saw some people commenting on the post and suggesting using different means to speed up count(*). I want to specifically focus on one of those comments and to warn our readers. max(id) – min(id) will […]
pg_show_plans: Watching execution plans in PostgreSQL LIVE
After 20 years in professional PostgreSQL support and consulting we are finally able to answer one of the most frequently asked questions: “How can I see all active query plans?” Ladies and gentlemen, let me introduce you to pg_show_plans, an extension which does exactly that. pg_show_plans is Open Source and can be used free of […]