Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Paul Ramsey
Paul Ramsey
The end is nigh! PostgreSQL has substantially tightened restrictions on the use of the "public" schema. Here, a standard login user (not superuser) tries to make a table, as one does: NoooO! Why can I not write a table into public? For developers and experimenters, one of the long-time joys of PostgreSQL has been the free-and-easy security policy that PostgreSQL has shipped with for the "public" schema. • "public" is in the default , so you can always find things in it; and, • any user can cr...
Read MoreCraig Kerstiens
Craig Kerstiens
Today I'm excited to introduce a new place for devs to polish their Postgres skills, a Postgres Playground and Tutorials from Crunchy Data. What is the playground? Put simply it is: 1. Postgres running in your local web browser 2. With canned datasets you can load 3. Guided tutorials to follow along to learn about the power of Postgres Postgres running in your local web browser With canned datasets you can load Guided tutorials to follow along to learn about the power of Postgres Wait?!?!? Pos...
Read MorePaul Ramsey
Paul Ramsey
The PostgreSQL BRIN index is a specialized index for (as the documentation says) "handling very large tables in which certain columns have some natural correlation with their physical location within the table". For data of that sort, BRIN indexes offer extremely low insert costs (good for high velocity data) and extremely small index sizes (good for high volume data). But what data has this "natural correlation"? Most frequently, data with a timestamp that is continuously adding new rows. • A l...
Read MorePaul Ramsey
Paul Ramsey
It should be the easiest thing in the world: you are modeling your data and you need a column for some numbers, what type do you use? PostgreSQL offers a lot of different number types, and they all have advantages and limitations. You want the number type that is going to: • Store your data using the smallest amount of space • Represent your data with the smallest amount of error • Manipulate your data using the correct logic Store your data using the smallest amount of space Represent your da...
Read MoreElizabeth Christensen
Elizabeth Christensen
Logical replication has been around since Postgres 10 and it's just one of the most useful things out there. Need to get your data to a business analytics engine? Want to create an ETL pipeline from Postgres to your data warehouse? Want to test a new version of Postgres locally with your data? Want to consolidate a few databases into one? Logical replication can do all those things! It is a replication tool, which means it will copy some or all of your data to a new location and continue updatin...
Read MoreElizabeth Christensen
Elizabeth Christensen
One of the things that makes Postgres so awesome for software development is the incredibly useful system of constraints. Constraints are a way to tell Postgres which kinds of data can be inserted into tables, columns, or rows. As an application developer, you're going to build in this logic to your application as well and that’s great. However…adding this logic into your database protects your data long-term from bad data, null statements, or application code that isn't working quite right and...
Read MoreElizabeth Christensen
Elizabeth Christensen
If you’ve read Crunchy blogs recently you probably noticed by now that we’re all big fans of indexing. Indexing is key to optimizing your database workloads and reducing query times. Postgres now supports quite a few types of indexes and knowing the basics is a key part of working with Postgres. The role of database indexes is similar to the index section at the back of a book. A database index stores information on where a data row is located in a table so the database doesn't have to scan...
Read MoreStephen Andert
Stephen Andert
"Who is in charge of this database?" Everyone on the DBA team shook their head and someone asked, "Is it Oracle or SQL server?" "I think it is called My SQL," the development manager said. During my 20 years as a database administrator, that is often how I ended up learning new RDBMS systems. As a result, I know first-hand how challenging it can be to learn another system when you get thrown into the deep end. New databases are added to a production environment in different ways. A new software...
Read MoreKat Batuigas
Kat Batuigas
The idea of writing a database query that can then go out to an external source may not occur to someone who is not a DBA early on. That is: instead of figuring out how to grab then load multiple data sets into the same store, or configuring your application backend to connect to a bunch of disparate sources, why not use query JOINs like you usually would across tables within one database? In case you're not familiar, the dblink module in PostgreSQL, along with the concept of database links o...
Read MoreKat Batuigas
Kat Batuigas
Early in on my SQL journey, I thought that searching for a piece of text in the database mostly involved querying like this: Then I would throw in some wildcard operators or regular expressions if I wanted to get more specific. Later on, I worked with a client who wanted search functionality in an app, so and regex weren't going to cut it. What I had known all along was just pattern matching . It works perfectly fine for certain purposes, but what happens when it's not just a matter of checki...
Read More