brandur.org

Prepared statements for psql operations

Veteran database users will be familiar with prepared statements, useful for performance by storing a known query so that it only needs to be parsed once. They have some benefits beyond performance, and I’ve come to use them in an unexpected place: psql.

We’re still running a pretty lean operation, and internal tooling is occasionally lacking or non-existent. Some operations require the use of a psql session, like enabling a feature flag. When doing so, we copy a query out of an operations doc and paste into a psql session (after a BEGIN to make sure nothing goes wrong):

INSERT INTO flag_team (
    flag_id,
    team_id
) VALUES (
    (SELECT id FROM flag WHERE name = '<flag_name>'),
    '<team_id>'
) ON CONFLICT DO NOTHING;

It works, but is inconvenient because the direction arrows are needed to move up into the query and navigate around the text to replace parameters like <flag_name> and <team_id>. The more complex the query (and some get quite big), the worse it gets.

Recently, we started replacing operational queries like the above with PREPARE and EXECUTE pairs:

PREPARE add_flag_to_team(text, uuid) AS INSERT INTO flag_team (
    flag_id,
    team_id
) VALUES (
    (SELECT id FROM flag WHERE name = $1),
    $2
) ON CONFLICT DO NOTHING;

EXECUTE add_flag_to_team('<flag_name>', '<team_id>');

The prepared statement’s performance benefits are superfluous here, but it provides a way to parameterize the query. After pasting the above, we still replace the <flag_name> and <team_id> parameters, but now they’re all on the one line at the end, making it faster and easier.

Repeated invocations less noisy, and more likely error free:

EXECUTE add_flag_to_team('use_metric_aggregates',
    eid_to_uuid('cdgsvmrdsncpbcqpcf5bkvn4qu'));
EXECUTE add_flag_to_team('use_metric_aggregates',
    eid_to_uuid('jhlqmezsejh53achhf2uu4w5lq'));
EXECUTE add_flag_to_team('use_metric_aggregates',
    eid_to_uuid('sjsx65x5lfguda25i3wjjsyp34'));

Yes, a minor improvement, but a good time saver over the long run.

Did I make a mistake? Please consider sending a pull request.