2ndQuadrant Blog

Proper queuing in RDBMSs can be tricky. 2ndQuadrant has a good write up of what is required to make sure you don't fall into any traps: https://blog.2ndquadrant.com/what-is-select-skip-locked-for-in-postgre… Now if we're just talking about persisting data to somewhere - postgres COPY is pretty amazing.

Cross-column statistics are one of my favorites. The way Postgres 10 handles it is really a _huge_ advancement that addresses a painful edge case that has been around for ages. https://blog.2ndquadrant.com/pg-phriday-crazy-correlated-column-crusad…

I noticed that this article was written in 2015 and I wonder whether SKIP LOCKED in Postgres 9.5 could have changed the entire situation, https://blog.2ndquadrant.com/what-is-select-skip-locked-for-in-postgre… http://www.cybertec.at/skip-locked-one-of-my-favorite-9-5-features Also, duplicate of: https://news.ycombinator.com/item?id=9576864

Another technique that gets used a fair amount to force certain planner behaviors are CTEs. I've used these to great effect in some rather complex queries where performance was critical. A good start to understanding how these work in an optimization context is: http://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/

For windows functions a good tutorial can be found at http://tapoueh.org/blog/2013/08/20-Window-Functions Postgresql also has good support for SQL-99 : http://www.slideshare.net/MarkusWinand/modern-sql WITH clauses (CTE) are great, but they are "optimization fences" in Postgresql : http://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/

yes. the linked 2ndquadrant blog post explaining how to implement this using SKIP LOCKED is a good read: https://blog.2ndquadrant.com/what-is-select-skip-locked-for-in-postgre… i used this a few years ago to implement my own (toy-scale: 2 workers 1 db running on a potato-class thin client computer) idiosyncratic job queue in postgres. i have a number of recurring jobs that need to be run periodically, and the jobs are not independent -- different jobs depend upon shared resources -- so if lots of jobs that depend on resource R just ran, we might need to wait a while before the other jobs that also depend on R are deemed feasible to be scheduled and run. it was reasonably easy to cobble something together of the form: each worker process does the following: 0. within a transaction 1. select a shortlist of rows for jobs that are due and are currently feasible, subject constraints ; 2. attempt to acquire one of of those rows using "select ... for update skip locked limit 1" 3. do work, update job result and status in db, commit the transaction the only part of this that is slightly clever is that i don't actually have a hard requirement for mutual exclusion between worker processes to mediate access to shared resources, so that means each worker process can maintain its own state of its 'fair share' of resources that it is consuming, and throttle itself without needing to coordinate this with other workers.

Great to see MySQL adding this stuff! There's still a ton of reasons to choose Postgres and more and more silicon valley startups seem to be choosing pg - I don't remember the last time I met a startup choosing MySQL. DBMSs are giant complex pieces of software with a million features - it's really hard to compare them. But if I had to sum it up, you can dump freaking line noise into Postgres and then hide the nastiness and manage and query it like a well-designed database. If you have a pretty database, good for you, but today's app writers have gotten lazy with NoSQL record stores and their databases resemble vomitoriums - and let's not talk about what people used to do in the 80s and early 90s. Without further ado, Postgres vomitorium cleanup features: - user defined functions/aggegrates/windowfuncs in your favorite language incl JavaScript, which means you can write tricky business logic once and run it where the data is, vs pulling out millions of records from the database. Language list: https://www.postgresql.org/docs/10/static/external-pl.html - foreign data wrappers with hundreds of connectors AND a 5 minute toolkit for authoring new wrappers in python and other scripting languages. https://wiki.postgresql.org/wiki/Foreign_data_wrappers http://multicorn.org/ - index goddamned anything. Postgres has the most array of index types of any open source database AND if you need, you can easily write a function (in javascript or python!!!) and create an index…

> As dead tuples continue to accumulate in the index, Postgres enters a hot loop as it searches the B-tree, comes up with an invisible tuple, and repeats the process again and again, surfacing empty-handed every time. By the end of the experiment illustrated in the charts above, every worker trying to lock a job would cycle through this loop 100,000 times. 1. I am curious if the author tried setting the autovacuum more appropriately for a table that is in continuous use at 50 new rows/second. This article (http://okigiveup.net/postgresql-vacuuming-an-introduction-for-busy-dev… suggests: ALTER TABLE person SET (autovacuum_vacuum_scale_factor = 0.0); ALTER TABLE person SET (autovacuum_vacuum_threshold = 4000); This should result in entries being removed from the index and table regularly. 2. If I understand correctly, it seems the other part of the problem is that there are lots of rows to be individually tested to see if they can be locked, based on the way the author has implemented their queue. While lock attempts are being made on a set of rows, they aren't being deleted, since they're 'in use'. This non-deletion causes things to get out of control. As far as I know, 9.5's SKIP LOCKED is meant to help address that (https://blog.2ndquadrant.com/what-is-select-skip-locked-for-in-postgre… Though it appears to be doing something kinda similar under the hood. If the author of the original article (@ brandur.org) is following this discussion I would be grateful…

I'm also using the Postgres JSON type in 9.3, but my experience is different, perhaps because I come from the ZODB world and so am not under any illusions that it is a panacea! I've ended up with a very generalised schema, all content is in JSON. I've not had to change the Postgres schema for over a year and it's easy to upgrade schemas inline while fetching from the app. We did write our own migration tools, but they're only a couple of pages of code. I don't understand your complaint about lack of transactional DDL when using JSON. It's still there, though probably not as important as most of your upgrades will only touch data, giving you transactional upgrades (should you desire them) in any database. DB queries of JSON data are definitely slow in 9.3 as the values need to be parsed every query, but the lack of JSON equality has not been a problem. You simply use the double chevron text extraction operators rather than the the json extraction operators in your query, col->foo->>bar rather than col->foo->bar. From there you can cast to any type and join to other tables. For us, the big advantage of using a generalized schema has been simple support for recording versions of data. With over 30 different content types doing this with a traditional relational schema would have resulted in maybe 90 tables. Our generic schema has six. I'm really looking forward to 9.4 as it will make ad-hoc queries across JSONB really fast using GIN indexes. I'd love to see the option of…

Don't forget SKIP LOCKED[0]! [0]: https://blog.2ndquadrant.com/what-is-select-skip-locked-for-in-postgre…

> 1: Never add a column with a default value This only applies to Postgres versions < 11. See https://blog.2ndquadrant.com/add-new-table-column-default-value-postgr… which was released after this article was written.

And no need to play with columns ordering like with psql: https://blog.2ndquadrant.com/on-rocks-and-sand/

This was an informative read: https://blog.2ndquadrant.com/on-rocks-and-sand/

Introduction to ICU in PostgreSQL: https://blog.2ndquadrant.com/icu-support-postgresql-10/

I meant the style choices. But it did also choke on Identity Columns IIRC (https://blog.2ndquadrant.com/postgresql-10-identity-columns/). Not knocking either project - just pondering adoption.

I think this is being addressed now: https://blog.2ndquadrant.com/enhancements-to-partitioning-and-indexes-…

No, it didn't. It had functions, not procedure. You can emulate (most of) procedure using function that return void. See also https://blog.2ndquadrant.com/postgresql-11-server-side-procedures-part…

Also not true. 9.6 is supported and the next release (scheduled soon) will support 10. Those changes will eventually end up in 12. https://www.2ndquadrant.com/en/resources/bdr/ https://blog.2ndquadrant.com/news-and-roadmap-for-bdr-multi-master-pos… I'm not sure what you mean by somewhat equivalent. Both are primarily asynchronous multimaster approaches.

> very interesting read! Well, it's also quite a misleading read as the numerous rebuttals will point out: http://rhaas.blogspot.co.uk/2016/08/ubers-move-away-from-postgresql.ht… https://blog.2ndquadrant.com/thoughts-on-ubers-list-of-postgres-limita… http://use-the-index-luke.com/blog/2016-07-29/on-ubers-choice-of-datab…

Give https://blog.2ndquadrant.com/untangling-the-postgresql-upgrade/ a read perhaps.

Does anyone know whatever happened to the columnar storage work that 2nd quadrant posted on their blog: https://blog.2ndquadrant.com/postgresql-10-roadmap/ Is this essentially a pipe dream?

> Just be sure to run your enqueueing process as a singleton, or each worker would be redundantly enqueueing lots of jobs. This can be guarded with a session advisory lock or a redis lock. If you're using PostgreSQL 9.5+ you can also use the new SKIP LOCKED capability, which is perfect for this sort of usage: https://blog.2ndquadrant.com/what-is-select-skip-locked-for-in-postgre…

It appears work is active (May 2017) in this area, but accent-insensitive collations are still a ways off: https://blog.2ndquadrant.com/icu-support-postgresql-10/ edit: fulafel corrects me below that you can get accent-insensitive collations working using PostgreSQL 10's new ICU collation support. Note that PostgreSQL 10 is currently pre-release.

there is, PostgreSQL https://blog.2ndquadrant.com/application-users-vs-row-level-security/ (and saying GraphQL can't work with databases I think is wrong, it's the same as saying OO languages are not a good fit for relational databases)

I hadn't heard of skip locked before, and very much enjoyed this article walking through it for postgres: https://blog.2ndquadrant.com/what-is-select-skip-locked-for-in-postgre…

Important features: Native Table Partitioning - https://www.keithf4.com/postgresql-10-built-in-partitioning/ Logical Replication - https://blog.2ndquadrant.com/logical-replication-postgresql-10/ or what is new in PG 10 https://wiki.postgresql.org/wiki/New_in_postgres_10 Postgresql really awesome!

https://blog.2ndquadrant.com/on-the-impact-of-full-page-writes/ this is fairly detailed analysis of this issue

There was a nice response from the PostgreSQL guys here: http://blog.2ndquadrant.com/dataloss-at-gitlab/ There is a bug that might have been hit, but it appears as though there were other issues at play as well.

How does this e.g. compare to Barman mentioned in the 2ndquadrant Gitlab data loss reply http://blog.2ndquadrant.com/dataloss-at-gitlab/

PG 9.6 brought bidirectional replication in and supported via mainline extension (no patches). http://blog.2ndquadrant.com/bdr-is-coming-to-postgresql-9-6/