1. Introduction In today’s data-driven landscape, choosing the right database system can significantly impact application performance, scalability, and maintainability. Whether you are building a small web application, a financial system, or a globally distributed service, the database plays a central … Read More
Bufisa Blog
Follow along with us as we share our experiences in the industry - the highs, the lows, and everything that falls in between.
Designing a Highly Available PostgreSQL Architecture Using HAProxy, Patroni, and etcd
Background In modern applications, database availability is critical. Downtime means not just a loss in revenue but also user trust. This article outlines our journey in designing a highly available PostgreSQL architecture using: – 5 PostgreSQL nodes (1 leader + … Read More
sqlstate and sqlerrm in PostgreSQL
what is sqlstate and sqlerrm ? All messages emitted by the PostgreSQL server are assigned five-character error codes that follow the SQL standard’s conventions for “SQLSTATE” codes. Applications that need to know which error condition has occurred should usually test the error code, rather … Read More
How to use Liquibase with PostgreSQL
What is Liquibase ? Liquibase is a database schema change management solution that enables you to revise and release database changes faster and safer from development to production. How liquibase works ? Liquibase uses SQL, XML, JSON, and YAML changelog files to list database … Read More
How to ‘Join’ in PostgreSQL
How we can accessed multiple table at a time? that were join queries came in handy. Queries that access multiple tables (or multiple instances of the same table) at one time are called join queries. They combine rows from one table with … Read More
Introduction to PostgreSQL common table expressions or CTEs
A common table expression is a temporary result set which you can reference within another SQL statement including SELECT, INSERT, UPDATE or DELETE. Common Table Expressions are temporary in the sense that they only exist during the execution of the query. The following shows the … Read More
PostgreSQL object hierarchy
Understanding the hierarchy of objects within PostgreSQL can help you avoid confusion as you get to know the system and read up on documentation. PostgreSQL’s main “global” object is a database cluster, which is just the name given to the collection … Read More
Vacuum and Vacuum Full PostgreSQL
VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it’s necessary to do VACUUM periodically, especially on frequently-updated tables. Plain VACUUM (without FULL) … Read More
Exception Handling in PostgreSQL
By default, any error occurring in an SPL program aborts execution of the program. You can trap errors and recover from them by using a BEGIN block with an EXCEPTION section. The syntax is an extension of the normal syntax for a BEGIN block: How it … Read More
PostGIS exercise : part 1
here we will have several question : suppose we have 5 points (a, b, c, d, e), each having it’s own latitude and longitude, consider we have another point (x) on another latitude and longitude, find the nearest and the … Read More