Explainer
How databases work: tables, SQL, transactions and indexes
Relational databases such as PostgreSQL store data in tables of rows and columns and are queried with SQL, an international standard formally known as ISO/IEC 9075.[1][2][3] They group changes into transactions that either happen completely or not at all and are saved to permanent storage before being reported complete.[4][5] Indexes let a database find rows without scanning a whole table, at the cost of extra work whenever data changes.[6][7] Not every database runs as a server or uses tables: SQLite lives inside the application in a single file, and MongoDB stores JSON-like documents.[8][9]
This page draws mainly on the PostgreSQL, SQLite and MongoDB documentation. Other database systems implement the same ideas in different ways.
Tables, rows and columns
PostgreSQL is a relational database management system (RDBMS): it manages data stored in relations, the mathematical term for tables.[1] Each table is a named collection of rows, every row in a table has the same named columns, and each column holds a specific type of data.[2] SQL does not guarantee the order of rows in a table.[2] In PostgreSQL, tables are grouped into databases, and the databases managed by one server instance form a database cluster.[10]
Tables are not the only design: the PostgreSQL documentation gives files and directories on Unix-like systems as an example of a hierarchical database, and mentions object-oriented databases as a more modern development.[11]
SQL, the shared language
Relational databases are queried with SQL, whose formal name is ISO/IEC 9075 “Database Language SQL”; the most recent revision is SQL:2023.[3] Products differ in how much of it they implement: PostgreSQL says it conforms to at least 170 of the 177 mandatory features for Core SQL:2023, and that at the time of writing no database management system claimed full Core conformance.[12]
Transactions: all or nothing
Think of moving money between two bank accounts: the money must leave one account and arrive in the other, or neither change should happen. A database transaction bundles several steps into one all-or-nothing operation, so if something fails partway, none of the steps count.[4] Once the database says a transaction is done, the changes have already been written to permanent storage.[5]
A transaction bundles multiple steps into a single all-or-nothing operation; if a failure prevents it completing, none of the steps affect the database.[4] A transactional database logs all of a transaction’s updates to permanent storage before reporting it complete.[5] An open transaction’s updates are invisible to other transactions until it completes, when they all become visible at once.[13] In PostgreSQL, transactions are wrapped in BEGIN and COMMIT, ROLLBACK cancels the updates so far, and every statement runs in a transaction even without an explicit BEGIN.[14]
Indexes: finding rows fast
Without preparation, a database looking for matching rows would have to scan the entire table row by row; with an index on the searched column it might only walk a few levels into a search tree.[6] The PostgreSQL documentation compares this to the alphabetical index at the back of a non-fiction book.[15] Once an index exists, PostgreSQL keeps it updated and uses it when its query planner judges that faster than a sequential scan.[16] Indexes are not free: keeping them synchronized adds overhead to every change, so the documentation advises removing indexes that are rarely used.[7]
Other kinds of database
Not every database runs as a separate server: SQLite is an embedded SQL engine with no server process; it reads and writes ordinary disk files and keeps a complete database in a single file.[8] The SQLite project says it is likely used more than all other database engines combined, is found in every Android and iOS device, and estimates that over one trillion SQLite databases are in active use.[17]
Document databases drop tables altogether: MongoDB stores records as BSON documents, a binary representation of JSON with more data types, made up of field-and-value pairs.[9] Each document’s _id field acts as its primary key and must be unique within its collection.[18] MongoDB says this model lets developers mirror the objects in their code and avoid the object-relational mapping that relational databases need.[19] For how software requests data from services, see how APIs work.
Read next
How APIs work: requests, responses, JSON, REST and OAuth
What an API is and how web APIs work: HTTP requests and methods, status codes, JSON data, the REST style and OAuth access tokens.
Questions readers ask
What is a relational database?
A system for managing data stored in relations, the mathematical term for tables. Each table is a named collection of rows, every row has the same named columns, and each column has a specific data type.[1][2]
Is SQL a standard?
Yes. Its formal name is ISO/IEC 9075 "Database Language SQL", most recently revised as SQL:2023. PostgreSQL says it meets at least 170 of the 177 mandatory Core features, and that at the time of writing no database system claimed full Core conformance.[3][12]
What is a database transaction?
A group of steps treated as one all-or-nothing operation. If something fails partway, none of the steps take effect, and other users don't see the changes until the transaction completes.[4][13]
Why do indexes make queries faster?
Without an index, a database may have to scan a whole table row by row; with one it might only walk a few levels into a search tree. The trade-off is extra work every time the table changes.[6][7]
Does a database always need a server?
No. SQLite is an embedded engine with no separate server process that keeps a complete database in a single file. The SQLite project estimates that over one trillion SQLite databases are in active use.[8][17]
What is a document database?
One that stores records as documents instead of table rows. MongoDB stores records as BSON documents, a binary form of JSON made of field-and-value pairs.[9]
Sources
· 19 claims from 3 publishers
Each numbered claim is a statement we checked against the sources listed with it. Tap a number in the text to see its sources in place.
- [1]
PostgreSQL is a relational database management system (RDBMS), meaning it manages data stored in relations, the mathematical term for tables. confirmedas of 2026-11-14
- Concepts (PostgreSQL tutorial) · PostgreSQL Global Development Group · Primary source · 2.2 Concepts (retrieved 2026-11-14)
- [2]
In a relational database, each table is a named collection of rows, every row in a table has the same named columns, each column has a specific data type, and SQL does not guarantee the order of rows in a table. confirmedas of 2026-11-14
- Concepts (PostgreSQL tutorial) · PostgreSQL Global Development Group · Primary source · 2.2 Concepts (retrieved 2026-11-14)
- Concepts (PostgreSQL tutorial) · PostgreSQL Global Development Group · Primary source · 2.2 Concepts (retrieved 2026-11-14)
- Concepts (PostgreSQL tutorial) · PostgreSQL Global Development Group · Primary source · 2.2 Concepts (retrieved 2026-11-14)
- [3]
SQL is an international standard, formally ISO/IEC 9075 "Database Language SQL"; according to the PostgreSQL documentation, the most recent revision is SQL:2023. confirmedas of 2026-11-14
- SQL Conformance (PostgreSQL documentation) · PostgreSQL Global Development Group · Primary source · Appendix D. SQL Conformance (retrieved 2026-11-14)
- [4]
A database transaction bundles several steps into one all-or-nothing operation; if a failure stops it from completing, none of the steps affect the database. confirmedas of 2026-11-14
- Transactions (PostgreSQL tutorial) · PostgreSQL Global Development Group · Primary source · 3.4 Transactions, first paragraph (retrieved 2026-11-14)
- Transactions (PostgreSQL tutorial) · PostgreSQL Global Development Group · Primary source · 3.4 Transactions, first paragraph (retrieved 2026-11-14)
- [5]
A transactional database logs all of a transaction's updates in permanent storage before reporting the transaction complete, so they are not lost in a crash shortly afterwards. confirmedas of 2026-11-14
- Transactions (PostgreSQL tutorial) · PostgreSQL Global Development Group · Primary source · 3.4 Transactions (retrieved 2026-11-14)
- [6]
Without an index, a database would have to scan an entire table row by row to find matching entries; with an index on the searched column it might only walk a few levels into a search tree. confirmedas of 2026-11-14
- Indexes: Introduction (PostgreSQL documentation) · PostgreSQL Global Development Group · Primary source · 11.1 Introduction (retrieved 2026-11-14)
- Indexes: Introduction (PostgreSQL documentation) · PostgreSQL Global Development Group · Primary source · 11.1 Introduction (retrieved 2026-11-14)
- [7]
Indexes add overhead to data changes because the system must keep them synchronized with the table, so the PostgreSQL documentation advises removing indexes that are seldom or never used. confirmedas of 2026-11-14
- Indexes: Introduction (PostgreSQL documentation) · PostgreSQL Global Development Group · Primary source · 11.1 Introduction (retrieved 2026-11-14)
- Indexes: Introduction (PostgreSQL documentation) · PostgreSQL Global Development Group · Primary source · 11.1 Introduction (retrieved 2026-11-14)
- [8]
SQLite is an embedded SQL database engine with no separate server process; it reads and writes ordinary disk files, and a complete database is contained in a single file. confirmedas of 2026-11-14
- About SQLite · SQLite project · Primary source · About SQLite, first paragraph (retrieved 2026-11-14)
- About SQLite · SQLite project · Primary source · About SQLite, first paragraph (retrieved 2026-11-14)
- [9]
MongoDB, a document database, stores records as BSON documents - a binary representation of JSON with more data types - made up of field-and-value pairs. confirmedas of 2026-11-14
- Documents (MongoDB manual) · MongoDB, Inc. · Primary source · Documents (retrieved 2026-11-14)
- Documents (MongoDB manual) · MongoDB, Inc. · Primary source · Document Structure (retrieved 2026-11-14)
- [10]
In PostgreSQL, tables are grouped into databases, and the databases managed by one PostgreSQL server instance form a database cluster. confirmedas of 2026-11-14
- Concepts (PostgreSQL tutorial) · PostgreSQL Global Development Group · Primary source · 2.2 Concepts (retrieved 2026-11-14)
- [11]
Tables are not the only way to organize a database; the PostgreSQL documentation gives files and directories on Unix-like systems as an example of a hierarchical database, and mentions object-oriented databases. confirmedas of 2026-11-14
- Concepts (PostgreSQL tutorial) · PostgreSQL Global Development Group · Primary source · 2.2 Concepts (retrieved 2026-11-14)
- [12]
PostgreSQL says it conforms to at least 170 of the 177 mandatory features for Core SQL:2023, and that at the time of writing no database management system claimed full Core SQL:2023 conformance. confirmedas of 2026-11-14
- SQL Conformance (PostgreSQL documentation) · PostgreSQL Global Development Group · Primary source · Appendix D. SQL Conformance (retrieved 2026-11-14)
- SQL Conformance (PostgreSQL documentation) · PostgreSQL Global Development Group · Primary source · Appendix D. SQL Conformance (retrieved 2026-11-14)
- [13]
The updates made by an open transaction are invisible to other transactions until it completes, when they all become visible at once. confirmedas of 2026-11-14
- Transactions (PostgreSQL tutorial) · PostgreSQL Global Development Group · Primary source · 3.4 Transactions (retrieved 2026-11-14)
- [14]
In PostgreSQL, a transaction is wrapped in BEGIN and COMMIT commands, ROLLBACK cancels the updates made so far, and every SQL statement runs inside a transaction even without an explicit BEGIN. confirmedas of 2026-11-14
- Transactions (PostgreSQL tutorial) · PostgreSQL Global Development Group · Primary source · 3.4 Transactions (retrieved 2026-11-14)
- Transactions (PostgreSQL tutorial) · PostgreSQL Global Development Group · Primary source · 3.4 Transactions (retrieved 2026-11-14)
- Transactions (PostgreSQL tutorial) · PostgreSQL Global Development Group · Primary source · 3.4 Transactions (retrieved 2026-11-14)
- [15]
The PostgreSQL documentation compares a database index to the alphabetical index at the back of a non-fiction book. confirmedas of 2026-11-14
- Indexes: Introduction (PostgreSQL documentation) · PostgreSQL Global Development Group · Primary source · 11.1 Introduction (retrieved 2026-11-14)
- [16]
Once an index exists, PostgreSQL updates it when the table changes and uses it in queries when it judges that more efficient than a sequential table scan. confirmedas of 2026-11-14
- Indexes: Introduction (PostgreSQL documentation) · PostgreSQL Global Development Group · Primary source · 11.1 Introduction (retrieved 2026-11-14)
- [17]
The SQLite project says SQLite is likely used more than all other database engines combined, is found in every Android and iOS device, and estimates that over one trillion SQLite databases are in active use. confirmedas of 2026-11-14
- Most Widely Deployed and Used Database Engine · SQLite project · Primary source · Most Widely Deployed and Used Database Engine, first paragraph (retrieved 2026-11-14)
- Most Widely Deployed and Used Database Engine · SQLite project · Primary source · Most Widely Deployed and Used Database Engine (retrieved 2026-11-14)
- Most Widely Deployed and Used Database Engine · SQLite project · Primary source · Most Widely Deployed and Used Database Engine, list (retrieved 2026-11-14)
- Most Widely Deployed and Used Database Engine · SQLite project · Primary source · Most Widely Deployed and Used Database Engine, list of deployments (retrieved 2026-11-14)
- [18]
In MongoDB, the _id field is reserved as a document's primary key, and its value must be unique within the collection. confirmedas of 2026-11-14
- Documents (MongoDB manual) · MongoDB, Inc. · Primary source · Field Names (retrieved 2026-11-14)
- [19]
MongoDB says its data model lets developers structure data to mirror the objects in their code, avoiding the object-relational mapping that relational databases require. confirmedas of 2026-11-14
- Documents (MongoDB manual) · MongoDB, Inc. · Primary source · Documents, introduction (retrieved 2026-11-14)
Revision history (1)
- Page created.
Created Nov 14, 2026. Last reviewed by an editor on Nov 14, 2026. Next scheduled review: Aug 14, 2027.
Cite this page
"How databases work: tables, SQL, transactions and indexes." ContentLora, updated Nov 14, 2026. https://contentlora.com/explain/how-databases-work
Spotted an error? Suggest a correction or emailcorrections@contentlora.com.