Transaction IDs (XID) have been something of a thorn in Postgres’ side since the dawn of time. On one hand, they’re necessary to differentiate tuple visibility between past, present, and concurrent transactions. On the other hand, the counter that stores it is only 32-bits, meaning it’s possible to eventually overflow without some kind of intervention. […]
Postgresql Interval, Date, Timestamp and Time Data Types
/0 Comments/in Elein’s PlanetPostgreSQL /by EleinGenerated columns in PostgreSQL 12
/4 Comments/in 2ndQuadrant, Kirk’s PlanetPostgreSQL /by Kirk RoybalComputed columns in PostgreSQL 12 are here, but still have a ways to go.
Beautiful things, strings.
/0 Comments/in Elein’s PlanetPostgreSQL /by EleinPostgresql String Functions and Operators. This blog today is going to talk about strings: how they are stored, how they are input, and lots of examples of how to use string operators and functions in order to manipulate them. Strings, strings, strings. What we are not going to cover is regular expressions, although we […]
Swoop de Dupe
/11 Comments/in Elein’s PlanetPostgreSQL /by EleinThe problem: duplicate rows Once upon a time, there was a database person who knows about referential integrity and its importance for creating database tables. The interesting part is that referential integrity, including primary keys, enable keeping with the standard of not having duplicate rows. However, they just wanted a private little table on their […]
Postgres is the coolest database – Reason #4: It is extendable
/1 Comment/in Umair's PlanetPostgreSQL /by Umair ShahidPostgreSQL is packed with features. What may not be part of the core is available as extensions. What are extensions – you ask? PostgreSQL exposes APIs that are designed to easily allow external programs to load into the database and function just like core features. So if you find that you need a feature in […]
Using Docker Hub PostgreSQL images
/2 Comments/in Craig's PlanetPostgreSQL /by craig.ringerDocker Hub carries Docker images for PostgreSQL, based on Debian Stretch or Alpine Linux. These are not official PostgreSQL Development Group (PGDG) images from postgresql.org, they’re maintained in the Docker Library on Github. But as Docker adoption grows these are going to become more and more people’s first exposure to PostgreSQL. I tried these images […]
Postgres is the coolest database – Reason #3: No vendor lock-in
/2 Comments/in Umair's PlanetPostgreSQL /by Umair ShahidYou buy a cool new technology for your organization in order to cut operational costs. It works really well for you, and incrementally, your entire business starts to rely on this tech for its day to day operations. You have successfully made it an essential component of your business. There are some issues now and […]
Postgres is the coolest database – Reason #2: The License
/0 Comments/in PostgreSQL, Umair's PlanetPostgreSQL /by Umair ShahidLegal documents = SCARY!! That’s the typical equation, and it’s true – except when it comes to PostgreSQL. Let me explain… I have been told by both prospects and clients, that when they sit down to negotiate terms with Oracle, they are faced with more lawyers than they have engineers. No wonder one shudders at […]
PG Phriday: Around the World in Two Billion Transactions
/0 Comments/in Shaun's PlanetPostgreSQL /by Shaun ThomasTransaction IDs (XID) have been something of a thorn in Postgres’ side since the dawn of time. On one hand, they’re necessary to differentiate tuple visibility between past, present, and concurrent transactions. On the other hand, the counter that stores it is only 32-bits, meaning it’s possible to eventually overflow without some kind of intervention. […]
Optimizing storage of small tables in PostgreSQL 12
/0 Comments/in John’s PlanetPostgreSQL /by John NaylorThe problem If your database has a large number of small tables, you likely have a lot of wasted space. To demonstrate this, let’s create a table with a single record: create table foo (str text); insert into foo values (‘a’); VACUUM foo; Now let’s find out the path of the file containing our data, […]