Monday, March 25

Author: craig.ringer

PostgreSQL-based application performance: latency and hidden delays

PostgreSQL-based application performance: latency and hidden delays

Craig's PlanetPostgreSQL, PostgreSQL
If you're trying to optimise the performance of your PostgreSQL-based application you're probably focusing on the usual tools: EXPLAIN (BUFFERS, ANALYZE), pg_stat_statements, auto_explain, log_statement_min_duration, etc. Maybe you're looking into lock contention with log_lock_waits, monitoring your checkpoint performance, etc too. But did you think about network latency? Gamers know about network latency, but did you think it mattered for your application server? (more…)

Visual PostgreSQL debugging

Craig's PlanetPostgreSQL
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. (more…)

Don’t set fsync=off if you want to keep your data

Craig's PlanetPostgreSQL
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 protected] Per a suggestion from Craig Ringer. This wording from Tom Lane, following discussion. (more…)

LVM: “Unable to create a snapshot smaller than 2 chunks”

Craig's PlanetPostgreSQL
If you try to create an LVM snapshot with lvm lvcreate -s -n snapname -L 100G myvg and it fails with Unable to create a snapshot smaller than 2 chunks. ... it's probably actually trying to tell you "I don't have 100GB free on this VG, allocate a smaller snapshot". It's choosing to phrase it creatively. You'll only get this error if you're within a certain margin of sizes: much too big and it'll tell you a rather clearer: Volume group "myvg" has insufficient free space (1000 extents): 2000 required. so it's really just a bit of a limitation or cosmetic bug. It's not very Google-able though, hence this post. Next time I hit this error I'll search for it and find my own writing, having completely forgotten that I was involved in a case where the team I was on saw (more…)

Failover slots for PostgreSQL

Craig's PlanetPostgreSQL
Logical decoding and logical replication is getting more attention in the PostgreSQL world. This means we need it working well alongside production HA systems - and it turns out there's a problem there. Replication slots are not themselves synced to physical replicas so you can't continue to use a slot after a master failure results in promotion of a standby. The failover slots patch changes that, syncing slot creation and updates to physical replica servers such as those maintained with WAL archives or streaming replication. That lets logical decoding clients seamlessly follow a failover promotion and continue replay without losing consistency. (more…)

Emulating row security in PostgreSQL 9.4

Craig's PlanetPostgreSQL
PostgreSQL 9.5 adds declarative row security. You can declare policies on tables and have them enforced automatically - for example, allowing user joe to only see rows with the owner column equal to joe. This is a great feature, and it's been a long time coming. It didn't make it into PostgreSQL 9.4, but automatically updatable security_barrier views did. They and LEAKPROOF functions form part of the foundation on which row security is built. You can use these pieces without the declarative policy support to achieve row-security-like effects in 9.4. I discussed security_barrier views earlier. That post contains examples of how information can be leaked from a view and how security_barrier views prevent such leaks. I'll assume you're familiar with the principles in the rest of this post, (more…)

When are we going to contribute BDR to PostgreSQL?

Craig's PlanetPostgreSQL
My colleagues and I are often asked "when will you (2ndQuadrant) contribute BDR to PostgreSQL?" This makes an interesting assumption: that we have not already done so. We have. Much of BDR has already been contributed to core PostgreSQL, with more to come. All of BDR is under the PostgreSQL license and could be merged into PostgreSQL at any time if the community wished it (but it doesn't and shouldn't; see below). It's now quite trivial to implement an extension to add simple multi-master on top of the facilities built-in to PostgreSQL 9.5 if you want to. See the work Konstantin Knizhnik and colleagues have been doing, for example. Many if not most of the facilties that make this possible were added for and as part of the BDR project. (more…)

How to find out which PostgreSQL table a file on disk corresponds to

Craig's PlanetPostgreSQL
Sometimes you need to determine which table a file on disk corresponds to. You have a path full of numbers like base/16499/19401 and you want to make sense of it. You might be looking at an error message that mentions a file name, for example, like: ERROR: could not read block 11857 of relation base/16396/3720450: read only 0 of 8192 bytes Viewing the path of a relation You can see the path of a table using: SELECT pg_relation_filepath('tablename'); but what about the reverse, getting the relation name from the path? There's a function named pg_filenode_relation that looks handy for that ... but to use it you already need to be connected to the particular database the file corresponds to, which means you need to know that. Structure of file paths Here's how to (more…)