Thom's memory leak

If you are of a certain age, the words 38911 BASIC BYTES FREE will bring memories flooding back. You remember the blue screen that you had spent hours staring at, and all those games in magazines that you could type in line by line, and not really understanding most of what you're even typing. You remember that the disk drive was device 8, and that you had time to go make a cup of tea before it would finish loading. All of that now runs inside PostgreSQL. PL/CBMBASIC is a procedural language extension that executes function bodies on Commodore 64 BASIC V2. The actual Microsoft/Commodore interpreter from 1982, by way of Michael Steil's cbmbasic project, which statically recompiled the 6502 ROM into C. That C is compiled into the extension's shared library, so the interpreter lives inside your backend process. Every function call is an in-memory power cycle: zero the 64KB RAM array, reset the CPU registers, and re-enter the ROM at $E394. The whole thing costs about 15 to 20...

As you may have heard, there are reportedly over 1,500 PostgreSQL servers that have been exploited to mine Bitcoin. And your server could be next if you haven't taken precautions. Firstly, you need to update to the latest minor release, just so no known exploitable bugs exist on your system. But regardless of whether you update, your PostgreSQL instance could still be misconfigured in a way that would allow unwelcome visitors access, so you need to make sure you shore up your defenses. Here are some steps you should take. 1. Get it off the internet Your database shouldn't be accessible from the open internet. People run port scanners on public IP addresses all the time, and yours will eventually be found. Edit postgresql.conf and ensure listen_addresses isn't set to "*" , because this tells PostgreSQL to accept connections from any network interface. Instead, make sure it only listens to connections from the local network. For example: listen_addresses = '...

SQL/JSON is coming to PostgreSQL 12 and provides a native way to query JSON data (although, to be specific, JSON data stored as a JSONB data type).  This also introduces the jsonpath data type which is used for SQL/JSON query expressions.  I'll not be going into its usage in detail, or covering performance characteristics (at least not in this post), but I will compare the syntax and functionality with jsquery.  Note that this may potentially change prior to final release. jsquery was introduced as a non-core extension for PostgreSQL 9.4 and higher by Teodor Sigaev, Alexander Korotkov and Oleg Bartunov.  Like jsonpath, it also used its own datatype, jsquery. We have some functions to which we can pass jsonpath expressions to, 2 of which have operators which can be used as shorthand (albeit without additional parameter control, as they exist primarily for indexing purposes): Function Operator Description jsonb_path_exists ...

In my previous blog post about horizontal scalability changes in PostrgreSQL 9.6, I covered pushing sorts and joins to the remote server, but Robert Haas (EnterpriseDB) has just committed a change that allows DML (that's UPDATEs and DELETEs) to be pushed to the remote server too. This is thanks to the work of Etsuro Fujita (NTT) with advice and testing from Rushabh Lathia (EnterpriseDB), Shigeru Hanada, Robert Haas (EnterpriseDB), Kyotaro Horiguchi (NTT), Albe Laurenz and myself (EnterpriseDB). So this calls for an appendix to my original post! DML pushdown The problem previously was that an update or a delete would mean fetching a batch of rows from the remote server, and sending individual UPDATE commands to the remote server. So if we ran this on the local server (where remote.big_table is a remote table which resides on the remote server): UPDATE remote.big_table SET content = content || '.'; The remote server would receive the following request from the local s...

This is part 2 in a 4 part blog series on improvements coming in PostgreSQL 9.6. Part 1 - Horizontal Scalability Join Pushdown Sort Pushdown "Walk and chew gum at the same time" Part 2 - Monitoring VACUUM progress monitoring Wait monitoring Part 3 - Parallelism Part 4 - Vertical Scalability VACUUM progress monitoring When you run VACUUM on a large table, do you know how much it's done after 5 minutes? Do you know how much more there is to go? The answer is, no. You can't know, at least not accurately or easily. In PostgreSQL 9.6, you'll be able to monitor just this (although not VACUUM FULL), thanks to the work of Amit Langote (NTT) and Robert Haas (EnterpriseDB), which was built upon the work done by Rahila Syed (EnterpriseDB) and Vinayak Pokale (NTT). We now have a system view called pg_stat_progress_vacuum. This is the first in, hopefully, several progress monitoring views to come in future. Here's an example of what it shows you (shown in...

PostgreSQL 9.6 is shaping up to be an impressive release, so I thought I'd try to summarise some of the big features and improvements that it will bring. This certainly won't be exhaustive, but it will touch upon areas I've looked at closely. I'm looking at breaking this down into the following areas: Part 1 - Horizontal Scalability Part 2 - Monitoring Part 3 - Parallelism Part 4 - Vertical Scalability So without further ado, here's Part 1 - Horizontal Scalability I guess I should start with the question some might be asking: "What is 'Horizontal Scalability'?" This is just a fancy way of describing the ability to distribute workload among several clusters/servers/locations etc. So rather than putting all the burden on one server, you can delegate at least some of the work to other servers to speed things up. For 9.6, I'll specifically be covering foreign tables (i.e. tables on remote servers which are being queried as if they wer...

The PostgreSQL Project is yet again participating in Google Summer of Code for 2014. Google will be funding students who take part at $5,500 USD per student (see GSoC FAQ for more details). We would like to hear from students who would be willing to work on projects to add new or enhance existing features. You won't be going it alone, we'll assign experienced community developers to mentor you throughout your project. Have a look at the TODO list on the wiki for inspiration, or the list of project ideas submitted so far by the community, although these are by no means what projects are limited to. Whether you've spotted something you think you could work on, or have a new idea to propose, submit it to the pgsql-students mailing list for discussion (see the mailing list page to subscribe) but do it soon as there's less than 3 weeks to register final proposals. Also, if you know of any students who may be interested in participating, please send them our way. ...

On my laptop I've managed to create 1,001 local chained (one-to-one) streaming-only (meaning no archive directory) asynchronous replication instances. The output of the status of the list is here: https://gist.github.com/darkixion/5694200 I also tested the promotion of the 1st standby to see if it would cope with propagation to the final 1,000th standby, and it worked flawlessly. This didn't work on my copy of Linux Mint without some adjustments to the kernel semaphores values, and it does take a while for all the standbys in the chain to reach full recovery. However, promotion propagation is very fast. Try it for yourself (if you have enough RAM that is). You may find it quicker to use my pg_rep_test tool . Just don't do this manually... it'll take far too long. Thanks to Heikki for putting in the changes that made this archiveless cascading replication possible. :) Update: some figures So looking at the logs, it's clear why it takes so long for all ...

This year's accepted Google Summer of Code projects have been published . Among them are 3 accepted proposals for PostgreSQL which not only will be fine addition to PostgreSQL's set of features, but also helps introduce new students to our community for hopefully long-term contributions. The projects that will be worked on are: Cube extension improvement (Stas Kelvich) Indexes on the cube data type (in the "cube" extension) tend to be on the large size, meaning maintenance of and accessing these indexes is expensive. The improvements this project aims to implement are in reducing the cost of indexes on cube data by using r-tree structures. In addition to this, PostgreSQL's relatively new K-Nearest Neighbour framework would serve to allow the creation of ordering operators for retrieving sorted data directly from the index, and ordering operators for kNN with different spatial norms. UPDATE ... RETURNING OLD (Karol Trzcionka) PostgreSQL can perform UPDATE...

If you have been using PostgreSQL for a long time, or you’re relatively new but have been following old instructions about how to use it, it’s possible that you’re using features that have been deprecated. The reason features disappear tend to be because they have been superseded by better features which cover the same functionality. It’s important to try to avoid using features which are destined to disappear if there’s a newer alternative. Also when planning an upgrade, it’s useful to know if a feature you’re using will suddenly break in the new version. Some of these features still continue to work, but have since been removed from documentation because they’re to be removed in a future release. Others have just been removed completely. We’ll start off with the absurdly old and work our way to the present: Version 6.2 timetravel contrib module Does it still work?: No. This is ooooold and was last supported back in PostgreSQL 6.1. What’s wrong with it?: This really dragged p...

There have previously been various posts on Writable Common Table Expressions (wCTEs) which demonstrate either performance gains or simple examples of how they work, but recently when giving some training, I wanted to come up with some new material to show folk that demonstrates an example of when you might wish to use it. Note that writable common table expressions are only possible in PostgreSQL, and only from version 9.1 onwards. But because 9.1 has been out for almost 2 months now, that means you can go and use this feature straight away! So in case you don't know what a writable common table expression is, in summary it's the same as a normal common table expression, except that you can also use UPDATEs, DELETEs and INSERTs with it. If you don't know what a normal common table expression is, it effectively let's you make the results of queries available to other queries in the same statement. What follows is a rather basic case, but the data is kept simple so as...

The first round of PostgreSQL Conference Europe talks have now been selected and already it's shaping up to be a great conference if these are any to go by. You can see the list so far at http://www.postgresql.eu/events/sessions/pgconfeu2011/ . If we take a brief look at a few of the talks, you'll see that you really don't want to miss this year's line-up. Firstly we have a talk by PostgreSQL veteran and core team member Bruce Momjian who will be "Explaining the Postgres Query Optimizer". This will enlighten attendees as to how PostgreSQL makes its decisions in response to queries which is important to understand in order to know how best to ask PostgreSQL what you want. Bruce gave a very popular MVCC talk last year, and no doubt this one will be one to watch. Also, Michael Meskes's talk entitled "Mission impossible? Can I replace my most important databases with PostgreSQL?" won't be a brief "yes", but will elaborate on Mi...

Peter Geoghegan (@sternocera) has just joined us on Planet PostgreSQL but unfortunately his post (which looks like he's put quite a bit of work into it) became too old to list on the main site's front page before his account was approved, so here's me waving my arms to give you a second chance to find it. You can read it here .

I returned from Brussels with Dave Page yesterday where I attended FOSDEM. It was my first time at this event and had a great time. Loads of organisations were represented there including Mozilla (promoting Firefox), LibreOffice , OpenOffice, Debian, OpenSUSE , Ubuntu , Fedora , FreeBSD, Perl, FSFE, Mandriva, Gnome, KDE, CentOS, CAcert, and many others. And of course, PostgreSQL, where I was helping out, selling various items and talking to people about Postgres itself. All the plush blue elephants were sold (and there were quite a lot), all the stress balls sold, many t-shirts and backpacks sold, and all the pins and pens were given away too. In fact the last pin was taken by a friendly Monty Widenius (creator MySQL and now MariaDB) which he put on there and then. Perl, who were our next-door neighbours, bought one of the plush PostgreSQL elephants and placed it atop their massive Perl camel , which we then declared made it pl/Perl . And one person even bought a batch of 100 str...

FOSDEM is running this weekend in Brussels, and there are a plethora of talks to attend. Naturally I'll be very interested in the PostgreSQL-related talks, and they are as follows: Sunday - 10am PL/Parrot - David Fetter will be giving his talk about this new procedural language addition. I don't actually know much about this, which is why I'm especially interested. Asynchronous Notifications for Fun and Profit - Marc Balmer's talk on asynchronous notifications in PostgreSQL will cover message brokering in distributed environments. This is, annoyingly, at the same time as David's talk. Sunday - 11am PostgreSQL extension's (sic) development - Dimitri Fontaine will be sharing his expertise on extension development, hopefully covering the subject of what he's been hard at work on: extension management within PostgreSQL itself. Sunday - 12pm Writing a user-defined type - The scarily clever Heikki Linnakangas will explain how you can create your own data t...