Tom Swartz
Tom Swartz
As a database grows and scales up from a proof of concept to a full-fledged production instance, there are always a variety of growing pains that database administrators and systems administrators will run into. Very often, the engineers on the Crunchy Data support team help support enterprise projects which start out as small, proof of concept systems, and are then promoted to large scale production uses. As these systems receive increased traffic load beyond their original proof-of-concept s...
Read MoreJonathan S. Katz
Jonathan S. Katz
We've seen an example of how to set up PostgreSQL monitoring in Kubernetes . We've looked at two sets of statistics to keep track of it in your PostgreSQL cluster: your vitals (CPU/memory/disk/network) and your DBA fundamentals . While staring at these charts should help you to anticipate, diagnose, and respond to issues with your Postgres cluster, the odds are that you are not staring at your monitor 24 hours a day. This is where alerts come in: a properly set up alerting system will let...
Read MoreJonathan S. Katz
Jonathan S. Katz
I am an accidental DBA, with a huge emphasis on "accidental." I came to PostgreSQL as an application developer who really liked to program with SQL and use the database to help solve my problems. Nonetheless, these systems would enter into production, and as such I had to learn to support them. PostgreSQL monitoring and performance optimization is a vast topic . In fact, I'll read content like what my colleague Greg Smith wrote on benchmarking PostgreSQL 13 on Ubuntu and be reminded that I h...
Read MoreJonathan S. Katz
Jonathan S. Katz
My professional background has been in application development with a strong affinity for developing with PostgreSQL (which I hope comes through in previous articles ). However, in many of my roles, I found myself as the "accidental" systems administrator, where I would troubleshoot issues in production and do my best to keep things running and safe. When it came to monitoring my Postgres databases, I initially took what I knew about monitoring a web application itself, i.e. looking at CPU, m...
Read MoreJonathan S. Katz
Jonathan S. Katz
You don't need monitoring until you need it. But if you're running anything in production, you always need it. This is particularly true if you are managing databases. You need to be able to answer questions like "am I running out of disk?" or "why does my application have degraded performance?" to be able to troubleshoot or mitigate problems before they occur. When I first made a foray into how to monitor PostgreSQL in Kubernetes , let alone in a containerized environment, I learned that a l...
Read MoreDavid Youatt
David Youatt
PostgreSQL has supported streaming replication and hot standbys since version 9.0 (2010), and synchronous replication since version 9.1 (2011). Streaming replication (and in this case we're referring to "binary" streaming replication, not "logical") sends the PostgreSQL WAL stream over a network connection from primary to a replica. By default, streaming replication is asynchronous: the primary does not wait for a replica to indicate that it wrote the data. With synchronous replication, the...
Read MoreGreg Smith
Greg Smith
PostgreSQL 13 was released last week. I'm excited about this one, as the more mature partitioning plus logical replication features allow some long-requested deployment architectures. I ran 13 through my usual 144 test quick spin to see if everything was working as expected. Mainly boring stuff, but I was pleased to see that with the simple 128 client/4X RAM benchmark workload, Postgres 13 is driving 1.6GB/s of random read traffic requests to my PCI-e 4.0 NVM-e SSD. It keeps up with a whole RAI...
Read MoreJohn Porvaznik
John Porvaznik
pgRouting is a powerful routing tool, usually used for pathfinding/mapping/direction applications. (See Paul Ramsey's introduction to pgRouting here ). It is, however, also a robust graph db implementation, and can be used for much more than just finding the directions to your great aunt Tildy’s. Yacht Rock (as if you didn’t know) is a music genre created well after its active era. It’s characterized by smooth dulcet sounds that bring to mind wavy blond-haired waspy men in boat shoes, and ult...
Read MoreKat Batuigas
Kat Batuigas
I've been digging a little bit into PL/Python recently-inspired by our recent launch of Crunchy Bridge: a multi-cloud Postgres service with PL/Python and PL/R built right in. One thing that has tripped me up a few times is how PostgreSQL data types work with Python - especially when using external packages like NumPy or SciPy . Data type mapping is something you'll deal with when working with different languages or trying to get separate systems to talk to one another. Mapping types isn'...
Read MoreKat Batuigas
Kat Batuigas
PL/Python can be a convenient and powerful way to take advantage of your PostgreSQL database. In an earlier post , I talked a little bit about how you can use modules included in the Python standard library with PL/Python functions. In this post, we'll try running NumPy in a simple user-defined function which also takes advantage of PL/Python database access functions. The function will show a working example of how to easily convert a data table in Postgres to a NumPy array. Perhaps you've...
Read More