J
Josh Berkus
databasesoup.com · Personal blog · on HN since 2012
Josh Berkus, a PostgreSQL core team member, writes about Postgres administration, schema design, versioning, and consulting.
What HN saysHN commenters recommend his series on xid wraparound and his posts on primary key misuse, citing them in database design discussions.
39HN people shared it
12linked it in comments
36stories submitted
438story points
8 years agolast shared
2012people per year2026
Most-shared posts
- 1Changing PostgreSQL Version Numbering5 people · 6 points · first shared 2016 · discussion on HN
- 2Misuse of Primary Keys in SQL (Primary Keyvil)3 people · 5 points · first shared 2015 · discussion on HN
- 3PostgreSQL needs a new load balancer2 people · 108 points · first shared 2012 · 18 comments on HN
- 4Sharding Postgres at Instagram2 people · 12 points · first shared 2012 · 1 comments on HN
- 5First Look at PostgreSQL RDS on Amazon2 people · 9 points · first shared 2013 · 3 comments on HN
- 6Tag All the Things2 people · 4 points · first shared 2015 · discussion on HN
- 7Freezing your tuples off part 12 people · first shared 2015
- 8Of Booze and Brogrammers1 people · 110 points · first shared 2012 · 66 comments on HN
- 9Why HStore2/jsonb is the most important patch of PostgreSQL 9.41 people · 67 points · first shared 2014 · 75 comments on HN
- 10PipelineDB: streaming Postgres1 people · 21 points · first shared 2015 · discussion on HN
- 11Please, security test our code!1 people · 12 points · first shared 2015 · 1 comments on HN
- 12Rules of Software Consulting1 people · 11 points · first shared 2013 · 2 comments on HN
- 13PostgreSQL 9.2 - Improvements to pg_stats1 people · 10 points · first shared 2012 · 2 comments on HN
- 14PostgreSQL 9.3: Current Feature Status1 people · 9 points · first shared 2013 · discussion on HN
- 1520 Rules of Software Consulting (2013)1 people · 7 points · first shared 2014 · discussion on HN
What HN people wrote when they linked it
MechanicalTwerk · 2019-02-05
If you're looking for a good series of blog posts about xid wraparound in Postgres check out these posts by Josh Berkus:
http://www.databasesoup.com/2012/09/freezing-your-tuples-off-part-1.ht…
http://www.databasesoup.com/2012/10/freezing-your-tuples-off-part-2.ht…
http://www.databasesoup.com/2012/12/freezing-your-tuples-off-part-3.ht…
And this more recent one by Robert Haas:
https://rhaas.blogspot.com/2018/01/the-state-of-vacuum.html
As Josh states at the end of the third post the current best practices for dealing with this are really workarounds and as Robert states it requires monitoring and management. Postgres is an amazing piece of software and managing this is doable but IMHO this is one of Postgres' worst warts. It would be awesome if someone could donate some funding to improve this.
ahachete · 2016-04-08
Sure, there's CASCADE, but as you pointed out it has several drawbacks.
That's why ideally natural keys should be immutable. That has many advantages over surrogate keys.
There's a follow up post (posts) to this presentation: http://www.databasesoup.com/2015/03/primary-keyvil-reprised.html where many problems caused by the abuse of surrogate keys are illustrated.
But the main point is simple: surrogate keys (abuse) is an easy way to data corruption. Not in the sense of durability as in ACID, but in the sense of humans (manually or by bugs in the software) duplicating information that is not enforced to be unique thanks to using non-sense surrogate keys as the uniqueness criteria.
hcm · 2015-05-20
Great article. We've just moved a large portion of our async jobs over from Resque to Que because of the huge benefits you get from transactional enqueuing and processing. Performance seems good so far, and if it really becomes an issue, running two queuing systems side by side (one transactional, one high-throughput) seems viable.
We're super cautious about long-running transactions anyway, as they cause a load of other issues (e.g. http://www.databasesoup.com/2013/11/alter-table-and-downtime-part-ii.h… - full blog post coming soon!)
grzm · 2018-02-09
Largely because the previous version numbering was sometimes confusing for the opposite reason: Postgres "major" releases were sometimes interpreted as point releases. For example, 9.1 to 9.2 was major version jump. While that was understood by those who already understood the versioning scheme, it was often confusing to those outside the community.
Josh Berkus discusses this here:
http://www.databasesoup.com/2016/05/changing-postgresql-version-number…
semiquaver · 2017-09-13
The very quick exclusive lock that postgres needs for things that are normally thought of as "free" like `ALTER TABLE ADD COLUMN` without a default can still cause problems on tables that are under extremely heavy contention. If you have hundreds of clients running long transactions against a table, even this fast metadata-only lock can cause an outage due to head-of-line blocking. Our team has had good luck with this lock-polling approach for migrations against very busy tables [1]. The idea is that you combine `lock_timeout` and attempt to `LOCK TABLE IN ACCESS EXCLUSIVE MODE` in a loop prior to running the DDL. Note that this is only safe for migrations that are actually instantaneous once the lock is acquired.
[1] http://www.databasesoup.com/2015/08/lock-polling-script-for-alter-tabl…
icebraining · 2017-02-05
AFAIK the dynamic WAL sizing has already landed in 9.5: http://www.databasesoup.com/2016/01/configuration-changes-in-95-transa…
Or is this something else you're talking about?
amitlan · 2016-12-15
Postgres is going in this direction starting with the next release: http://www.databasesoup.com/2016/05/changing-postgresql-version-number…
subleq · 2015-08-11
There is a docker container with everything set up: http://www.databasesoup.com/2015/07/test-postgresql-95-alpha-in-docker…
nierman · 2015-07-24
Running more aggressive vacuums that will clean up "old" tuples/tables is good for off-peak times. See Josh Berkus' short three part blog series on postgres' vacuum/freeze:
http://www.databasesoup.com/2012/09/freezing-your-tuples-off-part-1.ht…
and his python script for doing this:
https://github.com/pgexperts/flexible-freeze
Also, note that long running transactions can prevent cleanup of tuples. Look for old xact_start values of non-idle queries in pg_stat_activity (particularly "idle in transaction" connections) and old entries in pg_prepared_xacts.
rachbelaid · 2014-12-05
If you want more details about pg_shard, have a look to this blog post.
http://www.databasesoup.com/2014/12/whats-this-pgshard-thing.html
It explains a bit more what it does and doesn't
aschreyer · 2013-11-14
PL/Python is NOT supported: http://www.databasesoup.com/2013/11/first-look-at-postgresql-rds-on-am…
joevandyk · 2012-04-30
Instagram did just fine with postgresql with 27 million users. Many logical shards spread over a smaller amount of physical ones.
http://www.databasesoup.com/2012/04/sharding-postgres-with-instagram.h…