MongoDB vs PostgreSQL
MongoDB is classified as a NoSQL database program, it uses JSON-like documents with schemas. PostgreSQL is a relational database like MySQL.
SQL vs MongoDB Terminology and Concepts
| SQL Terms/Concepts | MongoDB Terms/Concepts |
|---|---|
| database | database |
| table | collection |
| row | document or BSON document |
| column | field |
| index | index |
| table joins | embedded documents and linking |
primary key Specify any unique column or column combination as primary key. |
In MongoDB, the primary key is automatically set to the _id field. |
| aggregation (e.g. group by) | aggregation framework |
Examples
The following table presents the various SQL statements and the corresponding MongoDB statements. The examples in the table assume the following conditions:
- The SQL examples assume a table named users.
- The MongoDB examples assume a collection named users that contain documents of the following prototype:
{
_id: ObjectID("509a8fb2f3f4948bd2f983a0"),
user_id: "abc123",
age: 55,
status: 'A'
}
Create and Alter
| SQL Schema Statements | MongoDB Schema Statements |
|---|---|
CREATE TABLE users (
id MEDIUMINT NOT NULL
AUTO_INCREMENT,
user_id Varchar(30),
age Number,
status char(1),
PRIMARY KEY (id)
) |
Implicitly created on first insert operation. The primary key _id is automatically added if _id field is not specified. db.users.insert( {
user_id: "abc123",
age: 55,
status: "A"
} )
However, you can also explicitly create a collection: db.createCollection("users") |
ALTER TABLE users
ADD join_date DATETIME |
Collections do not describe or enforce the structure of the constituent documents. See the Schema Design wiki page for more information. |
ALTER TABLE users
DROP COLUMN join_date |
Collections do not describe or enforce the structure of the constituent documents. See the Schema Design wiki page for more information. |
CREATE INDEX idx_user_id_asc
ON users(user_id) |
db.users.ensureIndex( { user_id: 1 } ) |
CREATE INDEX
idx_user_id_asc_age_desc
ON users(user_id, age DESC) |
db.users.ensureIndex( { user_id: 1, age: -1 } ) |
DROP TABLE users |
db.users.drop() |
Select
| SQL SELECT Statements | MongoDB find() Statements |
|---|---|
SELECT *
FROM users
|
db.users.find() |
SELECT id, user_id, status
FROM users
|
db.users.find(
{ },
{ user_id: 1, status: 1 }
)
|
SELECT user_id, status
FROM users
|
db.users.find(
{ },
{ user_id: 1, status: 1, _id: 0 }
) |
SELECT *
FROM users
WHERE status = "A"
|
db.users.find(
{ status: "A" }
) |
SELECT user_id, status
FROM users
WHERE status = "A"
|
db.users.find(
{ status: "A" },
{ user_id: 1, status: 1, _id: 0 }
)
|
SELECT *
FROM users
WHERE status != "A"
|
db.users.find(
{ status: { $ne: "A" } }
)
|
SELECT *
FROM users
WHERE status = "A"
AND age = 50
|
db.users.find(
{ status: "A",
age: 50 }
)
|
SELECT *
FROM users
WHERE status = "A"
OR age = 50
|
db.users.find(
{ $or: [ { status: "A" } ,
{ age: 50 } ] }
)
|
SELECT *
FROM users
WHERE age > 25
|
db.users.find(
{ age: { $gt: 25 } }
)
|
SELECT *
FROM users
WHERE age < 25
|
db.users.find(
{ age: { $lt: 25 } }
)
|
SELECT *
FROM users
WHERE age > 25
AND age <= 50
|
db.users.find(
{ age: { $gt: 25, $lte: 50 } }
)
|
SELECT *
FROM users
WHERE user_id like "%bc%"
|
db.users.find(
{ user_id: /bc/ }
)
|
SELECT *
FROM users
WHERE user_id like "bc%"
|
db.users.find(
{ user_id: /^bc/ }
)
|
SELECT *
FROM users
WHERE status = "A"
ORDER BY user_id ASC
|
db.users.find( { status: "A" } ).sort( { user_id: 1 } )
|
SELECT *
FROM users
WHERE status = "A"
ORDER BY user_id DESC
|
db.users.find( { status: "A" } ).sort( { user_id: -1 } )
|
SELECT COUNT(*)
FROM users
|
db.users.count() db.users.find().count() |
SELECT COUNT(user_id)
FROM users
|
db.users.count( { user_id: { $exists: true } } )
db.users.find( { user_id: { $exists: true } } ).count()
|
SELECT COUNT(*)
FROM users
WHERE age > 30
|
db.users.count( { age: { $gt: 30 } } )
db.users.find( { age: { $gt: 30 } } ).count()
|
SELECT DISTINCT(status)
FROM users
|
db.users.distinct( "status" )
|
SELECT *
FROM users
LIMIT 1
|
db.users.findOne() db.users.find().limit(1)
|
SELECT *
FROM users
LIMIT 5
SKIP 10
|
db.users.find().limit(5).skip(10) |
EXPLAIN SELECT *
FROM users
WHERE status = "A"
|
db.users.find( { status: "A" } ).explain()
|
Update
| SQL Update Statements | MongoDB update() Statements |
|---|---|
UPDATE users
SET status = "C"
WHERE age > 25
|
db.users.update(
{ age: { $gt: 25 } },
{ $set: { status: "C" } },
{ multi: true }
)
|
UPDATE users
SET age = age + 3
WHERE status = "A"
|
db.users.update(
{ status: "A" } ,
{ $inc: { age: 3 } },
{ multi: true }
)
|
Insert
| SQL INSERT Statements | MongoDB insert() Statements |
|---|---|
INSERT INTO users(user_id, age, status)
VALUES ("bcd001",
45,
"A")
|
db.users.insert( {
user_id: "bcd001",
age: 45,
status: "A"
} )
|
Delete
| SQL Delete Statements | MongoDB remove() Statements |
|---|---|
DELETE FROM users
WHERE status = "D"
|
db.users.remove( { status: "D" } )
|
DELETE FROM users |
db.users.remove( ) |
The Path of a Query in PostgresSQL
Taken from the Postgres 10 documentation. Here we give a short overview of the stages a query has to pass in order to obtain a result.- A connection from an application program to the PostgreSQL server has to be established. The application program transmits a query to the server and waits to receive the results sent back by the server.
- The parser stage checks the query transmitted by the application program for correct syntax and creates a query tree.
- The rewrite system takes the query tree created by the parser stage and looks for any rules (stored in the system catalogs) to apply to the query tree. It performs the transformations given in the rule bodies.
One application of the rewrite system is in the realization of views. Whenever a query against a view (i.e., a virtual table) is made, the rewrite system rewrites the user's query to a query that accesses the base tables given in the view definition instead. - The planner/optimizer takes the (rewritten) query tree and creates a query plan that will be the input to the executor.
It does so by first creating all possible paths leading to the same result. For example if there is an index on a relation to be scanned, there are two paths for the scan. One possibility is a simple sequential scan and the other possibility is to use the index. Next the cost for the execution of each path is estimated and the cheapest path is chosen. The cheapest path is expanded into a complete plan that the executor can use. - The executor recursively steps through the plan tree and retrieves rows in the way represented by the plan. The executor makes use of the storage system while scanning relations, performs sorts and joins, evaluates qualifications and finally hands back the rows derived.
References—
a