23:03
PostgreSQL Performance When It’s Not Your Job
My presentation from SCALE 10x, “PostgreSQL Performance When It’s Not Your Job” is now available for download.
24 January 2012
30 September 2011
22:39
“Sharding & IDs at Instagram”
I’d like to recommend an interesting post, “Sharding & IDs at Instagram”, about sharding using Postgres.
27 September 2011
22:25
Cleaning up after your Bucardo goats
If you are not familiar with it already, Bucardo is a nifty multi-master replication system for PostgreSQL, written by Greg Sabino Mullane. Written in Perl, it is great if you need replication that doesn’t have the restrictions associated with PG 9’s streaming replication.
To keep your Bucardo installation clean and tidy, a few regular cron jobs are required. One of them cleans up the archived replicated data (stored in a separate database by Bucardo) once you know you are done with it.
The Bucardo page above has a recommended script using all sorts of bashing, but I wanted something a bit more pure-PostgreSQL; it also doesn’t purge more than one old table at a time. So, I whipped up the following PL/pgSQL function.
(Note that this is for Bucardo 4.4. I haven’t played with the forthcoming Bucardo 5, so I’m not sure if this is still required.)
CREATE OR REPLACE FUNCTION bucardo.purge_frozen_child_qs(far_back interval)
RETURNS SETOF TEXT AS
$purge_frozen_child_qs$
DECLARE
t TEXT;
qt TEXT;
BEGIN
IF far_back IS NULL THEN
RAISE EXCEPTION 'Interval cannot be null.'
USING HINT = 'So, do not do that.';
END IF;
IF (now() + far_back) > now() THEN
RAISE EXCEPTION 'Interval must be negative.'
USING HINT = 'Consider using the "ago" form of intervals.';
END IF;
FOR t IN
SELECT tablename
FROM pg_tables
WHERE schemaname='freezer'
AND tablename like 'child_q_%'
AND (replace(tablename, 'child_q_', '')::timestamp with time zone) < now() + far_back::interval
ORDER BY tablename
LOOP
qt := 'freezer.' || t;
EXECUTE 'DROP TABLE ' || qt;
RETURN NEXT qt;
END LOOP;
DELETE FROM bucardo.q
WHERE (started < now() + far_back::interval
OR ended < now() + far_back::interval
OR aborted < now() + far_back::interval
OR cdate < now() + far_back::interval)
AND (ended IS NULL OR aborted IS NULL);
RETURN;
END
$purge_frozen_child_qs$
LANGUAGE plpgsql
VOLATILE;
To use it, just call it repeatedly from a cron job with the appropriate argument, along the lines of:
SELECT * FROM bucardo.purge_frozen_child_qs('7 days ago'::interval);
It returns the names of the tables it deleted.
This particular function doesn’t need to be run more often than once a day. And it keeps your Bucardo goats nice and clean.
(A “bucardo” is a now-extinct species of goat. For why Bucardo is goat-related, ask Greg.)
26 July 2011
14:14
Unbreaking Your Django Application
My tutorial at OSCON 2011, Unbreaking Your Django Application, is now available for download.
18 May 2011
28 March 2011
09:49
Not Convenient.
DjangoCon Europe and the Apple WWDC are at the exact same time. This is going to be a tough call.
Update: Well, that was quick. WWDC sold out in 10 hours, while I was dithering.
18 March 2011
18:08
What It Means to Be In Business
To bring everyone up to date:
- Justin Vincent wrote a post offering an opinion about the downsides of the chase of tech entrepreneurs over VC funding.
- Amy Hoy wrote a post expanding on Mr Vincent’s post.
- Alex Payne wrote a post criticizing this position, while finding it necessary to describe “long time acquaintance” Amy Hoy’s product as “duping credulous customers into overpaying for a time-tracking tool styled with this month’s CSS trends.”
- Unaccountably, he seems to have been surprised by the negative reaction this post generated, so he posted an explanation and partial retraction here.
Sadly, I find his last post as incoherent as his first one is vitriolic.
Rather than go through it point by point, the crux of his argument is:
Building a business around maximizing your individual happiness is not particularly useful or admirable. That is my position, and I’m well aware that it may be unpopular with some.
I am pleased to report, then, that Mr Payne has absolutely nothing to worry about, because no business that is built around the happiness of the owner as a primary goal has a hope of every getting anywhere, unless the business consists of the owner taking money out of one pocket and putting it in the other. Any business, unless it is operating in a grotesquely distorted marketplace, is primarily about pleasing its customers in exchange for their money.
I’m really not sure what these vaguely masturbatory companies Mr Payne is talking about do for a living, but every (successful) micro-business I know of is insanely, intensely focused on pleasing its customers. They have to be, because they don’t have an installed base, government-granted advantages, or (yes) piles of venture capital in the bank to fall back on if they fail to do so.
Mr Payne wants to run a big company. I wish him all the best. He seems to have his young heart in the right place. I have to say, though, that his emotional overreaction to the idea that someone might want to run a micro-business instead strikes me as the Puritan reacting to the idea that someone, somewhere, might be happy.
13 March 2011
15:42
Transaction-Level Advisory Locks in PostgreSQL 9.1
Advisory locks are one of the cool unsung features of PostgreSQL. In 9.1, they are getting even cooler with transaction level locks. Many details here.
9 March 2011
23:25
Concern Troll is Concerned: Verifone vs Square
Suppose a major manufacturer of computer keyboards announced a very serious security problem with a specific competitor’s keyboard: Someone could plug this keyboard into a computer running a malicious app, and cause a user to enter sensitive information. Thus, the manufacturer demands that their competitor recall all of these “insecure” keyboards.
Anyone with the technical sense of a rock would pause for a moment, and then burst out in laughter at the utter absurdity of this proclamation. No one would ever attempt to make such a ludicrous and obviously self-serving claim, would they?
Verifone would. Verifone is very very concerned about Square’s iPhone card scanner, because someone could run a malicious app on the iPhone and collect card data using it. The fact that Square just announced new pricing undercutting Verifone’s is, of course, completely coincidental.
Where to begin?
It is true that Square’s attachment does not encrypt the card track information between the iPhone and the card reader. This is true of pretty much every single card reader in the entire world. It is not the job of the card reader to encrypt data, any more than it is the job of the keyboard to encrypt your password. Verifone seems unconcerned at all of the other card readers you can buy from, say, Amazon (just for example).
For Verifone’s apocalyptic scenario to occur, the iPhone into which the card reader is plugged must be running a malicious app. This pretty much requires the iPhone user to be in on the scam, which means that they could be using any hardware they wish to collect this card data. If the merchant is crooked, then they’ll find a way to collect the card data, since they have possession of the card (on which is printed essentially all of the relevant data that is on the mag tracks, plus the CVV printed on the back).
Verifone’s competing solution, if the brochure is to be believed, encrypts the data at swipe-time. That’s nice, but the chance of card data being compromised between the reader and the iPhone, or during that extremely limited time that it is sitting unencrypted in the iPhone’s memory, is essentially zero. Again, Verifone seems unconcerned that Square’s app works exactly like every other PC-based credit card processing application in the entire world; indeed, Square’s is considerably more secure than most, since the merchant doesn’t have access to the card information. (For example, on my completely certified, authorized, and every-spec-compliant Nurit wireless card processing terminal, I can retrieve credit card numbers from a batch with no hassle whatsoever.)
In short, Verifone is bashing a competitor because the competitor’s pricing is more consumer-friendly than Verifone’s. Their technical arguments are nonsense, and they should be ashamed of launching a FUD campaign that plays on credit card security paranoia.
23:03
Django and PostgreSQL at PostgreSQL Conference East
I’ll be giving a full day tutorial about developing Django applications using PostgreSQL. If you are just getting started with Django, this is a great introduction; it is intended for developers who are just getting into serious Django/PG development.
It’ll cover general development in Django, with a lot of PostgreSQL-specific details.
And, of course, the whole conference will be a fount of great PostgreSQL geekery.