Monday, June 18

Partition Elimination in PostgreSQL 11

2ndQuadrant, David's PlanetPostgreSQL
The feature freeze for the PostgreSQL 11 release is now upon us. During the last few days my colleague Álvaro Herrera pushed two changes into the development branch of PostgreSQL: 1. Faster Partition Pruning 2. Partition Pruning at Execution Time These patches aim to improve the performance and usability of the declarative table partitioning feature (added in PostgreSQL 10). Amit Langote wrote the first of these two patches, with some assistance from me. I'm the author of the second patch. This one is based on an original patch by Beena Emerson. Background Internally in PostgreSQL, a partitioned table is made up from a series of individual tables. These tables are all grouped under one common parent partitioned table. Queries being run against the partitioned table need the (more…)

Using Java Database Interface (jDBI) with PostgreSQL

2ndQuadrant, Umair's PlanetPostgreSQL
jDBI is designed to be the middle ground between high level ORMs like JPA & Hibernate on one hand and raw JDBC on the other. You can think of it as a layer of convenience on top of JDBC that is still at a lower level than ORMs. When working with JDBC, you are exposed to the raw workings of the SQL language and that of the database you are connecting to. jDBI tries to expose API that encapsulates this and is oriented towards Java programmers rather than database developers. It does so in two different styles, namely fluent style and SQL object style. Fluent style provides the facility of inline querying within your Java program whereas the SQL object style simplifies DAO creation using annotations. Both styles are demonstrated below. Why Use jDBI? You should consider using jDBI (more…)

Power of Indexing in PostgreSQL [Webinar Follow-up]

Liaqat's PlanetPostgreSQL, Webinars
Given the massive size, complexity, and performance requirements of most databases, having an optimized database is critical for many organizations. A variety of different strategies are used for database performance enhancement - indexing being one of them. Since indexing is such a key component to database performance, 2ndQuadrant chose to highlight the power of indexing in the third instalment of their PostgreSQL webinar series. Power of Indexing in PostgreSQL was conducted by Pavan Deolasee, PostgreSQL contributor and developer at 2ndQuadrant. He covered various topics such as accessing data in PostgreSQL and types of indexes supported as well as their internals. If you weren’t able to make it to the live session, you can now view the recording here. Questions that (more…)

PGConf APAC 2018 – bigger & better each year

2ndQuadrant, Umair's PlanetPostgreSQL
Last week saw the 3rd event in as many years in Singapore, and yet this time something was very different. For the last 2 years, we had been conducting the conference as a PostgreSQL track with FOSSASIA. This year, we were able to pull off an entirely independent, 2-day event completely focused on PostgreSQL! The event kicked off with a keynote from Joe Conway (flying in all the way from USA!), who talked about the features to look forward to in PostgreSQL 11. The release is currently scheduled for September 2018. This was followed by 8 talks on the first day and 7 talks on the second day. We also had a round of lightening talks, which were followed by a speaker/sponsor dinner. As an organizer, one of the things that made the conference very exciting for me was the quality  (more…)

Don’t be hard-headed… Harden your PostgreSQL database to ensure security

2ndQuadrant, Britt's Marketing
When it comes to database security, the risk is definitely not worth the reward. Being hard-headed about database security procedures can not only disrupt your business and cost you millions, but it can make irreparable damage to your customer relationship and public identity. How important is the security of your data to your organization Nearly 90% of respondents in the 2017 Nexia International Global Cybersecurity Report  stated that cybersecurity is a top or moderate concern for their organization, yet only 53% reported having a formal cybersecurity plan in place. Even more alarming is that 20% of respondents that are required by law to have a formal Cybersecurity Plan do not actually have such a program in place [1]. It’s obvious that the need for cybersecurity plans and (more…)
Near-Zero Downtime Automated Upgrades of PostgreSQL Clusters in Cloud (Part I)

Near-Zero Downtime Automated Upgrades of PostgreSQL Clusters in Cloud (Part I)

2ndQuadrant, DevOps, Featured, Gulcin's PlanetPostgreSQL, pglogical, PostgreSQL
Last week, I was at Nordic PGDay 2018 and I had quite a few conversations about the tool that I wrote, namely pglupgrade, to automate PostgreSQL major version upgrades in a replication cluster setup. I was quite happy that it has been heard and some other people in different communities giving talks at meetups and other conferences about near-zero downtime upgrades using logical replication. Given that there is a talk that I gave at PGDAY'17 Russia, PGConf.EU 2017 in Warsaw and lastly at FOSDEM PGDay 2018 in Brussels, I thought it is better to create a blog post to keep this presentation available to the folks who could not make it to any of the conferences aforementioned. If you would like to directly go the talk and skip reading this blog post here is your link: Near-Zero Downtime (more…)
Prague PostgreSQL Meetup

Prague PostgreSQL Meetup

2ndQuadrant, Featured, Gulcin's PlanetPostgreSQL, International News, PostgreSQL, User Group
Last month, I was at 2ndQuadrant booth at P2D2, an annual conference organized in Prague by local user group called CSPUG (Czech and Slovak PostgreSQL User Group). The conference was certainly a huge success and more than 200 people showed up! Meeting a lot of PostgreSQL enthusiasts in town, I wondered why there are no regular meetups in Prague. At the conference after-party, I started to question folks and learned that given a chance some of them would give talks and many more would be happy to attend. The initial reactions verified my observation and I felt motivated enough to start organizing meetups myself! Even though I've been living in Prague for some time now, it has been a little difficult to manage the myself all by myself. My colleague Tomas Vondra stepped forward to (more…)

Using EclipseLink with PostgreSQL

2ndQuadrant, Umair's PlanetPostgreSQL
1. Introduction EclipseLink was announced in 2008 as the JPA 2.0 implementation from the Eclipse Foundation. It is based on the TopLink project from which then Oracle contributed code to the EclipseLink project. The project delivers an open source runtime framework supporting the Java Persistence API standards. The EclipseLink project provides a proven, commercial quality persistence solution that can be used in both Java SE and Java EE applications. EclipseLink is open source and is distributed under the Eclipse Public License. 2. Implementation Details Like Hibernate, EclipseLink is also fully JPA 2.0 compliant. This makes the implementation details quite similar to those already described in a preceding Hibernate blog. For the illustration below, we will continue using the (more…)

PostgreSQL – The most loved RDBMS

2ndQuadrant, Simon's PlanetPostgreSQL
The 2018 StackOverflow survey has just been published, with good news for PostgreSQL. StackOverflow got more than 100,000 responses from people in a comprehensive 30 minute survey. PostgreSQL is the third most commonly used database, with 33% of respondents, slightly behind MySQL and SQLServer, yet well ahead of other options. Early in January, the DBEngines results showed PostgreSQL in 4th place behind Oracle, yet here we see that actually Oracle heads up the Most Dreaded list along with DB2, leaving PostgreSQL to power through to 3rd place. PostgreSQL at 62% is the second most loved database, so close behind Redis (on 64%) that they're almost even. But then Redis is only used by (more…)

Using Java ORMs with PostgreSQL – MyBatis

2ndQuadrant, Umair's PlanetPostgreSQL
In my previous blogs, I wrote about Hibernate Query Language (HQL) and Querydsl in detail, now I'm going to talk about MyBatis. While ORMs typically map Java objects to database tables (or vice versa), MyBatis takes a different approach by mapping Java methods to SQL statements. This gives you complete control over writing SQL and its subsequent execution. With the help of a mapper, MyBatis also allows automatic mapping of database objects to Java objects. Like all other Java persistence frameworks, the main aim of MyBatis is to reduce the time and coding requirements of talking to a database using raw JDBC. It is licensed as Apache License 2.0 and is free to use. Why Use MyBatis? MyBatis design has a database-centric approach, so if your application is driven by relational (more…)