PostgreSQL's mascot is an elephant named Slonik. Every time I see it, I think that choosing an elephant is really fitting: it's steady, has a good memory, doesn't run very fast but definitely won't fall, and once it moves into your house, it's hard to get rid of it.
PostgreSQL is a relational database that has been continuously developed since 1996. It is licensed under the permissive PostgreSQL License, and version 16 was released in September 2023. For Windows, there is an EDB-maintained installer that can be installed in minutes. The default settings after installation are for general use cases. Below are a few things I immediately changed after installing it for a project that only runs on my own computer and continuously writes data from multiple sources.
Why wasn't it SQLite this time?
I usually love SQLite and have written an entire article in its defense. But the write pattern for this project is completely different: dozens of schedulers are writing to it every hour, and several streams are constantly coming in. SQLite can only have one writer at a time, so in this situation, it becomes a queue of windows, and the queue gets longer and longer. PostgreSQL's locking and parallel model is designed for this kind of scenario; it doesn't panic at all when many people are writing simultaneously.
Listen only to this machine
The installation program generatedpostgresql.conf insidelisten_addresses may be* This means "listen to all network interfaces." Since nothing on this machine needs to connect from the outside, it was changed to...localhost After making the changes, restart the service and use...netstat Confirm that only the local loop address is being listened to.
I suggest doing this within five minutes of installation. Not because something will happen immediately, but because you'll forget about it after five minutes, and then one day you'll see your data in a scan report.
Windows Integrated Authentication
pg_hba.confsupportsspi The authentication method involves directly connecting using the currently logged-in Windows account; no password is required in the configuration file. The application's connection string only contains the hostname, port, and database name. The superuser password is set manually during the installation wizard and is only used when necessary.
A password-free connection string might seem like something's missing, but it's actually missing something that can be copied, screenshotted, and pasted into the chat room. I've seen too many project configuration files with just one line like that.password= There was a note next to it that said "Remember to change it," but it hadn't been changed in three years.
Moving data catalog
The default data directory is on the system drive. To move it to another drive, the steps are: stop the service, and use...robocopy /E /COPYALL Copy the permissions along with the service, and modify the service's startup parameters.-D Change the path, modify the Data Directory logged into the installer, and start the service. I moved over 900 files once, with zero failures; the whole process was smoother than expected.
I was very nervous before moving it because databases give off a "don't touch it" vibe. After moving it, I realized it was just a folder; the elephant only looks heavy, but it's actually not that hard to move. Of course, that's assuming the service is down—you can't move it while the elephant is walking.
Sorting rules are pinned to C
I learned this after I finished installing it, and I'm writing it here so you don't have to learn it a second time.
The region settings during installation follow Windows, and the default database collation will be the local Windows code page. This poses a problem for sorting stability: the same query may result in different orders under different collations, and many deduplication and latest retrieval logics rely on order. What you think you are getting is the latest record, but it is actually the record that "appears to be the latest" under a certain sorting rule.
For any fields that have a meaningful sequence, explicitly add them in the query.COLLATE "C" Alternatively, you could choose C from the beginning of initdb, which would save a lot of trouble later. C sorting simply compares byte arrays, without any linguistic sophistication, and in databases, being unintelligent is often a good thing.
Partitioning, raw files, exporting
For continuously flowing observation data, the main table is declaratively partitioned by month, a feature that has been available in PostgreSQL since version 10. Each month has its own sub-table, and queries automatically know which tables to look in. When deleting old data, the entire sub-table is discarded, instead of deleting column by column.
The original responses from each source are archived, not stored in a database; the database only contains the normalized columns. Older partitions are periodically exported as Parquet for analysis tools, while the database only retains recently accessed data. The driver uses psycopg 3, exporting via a COPY streaming interface, avoiding reading the entire table into memory. This division of labor will be discussed in another article.
Mini-drama: Will this elephant eat a lot of resources?
"Isn't PostgreSQL something that requires a dedicated server? Can my laptop run it?"
"It can run. Its default memory settings are very conservative, so conservative that you may need to increase them to unleash its full potential."
"Won't it just keep consuming CPU resources in the background?"
"When there is no query, it hardly moves, just like an elephant is very quiet when it stands."
"So it's more like a big but very well-behaved pet."
"Yes, but you need to remember to feed it backups."
Backup using pg_dump
Do not copy the directory of materials that are already in use, otherwise the files may not be able to be opened.pg_dump Generate consistent logical backups.pg_basebackup Both physical backups are generated and executed during service operation, so the elephant doesn't need to stop.
I must be honest, I haven't scheduled a database backup for my own project yet, because I need to secure all the data sources first. I know this is a trap, and I'm writing it here as a reminder. It's better to openly admit what you haven't done than to pretend you have. At least next time someone asks, "Have you backed it up?" I can say, "Not yet, but I've written it on my blog," although that answer doesn't reassure anyone at all.
When to choose it instead of SQLite
My dividing line is the number of writers. For content-based systems where a single application writes and multiple users read, SQLite is sufficient; for many independent programs or streams writing simultaneously, PostgreSQL's locking and parallel model is more capable. As for analytical databases like ClickHouse, there's no native Windows version; on Windows, it requires Docker or WSL2. I'll leave that for when the data volume really reaches a certain level.
Elephants are great, but not every room needs an elephant. When needed, it will be the most reliable person in your home.
The origin of that elephant
PostgreSQL's predecessor was the POSTGRES project in Berkeley in 1986, led by Michael Stonebraker. The name means "after Ingres," which was another database he created earlier. After adding SQL support in 1996, it was renamed PostgreSQL, so the correct pronunciation is "Post-gres-Q-L," although everyone pronounces it "Postgres" and nobody cares.
The origin of the elephant is rather endearing. In 1997, during a mailing list discussion about what to use as a mascot, someone suggested an elephant, arguing that Agatha Christie wrote a novel called Elephants Can Remember, and since elephants have good memories, the database should too. And that was it. Its name, Slonik, means "little elephant" in Russian, and comes from a Russian developer. Having a database mascot inspired by a mystery novel felt very fitting for the community's character: serious, but with a touch of quirky humor.
It releases a major version every fall, a pattern it has maintained for many years; you can use it as a calendar. Each major version includes several useful new features, but older versions are maintained for five years, so you don't need to rush to catch up.
Three common misunderstandings
The first misconception is that "PostgreSQL is difficult." Its default settings and documentation are very user-friendly; the difficulty lies in "tuning," which is only necessary when the data volume reaches a certain level. Small projects can be installed and used immediately, just like SQLite, except that an additional service is running.
The second misconception is that "MySQL and PostgreSQL are similar." While routine CRUD operations are similar, their philosophies differ significantly when it comes to complex queries, JSON, full-text search, and extended functionality. PostgreSQL prioritizes "doing things right," which becomes apparent when you need it.
The third misconception is that "data directories can be backed up arbitrarily." This has been mentioned before, but it's worth reiterating because it can lead to data loss. Directly copying a data directory in use will likely result in an inconsistent copy.pg_dump .
A small exercise you can try over the weekend
Installing PostgreSQL takes only a few minutes on Windows using the installer and on Mac using Homebrew. After installation, do the first thing this article will discuss: find...postgresql.conf ,looklisten_addresses What is it, changed tolocalhost Restart.
Then usepsql Go in and create a table partitioned by month:CREATE TABLE readings (at timestamptz, value real) PARTITION BY RANGE (at); Create two more sub-tables. Insert a few records, and use...EXPLAIN SELECT * FROM readings WHERE at >= '2026-09-01' Watching it scan only one sub-table is the most delightful part of partitioning. This moment when "it knows where to look" is the most satisfying aspect of partitioning.
at lastpg_dump 你的資料庫 > backup.sql If you open it and take a look, you'll find that the backup is just a bunch of SQL statements, the kind that a human can understand.
Postscript
An elephant is good, but not every room needs an elephant. After writing this, I thought about it and realized it's actually about "understanding your own needs." I've used SQLite for many years because my needs are shaped like SQLite; this time I switched to an elephant because the shape of my needs has changed. Tools aren't good or bad, only suitable or unsuitable. As for backups, I'll schedule them, really.
Official website: postgresql.org