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…)
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…)
Two serious security vulnerabilities (code named Meltdown and Spectre) were revealed a couple of weeks ago. Initial tests suggested the performance impact of mitigations (added in the kernel) might be up to ~30% for some workloads, depending on the syscall rate.
Those early estimates had to be done quickly, and so were based on limited amounts of testing. Furthermore, the in-kernel fixes evolved and improved over time, and we now also got retpoline which should address Spectre v2. This post presents data from more thorough tests, hopefully providing more reliable estimates for typical PostgreSQL workloads.
PostgreSQL uses error context callbacks to allow code paths to annotate errors with additional information. For example, pl/pgsql uses them to add a CONTEXT message reporting the procedure that was executing at the time of the error.
But if you get it wrong when you use one in an extension or a patch to core, it can be quite hard to debug. I'd like to share some hints here for people involved in PostgreSQL's C core and extensions.
Various limits on the PostgreSQL database are listed here: https://www.postgresql.org/about/
One of those limits is the Maximum Table Size, listed as 32TB. It's been that way for many years now.
Only problem is that it has always been wrong, slightly. And now its got much, much bigger.
The table size is limited by the maximum number of blocks in a table, which is 2^32 blocks. The default block size is 8192 bytes, hence the default limit was 32TB as listed. That was wrong in a two ways, because PostgreSQL has always had a configurable block size which allows up to 32768 bytes per block, which would give a maximum size of 128TB. Only problem is that requires an unload/reload to change the block size, so the effective limit per table was 32TB as advertized.
PostgreSQL has always (more…)
Transaction control in PL procedures
A couple of months back, I wrote about how we now have the ability to write Stored Procedures in PostgreSQL. This post follows up on that and talks about the next step that was implemented: transaction control in PL procedures. The feature was committed on 22-Jan-2018.
With this addition, you now have the ability to call COMMIT and ROLLBACK commands in PL/pgSQL from within a procedure. To illustrate:
CREATE TABLE test1 (a int);
CREATE PROCEDURE transaction_test1()
FOR i IN 0..9 LOOP
INSERT INTO test1 (a) VALUES (i);
IF i % 2 = 0 THEN
RAISE NOTICE 'i=%, txid=% will be committed', i, txid_current();
Having a unified view when combining data from different sources is essential for every business. Whether you want to connect to a cloud application, import/export data to/from a text file, or sync data between your datasource and a 3rd party platform - having a database platform that provides a seamless and effective data integration process is critical.
This week on Tuesday, 13 February, 2ndQuadrant hosted the second webinar in their PostgreSQL webinar series. Principal Consultant, Shaun Thomas, conducted the webinar, covering various topics around integration of PostgreSQL with other data sources and database technologies.
The recording of the Webinar is now available. Those who couldn’t make it to the live session can now view Data Integration with PostgreSQL here.
OmniDB was designed to allow users to manage multiple databases from different RDMBS within an unified workspace. The philosophy is to use the same familiar web interface (same structures, icons, context menu and handy features such as SQL Autocomplete, Create/Edit Table Form and Edit Data) so the user feels comfortable and confident to manage different systems as if they are just different connections from the same system.
OmniDB 2.5.0 introduces basic support to Oracle databases. Users can connect and interact with Oracle databases the same way the tool already does for PostgreSQL databases. Oracle versions 11g and 12c are supported.
Let's connect to Oracle databases
As you can see below, I already have some OmniDB connections. Now let's connect to Oracle right away, click on New (more…)
OmniDB 2.4.0 introduces a new cool feature called Monitoring Dashboard. We know a picture is worth a thousand words, so please take a look:
As you can see, this is a new kind of inner tab showing some charts and grids. This Monitoring inner tab is automatically opened once you expand the tree root node (the PostgreSQL node). You can keep it open or close it at any time. To open it again, right-click the root node and click on Dashboard.
The dashboard is composed of handy information rectangles called Monitoring Units. Currently there are 3 types of Monitoring Units:
Grid: The most simple kind, just executes a query from time to time and shows the results in a data grid.
Chart: Every time it refreshes, it renders a new complete chart. The old set of values is (more…)
The PostgreSQL Developer Meeting went well with attendees from UK, Czechia, US, Russia and Germany. The meeting is in Brussels, near the FOSDEM conference.
Andreas Seltenreich was the new face at the meeting. Andreas' sqlsmith tool has found many PostgreSQL bugs, so we discussed extending that for the latest features in PostgreSQL 11.
We discussed all of the patches in the queue and moved a few forwards quickly. With more than 200 patches in the queue there is much work still to do and the last Commitfest hasn't even started yet.
Bruce was able to commit a patch to improve the usability of psql with commands exit and quit. Finally the usability annoyance of people not remembering "\q" command ends in 2018!
I've taken on shepherding the new WAIT FOR command which should allow us (more…)