Neues vom PostgreSQL Planet
Having covered pagination in a previous blog post, I want to explore method #5, LIMIT/OFFSET and WHERE, also called keyset or seek pagination. This method avoids many of the performance pitfalls and inconsistent results of the other methods, but it comes with its own complexities.
© Laurenz Albe 2020
Many people consider recursive queries a difficult topic. Still, they enable you to do things that would otherwise be impossible in SQL.
This articles gives a simple introduction with examples and shows the differences to Oracle’s implementation of recursive queries.Common table expressions (WITH clauses)
A common table expression (CTE) can be seen as a view that is only valid for a single query:
There you are writing some SQL, having a great time. Uh oh, you need to iterate over each item in a result set and apply a function. You think, "Now I am going to have to write a stored procedure." Well today's post will give you an alternative by using lateral joins in Postgres.
Lateral pass.... See what I did there?
I like terminal applications - usually TUI applications are fast and clean, and well readable. This interface is too simply, and then developers has to much more think about UI, and has not too much possibilities. On second hand sometimes the output of terminal applications are too limited. We run our terminal from GUI (usually), and then some graphic possibilities can be practical - graphs are better in pixel graphics than ascii art.
Pgpool-II probably is the most comprehensive clustering solution existing today for PostgreSQL. It provides a wide range of features like connection pooling, load balancing, automatic failover and high availability while using the Pgpool-II for load balancing and building a highly available PostgreSQL cluster is one of its most common use case.
Almost every organisation that I interact with wants a high availability system for PostgreSQL. This clearly depicts an active trend toward an increase in utilising PostgreSQL for critical business applications. In some cases it is a move away from other major database systems like Oracle or even Teradata.
We have all used applications that allow searches which return results in sections or "pages", like for products or flights. For example, you might search for a product, and there might be 1,500 matches, but you don't see 1,500 results — you only see ten results (1-10), and you have the option of seeing the next ten (11-20), and the next ten (21-30), etc. Pagination is done for several reasons: