Tuesday, January 23

OmniDB: Average execution time of PostgreSQL webmethods

OmniDB, William's PlanetPostgreSQL
1. Introduction Being a web application, OmniDB is composed of many webmethods. A webmethod is a special kind of function hosted on the server side (the web server), called from the client side (the web browser), when the user performs some action on OmniDB web interface. For instance, OmniDB has several webmethods that know how to communicate with all supported versions of PostgreSQL. This set of webmethods is called PostgreSQL webmethods of OmniDB. We automated the execution of all PostgreSQL webmethods through the Django unit tests feature. For each supported version of PostgreSQL (from 9.2 to 9.6 and also 10), there are 56 unit tests that simulate common user behavior. The database used for the tests is a well known PostgreSQL sample called dellstore2. Using vagrant, we (more…)

Java Object Oriented Querying (jOOQ)

Umair's PlanetPostgreSQL
jOOQ is an ORM-alternative that is relational model centric rather than domain model centric like most ORMs. For example, while Hibernate lets you write Java code and then automatically translates it to SQL, jOOQ let’s you write relational objects in your database using SQL and then automatically generates Java code to map to those relational objects. The writers of jOOQ believe in the power of SQL and assume that you want low level control of the SQL running for your application. This means that you can write your database and your schema without having to worry about how (and if!) it can be handled in Java. Why Use jOOQ? While JPA provides a huge framework with a great deal of flexibility and power, it can very quickly become quite complex. jOOQ provides a simpler interface for (more…)

PG Phriday: Smooth Operator

Shaun's PlanetPostgreSQL
One of the handy things Oracle does with dates is allow manipulation with standard arithmetic. Want tomorrow's date? Add one. Want a week ago? Subtract seven. Postgres does something close with its INTERVAL syntax, under the explanation that we don't necessarily want to make assumptions about what is being added to a date or timestamp. But Postgres has a big secret in the fact we can arbitrarily create operators to get the behavior we desire. No Place for Beginners or Sensitive Hearts So what happens if we happen to know that the developers and applications targeting the database prefer the Oracle approach, and that integers should always increment or decrement a date value? Let's start with something basic like adding a integers to a date. What happens if we try this with a (more…)

PG Phriday: Crazy Correlated Column Crusade

Shaun's PlanetPostgreSQL
For a long time, the Postgres query planner has sported a huge blinking neon blind-spot that frustrated and enraged DBAs throughout the universe to a level just shy of murderous frenzy. How is this even possible? What terrible lurking horror could elicit such a visceral response from probably the most boring and straight-laced people ever to draw breath? What else? Correlated statistics. The Adventure Begins! The Postgres query planner is a cost-estimation engine. Postgres gathers statistics on table contents such as most and least frequent values, value cardinality, rudimentary histograms, and so on. Along with multiple metrics related to hardware responsiveness, it generates multiple viable execution strategies, and chooses the one that probably costs the least resources. So far, this (more…)

News and Roadmap for BDR (Multi-master PostgreSQL)

2ndQuadrant, Simon's PlanetPostgreSQL
Postgres-BDR is an open source project from 2ndQuadrant that provides multi-master features for PostgreSQL. We have pursued a joint strategy of providing both working code available now and also submitting the features into core PostgreSQL. Postgres-BDR 1.0 runs on a variant distro of PG9.4. This is in Production now and receives regular maintenance and security updates. 2ndQuadrant will support this until 9.4 End of Life in December 2019. One of the greatest achievements to come out of our work on BDR is the logical replication technology. Our engineers spent a considerable amount of energy to contribute the tech to PostgreSQL core and I feel especially proud that this is a headline feature of the upcoming PG10 release. And Now BDR 2.0 …  BDR 2.0 runs on community PG9.6 as (more…)

News and Roadmap from Postgres-XL 10

2ndQuadrant, Pavan's PlanetPostgreSQL
We expect Postgres-XL 10 to be released in the next few months, with the new features of partitioning and logical replication. You’ll be able to load Postgres-XL directly from PostgreSQL. For the first time, you’ll be able to run massively parallel queries both across the datanodes and within the datanodes to give huge performance gains. Earlier today, we updated the master branch of the Postgres-XL repository to include all commits from PostgreSQL's master branch up to June 26. That means the XL project is now fully to date with the PostgreSQL source code, meaning there is now only minimal lag between PostgreSQL and Postgres-XL. At this point the code is only in Development/Alpha, though we expect to produce Beta1 soon after PostgreSQL Beta 3 in August. Support from the (more…)

PG Phriday: Uptown Func

Shaun's PlanetPostgreSQL
One of the coolest things about Postgres functions is that they can return rows as if they were a table. Not only is it possible, but creating a set-returning function in Postgres is almost frighteningly trivial when compared to other database engines. In fact, some Oracle users are probably rolling their eyes and muttering "Just use a cursor!" already. Just hear us out! Postgres functions can return cursors too, but using them afterwards isn't exactly a friendly experience. Cursors are passed by reference, so the function must either accept a parameter to name the cursor, or it generates something wacky like "<unnamed portal 1>". After that, cursors can only be used with FETCH instead of SELECT, greatly limiting their utility. What about views, then? Obviously they can return (more…)