The other day I got a link to an interesting post published by Uber, which has caught our attention here at Cybertec: https://eng.uber.com/go-geofence The idea behind geo-fencing is to provide information about an area to users. Somebody might want to find a taxi near a certain location or somebody might simply want to order a […]
As one of the founders of United States PostgreSQL, I have always had a vision of where the corporation should go. Our corporation has continued to move forward and we have achieved some great things. Although it has taken longer than I expected and we are still behind from some of my goals, PostgreSQL has become one of the dominant Open Source databases in the market. By sticking to a tried, true and tested model our community has built strongholds in every sector of business from small mom-and-pops to Wall Street to the Military.
Having recently been doing some debugging work where many watchpoints, conditional breakpoints etc were necessary I’d like to shout out to a really useful tool: The standalone CDT debugger.
It’s part of the Eclipse project, but before you run screaming – it doesn’t require project setup or anything and it serves as a good GUI gdb wrapper. It’s good for working with PostgreSQL because you don’t need to set up a fake IDE project or any such time-wasting business. You just launch the debugger. You’ve still got your .gdbinit with postgresql debug helper macros and everything.
The CDT debugger still gives you access to the gdb console and all the features you’re used to, it just has a good UI for showing the values of locals, watched variables, stack browsing, setup of conditional breakpoints, etc.
dnf install eclipse-cdt
aptitude install eclipse-cdt
then get the backend pid of interest from ps or SELECT pg_backend_pid() and
cdtdebug -a $PID
It supports core files, debugging a program under direct control, and attaching.
The debugger does have a small startup time, unlike gdb, so it’s worth keeping open and detaching/re-attaching as needed when convenient. But it’s well worth it:
I’m looking into how to incorporate Tomas’s gdbpg or even better, extension support for displaying a List*‘s contents in the variable browse tree. Even without that it’s pretty handy.
The new feature version of pglogical is now available. The new release brings support for sequence replication, manually configured parallel subscriptions, replica triggers, numerous usability improvements and bug fixes. Let’s look into the changes in more detail.
As the title of this post says, pglogical now supports sequence replication. While I am sure this is good news for everybody, for me the main importance of this feature is delivering better experience with the zero-downtime upgrades. With the first version of pglogical you’d have to manage sequences manually after the data were transfered which complicates the whole procedure, now they just get replicated.
PostgreSQL does not provide any infrastructure for capturing
sequence changes (not for lack
of trying, it just turns out that it’s hard to add support for
it). So we use similar trick as one of the previous replication
projects I worked on. We periodically capture state current state
of the sequence and send update to the replication queue with some
additional buffer. This means that in normal situation the sequence
values on subscriber will be ahead of those on provider. We
try to dynamically size the buffer so that the value is as close as
possible but also does not fall behind on frequently updated
sequences. It is however advisable to force the sequence update
after big data loads or upgrade.
Other than the above, the sequence replication works similarly to the way tables work. This includes the replication sets, so there are functions for adding and removing sequences to replication sets and subscribers can receive updates only for some sequences.
Another major change is support for multiple subscriptions between one pair of node. In pglogical 1.0 there was strict limit on single subscription between one pair of nodes. You could replicate from one node to multiple nodes or from multiple nodes to one node but no parallel subscriptions between the same two[...]
Having run into a bit of a snag with Postgres-XL, and not wanting to be dead in the water with our project, I went on a bit of a knowledge quest. Database scaling is hard, so I expected a bunch of either abandoned or proprietary approaches. In addition, as a huge fans of Postgres, compatibility or outright use of the Postgres core was a strict prerequisite.
So, what options are out there? Is there even anything worth further investigation? Maybe more importantly, what do you do when you’re under a bit of a scheduling constraint? Projects need to move forward after all, and regardless of preferences, sometimes concessions are necessary. The first step was obviously the list of databases derived from Postgres.
At first glance, that’s a pretty big list. If we look carefully though, we can see that quite a few of those projects were abandoned years ago. Others are commercial, not based on scalability, or both. Being commercial isn’t automatically a disqualification, but most of the commercial options were forked from practically ancient versions of Postgres and never kept up compatibility, or don’t mention the version at all. Amazon Redshift fits that profile, being based on Postgres 8.0, which few would want to use these days. Fujitsu Enterprise is another, which doesn’t even list which version they’re based on, nor do they provide a download for testing purposes.
What’s left? It’s hard to tell from the Wiki page, so I just started with the projects that include some kind of data scaling not based on replication. These candidates present a longer list than I’d initially anticipated, which is always a good problem to have!
Let’s scrutinize the nominees.
It’s not really a secret that CitusDB and Postgres-XL are both tackling the same problem, and are currently the top two contenders. Unlike Postgres-XL and its approach of extending SQL syntax to directly embrace data distribution, CitusDB is actually just a Postgres extension.
As a result, it’s a bit more janky. There’s no
TABLE ... DISTRIBUTE BY magic
The focus of my energies has been a long-awaited (by me) update to the OGR FDW extension for PostgreSQL. By binding the multi-format OGR library into PostgreSQL, we get access to the many formats supported by OGR, all with just one piece of extension code.
As usual, the hardest part of the coding was remembering how things worked in the first place! But after getting my head back in the game the new code flowed out and now I can reveal the new improved OGR FDW!
The new features are:
Foreign tables are now updateable! That means,
for OGR sources that support it, you can run
DELETE commands on your OGR FDW tables
and the changes will be applied to the source.
UPDATEABLEoption on the foreign server and foreign table definitions.
As usual, I’m in d[...]
Postgres has a wonderful feature called concurrent indexes. It allows you to create indexes on a table without blocking reads OR writes, which is quite a handy trick. There are a number of circumstances in which one might want to use concurrent indexes, the most common one being not blocking writes to production tables. There are a few other use cases as well, including:
In this article, I will focus on that last use case, restoring a database as quickly as possible. We recently upgraded a client from a very old version of Postgres to the current version (9.5 as of this writing). The fact that use of pg_upgrade was not available should give you a clue as to just how old the "very old" version was!
Our strategy was to create a new 9.5 cluster, get it optimized for bulk loading, import the globals and schema, stop write connections to the old database, transfer the data from old to new, and bring the new one up for reading and writing.
The goal was to reduce the application downtime as much as reasonably possible. To that end, we did not want to wait until all the indexes were created before letting people back in, as testing showed that the index creations were the longest part of the process. We used the "--section" flags of pg_dump to create pre-data, data, and post-data sections. All of the index creation statements appeared in the post-data file.
Because the client determined that it was more important for the data to be available, and the tables writable, than it was for them to be fully indexed, we decided to try using CONCURRENT indexes. In this way, writes to the tables could happen at the same time that they were being indexed - and those writes could occur as soon as the table was populated. That was the theory anyway.
The migration went smooth - the data was transferred over quickly,[...]
Product search is a common, yet sometimes challenging use-case for online retailers and marketplaces. It typically involves a combination of full-text search and filtering by attributes which differ for every product category. More complex use-cases may have many sellers that offer the same product, but with a different price and different properties.
PostgreSQL has the functionality required to build a product search application, but with a large product catalog scaling it can be difficult. With the Citus extension, PostgreSQL can distribute tables and parallelize queries across many servers, making it easy to scale out your memory and compute power. Couple Citus with PostgreSQL's full-text search and it becomes fast and easy to do interactive searches on a large product catalog. While the search functionality is not as comprehensive as in dedicated search solutions, a huge benefit of keeping the data in PostgreSQL is that it can be updated in real-time and tables can be joined. This post will go through the steps of setting up an experimental products database with parallel search functions using PostgreSQL and Citus, with the goal of showcasing several powerful features.
We start by setting up a multi-node Citus cluster on EC2 using 4 m3.2xlarge instances as workers. An even easier way to get started is to use the brand new Citus Cloud, which gives you a managed PostgreSQL cluster with Citus and full auto-fail-over. The main table in our database schema is the "product" table, which contains the name and description of a product, its price, and attributes in JSON format such that different types of products can use different attributes:
$ psql CREATE TABLE product ( product_id int primary key, name text not null, description text not null, price decimal(12,2), attributes jsonb );
To distribute the table using Citus, we call the functions for hash-distributing the table into 16 shards (one per physical core). The shards are distributed
There are a lot of amazing features coming in PostgreSQL 9.6, but I’m personally very happy about a really small, simple one that helps close a long-standing user foot-gun.
commit a31212b429cd3397fb3147b1a584ae33224454a6 Author: Robert Haas Date: Wed Apr 27 13:46:26 2016 -0400 Change postgresql.conf.sample to say that fsync=off will corrupt data. Discussion: email@example.com Per a suggestion from Craig Ringer. This wording from Tom Lane, following discussion.
There’s a bit of terrible advice floating around that turning fsync=off will make PostgreSQL run faster. Which is true, as far as it goes, but neglects that little risk massive data corruption on crash part. So users get bitten. I’ve tried to scrub as much of that advice from the Internet as I can or get it qualified with warnings, but I still see people advised to do it on random forums sometimes.
The docs do a good job of explaining that setting fsync=off is a bad idea, but the sample config file made it seem pretty innocuous:
#fsync = on # turns forced synchronization on or off
so users keep turning it off. Then teams like 2ndQuadrant land up doing expensive forensic data recovery on their database clusters once they crash weeks, months or years later and experience severe corruption… or notice the corruption they experienced a while ago. Or users just write off their data and start again because it’s all too hard and expensive.
To make turning fsync=off a little less attractive the sample now reads:
#fsync = on # flush data to disk for crash safety # (turning this off can cause # unrecoverable disk corruption)
It won’t stop someone using ALTER SYSTEM SET without realising, but there’s only so much you can do.
I’m really happy about this. It’s nice to knock off such minor improvements that have a disproportionate impact on usability and UX.
If you are ever tempted to set fsy[...]
I find myself quite often having to split a large write operation such as updating millions of rows in a heavily used table. This usually boils down to writing a small Perl script that runs a small one-off function that performs the task by updating a limited numbers of rows per run, and then committing in between to avoid a long running transaction.
This time I needed to do this I decided to not write yet another Perl script and to instead do something to improve the situation. I’ve not been able to find any cron-ish tools at all to run database functions, except for pgAgent, but that looks more like a client-side daemon, I wanted something bare-minimum that can run server-side and piggy-back on the OS cron.
A few hours later and 382 lines of code later, the project was finished and pushed to Github:
createuser pgcronjob psql -f install.sql crontab pgcronjob.crontab psql CREATE OR REPLACE FUNCTION public.CronJob_Function_Template_Skeleton() RETURNS batchjobstate LANGUAGE plpgsql SET search_path TO public, pg_temp AS $FUNC$ DECLARE BEGIN RAISE NOTICE 'Hello world!'; PERFORM pg_sleep(random()); RAISE NOTICE 'Slept for a while.'; IF random() < 0.5 THEN -- Tell CronJob() we have more work to do and we want it to run us again in due time RAISE NOTICE 'See you again!'; RETURN 'AGAIN'; ELSIF random() < 0.5 THEN -- Throw error to CronJob() to test errors RAISE EXCEPTION 'Simulate error in CronJob function'; ELSE -- Tell CronJob() we're done and we don't want it to run us ever again RAISE NOTICE 'Bye world!'; RETURN 'DONE'; END IF; END; $FUNC$; GRANT EXECUTE ON FUNCTION public.CronJob_Function_Template_Skeleton() TO pgcronjob; SELECT CronJob_Register('public','cronjob_function_template_skeleton');
Hopefully this will be useful for others as well. It would be fun to get some feedback.
The initial commit even comes with a nice README and install and uninstall scripts.
Extract from the README:
The settings are conditions that must all be TRUE for the cronjob to run, i.e.[...]
If you ever need quick help with PostgreSQL from other people (sure, you should check manuals first) just remember one link POSTGRES.chat.
Thanks PostgreSQL in Russia for hint.
After spending pretty much the entire week on freeing a customer from the chains of Oracle’s license policy, I found an interesting issue, which made me smile: When the Oracle code was built 15 years ago or so, somebody created a table called “pg_type” on the Oracle side as part of the application. With only […]
Having worked on Postgres for twenty years, I have seen a lot of features added. However, I usually evaluate new features in isolation and rarely see the big picture of how they fit together. Last month I created a new talk, Non-Relational Postgres, which presents a comprehensive overview of non-relational Postgres storage.
The consistent use of @> (containment operator) listed on slide 70 is interesting. It also has good coverage of how character strings can be split apart and indexed.
While attending the hallway track, I was talking to a gentleman whose name escapes me. He asked me how he could contribute to the community. I am always excited to have that conversation because we are able to discuss all kinds of different ways to contribute, whether it be social (user groups, pgday, speaking at alternative conferences), documentation, code, or infrastructure. Bringing people to the community truly inspires me to continue building our community into something special.
Then he said, "I talked to X and he said, 'Why would I tell you that, you are my competitor?'" I was stunned. It is a sad and unacceptable ideology to hold within any open source community. It is an ideal that should be left to bad business practices from past decades. It is a horrible statement to make to any potential community member and speaks to a less than stellar understanding of how our community works.
If any person asks you how they can help with .Org, you should answer them honestly and enthusiastically. If you don't know how they can help, find a contributor and help the new person get connected. You should do this for any person looking to contribute, no matter who they work for (even if it is a competitor). Any other way of handling it is taking away from the community and is a testament to a selfishness that has no place here.
We are an inclusive community and that means we recognize something greater than ourselves. We recognize the power of a sustainable, productive .Org that is a collaborative space without the belittling behavior described in this post. I strongly hope that we can eliminate negative behavior such as this and become the most un[...]
pg_rewind, a utility introduced in PostgreSQL 9.5 allowing one to reconnect a master to one of its promoted standbys, making unnecessary the need of a new base backup and hence saving a huge amount of time, has gained something new in Postgres 9.6 with the following commit:
commit: e50cda78404d6400b1326a996a4fabb144871151 author: Teodor Sigaev <firstname.lastname@example.org> date: Tue, 1 Dec 2015 18:56:44 +0300 Use pg_rewind when target timeline was switched Allow pg_rewind to work when target timeline was switched. Now user can return promoted standby to old master. Target timeline history becomes a global variable. Index in target timeline history is used in function interfaces instead of specifying TLI directly. Thus, SimpleXLogPageRead() can easily start reading XLOGs from next timeline when current timeline ends. Author: Alexander Korotkov Review: Michael Paquier
The following document gives a short introduction about pg_rewind. You may want to look at it before moving on with this post and understand why here is described a damn cool feature. First, before entering in the details of this feature, let’s take the example of the following cluster:
Node 1 / \ / \ / \ Node 2 Node 3 / / / Node 4
Node 1 is a master node, with two standbys directly connected to it, presented here as nodes 2 and 3. Finally node 4 is a cascading standby connected to node 2. Then say that all the nodes have been successively promoted. At promotion, a standby finishes recovery and jumps to a new timeline to identify a new series of WAL record generated by what is now a master node to avoid overlapping WAL records from another node. The promotion of each node in the cluster is done in the following order, and results in the following timelines being taken by each node:
Over at pgsql-general, Bráulio Bhavamitra asks:
I wonder if there is any plans to move postgresql entirely to a columnar store (or at least make it an option), maybe for version 10?
This is a pretty interesting question. Completely replacing the current row-based store wouldn’t be a good idea: it has served us extremely well and I’m pretty sure that replacing it entirely with a columnar store would be disastrous performance-wise for OLTP use cases.
That doesn’t mean columnar stores are a bad idea in general — because they aren’t. They just have a more limited use case than “the whole database”. For analytical queries on append-mostly data, a columnar store is a much more appropriate representation than the regular row-based store, but not all databases are analytical.
However, in order to attain interesting performance gains you need to do a lot more than just change the underlying storage: you need to ensure that the rest of the system can take advantage of the changed representation, so that it can execute queries optimally; for instance, you may want aggregates that operate in a SIMD mode rather than one-value-at-a-time as it is today. This, in itself, is a large undertaking, and there are other challenges too.
As it turns out, there’s a team at 2ndQuadrant working precisely on these matters. We posted a patch last year, but it wasn’t terribly interesting — it only made a single-digit percentage improvement in TPC-H scores; not enough to bother the development community with (it was a fairly invasive patch). We want more than that.
In our design, columnar or not is going to be an option: you’re going to be able to say Dear server, for this table kindly set up columnar storage for me, would you? Thank you very much. And then you’re going to get a table which may be slower for regular usage but which will rock for analytics. For most of your tables the current row-based store will still likely be the best option, because row-based storage is much bette[...]
What a great meeting last night! Made me feel a bit better about missing PgConf.US
We had a good turnout, too; several first-timers showed up, some hauling themselves all the way in from Nike & Intel.
Thanks to Eric Ferreira, AWS Database Engineer, and Tony Gibbs, AWS Solutions Architect, for coming all the way down from Seattle to give this excellent talk. Eric is the originator of Redshift (it’s based on Pg 8.0.4), and he shared with us some of the features & why he made some of the decision he did.
Special thanks goes to Veronika Megler, RDS Professional Services team, for proposing the topic and arranging for Eric & Tony to come on down to PDX.
We’re already working on a return visit for late summer, so if you missed this meeting, you will get another chance.
Postgres is a great tool for most databases. Larger installations however, pretty much require horizontal scaling; addressing multi-TB tables relies on multiple parallel storage streams thanks to the laws of physics. It’s how all immense data stores work, and for a long time, Postgres really had no equivalent that wasn’t a home-grown shard management wrapper. To that end, we’ve been considering Postgres-XL as a way to fill that role. At first, everything was going well. Performance tests showed huge improvements, initial deployments uncovered no outright incompatibilities, and the conversion was underway.
Then we started to use it.
Much to our chagrin, dev and test environments aren’t always indicative of real scenarios and utilization patterns. Once Postgres-XL reached our semi-production environment, everything started to go wrong. Here’s a list of what happened, and why our 43TB (and growing) database can’t use Postgres-XL until it matures a bit more. As a note, all of the examples listed here were observed using the latest 9.5r1 release.
I also want to strongly emphasize that this is not an indictment of Postgres-XL. Postgres greatly needs the capabilities it adds to truly break into the VLDB market. Sometimes though, projects aren’t as capable as we originally hoped, and subtle issues aren’t always readily apparent until software is field-tested. We’re still not ruling it out.
That said, on to the adventure.
In short, there isn’t any. This at least, was something we knew about. In our case, it wasn’t a problem because a VLDB installation is commonly a warehouse of some description. These tend to depend on ETL or other source scripts to supply data that’s already prepared and doesn’t rely on post-insert transformations.
For anyone else, this could be a roadblock. Trigger support isn’t always essential to running an active database, but they’re common enough that a conversion process will have to contend with replacing them. This missing feature practically relegates Postgres-XL to warehouse u[...]
We gave a PostGIS Intro Training and a PostGIS talk at PGConfUS 2016 in Brooklyn, New York and just got back. A number of people asked if we'd make the slides and material available. We have these posted on our presentation page: http://www.postgis.us/presentations and will be putting on the PostgreSQL Wiki as well in due time. There will be a video coming along for the talk, but the training was not recorded.
We also have two more talks coming up in North Carolina in Early May at FOSS4G NA 2016 - one on PostGIS Spatial Tricks which has more of a GIS specialist focus than the top 10 talk we gave, but there will be some overlap. The other talk is a topic a couple of people asked us in training and after our talk, on routing along constrained paths. If you are attending FOSS4G NA 2016, you won't want to miss our talk pgRouting: A Crash Course which is also the topic of our upcoming book.
Just like FOSS4G NA 2015, there is a pgDay track which is PostgreSQL specific material, useful to a spatial crowd, but not PostGIS focused.