{"id":108395,"date":"2026-02-23T14:22:00","date_gmt":"2026-02-23T14:22:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=108395"},"modified":"2026-02-23T15:56:04","modified_gmt":"2026-02-23T15:56:04","slug":"learning-postgresql-with-grant-data-storage","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/learning-postgresql-with-grant-data-storage\/","title":{"rendered":"Learning PostgreSQL with Grant: Data Storage"},"content":{"rendered":"\n

If you\u2019re hosting your databases within a Virtual Machine (VM)<\/a> or on some big iron, one of the principal bottlenecks you\u2019re likely to see within your PostgreSQL<\/a> clusters is I\/O<\/a>. With I\/O at the center of potential performance problems, a good understanding of how PostgreSQL manages it is very important. I\u2019m going to start with just how things are stored on disk. We\u2019ll get to how writes and reads occur in another article.<\/p>\n\n\n\n

Before we get into the details, I do want to address a couple of issues around data storage and PostgreSQL. First up, extensions<\/a>. Some of the most impactful extensions are directly focused on data storage (looking at you, TimeScale<\/a>). They can change the fundamentals we\u2019re going to cover here. As such, each of these would need to be addressed individually to understand how they impact PostgreSQL storage. <\/p>\n\n\n\n

Also, if you\u2019re running PostgreSQL on any of the cloud vendors’ Platform as a Service (PaaS) offerings<\/a>, storage is, again, usually very different than the core behaviors of PostgreSQL. They would also have to be addressed individually (but I\/O is still a very direct concern on the cloud). Some, but not all, of what\u2019s covered in this article apply to the cloud platforms.<\/p>\n\n\n\n

With that out of the way, let\u2019s get started.<\/p>\n\n\n\n

How PostgreSQL stores data: understanding page architecture<\/h1>\n\n\n\n

The whole idea behind a database is the ability to persist the data. You want your inventory of widgets to get stored so you can look at it later. That means writing out to disks. However, what is writing to disk and where is it being written? Unlike SQL Server<\/a> which has one (or more) big file for all data, PostgreSQL has a collection of a large number of files. There is a methodology and structure to these files that you need to understand in order to later understand how the data gets written to and retrieved from these files.<\/p>\n\n\n\n

While we\u2019re going to be very focused on file, page, folder, etc., throughout this article, that\u2019s just part of the physical nature of persisting your data. What is being persisted is still the logical information you\u2019re most interested in – rows and columns. I just wanted to emphasize the distinction between the two here.<\/p>\n\n\n\n

What are PostgreSQL pages?<\/h3>\n\n\n\n

The files themselves are stored in a collection of 8kb pages. Since this is PostgreSQL, you\u2019re not limited to that if you choose to change the page allocation size when – or if – you compile your server. Since I\u2019ll probably never be doing custom compilations of PostgreSQL (and most of you won\u2019t either), we can safely say PostgreSQL stores data in 8kb pages within the files that define a table or index.<\/p>\n\n\n\n

The pages have a defined structure. At the front, there\u2019s the PageHeaderData. This is a 24-byte collection of data about the page. It includes things like the Write Ahead Log (WAL)<\/a> Log Sequence Number (LSN) for the latest write to the page, an offset to where the free space within the page starts and other information about the data on the page. <\/p>\n\n\n\n

After that, it gets a little weird for me. You get a set of Item ID values. These are long term pointers to the data and include information like how long the piece of data is and where it\u2019s located, but that\u2019s not the weird part. The weird part is that the actual data is written backwards<\/em> on the page, so the free space and offset needed to find it are opposite the set of ID pointers to the data itself. I\u2019m no artist, but it looks something like this:<\/p>\n\n\n\n

\"A<\/figure>\n\n\n\n

The final bit of the page, Special Space, isn\u2019t used by tables. It\u2019s generally used by indexes<\/a> to define storage, linked lists, and other things like that, depending on the index type.<\/p>\n\n\n\n

\n
\n
\n
\n

Get started with PostgreSQL – free book download<\/h3>\n
\n ‘Introduction to PostgreSQL for the data professional’, authored by Grant and Ryan Booz, covers all the basics of how to get started with PostgreSQL. <\/div>\n <\/div>\n Download your free copy<\/a>\n <\/div>\n <\/div>\n<\/section>\n\n\n

Explaining TOAST in PostgreSQL<\/h3>\n\n\n\n

All this assumes that the data will fit on a page. When it doesn\u2019t, TOAST (The Oversized-Attribute Storage Technique)<\/a> comes into play. Basically, if a row or column just isn\u2019t going to fit on 8kb of storage, something has to be done in order to persist that data. Only variable length data will be stored as TOAST. Fixed-length data, like an integer, just doesn\u2019t need to overflow beyond a page. <\/p>\n\n\n\n

PostgreSQL developers use terms like TOASTable, for data that can be stored on TOAST, or TOASTed, for data stored on TOAST. It\u2019s all a bit cute, but it’s a very interesting technical solution. <\/p>\n\n\n\n

As explained earlier, each table has a file defined for its storage and, if columns on the table are TOASTable, there will be a TOAST file associated with the table’s data file. This works by a pointer to the appropriate place on TOAST being stored in regular page storage with the Item values, which is then used to find the appropriate place in the TOAST file.<\/p>\n\n\n\n

TOAST storage is compressed. You can pick and choose from different compression algorithms and set that on a per-column basis (because your data may benefit from a different algorithm, depending on the data in question). Also, TOAST data can be very large so, in addition to compression, they break it down into chunks, set by the TOAST_MAX_CHUNK_SIZE<\/code>. By default, this is around 2kb; 2kb stores four chunks on a page, and pages are still used for the actual storage.<\/p>\n\n\n\n

Since TOAST is stored separately from the rest of the data, it’s referred to as \u201cout of line\u201d storage (meaning it\u2019s not a standard part of the tuple or row of data stored with the table.) The default value where TOAST kicks off is 2kb. Up to there, a given column is considered OK to be stored with the row, assuming the rest of the row also fits within that 2kb limit. Otherwise, data will be moved out of line and stored in the TOAST file instead of the table file.<\/p>\n\n\n\n

This is considered to be efficient because most of the work of retrieving data will be done on the table files. By moving the large storage off these pages and files, you store more rows\/tuples per page, making both disk and memory I\/O more efficient overall. This is especially true if the TOASTed data isn’t to be retrieved by a given query.<\/p>\n\n\n\n

So, that’s the basics on what gets written to the pages within PostgreSQL. Now, let\u2019s talk about where those pages are stored. <\/p>\n\n\n\n

There are details to every different data type in exactly how they\u2019re stored and retrieved within the TOAST file, but we can\u2019t cover them all here!<\/em><\/p>\n\n\n\n

Where does PostgreSQL store files?<\/h1>\n\n\n\n

By default, there’s no defined location for storing PostgreSQL files – it simply comes down to how your cluster was initialized. Per the documentation, a \u201cpopular\u201d place for the files is: \/var\/lib\/pgsql\/data – and yes, Windows will be different, but you\u2019re not running PostgreSQL on Windows, right?!<\/p>\n\n\n\n

On my container, the location is \/home\/postgres\/pgdata\/data. Of course, you can take control and place the files where you want. To see where your files are located, run:<\/p>\n\n\n\n

SHOW data_directory;<\/code><\/p>\n\n\n\n

This will output the path for databases within your cluster. You can also query the settings, which might be handy for some automation:<\/p>\n\n\n\n

SELECT current_setting('data_directory') AS data_directory;<\/code><\/p>\n\n\n\n

With that location in hand, we\u2019re still not to where the databases are stored, but we are<\/em> to where all data necessary for running PostgreSQL is stored. You\u2019ll have subdirectories for things as varied as the location of the status of transaction commitments (pg_xact), to the storage of cluster-wide information (global.) What we\u2019re looking for is the directory \u201cbase\u201d which, on my container, looks like this:<\/p>\n\n\n\n

\"What<\/figure>\n\n\n\n

I can hear you now. Where are the databases? They\u2019re right there. I see postgres, template0, template1, bluebox, testdb and perftuning. No? OK. Here\u2019s a query that can help:<\/p>\n\n\n\n

SELECT\ndb.datname,\n              db.oid AS db_oid,\n              current_setting('data_directory') AS data_directory\nFROM\n              pg_database AS db\nJOIN pg_tablespace AS ts ON\n              db.dattablespace = ts.oid;<\/pre><\/div>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Each folder you see corresponds to an OID (Object Identifier)<\/a> for the object in question. In this case, databases. Within each folder, the database objects are stored in an individual file for each table and index. For example, here\u2019s folder 20967, the bluebox database:<\/p>\n\n\n\n
\"Folder<\/figure>\n\n\n\n
Each file is named after the file node number. In practice, at the start, that corresponds to the OID for the table. However, operations such as REINDEX<\/code> or ALTER TABLE<\/code> can result in the Node Number changing, even as the OID stays the same. So, when looking up what a given table or index is, based on these files, use the OID, not simply the Node Number. You can use the Node Number to get the OID.<\/p>\n\n\n\n
However, we need to understand a few things right up front. Some of those files are user tables or indexes, and others are system tables or even special objects. When you see a file node number, such as 1247 in the upper-left, it might be followed by one or two other files. <\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Free desktop tool for fast PostgreSQL monitoring and diagnostics<\/h3>\n                
\n                                            Stay in control of PostgreSQL performance with Redgate pgNow \u2013 a free desktop tool for fast, focused diagnostics. No agents, no setup, just actionable insights when you need them.                                    <\/div>\n            <\/div>\n                            Learn more & download now<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
In this case, you\u2019ll see 1247_fsm and 1247_vm. *_fsm designates a Free Space Map<\/a> – a storage mechanism that tracks the free space within the table, or relation, 1247. You\u2019ll see this with both indexes and tables. The *_vm shows which pages are frozen, meaning a given row within the page is always visible (part of the behaviors of the Multi-Value-Concurrency Control which I introduced here<\/a>.)<\/p>\n\n\n\n
You\u2019ll also have a list of the pages that are known to only contain values visible to all active transactions (also explained more in the MVCC article). Only tables will have a *_vm file. We just have to run a query to identify what a given table or index actually is:<\/p>\n\n\n\n
WITH rel AS (\n  SELECT pg_filenode_relation(0, 22224) AS regclass\n)\nSELECT\n  ns.nspname AS schema_name,\n  c.relname AS rel_name\nFROM rel\nJOIN pg_class     c ON c.oid = rel.regclass::oid\nJOIN pg_namespace ns ON ns.oid = c.relnamespace;<\/pre><\/div>\n\n\n\n
The function pg_filenode_relation<\/code> maps between the names of the files and the objects within the database. You have to pass it your database ID or, as I did, 0 for the current database, and 22224 for the file node number. It’s then just a question of combining this information with others, such as pg_namespace<\/code> to get the schema name, and pg_class<\/code> to get the object name. In this case, it\u2019s the bluebox.film table.<\/p>\n\n\n\n
You can also go the other way – if you know the schema and table name, you can easily find where it\u2019s being stored on the file system:<\/p>\n\n\n\n
SELECT\n\tns.nspname AS schema_name,\n\tc.relname AS rel_name,\n\tc.relfilenode AS filenode\nFROM\n\tpg_class AS c\nJOIN pg_namespace AS ns ON\n\tns.oid = c.relnamespace\nWHERE\n\tns.nspname = 'bluebox'\n\tAND c.relname = 'film';<\/pre><\/div>\n\n\n\n
With that, you have a good idea of how PostgreSQL stores the objects that make up your database…although…where are the functions? Where are the views? Do they have their own files or folders somewhere? Actually, no.<\/p>\n\n\n\n
Where are functions, views, procedures, etc, stored in PostgreSQL?<\/h3>\n\n\n\n
In PostgreSQL, functions, views, procedures, triggers and the like, are stored within various system catalogs. For example, a function is stored in pg_proc<\/code> and a view is stored in pg_class<\/code>.<\/p>\n\n\n\n
So, clearly, changes made to a table or index is dealt with pretty radically differently than a change made to a function. We\u2019ll get into the details of what happens during writes in another article (although, the MVCC and Vacuum<\/a> articles do cover some of this behavior).<\/p>\n\n\n\n
One other concept worth addressing here is the idea of a TableSpace<\/a>. Most of what I\u2019ve described up to now is the behavior of the default PostgreSQL tablespace. However, you can add additional tablespaces to your database. At its core, a tablespace is just another location for storing data (specifically, relations or tables & indexes). <\/p>\n\n\n\n
While the location of this new tablespace may be a new disk or a networked disk subsystem, for example, the basic behavior from within PostgreSQL is roughly the same. It\u2019s a new folder for storing data. It\u2019ll have a folder for a database, and files for tables and indexes.<\/p>\n\n\n\n
The files will behave the same way already described with similar page layouts and behaviors: you can control which tablespace is the default for a database, and you can individually specify a tablespace for individual tables or indexes.<\/p>\n\n\n\n
Finally, the Write Ahead Log for transaction management (and more) is a different storage mechanism that I covered in another article<\/a>.<\/p>\n\n\n\n
Final Thoughts<\/h2>\n\n\n\n
What we have with PostgreSQL is both a very simple structure and<\/em> a very complicated one. The core behaviors are easy enough to get your head around – tables are files, a database is a collection of tables so it\u2019s a folder, etc – but the devil, as always, is in the details. The interaction between all these various storage mechanisms when combined with reads, writes, transactions…it all introduces complexity. However, you should now have a basic understanding of what gets stored where within PostgreSQL.<\/p>\n\n\n\n
\n    
FAQs: Data Storage in PostgreSQL<\/h2>\n\n                        
1. How does PostgreSQL store data on disk?<\/h3>\n            
\n                
PostgreSQL<\/span><\/span> stores data in 8KB pages within individual files for each table and index. Each database is a folder, and each table or index is stored as its own file inside that folder.<\/p>\n            <\/div>\n                    
2. What is PostgreSQL page architecture?<\/h3>\n            
\n                
PostgreSQL pages contain a PageHeaderData section, Item IDs (row pointers), actual row data stored in reverse order, and optional special space for indexes. This structured layout improves storage efficiency and I\/O performance.<\/p>\n            <\/div>\n                    
3. What is TOAST in PostgreSQL?<\/h3>\n            
\n                
TOAST (The Oversized-Attribute Storage Technique) handles large variable-length data that doesn\u2019t fit within an 8KB page. It stores large values \u201cout of line\u201d in separate TOAST files, often using compression for better storage efficiency.<\/p>\n            <\/div>\n                    
4. Where are PostgreSQL database files located?<\/h3>\n            
\n                
Database files are stored in the cluster\u2019s data_directory<\/code>. Inside it, the base folder contains subfolders named after database OIDs, each holding table and index files.<\/p>\n            <\/div>\n                    
5. What are *_fsm and *_vm files in PostgreSQL?<\/h3>\n            
\n                
    \n
  • \n
    _fsm (Free Space Map) tracks available space in tables and indexes.<\/p>\n<\/li>\n
  • \n
    _vm (Visibility Map) tracks pages visible to all transactions.

    \nThese files help optimize performance and support MVCC operations.<\/p>\n<\/li>\n<\/ul>\n            <\/div>\n                    
    6. Do PostgreSQL views and functions have their own files?<\/h3>\n            
    \n                
    No. Objects like views, functions, triggers, and procedures are stored in system catalogs (e.g., pg_class<\/code>, pg_proc<\/code>) rather than as separate files.<\/p>\n            <\/div>\n                    
    7. What is a PostgreSQL tablespace?<\/h3>\n            
    \n                
    A tablespace is an additional storage location for database objects. It allows administrators to store specific tables or indexes on different disks to optimize performance and manage I\/O workloads.<\/p>\n            <\/div>\n                    
    8. Why is understanding PostgreSQL storage important for performance?<\/h3>\n            
    \n                
    PostgreSQL performance bottlenecks – especially I\/O issues – are directly tied to how pages, files, TOAST data, and tablespaces are structured. Understanding storage architecture helps optimize disk usage and database tuning.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
    Learn how PostgreSQL stores data on disk, including page architecture, TOAST storage, file structure, tablespaces, and I\/O performance optimization strategies.…<\/p>\n","protected":false},"author":221792,"featured_media":105920,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143523,53,143534],"tags":[158977,158978,4150],"coauthors":[6785],"class_list":["post-108395","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-featured","category-postgresql","tag-learningpostgresqlwithgrant","tag-postgresql","tag-sql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/108395","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221792"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=108395"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/108395\/revisions"}],"predecessor-version":[{"id":108562,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/108395\/revisions\/108562"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/105920"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=108395"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=108395"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=108395"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=108395"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}