Postgres stores data on disk – this one's a page turner
PostgreSQL stores data in a structured directory at /var/lib/postgresql/data, containing essential subdirectories and files for database operations, access control, statistics, and transaction management, aiding developers in data optimization.
Read original article
PostgreSQL, a widely used relational database, stores its data in a structured directory located at /var/lib/postgresql/data. This directory contains various subdirectories and files essential for database operations. When a fresh PostgreSQL installation is initiated, several folders are created, including 'base' for individual databases, 'global' for cluster-wide tables, and 'pg_wal' for Write-Ahead Log files, which are crucial for data recovery. The database operates through a series of concepts such as database clusters, connections, sessions, transactions, and snapshots, which help manage data integrity and concurrency.
The 'pg_hba.conf' file controls access permissions, while 'pg_stat' and 'pg_stat_tmp' store statistics for query optimization. Other directories like 'pg_commit_ts' and 'pg_serial' manage transaction data, and 'pg_replslot' supports replication for data redundancy. Understanding these components is vital for developers to effectively utilize PostgreSQL, as it allows them to grasp how data is organized and manipulated within the system. The article emphasizes that while databases may seem complex, they are built on fundamental principles that can be understood with some exploration. This knowledge is particularly beneficial for developers looking to optimize their applications and ensure efficient data management.
Related
Automatically setup pgAdmin with a Docker database
Developer experience is enhanced by configuring a PostgreSQL local environment with Docker Compose. Defining variables and leveraging folders streamlines setup. Integrating pgAdmin automates connections, improving efficiency and simplifying future deployments.
Using short lived Postgres servers for testing
Database servers like PostgreSQL can be quickly set up for short-lived environments or CI/CD pipelines by creating new data directories and using pg_basebackup for efficient data population. This method simplifies testing and demo setups.
Difference between running Postgres for yourself and for others
The post compares self-managed PostgreSQL with managing it for others, focusing on provisioning, backup/restore, HA, and security. It addresses complexities in provisioning, backup strategies, HA setup, and security measures for external users.
Is an All-in-One Database the Future?
Specialized databases are emerging to tackle complex data challenges, leading to intricate infrastructures. A universal, all-in-one database remains unfulfilled due to optimization issues and unique challenges of different database types.
- Discussion on the technical aspects of PostgreSQL, including data storage formats and logical decoding.
- Comparisons between PostgreSQL and other database systems, particularly InnoDB and NoSQL solutions.
- Curiosity about PostgreSQL's design choices, such as page size and data ordering.
- Technical issues encountered by users, particularly with Docker commands related to PostgreSQL.
- General appreciation for the article and anticipation for follow-up content.
15 commentsNext paragraph mentions TOAST and this byte is related to that. The low order bits (on little endian platforms) determine whether the value is stored inline (00, first 4 bytes are total length), is stored in TOAST table (11) or is shorter than 127 bytes (01 for even length, 10 for odd length, the total length is first byte >> 1). So for 0x25 you get 01, so length is 0x25 >> 1 = 18, which is that byte followed by "Equatorial Guinea".
Edit: the reason why endianness matters is that the same representation is also used in memory and the whole first word is interpreted as one length value. The toast tag bits have to be in first byte, which is most easily done as two highest order bits of that word on big endian. That means that it is placed in the two highest bits of the byte.
While logical decoding is about WAL, it is not related to the recovery process. Logical decoding is a mechanism to convert the WAL entries back into the high-level operations that caused the WAL entries, for example for replication or audit.
printf '{HEX STRING}' | xxd -r -p | xxd
> printf '0a00000029416e746967756120616e64204261726275646107414709415447093032381d49534f20333136362d323a414713416d657269636173414c6174696e20416d657269636120616e64207468652043617269626265616e1543617269626265616e093031390934313909303239' | xxd -r -p | xxd
00000000: 0a00 0000 2941 6e74 6967 7561 2061 6e64 ....)Antigua and
00000010: 2042 6172 6275 6461 0741 4709 4154 4709 Barbuda.AG.ATG.
00000020: 3032 381d 4953 4f20 3331 3636 2d32 3a41 028.ISO 3166-2:A
00000030: 4713 416d 6572 6963 6173 414c 6174 696e G.AmericasALatin
00000040: 2041 6d65 7269 6361 2061 6e64 2074 6865 America and the
00000050: 2043 6172 6962 6265 616e 1543 6172 6962 Caribbean.Carib
00000060: 6265 616e 0930 3139 0934 3139 0930 3239 bean.019.419.029docker: Error response from daemon: create ./pg-data: "./pg-data" includes invalid characters for a local volume name, only "[a-zA-Z0-9][a-zA-Z0-9_.-]" are allowed. If you intended to pass a host directory, use absolute path.
>Something really important about tables which isn’t obvious at first is that, even though they might have sequential primary keys, tables are not ordered.
This was very surprising to read.
I disagree. SQLite does a good job in uniting the 2 worlds: complex SQL queries with excellent data consistency and simple file(s). Although SQLite is for sure not the one size fits all solution.
(Enjoyed the post)
Related
Automatically setup pgAdmin with a Docker database
Developer experience is enhanced by configuring a PostgreSQL local environment with Docker Compose. Defining variables and leveraging folders streamlines setup. Integrating pgAdmin automates connections, improving efficiency and simplifying future deployments.
Using short lived Postgres servers for testing
Database servers like PostgreSQL can be quickly set up for short-lived environments or CI/CD pipelines by creating new data directories and using pg_basebackup for efficient data population. This method simplifies testing and demo setups.
Difference between running Postgres for yourself and for others
The post compares self-managed PostgreSQL with managing it for others, focusing on provisioning, backup/restore, HA, and security. It addresses complexities in provisioning, backup strategies, HA setup, and security measures for external users.
Is an All-in-One Database the Future?
Specialized databases are emerging to tackle complex data challenges, leading to intricate infrastructures. A universal, all-in-one database remains unfulfilled due to optimization issues and unique challenges of different database types.