- Postgres uses process instead of thread, connection pool is used to prevent frequent creating and closing processes.
- Different languages/framework have different modules with different quality to manage the connection pool, which often causes problems. Use middleware.
- Postgres can easily handle 10k insertions per second, which can replace a dedicated pub/sub server like Kafka.
Understanding caching in Postgres - An in-depth guide: Postgres is a process based system, i.e each connection has a native OS process...Postgres as a cross platform database, relies heavily on the operating system for its caching...WAL is a redo log that basically keeps track of whatever that is happening to the system...It is always better to monitor something directly from postgres,rather than going through the OS route.
- Inefficient architecture for writes;
- Inefficient data replication;
- Issues with table corruption;
- Poor replica MVCC support;
- Difficulty upgrading to newer releases.
- The most important architectural difference is that while Postgres directly maps index records to on-disk locations, InnoDB maintains a secondary structure. Instead of holding a pointer to the on-disk row location (like the ctid does in Postgres);
- MySQL supports multiple different replication modes; InnoDB storage engine implements its own LRU in something it calls the InnoDB buffer pool; MySQL implements concurrent connections by spawning a thread-per-connection. This is relatively low overhead.
- TimescaleDB is packaged as a PostgreSQL extension
- TimescaleDB is more than 20x faster than vanilla PostgreSQL when inserting data at scale
- TimescaleDB enables you to scale to 1 billion rows with no real impact to insert performance
Pull the image:
$ docker pull postgres
$ docker run --name test-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres
-p <host_port>:<container_port>: mapping from host port to container port, e.g.
-d: detached, run as a daemon
--name: give the container a name, can be checked by
$ docker container ls
Connect to postgress by
psql, it needs to run from within the container.
$ docker exec -it test-postgres psql -U postgres
-it is for an interactive commandline,
-i for "interactive",
-t for tty.
Or access bash first:
$ docker exec -it my-postgres bash # now inside the container $ psql -U postgres
postgres=# CREATE DATABASE mytestdb; CREATE DATABASE postgres=#\q