I’m learning PostgreSQL so I can support it. Everyone told me it was easy. Some even said it was better than SQL Server.
Let’s just say there were a lot of “What the…?!” moments.
I’ve learned a ton from Grant Fritchey and Ryan Booz’s Redgate book, PostgreSQL for SQL Server DBAs, and Buck Woody’s hands-on labs.
Here are ten things that surprised me coming from SQL Server.
1. A “Cluster” Isn’t a Cluster
Coming from SQL Server, the word cluster usually means Windows Failover Clustering or some type of high availability.
In PostgreSQL, a cluster is simply a single PostgreSQL server (instance) that manages one data directory. That instance can contain multiple databases.
Same word. Completely different meaning.
2. PowerShell ISE and PostgreSQL Don’t Get Along
One of the first things I ran into had nothing to do with PostgreSQL.
psql expects a real interactive console. PowerShell ISE doesn’t provide one, so it hangs waiting for input that never comes.
It works just fine in:
- Windows PowerShell
- PowerShell 7
- Windows Terminal
- Command Prompt
Turns out this is more of a PowerShell ISE limitation than a PostgreSQL problem, but it still cost me time figuring it out.
3. There Is No SQL Agent
If you’re expecting SQL Server Agent, you’re going to be disappointed.
PostgreSQL doesn’t include a built-in job scheduler.
Instead, people commonly use:
- pg_cron
- pgAgent
- Linux cron
- Windows Task Scheduler
- Kubernetes CronJobs
Many cloud platforms also provide their own scheduling options.
4. Extensions, Extension, Extensions
One of the biggest mindset shifts for me was realizing how much functionality comes through extensions.
Need job scheduling? Extension.
Need better query statistics? Extension.
Need auditing? Extension.
Need distribution? Extension.
Need to store and query geographic data? Extension.
PostgreSQL keeps the core engine focused and lets you add capabilities when you need them.
It’s a very different philosophy than SQL Server, where many of those features are built into the product.
5. TEXT Isn’t the Villain
SQL Server taught me to avoid large text types. PostgreSQL taught me not to.
TEXT and VARCHAR perform essentially the same. Large values are handled automatically through TOAST.
Ironically, char(n) is usually the one to avoid.
6. Case Sensitivity Can Bite You
This one can be painful when you’re coming from SQL Server.
By default, PostgreSQL converts unquoted object names to lowercase.
If someone creates an object using quoted mixed-case names, you must reference it with the exact same capitalization every single time.
7. Azure Database for PostgreSQL Has Query Store
This one was a pleasant surprise.
Azure Database for PostgreSQL includes Query Store, giving you historical query performance data that’s incredibly useful for troubleshooting.
It isn’t identical to SQL Server’s implementation, but the overall concept feels very familiar.
8. Roles Work Differently
Security took me a minute to understand and I am still processing this.
In PostgreSQL, roles exist at the instance level rather than inside a single database. The same role can access multiple databases, while permissions are granted within each database.
Users and groups are both implemented as roles, which is another shift in thinking for SQL Server DBAs.
9. Memory Works Very Differently
This may have been the biggest adjustment for me.
SQL Server generally wants to own as much memory as you let it.
PostgreSQL leans much more on the operating system and uses several memory settings that each serve different purposes.
Understanding settings like shared_buffers, work_mem, and effective_cache_size becomes much more important than I expected.
One thing that surprised me is that effective_cache_size isn’t memory PostgreSQL allocates. It’s an assumption to the query planner about how much cache is likely available.
10. The Default Memory Settings Are Tiny
A default PostgreSQL installation starts with shared_buffers set to just 128 MB.
I learned this during Jonathan Dunstan’s PostgreSQL memory session at the Utah SQL Meetup.
It reminded me of my neighbor growing up who’d hand me a dollar for my birthday and say, “Don’t spend it all in one place.”
The small default is intentional. PostgreSQL is built to run almost anywhere.
If you’re building a production PostgreSQL server yourself, you’ll almost certainly want to tune the memory settings.
Cloud platforms like Azure automatically optimize many of these values for you.
The Biggest Lesson
The biggest thing I’ve learned is that PostgreSQL isn’t trying to be SQL Server.
It has its own terminology, architecture, and philosophy. That’s what makes learning it both frustrating and fun.
I’ve learned that just because two database engines speak SQL doesn’t mean they think the same way.
If you’re going to support PostgreSQL in production, you have to stop assuming your SQL Server experience automatically applies. Some concepts transfer, but many don’t.
What surprised you when you started using PostgreSQL?
Leave a Reply