Tuesday, October 24

Tag: sql

Evolution of Fault Tolerance in PostgreSQL: Synchronous Commit

Evolution of Fault Tolerance in PostgreSQL: Synchronous Commit

2ndQuadrant, Featured, Gulcin's PlanetPostgreSQL, PostgreSQL
PostgreSQL is an awesome project and it evolves at an amazing rate. We’ll focus on evolution of fault tolerance capabilities in PostgreSQL throughout its versions with a series of blog posts. This is the fourth post of the series and we’ll talk about synchronous commit and its effects on fault tolerance and dependability of PostgreSQL. If you would like to witness the evolution progress from the beginning, please check the first three blog posts of the series below. Each post is independent, so you don't actually need to read one to understand another. Evolution of Fault Tolerance in PostgreSQL  Evolution of Fault Tolerance in PostgreSQL: Replication Phase  Evolution of Fault Tolerance in PostgreSQL: Time Travel Synchronous Commit By default, PostgreSQL (more…)
Evolution of Fault Tolerance in PostgreSQL: Time Travel

Evolution of Fault Tolerance in PostgreSQL: Time Travel

2ndQuadrant, Featured, Gulcin's PlanetPostgreSQL, PostgreSQL
PostgreSQL is an awesome project and it evolves at an amazing rate. We’ll focus on evolution of fault tolerance capabilities in PostgreSQL throughout its versions with a series of blog posts. This is the third post of the series and we’ll talk about timeline issues and their effects on fault tolerance and dependability of PostgreSQL. If you would like to witness the evolution progress from the beginning, please check the first two blog posts of the series: Evolution of Fault Tolerance in PostgreSQL  Evolution of Fault Tolerance in PostgreSQL: Replication Phase  Timelines The ability to restore the database to a previous point in time creates some complexities which we’ll cover some of the cases by explaining failover (Fig. 1), switchover (Fig. 2) and pg_rewind (Fig (more…)
Evolution of Fault Tolerance in PostgreSQL: Replication Phase

Evolution of Fault Tolerance in PostgreSQL: Replication Phase

2ndQuadrant, Featured, Gulcin's PlanetPostgreSQL, PostgreSQL
PostgreSQL is an awesome project and it evolves at an amazing rate. We’ll focus on evolution of fault tolerance capabilities in PostgreSQL throughout its versions with a series of blog posts. This is the second post of the series and we'll talk about replication and its importance on fault tolerance and dependability of PostgreSQL. If you would like to witness the evolution progress from the beginning, please check the first blog post of the series: Evolution of Fault Tolerance in PostgreSQL PostgreSQL Replication Database replication is the term we use to describe the technology used to maintain a copy of a set of data on a remote system.  Keeping a reliable copy of a running system is one of the biggest concerns of redundancy and we all like maintainable, easy-to-use and (more…)

Why should you learn PostgreSQL?

PostgreSQL
A few weeks ago I asked one of my colleagues why I should learn PostgreSQL. Harald’s answer was: “To start any new journey of learning, asking ‘why’ is an excellent question. It gives us both a direction to send us on our way and an expected destination. Without a clear path to follow, our brains tend to filter out new information.” He then came up with some very valid reasons to learn PostgreSQL. SQL is a powerful tool to get data under control Why should we learn Structured Query Language (SQL)? SQL was initially created to enable non-IT specialists to query information from datasets without needing to ask a programmer for help. SQL aimed to be much more powerful than interfaces such as query by example, and more flexible than prebuild reports. The objective was to enable (more…)

PostgreSQL anti-patterns: read-modify-write cycles

Craig's PlanetPostgreSQL
Shaun Thomas's recent post about client-side loops as an SQL anti-pattern is well worth a read if you're relatively new to SQL-based application development. It's reminded me of another SQL coding anti-pattern that I see quite a lot: the naïve read-modify-write cycle. Here I'll explain what this common development mistake is, how to identify it, and options for how to fix it. Imagine your code wants to look up a user's balance, subtract 100 from it if doing so won't make it negative, and save it. It's common to see this written as three steps: SELECT balance FROM accounts WHERE user_id = 1; -- in the application, subtract 100 from balance if it's above -- 100; and, where ? is the new balance: UPDATE accounts SET balance = ? WHERE user_id =1; and everything will appear to work fine to the developer. However, this code is critically wrong, and will malfunction as soon as the same user is updated by two different sessions at the same time. Imagine two concurrent sessions, each subtracting 100 from the user's balance, starting with an initial value of 300. Session 1Session 2 SELECT balance FROM accounts WHERE user_id = 1; (returns 300)   SELECT balance FROM accounts WHERE user_id = 1; (also returns 300) UPDATE balance SET balance = 200 WHERE user_id = 1; (300 - 100 = 200)   UPDATE balance SET balance = 200 WHERE user_id = 1; (300 - 100 = 200) Whoops!. The balance is 200, but you took out 200 from a starting point of 300. So 100 has just vanished. Most testing and development is done on standalone servers running single sessions, so unless you're doing rigorous testing this sort of thing often doesn't get noticed until production, and can be painful to debug. It's important to know about it so you can code defensively. (more…)