We just pushed out installers for PostGIS 2.2.0 for PostgreSQL 9.5beta2 windows both 32-bit and 64-bit on Application Stackbuilder. These installers are also available as standalone listed on PostGIS windows page. This is the first PostGIS 2.2.0 release for the PostgreSQL 9.5 32-bit and a rerelease for PostgreSQL 9.5 x 64-bit (this time compiled against beta2 instead of beta1).
On quick testing the PostGIS 2.2 beta1 release and pgRouting 2.1.0 worked fine on 9.5beta2, however you may want to reinstall anyway just to be safe. You can just reinstall over your existing install, no need to uninstall first. Similarly just upgrading a PostgreSQL 9.5beta1 to 9.5beta2 seemed to not require pg_upgrade or dump/restore, so safe to just upgrade from 9.5beta1 to 9.5beta2. Other notes about this 9.5beta2 PostGIS 2.2.0 release:
rm_worktrees => 1,
It has occurred to me that I may have been spending a bit too much time being excited about new Postgres features and developments in the community. One of the intents of this weekly article was for educational purposes, so this week, let’s get back to basics. To that end, the topic for this week boils down to the tools available for managing Postgres instances, and how to use them. Surprisingly, it’s not as straight-forward as you might think.
Having used Postgres for almost 15 years now, it’s easy to forget (or mentally block!) the early struggles. With new tools and wrappers always being developed, it’s not always clear what the best practices for managing a Postgres cluster actually are. Indeed, it often depends on how Postgres is installed.
Let’s start with a basic source-code installation. I won’t cover
that process, but we can assume that after the smoke clears, the
binaries are somewhere in
/usr/local/bin, and are thus in our execution path.
Given that, let’s say we have an available mount point at
/data and want to create a new cluster there. Here’s
how that might go:
sudo mkdir /data/pgsql sudo chown postgres:postgres /data/pgsql sudo su - postgres initdb -D /data/pgsql/my_db pg_ctl -D /data/pgsql/my_db start
We now have an active Postgres instance at
/data/pgsql/my_db. It’s extremely common to use the
postgres OS user for this kind of thing, hence all of
sudo commands to prepare. It’s entirely possible
to do this as a regular user, but I usually don’t recommend that
In any case, this type of installation essentially depends on
pg_ctl command-line tool. It does everything
related to controlling a Postgres instance. But it’s also annoying
to use the
-D parameter all the time when using
Postgres tools, so there are several
environment variables that can also do the job. This lets us
prime our environment with
.bashrc, for example. Let’s
stop the instance:
export PGDATA=/data/pgsql/my_db pg_ctl stop -m fast
-m fast part? By default, Postgres is
The Paxos algorithm is a powerful building block for building highly available distributed systems. Paxos can be seen as a function paxos(k,v) that returns the same value on all servers in a group for a certain key (k), and the value is one of the inputs (v). Paxos is most commonly used to implement log replication through a technique called Multi-Paxos. In Multi-Paxos, nodes call paxos using the indexes in a log as keys and state changes (e.g. 'set primary to node2') as values. Using this technique, data can be kept consistent and available across multiple servers, even if some of them fail.
We recently started exploring Paxos as a technique for doing automated fail-over of PostgreSQL servers and wrote a simple implementation of Paxos and Multi-Paxos including basic support for membership changes in about 1000 lines of PL/pgSQL. We found PL/pgSQL to be an excellent tool for implementing Paxos, because the necessary transactional semantics are a natural part of the language. Other consensus algorithms would have been harder to implement in this way, because they rely on timers and other background tasks.
The pg_paxos extension demonstrates how the Paxos functions can be used for fault-tolerant, consistent table replication in PostgreSQL. While a replicated table has high write and read latencies due to the network round-trips involved, it can be very useful for applications such as automated fail-over. We plan to add optimisations such as the ones applied in Google Megastore to reduce the overhead and develop the extension further to be able to replace components like etcd or Zookeeper.
While discussing results of various filesystem benchmarks - both here and in talks given on conferences, one of the things I've been questioning was the negligible impact of TRIM/DISCARD mount option.
I was speculating that maybe TRIM only really matters when the drive gets almost full, because until then the controller has enough room where to write the incoming data without getting the internal garbage collection under pressure. But I've recently did a a few pgbench runs aiming to test exactly this, using a rather large scale (filling more than 90GB of the 100GB drive), yet still no diference.
Another speculation was that maybe this particular SSD is really good and there's so much additional overprovisioning that the TRIM still makes no difference. After all, it's a good SSD frin Intel (S3700) meant for write-intensive data center applications, so I wasn't entirely surprised by that.
But then it dawned upon me ...
The TRIM does not matter because pgbench does not discard any pages, it simply overwrites the pages in place, so there's nothing to TRIM.
What pgbench does is that (a) it writes new data by adding new pages and (b) updates those pages, by rewriting them in place. None of that involves discarding pages - the controller knows which page of the file it's overwriting, and therefore knows which SSD pages are dirty and need to be erased. Similarly for WAL segments, that are reclaimed and overwritten in-place.
Maybe the TRIM does matter for other workloads (e.g. creating a lot of temporary files on the SSD device), or in databases where tables are often dropped or truncated. But for the simple pgbench workloads, TRIM does not matter at all.
I’m using this perfect tool called Sublime Text 3
for a bunch of tasks. One of them is viewing SQL scripts from time
to time. ST3 has perfect SQL highlighting, but what I miss the most
is the context help functionality, e.g. I select “LEFT
JOIN” and hit F1 hot key. But that’s not problem since ST3 has
a lot of packages. To solve my problem I need GotoDocumentation package and some tuning. Here
is my settings for GotoDocumentation:
// obj containing the docs for each scope
// these are merged with the default ones
// the key value pair represent scope -> doc url
// supported placeholders:
// - %(query)s the selected text/word
// - %(scope)s the current scope
// if we have no docs for the current scope
// we will try using the fallback one,
// to disable set to false
As you may be aware, PostgreSQL splits data files into 8kB pages by default. It's possible to use different page sizes (up to 32kB), but that requires compiling custom packages so almost no one does that. It's quite rare to come across an installation using different page size at a customer, for example.
Why 8kB data pages and not a different size? Clearly, smaller or larger pages might be more efficient in some cases, and indeed some other databases use different page sizes. For example InnoDB uses 16KB pages by default, and some databases even allow a mix of page sizes. So there seems to be a gain in using different page sizes, otherwise engineers working on those products would not waste time implementing it.
So what factors determine the optimal page size? And how do SSD disks change the results?
One of the fundamental rules about page size, called Five Minute Rule, was formulated by Jim Gray and G. F. Putzolu in 1985, and approaches the problem as an economic question "Given disk and RAM prices, how long should a page of given size be kept in memory?" The equation expressing the rule is essentially this
break_even_interval = (pages_per_MB * price_per_MB_RAM) * (IOPS_per_disk * price_per_disk)
and for values common in 1985 (1kB pages and disk/RAM prices in 1985), the break even interval came out as roughly 5 minutes (hence the name of the rule).
However since 1985 a lot has changed - the prices of RAM and disks plummeted, performance of rotational storage slightly improved and so on. Despite that, the 1997 revision of the paper came to about the same conclusion - the break even interval is still about 5 minutes, however assuming 8kB page size (not 1kB as used in the 1985 paper).
This is probably also the reason why PostgreSQL uses 8kB pages - Postgres95 (as it was called at that time) was released shortly before the paper was published, but it had to follow the same reasoning.
But again, a lot has changed since 1997 - the RAM/disk prices continued to plummet and, probably more importantly, flash storage is
I had the pleasure to be invited to speak at All Your Base Conference 2015 about PostgreSQL (of course). The conference gathers together lots of user experience around data management and database products, either in the now classic meaning of the word (I mean relational database management systems here) or the newer set of trade-offs represented by the NoSQL set of tools.
The conference was very nicely organized and ran smoothly, and I got the unique chance to get a back from the trenches story series about people tools of choices and the reason why they do things their way. It has been very enlightning!
If you ever get the chance to attend All Your Base, take my word for it and just go!
People having experience with commercial RDBMS are used to have the ability to answer the question “What a particular session is doing right now?” Or even “What was that session waiting 5 minutes ago?” For a long time PostgreSQL did not have such diagnostic tools and DBAs used to get out with different ways of sophistication. I gave a talk on pgday.ru (in Russian) about how we do it. This talk was collaborative with Ildus Kurbangaliev from PostgrePro. And Ildus was just speaking about tool that allows to answer questions above.
Strictly speaking it is not the first try to implement what
people used to call wait [events] interface, but all previous
attempts were not brought to some reasonable state and died as
proof of concept patches. But
currently available as
a set of patches to current stable 9.4 branch and currently
developing 9.6 (actual versions should be looked
After quite long testing and fixing bugs we even deployed them to production.
Before it all becomes part of core PostgreSQL you need to
recompile postgres. I think description of rebuilding as
./configure && make && sudo make
install is meaningless — much better to look into documentation.
After it you should add
shared_preload_libraries. Additionally, you can add
following options to
waits_monitoring = on- enabling functionality on,
pg_stat_wait.history = on- storing history of wait events,
pg_stat_wait.history_size = 1000000- number of last events to keep in history,
pg_stat_wait.history_period = 1000- how often should wait events be stored in history (ms).
After that you should restart PostgreSQL and make
EXTENSION pg_stat_wait. After that everything will
What exactly will start to work? First you may look at what is inside the extension:
rpopdb01g/postgres M # \dxS+ pg_stat_wait Objects in extension "pg_stat_wait" Object Description --------------------------------------------------------- fu
FOSDEM PGDay is a one day conference that will be held ahead of FOSDEM in Brussels, Belgium, on Jan 29th, 2016. This will be a one-day focused PostgreSQL event, with a single track of talks. Registration is required to attend, the registration will open soon. Since we have a limited number of seats available for this event, we urge everybody to register as soon as possible once open.
PostgreSQL Europe will also have our regular devroom at FOSDEM on Sunday the 31st, which will be held at the main FOSDEM venue at ULB. This day will, of course, continue to be free of charge and open to all FOSDEM entrants. No registration is required to attend this day.
For full details about the conference, venue and hotel, see http://fosdem2016.pgconf.eu/.
The call for papers is now open for both these events. We are looking for talks to fill both these days with content for both insiders and new users. Please see http://fosdem2016.pgconf.eu/callforpapers/ for details and submission information.
The deadline for submissions is December 7th, 2015, but we may as usual pre-approve some talks, so get your submissions in soon!
We will provide a special rate with the Brussels Marriott Hotel. For details, see http://fosdem2016.pgconf.eu/venue/.
but you find it doesn't work. VALUES isn't allowed to have an empty list. Here's what you do:insert into mytable values () returning my_id;
This will insert the default value explicitly into first column in the table, and implicitly into all the other columns. It doesn't matter if any of the columns doesn't have an explicit default value set - in that case the default is NULL. Of course, that means this won't work if you have NOT NULL columns without a default set.insert into mytable values (default) returning my_id;
I have gone through an interesting small size C code package for QR encoding at https://github.com/swex/QR-Image-embedded. Its logic is contained in two small files: QR_Encode.h (3.7KB) and QR_Encode.c (61KB). Then it is sun-shiny Saturday morning as I manage to add in-memory monochrome bitmap construction (1 bit per pixel) and wrap the whole package as PostgreSQL extension module. I share it at qrcode.tar.bz2.
Please modify PG_CONFIG = /opt/pgsql/9.4/bin/pg_config in Makefile as necessary. Then make and make install.
Connect to a database then invoke sql command
SELECT qr('QR Code with PostgreSQL', 0, 0, 4);
A couple days ago, Robert Haas announced that he checked in the first iteration of parallel sequence scans in the Postgres 9.6 branch. And no, that’s not a typo. One of the great things about the Postgres devs is that they have a very regimented system of feature freezes to help ensure timely releases. Thus even though 9.5 just released its second beta, they’re already working on 9.6.
So what is a sequence scan, and why does this matter? Past articles have covered this, but Postgres uses sequence scans when it needs to read the entire contents of a table. For larger entities consisting of tens or hundreds of millions of rows, this is a time-consuming process. Even the best processor can only handle a few million rows per second, so as scale increases vertically, there’s currently no way to address these larger data volumes efficiently without significant effort.
As slow as a sequence scan is, it’s also a relatively simple process, and a great starting point for the long road to a parallel Postgres engine. Postgres knows how big the table files are and obviously knows its own storage format, so it merely needs to set scan ranges and farm them out to workers that report the results back to a coordinator process. In theory, that’s a clean transition that avoids complicated aggregation rules or clause pushdown headaches.
But how well does it work? To see this for myself, I did something I’ve actually never done before now: download the Postgres git tree. Then I set up a test case:
CREATE TABLE test_tab AS SELECT a.id, repeat(' ', 20) AS junk FROM generate_series(1, 20000000) a(id); ANALYZE test_tab;
With this test table, there are 20-million rows of empty junk
and no indexes, so we’re effectively forcing Postgres to use a
sequence scan for any query. Then we have to enable the feature
max_parallel_degree parameter. And finally we
invoke a query with a naive
WHERE clause applied to
every row so the engine has to actually do some work.
SET max_parallel_degree TO 1; EXPLAIN ANALYZE SELECT *
We are pleased to announce the final agenda of keynote addresses for the first annual PGConf Silicon Valley conference. Keynotes will be delivered by Jay Kreps, CEO of Confluent, Ivan Novick, Greenplum Database Product Manager at Pivotal Software, and Umur Cubukcu, CEO and Co-founder of Citus Data. The conference is next week, November 17-18, 2015, at the South San Francisco Conference Center. Organized by Citus Data in cooperation with the San Francisco PostgreSQL Users Group, PGConf Silicon Valley is a technical conference aimed at the Silicon Valley PostgreSQL community and beyond.
This year's conference theme is "High Performance & Scalability". Breakout session tracks include DevOps, New Features, PostGIS, Tales from the Trenches, Hacking Postgres, and Data at Scale.
On Wednesday, November 18, we will hear three keynote addresses that will launch a full day of breakout sessions:
On Tuesday, November 17, following the day’s tutorial sessions, there will be a no host community happy hour at The Brass Elephant Lounge and Sports Bar. Promoted in conjunction with the San Francisco PostgreSQL Users Group (SFPUG), the lounge
pg_basebackup: could not read COPY data: SSL error: decryption failed or bad record mac
$ du -s 9.2/data.test* 67097452 9.2/data.test 67097428 9.2/data.test2While also being a nearly identical size, those numbers are also suspiciously close to 64GB. I like round numbers, when a problem happens close to one that's often a pretty good tell of some boundary or limit. On a hunch that it wasn't a coincidence I checked around for any similar references and found a recent openssl package bug report:
SSL connection (cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256)
The following commit has popped up not so long ago in the development branch of Postgres 9.6:
commit: d89494166351e1fdac77d87c6af500401deb2422 author: Tom Lane <email@example.com> date: Tue, 3 Nov 2015 18:42:18 -0500 Allow postgres_fdw to ship extension funcs/operators for remote execution. The user can whitelist specified extension(s) in the foreign server's options, whereupon we will treat immutable functions and operators of those extensions as candidates to be sent for remote execution. Whitelisting an extension in this way basically promises that the extension exists on the remote server and behaves compatibly with the local instance. We have no way to prove that formally, so we have to rely on the user to get it right. But this seems like something that people can usually get right in practice.
Up to 9.5, postgres_fdw evaluates the shippability, or the possibility to execute safely a given operator or function on a remote server instead of a local using roughly three factors:
If all conditions are satisfied, this function or operator is thought as safe for remote execution, providing a huge performance gain particularly if a query works on many tuples at once. If any of those conditions is not, the function or operator will be executed locally after fetching all tuples from the remote source.
The commit above, that will be present in Postgres 9.6 in the extension postgres_fdw, brings an improvement in this area by leveraging the first condition. In short, an object does not need anymore to be a built-in one to be considered as shippable for remote execution: if an operator or function, which is immutable, is part of a white-listed extension it will be flagged as shippable wit