pg_dump and pg_restore are commonly used in data movement between PostgreSQL servers. Best practices for pg_dump and pg_restore assume access to superuser accounts, which are not always available in cloud offerings. Shau Phang shares some tips and tricks that will help you in executing pg_dump and pg_restore when you are migrating data from on-premise to the cloud.
Read Shau’s post → Last week before DjangoCon US, Python developer and Azure Postgres engineer Louise Grandjonc sat down for an interview with Claire Giordano about her DjangoCon talk “Postgres Index Types and where to find them”. Louise explains technical topics in a way that makes sense—and she often uses unusual (and fun) examples, from crocodiles to owls, from Harry Potter to Taylor Swift. Here’s an edited transcript of Claire’s interview with Louise (@louisemeta). Enjoy.
Read the interview → We created the landlord feature in Citus to give you statistics by tenant, going one step beyond the awesomeness of pg_stat_statements in Postgres. Landlord is useful for developers of multi-tenant apps who want insight into whether any tenants are creating load hotspots.
Find out more about landlord → CTEs are a powerful tool to help build logic blocks and gradually construct more complex SQL queries. With CTEs, your SQL becomes readable to someone who doesn’t have detailed knowledge of the application. If you use CTEs, your colleagues may appreciate reading your query—even a query hundreds of lines long. But, you’ll want to be careful: if heavily used within your app, CTEs are an optimization boundary. Yet for use cases where readability is as important as a few milliseconds, CTEs are a good choice.
Craig has fun with SQL → Dev/prod parity is a challenge when it comes to databases. You can run the same version of your database, and have a sandbox copy, but testing & staging with production data is a challenge. In this post, Craig reminds us that Dev/prod parity is one of the factors defined by the Twelve-Factor app, and explains why Dev/prod parity is easy, until it's not.
More about 12 Factor and databases → In his talk at PostgresOpen, Colton Shepard shared his insights on when to use jsonb, and when not to. He also covered operator usage and optimization, indexing, and what’s coming in Postgres 12. In this talk, you will get details on these topics, complete with practical examples and real-world stories.
Watch the video → A patch Brandur recently committed added SortSupport for the inet and cidr types, which by his measurement, a little more than doubles sorting speed on them. In this article, Brandur explains some of the subtleties which made designing an abbreviated key that would be faithful to those subtleties but still efficient.
Read Brandur’s article → Hyperscale (Citus) is now available in preview on Azure Database for PostgreSQL. Want to stay in the loop? We’ve made it easy for you. Just sign up to the Citus on Azure interest list.
Sign up on the Citus on Azure interest list → Ignite is Microsoft’s annual gathering of technology leaders and practitioners. It is 4 days of vision, discussions, and hands-on learning about the latest tools—as well as the insights that are driving tomorrow’s innovations. The Postgres team will be there with talks on Hyperscale (CItus), why developers love Postgres, running Postgres at scale on-premise and in the cloud, and more. Hope to see you there!
More on Ignite → Here, Craig explains the benefits of PgBouncer. Even if your database already has connection pooling setup at the application level, your database might still benefit from connection pooling with PgBouncer. This can begin as early as 10s of connections to your database, and is critical when you have several hundred connections.
More on PgBouncer → PGConf EU, Percona Live Europe & more: |